Oracle SQL 19如何获取指定日期所属月份的第5个工作日

核心逻辑 #

1. 生成输入日期所属月份的全部日期
2. 用CTE定义联邦/银行假日规则，无需存储到物理表
3. 排除周末和法定假日后，对工作日排序取第5条

完整SQL代码 #

WITH 
-- 输入参数：替换为需要查询的给定日期
input_date AS (
    SELECT DATE '2021-09-08' dt FROM DUAL
),
-- 生成当月所有日期
month_dates AS (
    SELECT TRUNC(dt, 'MM') + LEVEL - 1 AS curr_date
    FROM input_date
    CONNECT BY TRUNC(dt, 'MM') + LEVEL -1 <= LAST_DAY(dt)
),
-- 定义美国联邦/银行假日规则，可根据实际地区规则修改
federal_holidays AS (
    -- 新年：1月1日，遇周末调休到最近工作日
    SELECT 
        CASE 
            WHEN TO_CHAR(TRUNC(curr_date, 'YYYY'), 'DY', 'NLS_DATE_LANGUAGE=AMERICAN') = 'SAT' THEN TRUNC(curr_date, 'YYYY') - 1
            WHEN TO_CHAR(TRUNC(curr_date, 'YYYY'), 'DY', 'NLS_DATE_LANGUAGE=AMERICAN') = 'SUN' THEN TRUNC(curr_date, 'YYYY') + 1
            ELSE TRUNC(curr_date, 'YYYY') 
        END AS holiday
    FROM month_dates WHERE EXTRACT(MONTH FROM curr_date) = 1 AND EXTRACT(DAY FROM curr_date) =1
    UNION ALL
    -- 马丁路德金日：1月第三个周一
    SELECT curr_date FROM month_dates 
    WHERE EXTRACT(MONTH FROM curr_date) =1 
    AND TO_CHAR(curr_date, 'DY', 'NLS_DATE_LANGUAGE=AMERICAN') = 'MON'
    AND TO_CHAR(curr_date, 'W') = '3'
    UNION ALL
    -- 总统日：2月第三个周一
    SELECT curr_date FROM month_dates 
    WHERE EXTRACT(MONTH FROM curr_date) =2 
    AND TO_CHAR(curr_date, 'DY', 'NLS_DATE_LANGUAGE=AMERICAN') = 'MON'
    AND TO_CHAR(curr_date, 'W') = '3'
    UNION ALL
    -- 阵亡将士纪念日：5月最后一个周一
    SELECT curr_date FROM month_dates 
    WHERE EXTRACT(MONTH FROM curr_date) =5 
    AND TO_CHAR(curr_date, 'DY', 'NLS_DATE_LANGUAGE=AMERICAN') = 'MON'
    AND curr_date = LAST_DAY(curr_date) - MOD(LAST_DAY(curr_date) - DATE '1900-01-01',7)
    UNION ALL
    -- 独立日：7月4日，遇周末调休
    SELECT 
        CASE 
            WHEN TO_CHAR(TRUNC(curr_date, 'YYYY') + 184, 'DY', 'NLS_DATE_LANGUAGE=AMERICAN') = 'SAT' THEN TRUNC(curr_date, 'YYYY') + 183
            WHEN TO_CHAR(TRUNC(curr_date, 'YYYY') + 184, 'DY', 'NLS_DATE_LANGUAGE=AMERICAN') = 'SUN' THEN TRUNC(curr_date, 'YYYY') + 185
            ELSE TRUNC(curr_date, 'YYYY') + 184 
        END AS holiday
    FROM month_dates WHERE EXTRACT(MONTH FROM curr_date) =7 AND EXTRACT(DAY FROM curr_date) =4
    UNION ALL
    -- 劳工节：9月第一个周一
    SELECT curr_date FROM month_dates 
    WHERE EXTRACT(MONTH FROM curr_date) =9 
    AND TO_CHAR(curr_date, 'DY', 'NLS_DATE_LANGUAGE=AMERICAN') = 'MON'
    AND TO_CHAR(curr_date, 'W') = '1'
    UNION ALL
    -- 感恩节：11月第四个周四
    SELECT curr_date FROM month_dates 
    WHERE EXTRACT(MONTH FROM curr_date) =11 
    AND TO_CHAR(curr_date, 'DY', 'NLS_DATE_LANGUAGE=AMERICAN') = 'THU'
    AND TO_CHAR(curr_date, 'W') = '4'
    UNION ALL
    -- 圣诞节：12月25日，遇周末调休
    SELECT 
        CASE 
            WHEN TO_CHAR(TRUNC(curr_date, 'YYYY') + 358, 'DY', 'NLS_DATE_LANGUAGE=AMERICAN') = 'SAT' THEN TRUNC(curr_date, 'YYYY') + 357
            WHEN TO_CHAR(TRUNC(curr_date, 'YYYY') + 358, 'DY', 'NLS_DATE_LANGUAGE=AMERICAN') = 'SUN' THEN TRUNC(curr_date, 'YYYY') + 359
            ELSE TRUNC(curr_date, 'YYYY') + 358 
        END AS holiday
    FROM month_dates WHERE EXTRACT(MONTH FROM curr_date) =12 AND EXTRACT(DAY FROM curr_date) =25
),
-- 筛选工作日，排除周末和假日，排序取第5个
workdays_ranked AS (
    SELECT 
        curr_date,
        ROW_NUMBER() OVER(ORDER BY curr_date) AS rn
    FROM month_dates m
    WHERE 
        -- 排除周六周日
        TO_CHAR(m.curr_date, 'DY', 'NLS_DATE_LANGUAGE=AMERICAN') NOT IN ('SAT', 'SUN')
        -- 排除法定假日
        AND NOT EXISTS (SELECT 1 FROM federal_holidays h WHERE h.holiday = m.curr_date)
)
SELECT curr_date AS 第五个工作日
FROM workdays_ranked
WHERE rn =5;

测试验证 #

替换input_date中的日期可得到对应结果，和需求示例完全匹配：

• 输入DATE '2021-09-08'，返回2021-09-08
• 输入DATE '2021-01-21'，返回2021-01-08
• 输入DATE '2021-02-17'，返回2021-02-05

规则调整说明 #

如果需要适配其他地区的银行/公共假日，仅修改federal_holidays CTE中的假日规则即可，其他逻辑无需变动。

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