Postgres按非间隙对数据进行分组

Question

我正在尝试按没有间隔的日期对一些数据进行分组

实际上，我也在尝试将其构建为一个视图。

identifier start_date     end_date
4EF        2021-07-29     2021-08-05
4EF        2021-08-06     2021-08-09    
4EF        2021-10-07     2021-10-12    
4EF        2021-10-13     2021-11-12    
2AB        2021-01-01     2021-06-20    
2AB        2021-06-22     2021-12-01    

上述预期结果为：

identifier start_date     end_date
4EF        2021-07-29     2021-08-09
4EF        2021-10-07     2021-11-12    
2AB        2021-01-01     2021-06-20    
2AB        2021-06-22     2021-12-01 

最有效的方法是什么？

Answer 1

这是一个相当棘手的问题，因为它是一个缺口和孤岛问题，但您的初始数据集还远远没有缺口或孤岛的明确值。这里有一种方法：

WITH cte AS (
SELECT *, CASE WHEN start_date - LAG(end_date) OVER (PARTITION BY identifier
ORDER BY start_date) = 1
THEN 0 ELSE 1 END AS label
FROM yourTable
),
cte2 AS (
SELECT *, SUM(label) OVER (PARTITION BY identifier ORDER BY start_date) AS grp
FROM cte
)
SELECT
identifier,
MIN(start_date) AS start_date,
MAX(end_date) AS end_date
FROM cte2
GROUP BY
identifier,
grp
ORDER BY
identifier,
MIN(start_date);

演示

第一个CTE的逻辑是根据给定标识符的先前记录是否属于相同的连续日期范围来分配0或1。一旦我们分配了这些0和1，那么我们就可以在每个标识符中进行滚动求和，得出一个组号。这里的重点是，我们为每个标识符和连续日期生成一个伪组。