如何用SQL合并连续相同状态的时间区间工作记录？

问题描述 #

现有一张按日期记录工作状态的表，原始数据如下：

需要将连续相同状态的记录合并为一个区间，预期结果：

直接用MAX()/MIN()聚合会错误合并非连续的相同状态记录（比如把前后两段In progress合并成跨多天的区间），不符合需求。

解决方案 #

这是典型的**间隙与孤岛（Gaps and Islands）**问题，需先识别连续相同状态的记录组，再对每组聚合日期区间。以下是通用实现：

方法：窗口函数标记连续分组（适用于MySQL 8.0+、PostgreSQL、SQL Server等） #

WITH grouped_data AS (
    SELECT 
        `date from`,
        `date to`,
        Status,
        -- 状态与上一行不同时，组号+1，标记连续状态组
        SUM(CASE WHEN Status = LAG(Status) OVER (ORDER BY `date from`) THEN 0 ELSE 1 END) 
        OVER (ORDER BY `date from`) AS group_id
    FROM your_table_name
)
SELECT
    MIN(`date from`) AS `date from`,
    MAX(`date to`) AS `date to`,
    Status
FROM grouped_data
GROUP BY group_id, Status
ORDER BY `date from`;

原理说明 #

1. LAG()窗口函数：获取当前行的上一行状态，对比当前行状态，状态不同则标记为新组起始。
2. SUM() OVER()：累加标记值生成唯一group_id，相同连续状态的记录会被分到同一组。
3. 分组聚合：按group_id和Status分组，取每组最小的起始日期和最大的结束日期，得到合并后的区间。

MySQL 5.x兼容方案（无窗口函数时用变量模拟） #

SELECT
    MIN(`date from`) AS `date from`,
    MAX(`date to`) AS `date to`,
    Status
FROM (
    SELECT 
        `date from`,
        `date to`,
        Status,
        @group_id := IF(@prev_status = Status, @group_id, @group_id + 1) AS group_id,
        @prev_status := Status
    FROM your_table_name, (SELECT @group_id := 0, @prev_status := '') AS vars
    ORDER BY `date from`
) AS grouped_data
GROUP BY group_id, Status
ORDER BY `date from`;

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