SQL如何在lag日期差值超30天时重置row_num行序号

实现逻辑 #

• 首先通过条件聚合窗口函数生成分组标识：每次遇到lag字段值大于30时，分组标识累加1，将间隔小于等于30天的连续行归为同一分组
• 再对每个分组内的行按原排序规则重新生成行号，即可得到重置后的row_num

适用范围 #

支持标准SQL窗口函数的数据库均可使用，包括MySQL 8.0+、PostgreSQL、Spark SQL、Hive、SQL Server等。

完整实现代码 #

WITH group_calculation AS (
    SELECT
        id,
        time,
        lag,
        -- 生成分组标识，按id分区、原全局行号排序
        SUM(CASE WHEN lag > 30 THEN 1 ELSE 0 END) OVER (PARTITION BY id ORDER BY row_num) AS group_id
    FROM 你的表名 -- 替换为实际的表名
)
SELECT
    id,
    time,
    lag,
    -- 分组内重新生成连续行号
    ROW_NUMBER() OVER (PARTITION BY id, group_id ORDER BY row_num) AS row_num
FROM group_calculation;

效果验证 #

针对你提供的样例数据，该SQL运行结果与你期望的输出完全一致：前4行间隔均小于30天，行号连续为1-4；第5行间隔为43天超过阈值，触发分组重置，后续行号重新从1开始计数。

注意事项 #

• 代码中的你的表名需要替换为实际使用的表名
• 如果业务不需要按id分组计算，可删除两处窗口函数中的PARTITION BY id配置

内容的提问来源于stack exchange，提问作者Chiến Lê