如何为连续日期生成唯一ID？SQL查询优化需求

源表数据 #

尝试的查询语句 #

Select snapshot_date, channel,case_id
,case_id||channel||Dateadd('day', -(row_number() over (partition by case_id, channel order by snapshot_date)), snapshot_date+1) as ID
From test

当前输出结果 #

期望输出结果 #

解决方案 #

原查询的问题在于，row_number()是对整个case_id+channel分组排序，没有识别日期的连续性，导致日期中断后的组基准日期计算错误。正确逻辑是先标记连续日期组，再用每组的起始日期构造ID：

WITH grouped_data AS (
    SELECT 
        snapshot_date,
        channel,
        case_id,
        -- 标记连续日期组：当前日期与前一天不连续时，生成新组
        SUM(CASE WHEN DATEADD('day', 1, LAG(snapshot_date) OVER (PARTITION BY case_id, channel ORDER BY snapshot_date)) = snapshot_date THEN 0 ELSE 1 END) 
        OVER (PARTITION BY case_id, channel ORDER BY snapshot_date) AS group_id
    FROM test
),
group_start_dates AS (
    SELECT 
        case_id,
        channel,
        group_id,
        MIN(snapshot_date) AS start_date
    FROM grouped_data
    GROUP BY case_id, channel, group_id
)
SELECT 
    gd.snapshot_date,
    gd.channel,
    gd.case_id,
    gd.case_id || gd.channel || gsd.start_date AS ID
FROM grouped_data gd
JOIN group_start_dates gsd 
    ON gd.case_id = gsd.case_id 
    AND gd.channel = gsd.channel 
    AND gd.group_id = gsd.group_id
ORDER BY gd.case_id, gd.channel, gd.snapshot_date;

逻辑说明 #

1. 标记连续组：用LAG函数获取上一行日期，判断当前日期是否为上一行的次日，非次日则标记为新组，累加生成group_id。
2. 获取组起始日期：按case_id+channel+group_id分组，取每组最小日期作为连续组的起始日期。
3. 构造唯一ID：将case_id、channel与组起始日期拼接，确保同一连续日期组ID一致，不同组ID唯一。

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