分組按日期休息

我正在嘗試對同質時間段的數據進行分組（不確定這是否清楚）。這是我的數據的簡化範例：

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');

結果如下：

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|

我需要獲得的結果將數據分組為 3 個時段：從 2020 年 1 月 1 日到 2020 年 2 月 28 日，工人 W1 每週工作 35 小時。然後從 2020-03-01 到 2020-04-15 W1 每週工作 39 小時。然後從 2020-04-16 到時間結束，回到 35 小時。

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|

我第一次天真地嘗試了一個簡單的小組：

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

但當然，第一期和第三期分為 2020-01-01 到 2111-11-11 期間，這不是我想要的。

然後我玩弄了視窗功能，閱讀了群組和島嶼，但沒有找到解決方案……

任何的想法 ？

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

為什麼這行得通？在使用 row_number 的第一個選擇中，按 k（您的問題中的工作列，為簡單/簡潔而縮短）和 h 為每個工作人員的每個 h 值提供獨立增加的序列。然後，您只需減去工人的整體行號序列。h 值相同的連續行將使兩個序列都增加 1，因此減去非工作時間範圍的行號將為兩行提供相同的值。同時使用 h 和減去的值為我們提供了一個鍵，該鍵標識每個工人的每個工作小時的每個連續行集；這是下一個查詢中的 group_key 列。

那時，它是 group_key 的簡單 GROUP BY，並且 min/max 是開始和結束。就是這樣。

引用自：https://dba.stackexchange.com/questions/271155