基于连续周数据生成衍生周起止日期的SQL实现需求

问题回顾 #

需要基于员工的周末日期记录，将连续周的记录归为一组，每组生成对应的起始/结束衍生日期：连续周（间隔7天）为同一组，间隔超7天则开启新分组，组内取最小日期作为衍生起始日期、最大日期作为衍生结束日期。

示例数据 #

SQL实现 #

核心思路是用窗口函数标记分组，再基于分组计算衍生日期：

WITH grouped_weeks AS (
    SELECT
        employee,
        week_end_date,
        -- 累加生成分组ID：当前记录与上一条间隔超7天时，分组ID+1
        SUM(CASE 
            WHEN DATEDIFF(day, LAG(week_end_date) OVER (PARTITION BY employee ORDER BY week_end_date), week_end_date) > 7 
            THEN 1 
            ELSE 0 
        END) OVER (PARTITION BY employee ORDER BY week_end_date) AS group_id
    FROM employee_weeks
)
SELECT
    employee,
    week_end_date,
    MIN(week_end_date) OVER (PARTITION BY employee, group_id) AS derived_start_week_end_date,
    MAX(week_end_date) OVER (PARTITION BY employee, group_id) AS derived_end_week_end_date
FROM grouped_weeks
ORDER BY employee, week_end_date;

逻辑说明 #

1. 标记分组：通过LAG函数获取当前员工的上一条周末日期，计算日期差；用SUM窗口累加，每次间隔超7天则分组ID递增，确保连续周被分到同一组。
2. 生成衍生字段：基于员工+分组ID的窗口，取组内最小/最大日期作为衍生起始/结束日期。

方言适配 #

• PostgreSQL：替换日期差判断为

  WHEN (week_end_date - LAG(week_end_date) OVER (PARTITION BY employee ORDER BY week_end_date)) > INTERVAL '7 days'
  

• Oracle：直接计算天数差

  WHEN (week_end_date - LAG(week_end_date) OVER (PARTITION BY employee ORDER BY week_end_date)) > 7
  

• SQL Server：保留原DATEDIFF写法即可

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