如何用SQL将日期区间对应NAME匹配填充至每日记录

问题分析 #

你需要把表中部分行的NAME，填充到其START_DATE至END_DATE范围内的所有DATES行，非区间内日期的NAME留空。之前用count(END_Date) over (ORDER BY DATE)分组再取first_value的思路失败，原因是这种分组逻辑仅按END_DATE的出现次数划分组，无法关联到对应区间的起始点START_DATE，导致区间内的日期被分到错误的组，自然无法正确填充NAME。

可行解决方案 #

最直接可靠的方法是先提取所有有效区间数据，再将日期与区间做关联匹配，适合绝大多数数据库：

SELECT
    t.DATES,
    -- 若存在重叠区间，可根据需求替换为MAX/MIN等聚合函数
    MAX(interval_data.NAME) AS NAME
FROM
    表A t
LEFT JOIN (
    -- 筛选出所有带有有效区间和NAME的行
    SELECT
        START_DATE,
        END_DATE,
        NAME
    FROM 表A
    WHERE START_DATE IS NOT NULL 
      AND END_DATE IS NOT NULL 
      AND NAME IS NOT NULL
) interval_data
    ON t.DATES BETWEEN interval_data.START_DATE AND interval_data.END_DATE
GROUP BY t.DATES
ORDER BY t.DATES;

逻辑说明 #

1. 子查询interval_data先过滤出所有START_DATE、END_DATE、NAME都非空的有效区间行；
2. 主查询将原表的每个DATES与这些区间做左连接，判断日期是否落在某个区间内；
3. 用MAX聚合函数（无重叠区间时等价于直接取值）得到对应日期的NAME，不在任何区间的日期会返回NULL。

适配不同数据库的窗口函数方案（需支持IGNORE NULLS） #

如果你的数据库（如Oracle、PostgreSQL 11+）支持IGNORE NULLS，也可以用窗口函数实现：

SELECT
    DATES,
    CASE
        -- 判断当前日期是否在最近的有效区间内
        WHEN DATES BETWEEN 
            LAST_VALUE(START_DATE IGNORE NULLS) OVER (ORDER BY DATES) 
            AND LAST_VALUE(END_DATE IGNORE NULLS) OVER (ORDER BY DATES)
        THEN LAST_VALUE(NAME IGNORE NULLS) OVER (ORDER BY DATES)
        ELSE NULL
    END AS NAME
FROM 表A
ORDER BY DATES;

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