SQL基于日期范围和day_one字段填充week_num的高效实现问题

实现思路 #

原有多lag函数的写法复杂度随周期长度线性上升，数据量、周期数大时性能极差。改用窗口分组+组内序号计算的方案，仅需一次全表扫描+窗口排序，时间复杂度稳定为O(n)，适配任意数据量和周期长度。

基础需求实现（周数递增） #

基础需求要求从周期首日开始，每7天对应一个递增的周数，实现代码如下：

WITH t_grp AS (
    SELECT 
        emp_id,
        date,
        day_one,
        -- 按员工分组，遇到周期首日就新增分组，同一分组内为同一个连续周期段
        SUM(CASE WHEN day_one = TRUE THEN 1 ELSE 0 END) OVER(PARTITION BY emp_id ORDER BY date) AS grp_id,
        -- 分组内按日期排序生成行号，从1开始
        ROW_NUMBER() OVER(PARTITION BY emp_id, grp_id ORDER BY date) AS rn
    FROM table1
)
SELECT 
    emp_id,
    date AS dates,
    day_one,
    -- 若SQL引擎不支持CEIL函数，可替换为 FLOOR((rn - 1)/7) + 1，计算结果完全一致
    CEIL(rn / 7) AS week_num
FROM t_grp
ORDER BY emp_id, date;

进阶需求实现（周数1、2循环） #

进阶需求要求每14天为一个大周期，前7天周数为1，后7天周数为2循环，仅需调整周数计算逻辑即可：

WITH t_grp AS (
    SELECT 
        emp_id,
        date,
        day_one,
        SUM(CASE WHEN day_one = TRUE THEN 1 ELSE 0 END) OVER(PARTITION BY emp_id ORDER BY date) AS grp_id,
        ROW_NUMBER() OVER(PARTITION BY emp_id, grp_id ORDER BY date) AS rn
    FROM table1
)
SELECT 
    emp_id,
    date AS dates,
    day_one,
    -- 先按7天分组得到递增周数，对2取模后+1即可实现1、2循环
    (CEIL(rn / 7) - 1) % 2 + 1 AS week_num
FROM t_grp
ORDER BY emp_id, date;

方案说明 #

• 兼容多员工场景：所有窗口函数均按emp_id分区，不同员工的周期计算完全独立
• 兼容中间插入新周期首日的场景：如果后续有新的day_one=TRUE的记录，会自动重新从第1周开始计数
• 性能优异：无自关联、无多重lag计算，即使千万级数据也能快速跑完

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