如何在Redshift中用SQL按分组识别连续日期范围？

原始数据表 #

假设数据表名为event_table，数据如下：

GRP     EVENT_DATE
A       2023-03-19
A       2023-03-20
A       2023-03-21
A       2023-03-23
B       2023-03-25
B       2023-03-26

实现SQL #

可以利用窗口函数和日期计算识别连续日期段，以下是可在Redshift中运行的查询语句：

WITH date_groups AS (
    SELECT
        GRP,
        EVENT_DATE,
        -- 连续日期会生成相同的group_key，以此区分非连续段
        DATEADD(day, -ROW_NUMBER() OVER (PARTITION BY GRP ORDER BY EVENT_DATE), EVENT_DATE) AS group_key
    FROM event_table
)
SELECT
    GRP,
    MIN(EVENT_DATE) AS START_DATE,
    MAX(EVENT_DATE) AS END_DATE
FROM date_groups
GROUP BY GRP, group_key
ORDER BY GRP, START_DATE;

思路说明 #

1. 生成分组标识：按GRP分组后，用ROW_NUMBER()给每行日期排序，再通过DATEADD把日期减去对应的行号。连续日期经过计算后会得到同一个group_key，比如A组前三天计算后都是2023-03-18，2023-03-23则得到2023-03-20，以此区分开非连续的日期段。
2. 聚合起止日期：按GRP和group_key分组，取每组的最小日期作为起始、最大日期作为结束，就能得到每个连续日期段的范围。

期望输出 #

执行上述查询后，会得到如下结果：

GRP    START_DATE    END_DATE   
A      2023-03-19    2023-03-21
A      2023-03-23    2023-03-23
B      2023-03-25    2023-03-26

内容的提问来源于stack exchange，提问作者DJC