SQL Server 2019：优化指定日期回溯1200天的服务日期查询

需求背景 #

• 临时表#input：存储ID和计算日期cdate
• 临时表#service：存储每个ID的服务时段，countable=1表示该时段日期需计入统计
• 目标：对每个ID+cdate组合，生成从cdate向前回溯的最多1200条日期记录，仅保留落在countable=1服务时段内的日期

原方案性能瓶颈 #

原方案通过日历表全量关联#service和#input，再用ROW_NUMBER()筛选前1200条。但每个ID的服务记录常回溯数十年，导致CTE中间结果集异常庞大，处理数千ID时性能急剧下降。

优化方案 #

方案1：递归CTE（逐段回溯，满足条件即终止） #

利用递归CTE从cdate开始，反向遍历每个ID的countable=1服务时段，计算每个时段能贡献的有效天数，直到累计天数达到1200或无更早服务时段为止，最后生成对应日期。

DROP TABLE IF EXISTS #input, #service, #result;
GO

-- 创建测试表
CREATE TABLE #input (id int, cdate date);
INSERT INTO #input VALUES 
(1,'2022-03-31'), (2, '2023-02-26'), (3, '2023-04-01');

CREATE TABLE #service (id int, start_date date, end_date date, countable bit);
INSERT INTO #service VALUES
(1,'1978-11-15','2008-07-30',1),
(1,'2008-07-31','2008-07-31',0),
(1,'2008-08-01','2008-08-19',1),
(1,'2008-08-20','2008-08-20',0),
(1,'2008-08-21','2011-06-29',1),
(1,'2011-06-30','2011-06-30',0),
(1,'2011-07-01','2012-08-29',1),
(1,'2012-08-30','2012-11-30',0),
(1,'2012-12-01','2013-03-19',1),
(1,'2013-03-20','2013-03-20',0),
(1,'2013-03-21','2013-05-12',1),
(1,'2013-05-13','2013-05-14',0),
(1,'2013-05-15','2014-07-09',1),
(1,'2014-07-10','2014-07-10',0),
(1,'2014-07-11','2015-03-31',1),
(1,'2015-04-01','2017-07-16',1),
(1,'2017-07-17','2017-07-30',0),
(1,'2017-07-31','2018-07-15',1),
(1,'2018-07-16','2018-07-29',0),
(1,'2018-07-30','2019-07-28',1),
(1,'2019-07-29','2019-08-11',0),
(1,'2019-08-12','2020-08-02',1),
(1,'2020-08-03','2020-08-16',0),
(1,'2020-08-17','2020-10-31',1),
(1,'2020-11-01','2021-07-04',1),
(1,'2021-07-05','2021-07-18',0),
(1,'2021-07-19','2022-07-10',1),
(1,'2022-07-11','2022-07-24',0),
(1,'2022-07-25','2023-01-31',1),
(1,'2023-02-01','2023-02-01',0),
(1,'2023-02-02','2023-04-27',1),
(1,'2023-04-28','2023-04-28',0),
(1,'2023-04-29','2999-12-31',1),
(2,'1984-01-04','2004-01-28',1),
(2,'2004-01-29','2004-01-30',0),
(2,'2004-01-31','2004-11-30',1),
(2,'2004-12-01','2006-07-31',1),
(2,'2006-08-01','2011-06-29',1),
(2,'2011-06-30','2011-06-30',0),
(2,'2011-07-01','2011-11-29',1),
(2,'2011-11-30','2011-11-30',0),
(2,'2011-12-01','2013-03-19',1),
(2,'2013-03-20','2013-03-20',0),
(2,'2013-03-21','2013-06-06',1),
(2,'2013-06-07','2013-06-07',0),
(2,'2013-06-08','2014-07-09',1),
(2,'2014-07-10','2014-07-10',0),
(2,'2014-07-11','2014-10-14',1),
(2,'2014-10-15','2014-10-15',0),
(2,'2014-10-16','2015-11-30',1),
(2,'2015-12-01','2017-01-30',1),
(2,'2017-01-31','2017-01-31',1),
(2,'2017-02-01','2022-10-01',1),
(2,'2022-10-02','2022-11-08',1),
(2,'2022-11-09','2023-02-26',0),
(3,'2022-01-15','2022-04-30',1),
(3,'2022-05-01','2022-05-31',0),
(3,'2022-06-01','2999-12-31',1);

-- 递归CTE实现
WITH service_ranked AS (
    -- 为每个ID的可统计服务时段按结束日期倒序排序
    SELECT 
        id, 
        start_date, 
        end_date,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY end_date DESC) AS rn
    FROM #service
    WHERE countable = 1
),
recursive_dates AS (
    -- 初始节点：从每个ID的cdate开始，找到第一个包含cdate的可统计时段
    SELECT 
        i.id,
        i.cdate,
        CASE WHEN s.start_date > i.cdate THEN NULL ELSE s.start_date END AS period_start,
        CASE WHEN s.end_date < i.cdate THEN s.end_date ELSE i.cdate END AS period_end,
        CASE 
            WHEN s.start_date > i.cdate THEN 0
            ELSE DATEDIFF(day, s.start_date, CASE WHEN s.end_date < i.cdate THEN s.end_date ELSE i.cdate END) + 1
        END AS days_in_period,
        1200 AS remaining_days,
        s.rn AS current_rn
    FROM #input i
    LEFT JOIN service_ranked s ON i.id = s.id AND i.cdate BETWEEN s.start_date AND s.end_date

    UNION ALL

    -- 递归节点：继续回溯上一个可统计时段，直到剩余需取天数为0或无更多时段
    SELECT 
        rd.id,
        rd.cdate,
        s.start_date,
        s.end_date,
        CASE 
            WHEN DATEDIFF(day, s.start_date, s.end_date) + 1 >= rd.remaining_days THEN rd.remaining_days
            ELSE DATEDIFF(day, s.start_date, s.end_date) + 1
        END AS days_in_period,
        CASE 
            WHEN DATEDIFF(day, s.start_date, s.end_date) + 1 >= rd.remaining_days THEN 0
            ELSE rd.remaining_days - (DATEDIFF(day, s.start_date, s.end_date) + 1)
        END AS remaining_days,
        s.rn AS current_rn
    FROM recursive_dates rd
    JOIN service_ranked s ON rd.id = s.id AND s.rn = rd.current_rn + 1
    WHERE rd.remaining_days > 0 AND rd.period_start IS NOT NULL
)
-- 生成最终日期记录
SELECT 
    ROW_NUMBER() OVER (PARTITION BY rd.id, rd.cdate ORDER BY d.date DESC) AS rn,
    rd.id,
    rd.cdate,
    d.date
