SQL如何使用Lag/Lead窗口函数合并同值连续行的起止日期

现有表共4个字段：ID、STARTDATE、ENDDATE、BADGE，需实现行合并逻辑：仅合并ID、BADGE取值完全相同的连续排列行，合并后取组内最早的STARTDATE为开始日期、最晚的ENDDATE为结束日期。

• 输入示例：
  
• 期望输出示例：
  

已提交的窗口函数写法存在两处核心错误，导致结果不符合预期：

• 分区/判断维度遗漏BADGE字段，仅以ID作为分区依据，无法满足ID+BADGE双字段同时匹配才合并的规则
• 未正确生成连续同值行的唯一分组标识，直接取上一行STARTDATE的逻辑无法适配连续3行及以上同值的场景，会出现分组错位

核心逻辑：先通过窗口函数为每段连续的ID+BADGE同值行生成独立的分组标记，再基于该标记聚合即可。

通用SQL写法（适配绝大多数支持窗口函数的数据库：MySQL8.0+、PostgreSQL、SQL Server、Hive等） #

WITH temp_group AS (
    SELECT
        ID,
        BADGE,
        STARTDATE,
        ENDDATE,
        -- 生成连续分组标记：当前行BADGE和同ID下上一行不同时，标记值累加1
        SUM(
            CASE
                WHEN LAG(BADGE) OVER(PARTITION BY ID ORDER BY STARTDATE) = BADGE THEN 0
                ELSE 1
            END
        ) OVER(PARTITION BY ID ORDER BY STARTDATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS group_tag
    FROM myTable
)
SELECT
    ID,
    MIN(STARTDATE) AS STARTDATE,
    MAX(ENDDATE) AS ENDDATE,
    BADGE
FROM temp_group
GROUP BY ID, BADGE, group_tag
ORDER BY ID, STARTDATE;

简化写法（适配Snowflake、Databricks等支持CONDITIONAL_CHANGE_EVENT函数的引擎） #

SELECT
    ID,
    MIN(STARTDATE) AS STARTDATE,
    MAX(ENDDATE) AS ENDDATE,
    BADGE
FROM myTable
GROUP BY
    ID,
    BADGE,
    CONDITIONAL_CHANGE_EVENT(BADGE) OVER(PARTITION BY ID ORDER BY STARTDATE)
ORDER BY ID, STARTDATE;

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