Azure SQL中基于3秒滚动时间戳的数据分组需求

针对按3秒滚动时间窗口为票务记录分组的需求，可通过窗口函数组合实现高效处理，以下是具体方案：

核心SQL代码 #

WITH ranked_data AS (
    SELECT 
        TICKET_ID,
        TICKET_ROW_NUMBER,
        ACTIVITY_ID,
        PERFORMED_AT,
        PREVIOUS_PERFORMED,
        -- 判断当前行与上一行时间差是否超3秒，超则标记为新组
        CASE 
            WHEN DATEDIFF(SECOND, LAG(PERFORMED_AT) OVER (PARTITION BY TICKET_ID ORDER BY PERFORMED_AT), PERFORMED_AT) > 3
            THEN 1
            ELSE 0
        END AS is_new_group
    FROM your_table_name
)
SELECT 
    TICKET_ID,
    TICKET_ROW_NUMBER,
    ACTIVITY_ID,
    PERFORMED_AT,
    PREVIOUS_PERFORMED,
    -- 累加新组标记生成连续组号
    SUM(is_new_group) OVER (PARTITION BY TICKET_ID ORDER BY PERFORMED_AT ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) + 1 AS GROUP_NUMBER
FROM ranked_data
ORDER BY TICKET_ID, TICKET_ROW_NUMBER;

逻辑说明 #

1. 分区排序：通过PARTITION BY TICKET_ID确保分组逻辑在单票务内生效，ORDER BY PERFORMED_AT保证记录按时间顺序处理。
2. 新组判断：用LAG(PERFORMED_AT)获取上一行时间戳，DATEDIFF(SECOND, ...)计算时间差，超过3秒则标记为1（新组），否则为0。
3. 生成组号：通过累加新组标记，从第一行到当前行的累加值加1，得到连续的GROUP_NUMBER（初始组为1）。

性能优化建议 #

针对数十万条记录的场景，建议创建复合索引减少全表扫描：

CREATE NONCLUSTERED INDEX IX_Ticket_PerformedAt ON your_table_name (TICKET_ID, PERFORMED_AT)
INCLUDE (TICKET_ROW_NUMBER, ACTIVITY_ID, PREVIOUS_PERFORMED);

结果验证 #

将代码应用到你的示例数据后，会生成与目标示例完全匹配的GROUP_NUMBER：

• 行1、2时间差0秒，同属组1
• 行3与行2差23秒>3秒，开启组2；行4与行3差3秒，归入组2
• 后续行按时间差规则依次生成组3、组4，完全符合需求

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