SQL Workbench/J按90天规则合并考勤周期的实现方案求助

问题背景 #

在SQL Workbench/J中，需对存储人员考勤日期的表按以下规则划分考勤周期：

• 相邻考勤记录间隔≤90天，合并为同一周期
• 间隔＞90天，视为独立新周期

已通过lag()函数计算出相邻考勤的间隔天数，但因数据集较大，无法逐行分析完成周期划分，寻求高效方案。

原始数据表（示例） #

...（剩余数据略）

期望输出表 #

解决方案SQL #

利用窗口函数累加分组标记的方式，无需逐行分析，可高效处理大数据集：

WITH att_with_diff AS (
    SELECT 
        id, 
        start_date, 
        end_date,
        -- 计算与上一条记录的间隔天数
        LAG(start_date) OVER (PARTITION BY id ORDER BY start_date) AS prev_att_month,
        DATEDIFF(start_date, LAG(start_date) OVER (PARTITION BY id ORDER BY start_date)) AS diff
    FROM source
),
att_with_group AS (
    SELECT 
        id,
        start_date,
        end_date,
        -- 生成分组标记：间隔>90天或为第一条记录时，标记为新组起点
        CASE 
            WHEN prev_att_month IS NULL OR diff > 90 THEN 1 
            ELSE 0 
        END AS group_flag,
        -- 累加标记得到每个记录所属的组ID
        SUM(CASE 
            WHEN prev_att_month IS NULL OR diff > 90 THEN 1 
            ELSE 0 
        END) OVER (PARTITION BY id ORDER BY start_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cycle_group_id
    FROM att_with_diff
)
-- 按ID和组ID聚合，得到每个周期的起止日期
SELECT 
    id,
    MIN(start_date) AS start_date,
    MAX(end_date) AS end_date
FROM att_with_group
GROUP BY id, cycle_group_id
ORDER BY id, start_date;

代码说明 #

1. att_with_diff CTE：计算每条记录与上一条考勤记录的间隔天数，核心是LAG()窗口函数按人员ID分组、按考勤日期排序。
2. att_with_group CTE：通过CASE生成分组标记，再用SUM() OVER()累加标记，为连续的符合间隔要求的记录分配同一个组ID。
3. 最终聚合：按人员ID和组ID分组，取组内最早的start_date和最晚的end_date，即为一个完整的考勤周期。

这个方案完全基于SQL窗口函数实现，无需逐行遍历，能高效处理大规模数据集。

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