SQL按时间线分组获取连续子组起止时间解决方案咨询

你需要解决的是**间隙与岛屿（Gap and Island）**问题：将同一shift_date、associate_id、name下，连续出现的相同description记录合并，取该连续组的最小START_TRAN_DATE和最大END_TRAN_DATE。普通GROUP BY会将所有相同description的记录合并，无法区分非连续的分组，因此需要用行号差值法来实现。

现有查询（无法满足需求） #

select shift_date,associate_id,name,description , min(START_TRAN_DATE) as startdate, max(end_tran_date) as end_date
from ltu_vt 
group by shift_date,associate_id,name,description

现有数据 #

期望输出 #

解决方案SQL #

WITH ranked_data AS (
    SELECT 
        shift_date,
        associate_id,
        name,
        description,
        START_TRAN_DATE,
        END_TRAN_DATE,
        -- 按人员+日期分组，按交易开始时间排序的全局行号
        ROW_NUMBER() OVER (PARTITION BY shift_date, associate_id, name ORDER BY START_TRAN_DATE) AS global_row,
        -- 按人员+日期+描述分组，按交易开始时间排序的分组行号
        ROW_NUMBER() OVER (PARTITION BY shift_date, associate_id, name, description ORDER BY START_TRAN_DATE) AS desc_row
    FROM ltu_vt
)
SELECT 
    shift_date,
    associate_id AS ID,
    name,
    description AS DESC,
    MIN(START_TRAN_DATE) AS START_TRAN_DATE,
    MAX(END_TRAN_DATE) AS END_TRAN_DATE
FROM ranked_data
GROUP BY 
    shift_date,
    associate_id,
    name,
    description,
    (global_row - desc_row)  -- 差值相同即为连续的同一描述组
ORDER BY 
    MIN(START_TRAN_DATE);

原理说明 #

• 第一步生成两个行号：global_row是同一人员、同一日期下按交易时间排序的全局序号；desc_row是同一人员、日期、描述下的分组序号。
• 当连续出现相同描述时，两个行号同步递增，差值保持不变；当描述切换时，desc_row会重置为1，差值发生变化，以此区分不同的连续分组。
• 最后按差值分组，即可合并连续的相同描述记录，取对应时间范围。

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