INTO #result
FROM recursive_dates rd
-- 用系统数字表生成时段内的日期（无需预创建日历表）
CROSS APPLY (
    SELECT DATEADD(day, -n.number, rd.period_end) AS date
    FROM master..spt_values n
    WHERE n.type = 'P' 
        AND n.number BETWEEN 0 AND rd.days_in_period - 1
        AND DATEADD(day, -n.number, rd.period_end) >= rd.period_start
) d
WHERE rd.days_in_period > 0
ORDER BY rd.id, rd.cdate, rn;

-- 查看结果
SELECT * FROM #result;

方案2：批量计算时段贡献（减少逐行生成） #

先对每个ID的可统计时段按时间倒序排列，计算每个时段能贡献的有效天数，累加直到达到1200天，再生成对应日期。此方法避免了递归，适合对递归性能敏感的场景。

DROP TABLE IF EXISTS #input, #service, #result;
GO

-- 创建测试表（同方案1）
CREATE TABLE #input (id int, cdate date);
INSERT INTO #input VALUES 
(1,'2022-03-31'), (2, '2023-02-26'), (3, '2023-04-01');

CREATE TABLE #service (id int, start_date date, end_date date, countable bit);
INSERT INTO #service VALUES
-- 同方案1的插入数据，此处省略

-- 预处理可统计时段，按ID分组倒序排列
WITH service_sorted AS (
    SELECT 
        id,
        start_date,
        end_date,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY end_date DESC) AS seq
    FROM #service
    WHERE countable = 1
),
-- 计算每个时段的累计贡献天数
period_contribution AS (
    SELECT 
        i.id,
        i.cdate,
        s.start_date,
        -- 时段的有效结束日期：首次时段取cdate和时段end_date的较小值
        CASE WHEN s.seq = 1 THEN CASE WHEN s.end_date > i.cdate THEN i.cdate ELSE s.end_date END ELSE s.end_date END AS period_end,
        s.seq,
        -- 计算时段的有效天数
        CASE WHEN s.seq = 1 THEN 
            CASE WHEN s.start_date > i.cdate THEN 0 ELSE DATEDIFF(day, s.start_date, CASE WHEN s.end_date > i.cdate THEN i.cdate ELSE s.end_date END) + 1 
        END
        ELSE DATEDIFF(day, s.start_date, s.end_date) + 1 END AS days,
        -- 累计天数（窗口函数）
        SUM(
            CASE WHEN s.seq = 1 THEN 
                CASE WHEN s.start_date > i.cdate THEN 0 ELSE DATEDIFF(day, s.start_date, CASE WHEN s.end_date > i.cdate THEN i.cdate ELSE s.end_date END) + 1 
            END
            ELSE DATEDIFF(day, s.start_date, s.end_date) + 1 END
        ) OVER (PARTITION BY i.id, i.cdate ORDER BY s.seq) AS cumulative_days
    FROM #input i
    LEFT JOIN service_sorted s ON i.id = s.id 
        AND (s.seq = 1 AND i.cdate BETWEEN s.start_date AND s.end_date OR s.seq > 1)
),
-- 筛选出累计天数不超过1200的时段，最后一个时段可能只取部分天数
filtered_periods AS (
    SELECT 
        id,
        cdate,
        start_date,
        period_end,
        seq,
        CASE 
            WHEN cumulative_days <= 1200 THEN days
            ELSE 1200 - (cumulative_days - days)
        END AS actual_days
    FROM period_contribution
    WHERE cumulative_days - days < 1200
)
-- 生成日期记录
SELECT 
    ROW_NUMBER() OVER (PARTITION BY fp.id, fp.cdate ORDER BY d.date DESC) AS rn,
    fp.id,
    fp.cdate,
    d.date
INTO #result
FROM filtered_periods fp
CROSS APPLY (
    SELECT DATEADD(day, -n.number, fp.period_end) AS date
    FROM master..spt_values n
    WHERE n.type = 'P' 
        AND n.number BETWEEN 0 AND fp.actual_days - 1
        AND DATEADD(day, -n.number, fp.period_end) >= fp.start_date
) d
WHERE fp.actual_days > 0
ORDER BY fp.id, fp.cdate, rn;

-- 查看结果
SELECT * FROM #result;

方案优势 #

两种方案均避免了全量关联日历表与服务表：

• 递归CTE：逐段回溯，一旦累计天数达到1200立即终止递归，无需处理更早的无关时段
• 批量计算：通过窗口函数累计天数，仅筛选出能贡献有效日期的时段，再生成对应日期

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