基于月末日期拆分周日期范围的Oracle SQL实现需求

原查询功能 #

以下Oracle SQL可根据指定的起始、结束日期，生成间隔7天的日期范围：

WITH Ranges AS (
    SELECT
        TO_DATE('2023-10-16 00:00:00', 'YYYY-MM-DD HH24:MI:SS') START_DATE,
        TO_DATE('2023-11-13 00:00:00', 'YYYY-MM-DD HH24:MI:SS') END_DATE
    FROM
        DUAL
)
SELECT
        GREATEST(START_DATE, TRUNC(START_DATE + 7 *(LEVEL - 1))) WEEK_FIRST_DATE,
        LEAST(END_DATE, TRUNC(START_DATE + 7 *(LEVEL - 1)) + 7 - INTERVAL '1' SECOND) WEEK_LAST_DATE,
        LEVEL SL_NO
    FROM
        Ranges
    CONNECT BY
        START_DATE + 7 * ( LEVEL - 1 ) <= END_DATE

优化需求 #

原查询无法处理月末日期落在区间内的场景，需要优化实现：

• 若某7天区间包含月末日期，则拆分该区间，以月末当天23:59:59作为该段的结束日期
• 从下月1日00:00:00开始，继续按7天间隔生成日期范围，直至覆盖原指定的结束日期

优化后的查询 #

通过递归CTE处理月末拆分逻辑，实现需求：

WITH Ranges AS (
    SELECT
        TO_DATE('2023-10-16 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AS START_DATE,
        TO_DATE('2023-11-13 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AS END_DATE
    FROM DUAL
),
Recursive_Dates (current_start, current_end, sl_no) AS (
    SELECT
        START_DATE,
        LEAST(
            END_DATE,
            LEAST(
                TRUNC(START_DATE) + 7 - INTERVAL '1' SECOND,
                LAST_DAY(TRUNC(START_DATE)) + INTERVAL '23:59:59' HOUR TO SECOND
            )
        ),
        1
    FROM Ranges
    UNION ALL
    SELECT
        CASE
            WHEN current_end = LAST_DAY(TRUNC(current_end)) + INTERVAL '23:59:59' HOUR TO SECOND
                THEN TRUNC(current_end) + INTERVAL '1' DAY
            ELSE current_end + INTERVAL '1' SECOND
        END AS next_start,
        LEAST(
            R.END_DATE,
            LEAST(
                CASE
                    WHEN current_end = LAST_DAY(TRUNC(current_end)) + INTERVAL '23:59:59' HOUR TO SECOND
                        THEN TRUNC(current_end) + INTERVAL '1' DAY + 6
                    ELSE TRUNC(current_end + INTERVAL '1' SECOND) + 6
                END + INTERVAL '23:59:59' HOUR TO SECOND,
                LAST_DAY(TRUNC(
                    CASE
                        WHEN current_end = LAST_DAY(TRUNC(current_end)) + INTERVAL '23:59:59' HOUR TO SECOND
                            THEN TRUNC(current_end) + INTERVAL '1' DAY
                        ELSE current_end + INTERVAL '1' SECOND
                    END
                )) + INTERVAL '23:59:59' HOUR TO SECOND
            )
        ),
        sl_no + 1
    FROM Recursive_Dates RD
    JOIN Ranges R ON RD.current_end < R.END_DATE
)
SELECT
    TO_CHAR(current_start, 'DD-MON-RR HH24:MI:SS') AS WEEK_FIRST_DATE,
    TO_CHAR(current_end, 'DD-MON-RR HH24:MI:SS') AS WEEK_LAST_DATE,
    sl_no AS SL_NO
FROM Recursive_Dates
ORDER BY sl_no;

期望输出 #

执行优化后的查询，将得到如下结果：

16-OCT-23 00:00:00  22-OCT-23 23:59:59  1
23-OCT-23 00:00:00  29-OCT-23 23:59:59  2
30-OCT-23 00:00:00  31-OCT-23 23:59:59  3
01-NOV-23 00:00:00  07-NOV-23 23:59:59  4
08-NOV-23 00:00:00  13-NOV-23 23:59:59  5

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