1

I am trying to group data on homogenous periods of time (not sure this is clear). Here is a simplified example of my data:

CREATE TABLE workhours_over_time (
             worker varchar,
             workhours integer,
             otherinfotobeignored varchar,
             startofperiod date,
             endofperiod date);

INSERT INTO workhours_over_time (worker, workhours, 
            otherinfotobeignored, startofperiod, endofperiod)
VALUES ('W1', '35', 'any info', '2020-01-01','2020-01-31');

INSERT INTO workhours_over_time (worker, workhours, 
            otherinfotobeignored, startofperiod, endofperiod)
VALUES ('W1', '35', 'any other info', '2020-02-01','2020-02-28');

INSERT INTO workhours_over_time (worker, workhours,
            otherinfotobeignored, startofperiod, endofperiod)
VALUES ('W1', '39', 'any info', '2020-03-01','2020-04-15');

INSERT INTO workhours_over_time (worker, workhours,
            otherinfotobeignored, startofperiod, endofperiod)
VALUES ('W1', '35', 'any info', '2020-04-16','2111-11-11');

So here is the result:

worker|workhours|otherinfotobeignored|startofperiod|endofperiod|
------|---------|--------------------|-------------|-----------|
W1    |       35|any info            |   2020-01-01| 2020-01-31|
W1    |       35|any other info      |   2020-02-01| 2020-02-28|
W1    |       39|any info            |   2020-03-01| 2020-04-15|
W1    |       35|any info            |   2020-04-16| 2111-11-11|

The result I need to obtain would group data in 3 periods: from 2020-01-01 to 2020-02-28, worker W1 works 35 hours a week. Then from 2020-03-01 to 2020-04-15 W1 works 39 hours a week. Then from 2020-04-16 to end of time, back to 35 hours.

worker|workhours|startofperiod|endofperiod|
------|---------|-------------|-----------|
W1    |       35|   2020-01-01| 2020-02-28|
W1    |       39|   2020-03-01| 2020-04-15|
W1    |       35|   2020-04-16| 2111-11-11|

I had first naive try with a simple group by:

select worker, workhours, min(startofperiod), max(endofperiod)
from workhours_over_time
group by worker, workhours

but of course, first and third period where grouped into a 2020-01-01 to 2111-11-11 period, which is not what I want.

Then I have toyed with window function, read on groups and island, but didn't find the solution...

Any idea ?

0

2 Answers 2

1
testdb=# create table t(k text, h integer, s date, e date);
CREATE TABLE
testdb=# insert into t select 'w1', 35, '2020-01-01', '2020-01-31';
INSERT 0 1
testdb=# insert into t select 'w1', 35, '2020-02-01', '2020-02-28';
INSERT 0 1
testdb=# insert into t select 'w1', 39, '2020-03-01', '2020-04-15';
INSERT 0 1
testdb=# insert into t select 'w1', 35, '2020-04-16', '2021-12-31';
INSERT 0 1
testdb=# select * from t;
 k  | h  |     s      |     e      
----+----+------------+------------
 w1 | 35 | 2020-01-01 | 2020-01-31
 w1 | 35 | 2020-02-01 | 2020-02-28
 w1 | 39 | 2020-03-01 | 2020-04-15
 w1 | 35 | 2020-04-16 | 2021-12-31
(4 rows)

testdb=# select row_number() over (partition by k order by s) r1,
                row_number() over (partition by k, h order by s) r2,
                t.* from t;
 r1 | r2 | k  | h  |     s      |     e      
----+----+----+----+------------+------------
  1 |  1 | w1 | 35 | 2020-01-01 | 2020-01-31
  2 |  2 | w1 | 35 | 2020-02-01 | 2020-02-28
  3 |  1 | w1 | 39 | 2020-03-01 | 2020-04-15
  4 |  3 | w1 | 35 | 2020-04-16 | 2021-12-31

testdb=# select (h, row_number() over (partition by k order by s) -
    row_number() over (partition by k, h order by s)) as group_key, t.* from t;
 group_key | k  | h  |     s      |     e      
-----------+----+----+------------+------------
 (35,0)    | w1 | 35 | 2020-01-01 | 2020-01-31
 (35,0)    | w1 | 35 | 2020-02-01 | 2020-02-28
 (39,2)    | w1 | 39 | 2020-03-01 | 2020-04-15
 (35,1)    | w1 | 35 | 2020-04-16 | 2021-12-31
