PostgreSQL：查询连续状态的开始日期与结束日期

这是典型的**间隙与岛屿（Gaps and Islands）**问题，用窗口函数组合就能解决，以下是针对你数据集的完整解法：

完整SQL代码 #

WITH status_groups AS (
    SELECT
        processed_on,
        status,
        -- 当前状态与前一行不同时标记为1，否则为0
        CASE WHEN LAG(status) OVER (ORDER BY processed_on) = status THEN 0 ELSE 1 END AS group_flag,
        -- 累加标记得到连续状态组的唯一ID
        SUM(CASE WHEN LAG(status) OVER (ORDER BY processed_on) = status THEN 0 ELSE 1 END) OVER (ORDER BY processed_on) AS group_id
    FROM your_table_name -- 替换成你的实际表名
)
SELECT
    MIN(processed_on) AS start_date,
    MAX(processed_on) AS end_date,
    status
FROM status_groups
GROUP BY group_id, status
ORDER BY start_date;

步骤拆解 #

1. 生成分组标记：用LAG(status) OVER (ORDER BY processed_on)取当前日期的前一行状态，和当前状态对比——如果不一样，说明是新的连续组开始，标记为1；相同则标记为0。
2. 生成分组ID：用SUM(...) OVER (ORDER BY processed_on)累加上面的标记，这样连续相同状态的行就会共享同一个group_id。
3. 分组聚合：按group_id和status分组，取每组的最小日期作为开始日期、最大日期作为结束日期，最后按开始日期排序。

测试结果 #

代入你提供的数据集执行后，会得到符合预期的结果（你期望输出里的023-01-07是笔误，实际结果会是2023-01-07）：

start_date | end_date   | status
-----------+------------+--------
2023-01-01 | 2023-01-03 | Success
2023-01-04 | 2023-01-05 | Fail
2023-01-06 | 2023-01-06 | Success
2023-01-07 | 2023-01-07 | Fail
2023-01-08 | 2023-01-09 | Success

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