Oracle SQL中统计两日期间周日数量及现有代码逻辑排查求助

需求说明 #

核心需求是统计CREATEDDATE到UPDATEDDATE两个日期之间的周日数量：

• 示例场景：CREATED_DATE为21-10-23 06:00:00.000000000 PM（周六），UPDATED_DATE为23-10-23 06:00:00.000000000 PM（周一），区间包含22-10-23（周日），期望结果为1。

现有代码（重点CASE语句） #

SUM(
    CASE
        WHEN EXTRACT(HOUR FROM (UPDATEDDATE - CREATEDDATE)) <= 4   -- 小时数需<=4
        AND (
            EXTRACT(DAY FROM (UPDATEDDATE - CREATEDDATE)) -        -- 间隔天数
            (
                SELECT COUNT(*)
                FROM DUAL
                WHERE TO_CHAR(CREATEDDATE + LEVEL, 'D') = '1'      -- 判断是否为周日
                CONNECT BY CREATEDDATE + LEVEL <= UPDATEDDATE      -- 遍历至结束日期
            ) = 0
        )
        THEN 1
        ELSE 0
    END
) AS "0_4",

测试数据问题 #

测试数据：CREATEDDATE为04-11-23 06:00:00.000000000 PM（周六），UPDATEDDATE为04-11-23 08:00:00.000000000 PM（周六），两日期间隔2小时、0天，但代码条件不满足，且无法正确统计区间内的周日数量。

逻辑错误分析 #

1. 子查询统计周日的逻辑失效 #

• 子查询使用CONNECT BY CREATEDDATE + LEVEL <= UPDATEDDATE生成中间日期的写法错误：LEVEL从1开始，当两日期间隔0天时，CREATEDDATE + 1必然晚于UPDATEDDATE，子查询返回0，无法正确判断当日是否为周日。
• TO_CHAR(日期, 'D')的返回值依赖数据库的NLS_DATE_LANGUAGE设置，'1'不一定代表周日（部分地区默认1为周一），存在逻辑隐患。

2. CASE语句的核心逻辑完全偏离需求 #

原CASE的条件是间隔小时数<=4 且 间隔天数等于区间内周日数量，这个逻辑是在筛选“区间内所有天数都是周日”的特殊场景，然后给这类记录标记为1，SUM后得到的是符合该条件的记录数，而非每个记录对应的周日数量，完全违背了“统计区间内周日数量”的核心需求。

3. 日期差提取的逻辑冗余 #

EXTRACT(DAY FROM (UPDATEDDATE - CREATEDDATE))仅能提取间隔的整数天数，当间隔不足1天时返回0，但结合后续条件，无法覆盖跨天但小时数<=4的场景（比如周五22点到周六2点，间隔4小时但跨天）。

修正思路 #

要正确统计区间内的周日数量，可采用两种高效实现方式：

方式1：递归生成日期统计（直观易读） #

SUM(
    (SELECT COUNT(*)
     FROM DUAL
     WHERE TO_CHAR(TRUNC(CREATEDDATE) + LEVEL - 1, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') = 'SUN'
     CONNECT BY LEVEL <= TRUNC(UPDATEDDATE) - TRUNC(CREATEDDATE) + 1)
) AS SUNDAY_COUNT

• 用TRUNC()截断时间部分，确保按自然日统计
• 指定NLS_DATE_LANGUAGE=ENGLISH避免语言环境影响DY格式的返回值
• 递归生成从CREATEDDATE到UPDATEDDATE的所有自然日，统计其中周日的数量

方式2：数学公式计算（性能更优） #

SUM(
    FLOOR((TRUNC(UPDATEDDATE) - NEXT_DAY(TRUNC(CREATEDDATE) - 1, 'SUN')) / 7) +
    CASE WHEN TO_CHAR(TRUNC(CREATEDDATE), 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') = 'SUN' THEN 1 ELSE 0 END +
    CASE WHEN TO_CHAR(TRUNC(UPDATEDDATE), 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') = 'SUN' AND TRUNC(UPDATEDDATE) > TRUNC(CREATEDDATE) THEN 1 ELSE 0 END
) AS SUNDAY_COUNT

• NEXT_DAY(TRUNC(CREATEDDATE)-1, 'SUN')获取CREATEDDATE前的最后一个周日
• 通过天数差除以7计算完整周的周日数量
• 单独判断首尾日期是否为周日，补充统计

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