如何在SQL中用下一个非空值填充NULL（自定义周四为周起始）

问题背景 #

需要为2023年11月的日期表生成自定义周分组：每周从周四开始，周三结束。要求每个日期对应的WEEK_STARTING列显示所在周的周四日期，同时后续需要生成周结束列。尝试使用FIRST_VALUE函数填充NULL值未成功。

原代码问题分析 #

原代码通过CASE仅标记周四日期为WEEK_STARTING，其余为NULL，再尝试用FIRST_VALUE向后取非空值，但存在两个问题：

1. 逻辑方向错误：实际需要取当前日期之前最近的周四，而非之后的；
2. 部分数据库（如MySQL 8.0.22之前版本）不支持IGNORE NULLS语法，导致函数无法正确跳过NULL。

解决方案一：修复窗口函数实现 #

使用LAST_VALUE结合正确的窗口范围，实现NULL值填充为最近的上一个周四：

WITH RECURSIVE ALL_DATES_BETWEEN AS (
    SELECT '2023-11-01' AS ALL_DATES
    UNION ALL
    SELECT DATE_ADD(ALL_DATES, INTERVAL 1 DAY) FROM ALL_DATES_BETWEEN
    WHERE ALL_DATES < '2023-11-30'
),
GET_DATE AS (
SELECT *, 
    DAYNAME(ALL_DATES) AS WEEK_DAY_NAME,
    WEEK(ALL_DATES) AS WEEK_NUM,
    CASE WHEN DAYNAME(ALL_DATES)='Thursday' THEN ALL_DATES ELSE NULL END AS WEEK_STARTING_TMP
FROM ALL_DATES_BETWEEN)
SELECT 
    ALL_DATES,
    WEEK_DAY_NAME,
    LAST_VALUE(WEEK_STARTING_TMP IGNORE NULLS) OVER (ORDER BY ALL_DATES ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS WEEK_STARTING,
    DATE_ADD(LAST_VALUE(WEEK_STARTING_TMP IGNORE NULLS) OVER (ORDER BY ALL_DATES ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), INTERVAL 6 DAY) AS WEEK_ENDING
FROM GET_DATE
ORDER BY ALL_DATES;

关键调整： #

• 将原WEEK_NUMBER列名改为WEEK_DAY_NAME，避免与周编号混淆；
• 使用LAST_VALUE替代FIRST_VALUE，窗口范围设为UNBOUNDED PRECEDING AND CURRENT ROW，确保取到当前行之前最近的非空周四日期；
• 新增WEEK_ENDING列，通过周起始日期加6天得到周三。

解决方案二：直接日期计算（更高效） #

无需先标记NULL再填充，直接通过日期偏移计算所在周的周四，性能更优：

WITH RECURSIVE ALL_DATES_BETWEEN AS (
    SELECT '2023-11-01' AS ALL_DATES
    UNION ALL
    SELECT DATE_ADD(ALL_DATES, INTERVAL 1 DAY) FROM ALL_DATES_BETWEEN
    WHERE ALL_DATES < '2023-11-30'
)
SELECT 
    ALL_DATES,
    DAYNAME(ALL_DATES) AS WEEK_DAY_NAME,
    WEEK(ALL_DATES) AS WEEK_NUM,
    -- 计算所在周的周四（周起始）
    CASE
        WHEN DAYOFWEEK(ALL_DATES) = 5 THEN ALL_DATES -- 周四当天为起始
        WHEN DAYOFWEEK(ALL_DATES) > 5 THEN DATE_SUB(ALL_DATES, INTERVAL (DAYOFWEEK(ALL_DATES) - 5) DAY) -- 周五/周六，回退1/2天到周四
        ELSE DATE_SUB(ALL_DATES, INTERVAL (DAYOFWEEK(ALL_DATES) + 2) DAY) -- 周日-周三，回退3-6天到上周四
    END AS WEEK_STARTING,
    -- 计算所在周的周三（周结束）
    DATE_ADD(
        CASE
            WHEN DAYOFWEEK(ALL_DATES) = 5 THEN ALL_DATES
            WHEN DAYOFWEEK(ALL_DATES) > 5 THEN DATE_SUB(ALL_DATES, INTERVAL (DAYOFWEEK(ALL_DATES) - 5) DAY)
            ELSE DATE_SUB(ALL_DATES, INTERVAL (DAYOFWEEK(ALL_DATES) + 2) DAY)
        END, INTERVAL 6 DAY
    ) AS WEEK_ENDING
FROM ALL_DATES_BETWEEN
ORDER BY ALL_DATES;

说明： #

• 基于MySQL的DAYOFWEEK函数定义（1=周日，2=周一，...，5=周四）计算偏移量；
• 若使用其他数据库（如PostgreSQL），需根据对应星期函数的定义调整偏移逻辑，核心思路保持一致。

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