如何通过SQL查询将日期增加3个含银行假日的工作日（无函数权限）

假设你的DimDate表结构包含DateKey(DATE)、WORK_DAY_FLAG(INT，1=工作日)、BANK_HOLIDAY(INT，1=银行假日)，需要对任意输入日期返回往后推3个有效工作日的日期（有效工作日定义：WORK_DAY_FLAG=1且BANK_HOLIDAY=0）。以下是针对当前查询返回NULL问题的修复方案：

测试用表与数据 #

先定义测试环境方便验证：

CREATE TABLE DimDate (
    DateKey DATE PRIMARY KEY,
    WORK_DAY_FLAG INT,
    BANK_HOLIDAY INT
);

INSERT INTO DimDate VALUES
('2024-05-20', 1, 0), -- 周一（正常工作日）
('2024-05-21', 1, 0), -- 周二
('2024-05-22', 1, 1), -- 周三（银行假日）
('2024-05-23', 1, 0), -- 周四
('2024-05-24', 1, 0), -- 周五
('2024-05-25', 0, 0), -- 周六
('2024-05-26', 0, 0), -- 周日
('2024-05-27', 1, 0), -- 周一
('2024-05-28', 1, 0); -- 周二

问题分析 #

原有查询只处理输入为有效工作日的场景，当输入是周末或银行假日时，子查询无法匹配到足够的偏移量，导致返回NULL。我们需要先确定每个输入日期对应的起始有效工作日序号，再找到第3个后续有效工作日。

解决方案1：窗口函数实现（推荐，简洁高效） #

利用ROW_NUMBER()给所有有效工作日排序，再通过起始序号定位目标日期：

WITH ValidWorkdays AS (
    -- 筛选所有有效工作日并分配连续序号
    SELECT 
        DateKey,
        ROW_NUMBER() OVER (ORDER BY DateKey) AS WorkdaySeq
    FROM DimDate
    WHERE WORK_DAY_FLAG = 1 AND BANK_HOLIDAY = 0
),
OriginalStartSeq AS (
    -- 为每个原始日期找到对应的起始有效工作日序号
    SELECT 
        d.DateKey AS OriginalDate,
        -- 如果原始日期是有效工作日，用自身序号；否则取之后第一个有效工作日的序号
        COALESCE(v.WorkdaySeq, (SELECT MIN(WorkdaySeq) FROM ValidWorkdays v2 WHERE v2.DateKey > d.DateKey)) AS StartSeq
    FROM DimDate d
    LEFT JOIN ValidWorkdays v ON d.DateKey = v.DateKey
)
-- 关联找到起始序号+2的日期（因为要3个工作日，序号1→4即+2）
SELECT 
    ods.OriginalDate,
    v.DateKey AS DatePlus3Workdays
FROM OriginalStartSeq ods
JOIN ValidWorkdays v ON v.WorkdaySeq = ods.StartSeq + 2;

解决方案2：兼容低版本数据库（无窗口函数支持） #

如果你的数据库不支持窗口函数，用计数子查询实现：

SELECT 
    d1.DateKey AS OriginalDate,
    (SELECT MIN(d3.DateKey)
     FROM DimDate d3
     WHERE 
         -- 情况1：原始日期是有效工作日，需包含自身在内共3个有效工作日
         (d1.WORK_DAY_FLAG = 1 AND d1.BANK_HOLIDAY = 0 
          AND (SELECT COUNT(*) 
               FROM DimDate d2 
               WHERE d2.DateKey BETWEEN d1.DateKey AND d3.DateKey
                 AND d2.WORK_DAY_FLAG = 1 
                 AND d2.BANK_HOLIDAY = 0) = 3)
         -- 情况2：原始日期非有效工作日，需在其之后找3个有效工作日
         OR (d1.WORK_DAY_FLAG = 0 OR d1.BANK_HOLIDAY = 1
             AND (SELECT COUNT(*) 
                  FROM DimDate d2 
                  WHERE d2.DateKey > d1.DateKey 
                    AND d2.DateKey <= d3.DateKey
                    AND d2.WORK_DAY_FLAG = 1 
                    AND d2.BANK_HOLIDAY = 0) = 3)) AS DatePlus3Workdays
FROM DimDate d1;

验证结果 #

执行上述查询后，关键日期的预期输出：

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