如何简化日历表中假期关联后续工作日的SQL查询？

问题描述 #

我有一个日历表mts_calendar，每条日期记录带有标识字段mts，若为假期则标识为'H'。我希望创建一个派生表，将每个假期日期关联至紧随其后的首个工作日。当前使用的SQL查询虽能实现需求，但执行成本较高，是否有简化该代码的方法？

现有SQL：

select a.data_oss hd, min(b.data_oss) next_wd
from mts_calendar a 
join mts_calendar b on b.mts != 'H' and b.data_oss > a.data_oss and b.data_oss < a.data_oss +10
where a.mts = 'H'
group by a.data_oss

优化方案 #

原查询通过自连接加分组聚合实现需求，但每个假期日期都要扫描后续10天的数据，执行效率低下。可以利用窗口函数优化，避免冗余的自连接和分组操作：

方法1：支持IGNORE NULLS的数据库（如PostgreSQL 11+、Oracle、SQL Server 2022+） #

直接跳过假期的NULL值，获取后续首个工作日，仅需一次全表扫描：

WITH calendar_with_next_wd AS (
    SELECT 
        data_oss,
        mts,
        LAST_VALUE(CASE WHEN mts != 'H' THEN data_oss END) OVER (
            ORDER BY data_oss 
            ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
            IGNORE NULLS
        ) AS next_wd
    FROM mts_calendar
)
SELECT 
    data_oss AS hd,
    next_wd
FROM calendar_with_next_wd
WHERE mts = 'H'

方法2：不支持IGNORE NULLS的数据库（如MySQL 8.0） #

通过反向遍历标记最近工作日，同样实现高效查询：

WITH reversed_calendar AS (
    SELECT 
        data_oss,
        mts,
        MAX(CASE WHEN mts != 'H' THEN data_oss END) OVER (ORDER BY data_oss DESC) AS next_wd
    FROM mts_calendar
)
SELECT 
    data_oss AS hd,
    next_wd
FROM reversed_calendar
WHERE mts = 'H'
ORDER BY data_oss

优化说明 #

原查询的自连接逻辑时间复杂度为O(n²)，窗口函数方案仅需一次线性扫描（O(n)），数据量越大效率提升越明显。另外，给data_oss字段建立索引，能进一步加快窗口函数的排序计算速度。

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