(4 rows)

testdb=# with q0 as (
    select (h, row_number() over (partition by k order by s)
              -row_number() over (partition by k, h order by s)
              ) AS group_key,
           t.* from t)
select k, h,
       min(s) as s,
       max(e) as e
from q0 group by group_key, k, h order by min(s);
 k  | h  |     s      |     e
----+----+------------+------------
 w1 | 35 | 2020-01-01 | 2020-02-28
 w1 | 39 | 2020-03-01 | 2020-04-15
 w1 | 35 | 2020-04-16 | 2021-12-31

Why does this work? In the first select with row_number, partitioning by by both k (the worker column in your question, just shortened for simplicity/brevity) and h gives independently-increasing sequences for each h value for each worker. You then just subtract the worker's overall row number sequence. Consecutive rows whose h values are the same will have both sequences increasing by 1, so subtracting non-hours-worked-scoped row number will give the same value for both rows. Using both h and that subtracted value gives us a key that identifies each contiguous set of rows for each hours-worked for each worker; that's the group_key column in the next query.

At that point, it's a simple GROUP BY of group_key, and min/max the start and end. And that's it.

3
  • Many thanks for the solution and the step by step explaination. Notice I'll also will have to handle the case of gaps between periods, so I will probably need to mix you solution with the lag solution of the other answer by bbaird.
    – alci
    Jul 18, 2020 at 6:10
  • Just to be complete: I added "e-lead(s) over (partition by k order by s)" to the group_key, to handle the case of gaps betwwen periods. Many thanks to both of you AdmaKG and @bbaird
    – alci
    Jul 18, 2020 at 15:15
  • Actually gaps was a bit more complicated. I ended up with: "with q0 as ( select (sum(start_of_period) over (order by s), rn - rn2, h) as group_key, * from ( select case when s - interval '1' day = lag(e) over (partition by k order by s) then 0 else 1 end as start_of_period, row_number() over (partition by k order by s) rn, row_number() over (partition by k, h order by s) rn2, t.* from t order by e) q) select k, h, min(s) as s, max(e) as e from q0 group by group_key, k, h order by min(s);"
    – alci
    Jul 21, 2020 at 13:54
2

This is probably the most straightforward way to do this (edited to handle gaps):

SELECT
  Worker
 ,WorkHours
 ,startofperiod
 ,LEAD(priorend,1,'2111-11-11') OVER (Partition By Worker ORDER BY startofperiod) AS endofperiod
FROM
  (
    SELECT
      Worker
     ,startofperiod
     ,endofperiod
     ,LAG(endofperiod) OVER (PARTITION BY Worker ORDER BY startofperiod) AS priorend --Get prior end date in case of gap
     ,WorkHours
     ,CASE
        WHEN WorkHours <> LAG(WorkHours,1,-1) OVER (PARTITION BY Worker ORDER BY startofperiod) THEN 'Y'  --If hours change
        WHEN LAG(endofperiod + Interval '1 DAY') OVER (PARTITION BY Worker ORDER BY startofperiod) <> startofperiod THEN 'Y'  --If gap
        ELSE 'N'
      END AS Change_Ind
    FROM
      workhours_over_time
  ) summary
WHERE
  Change_Ind = 'Y'

Returns:

Worker  |   WorkHours   |   startofperiod   |   endofperiod
W1      |   35          |   2020-01-01      |   2020-02-29
W1      |   39          |   2020-03-01      |   2020-04-15
W1      |   35          |   2020-04-16      |   2111-11-11

This will handle gaps as long as your intervals are correct (in your example they are not as 2020 is a leap year). You could change the logic to LAG(endofperiod + Interval '2 DAYS') OVER (PARTITION BY Worker ORDER BY startofperiod) < startofperiod or just fix the end dates.

2
  • Great, thanks. But you're right, there might be gaps between periods... I'll have to handle that.
    – alci
    Jul 18, 2020 at 6:07
  • Turns out it wasn't that much work. Do note this solution does not require temp tables or reading the data more than once (it will require at most two sorts) and can easily be turned into a view for reuse.
    – user212533
    Jul 18, 2020 at 15:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.