如何按时间顺序对连续同TYPE数据分组并计算MIN/MAX值？

问题说明 #

需要针对连续相同的TYPE数据按时间顺序分组，每组返回对应的TYPE、最小Start_dath和最大End_dath。原有的GROUP BY TYPE语句会合并所有同类型数据，无法区分非连续的同类型组，因此无法得到正确结果。

示例输入 #

期望输出 #

解决方案 #

这是典型的SQL连续相同分组（岛屿问题），可以通过窗口函数生成组ID来实现：

WITH ranked_data AS (
    SELECT 
        TYPE,
        Start_dath,
        End_dath,
        -- 计算组ID：连续同TYPE的行将拥有相同的group_id
        ROW_NUMBER() OVER (ORDER BY Start_dath) 
        - ROW_NUMBER() OVER (PARTITION BY TYPE ORDER BY Start_dath) AS group_id
    FROM your_table_name
)
SELECT 
    TYPE,
    MIN(Start_dath) AS Start_dath,
    MAX(End_dath) AS End_dath
FROM ranked_data
GROUP BY TYPE, group_id
ORDER BY MIN(Start_dath);

逻辑解释 #

1. 生成组ID：
   • 第一个ROW_NUMBER()按时间顺序给所有行全局编号
   • 第二个ROW_NUMBER()按TYPE分组后，给每组内的行按时间编号
   • 两者的差值对于连续的同TYPE行是固定值，以此作为分组标识
2. 分组聚合：按TYPE和group_id分组，取每组的最小开始时间和最大结束时间
3. 排序输出：按开始时间排序，保证结果顺序符合时间逻辑

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