Oracle中基于工作日调整日期的SQL查询优化需求

需求概述 #

仅将周一至周五视为工作日，处理规则如下：

1. 对输入日期应用指定的日历天数偏移（支持负数、0、正数），得到临时日期
2. 若临时日期为周一至周五，直接返回该日期
3. 若临时日期为周六或周日，返回最近的工作日（周五）

案例验证 #

案例解释 #

• Case 1：输入日期为周六，偏移0（即原日期）或-1天（周五），均返回工作日周五
• Case 2：输入日期为周日，偏移0（原日期）或-1天（周六），均返回最近的工作日周五
• Case 3：输入日期为周二，偏移+4天（周六）或+5天（周日），均返回最近的工作日周五

测试表结构 #

CREATE TABLE table_name (dt, days) AS
SELECT TRUNC(SYSDATE, 'IW') + 0, -3 FROM DUAL UNION ALL
SELECT TRUNC(SYSDATE, 'IW') + 2.5, -3 FROM DUAL UNION ALL
SELECT TRUNC(SYSDATE, 'IW') + 2, -1 FROM DUAL UNION ALL
SELECT TRUNC(SYSDATE, 'IW') + 3, 2 FROM DUAL UNION ALL
SELECT TRUNC(SYSDATE, 'IW') + 2, 7 FROM DUAL UNION ALL
SELECT TRUNC(SYSDATE, 'IW') + 5.5, 0 FROM DUAL UNION ALL
SELECT TRUNC(SYSDATE, 'IW') + 5.5, 7 FROM DUAL UNION ALL
SELECT TRUNC(SYSDATE, 'IW') + 0.2, 5 FROM DUAL;

修正后的查询语句 #

SELECT 
    dt,
    days,
    -- 最终返回的工作日日期
    CASE
        -- 判断临时日期是否为周六（偏移5）或周日（偏移6）
        WHEN (temp_date - TRUNC(temp_date, 'IW')) IN (5, 6) THEN
            -- 返回当周周五（周一+4天）
            TRUNC(temp_date, 'IW') + 4
        ELSE
            temp_date
    END AS offset_working_day
FROM (
    -- 先计算输入日期加上偏移量后的临时日历日期
    SELECT 
        dt,
        days,
        dt + days AS temp_date
    FROM table_name
);

逻辑说明 #

1. 子查询层：计算输入日期与偏移量相加后的临时日历日期，不区分工作日/周末
2. 主查询层：
   • 通过TRUNC(temp_date, 'IW')获取临时日期所在周的周一（IW格式符确保周起始为周一，不受地域设置影响）
   • 计算临时日期与周一的天数差，判断是否为周六（差5）或周日（差6）
   • 若为周末，直接返回当周周五（周一+4天）；否则返回临时日期本身

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