SQL Server：如何高效计算指定日期起第5个工作日的对应日期？

问题背景 #

需基于日期表，计算从指定日期起累计工作日达5天的对应结束日期。例如从2023-01-09起算，累计5个工作日对应的结束日期是2023-01-16，因为这两个日期之间的working_days列总和为5。

原SQL实现如下，但在1000行数据的业务表中执行耗时超1分钟，效率极低：

WITH dates AS
(
    SELECT t_from.start_date, t_to.start_date end_date
    FROM #t t_from, #t t_to
    WHERE t_from.start_date < t_to.start_date 
),
sum_days AS 
(
    SELECT
        start_date, end_date,
        (SELECT SUM(t_sum.working_days) 
         FROM #t t_sum 
         WHERE t_sum.start_date BETWEEN d.start_date AND d.end_date) tot_days
    FROM
        dates d
)
SELECT
    start_date, MAX(end_date) end_date
FROM
    sum_days
WHERE
    tot_days = 5
GROUP BY
    start_date

输入表格 #

期望输出 #

建表SQL #

drop table if exists #t;
GO

select '2023-01-09' start_date,1 working_days into #t;
GO

insert into #t values('2023-01-10',1) ;
go 

insert into #t values('2023-01-11',1);
insert into #t values('2023-01-12',1);
insert into #t values('2023-01-13',1);
insert into #t values('2023-01-14',0);
insert into #t values('2023-01-15',0);
insert into #t values('2023-01-16',0);
insert into #t values('2023-01-17',1);
insert into #t values('2023-01-18',1);
insert into #t values('2023-01-19',1);
insert into #t values('2023-01-20',1);
insert into #t values('2023-01-21',0);
insert into #t values('2023-01-22',0);
insert into #t values('2023-01-23',1);
insert into #t values('2023-01-24',1);
go

高效解决方案 #

原SQL效率低的核心原因是生成了全量日期笛卡尔积，且子查询重复计算累计和，时间复杂度为O(n²)。以下两种方案通过窗口函数将复杂度降至O(n)，大幅提升执行效率：

方案1：累计前缀和+关联查找 #

WITH cumulative_days AS (
    SELECT 
        start_date,
        working_days,
        SUM(working_days) OVER (ORDER BY start_date) AS total_working
    FROM #t
)
SELECT 
    c1.start_date,
    MIN(c2.start_date) AS end_date
FROM cumulative_days c1
JOIN cumulative_days c2 
    ON c2.total_working >= c1.total_working + 5 - c1.working_days
    AND c2.start_date >= c1.start_date
GROUP BY c1.start_date
ORDER BY c1.start_date;

逻辑说明：

1. 先计算从最早日期到当前日期的累计工作日total_working；
2. 对每个起始日期c1，找到第一个满足「累计工作日 ≥ 起始日累计值 + 5 - 起始日工作日」的日期c2——减去起始日工作日是为了确保起始日当天的工作日被计入累计，最终满足包含起始日在内累计满5天；
3. 取满足条件的最小c2.start_date作为结束日期。

方案2：SQL Server 2022+专属优化 #

如果使用SQL Server 2022及以上版本，可直接利用动态窗口范围简化查询：

WITH date_ranked AS (
    SELECT 
        start_date,
        working_days,
        SUM(working_days) OVER (ORDER BY start_date) AS running_total
    FROM #t
)
SELECT 
    start_date,
    (SELECT MIN(start_date) 
     FROM date_ranked dr2 
     WHERE dr2.running_total >= dr1.running_total + 5 - dr1.working_days) AS end_date
FROM date_ranked dr1
ORDER BY start_date;

额外性能优化建议 #

• 给#t表的start_date列创建聚集索引，窗口函数和关联查询会依赖索引排序，可大幅降低执行时间；
• 若数据量极大，可预先计算并存储累计工作日值，避免重复计算。

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