Oracle SQL统计日期范围内缺失的日期数量

要解决这个问题，核心思路是先生成日期范围内的所有连续日期，再对比原表找出那些不存在的日期，最后统计缺失数量。下面我会给出两种实用的Oracle SQL实现方案，覆盖不同版本的Oracle数据库。

前提假设 #

假设你的表名为date_records，存储日期的字段为date_col（建议避免使用date作为字段名，因为它是Oracle的关键字）。如果你的日期字段是字符串类型（比如示例中的1/1/2022），需要先用TO_DATE()函数转换为DATE类型，我会在示例中说明如何处理。

方案一：使用递归CTE（Oracle 11g及以上版本） #

递归CTE是Oracle 11g引入的特性，写法更直观易读：

1. 查询缺失的具体日期 #

WITH date_range AS (
    -- 第一步：获取原数据的日期范围（最小和最大日期）
    SELECT 
        MIN(TO_DATE(date_col, 'MM/DD/YYYY')) AS start_date,  -- 如果是DATE类型可直接用date_col
        MAX(TO_DATE(date_col, 'MM/DD/YYYY')) AS end_date
    FROM date_records
),
continuous_dates AS (
    -- 第二步：递归生成从start_date到end_date的所有连续日期
    SELECT start_date AS curr_date
    FROM date_range
    UNION ALL
    SELECT curr_date + INTERVAL '1' DAY
    FROM continuous_dates
    WHERE curr_date < (SELECT end_date FROM date_range)
)
-- 第三步：对比原表，找出缺失的日期
SELECT curr_date AS missing_date
FROM continuous_dates
LEFT JOIN date_records 
    ON continuous_dates.curr_date = TO_DATE(date_records.date_col, 'MM/DD/YYYY')  -- 字符串转DATE
WHERE date_records.date_col IS NULL
ORDER BY missing_date;

运行这个查询，会返回示例中的04-JAN-22（或你的日期格式）和09-JAN-22，也就是缺失的4/1/2022和9/1/2022。

2. 统计缺失日期的数量 #

如果只需要统计数量，把上面的查询修改为：

WITH date_range AS (
    SELECT 
        MIN(TO_DATE(date_col, 'MM/DD/YYYY')) AS start_date,
        MAX(TO_DATE(date_col, 'MM/DD/YYYY')) AS end_date
    FROM date_records
),
continuous_dates AS (
    SELECT start_date AS curr_date
    FROM date_range
    UNION ALL
    SELECT curr_date + INTERVAL '1' DAY
    FROM continuous_dates
    WHERE curr_date < (SELECT end_date FROM date_range)
)
SELECT COUNT(*) AS missing_dates_count
FROM continuous_dates
LEFT JOIN date_records 
    ON continuous_dates.curr_date = TO_DATE(date_records.date_col, 'MM/DD/YYYY')
WHERE date_records.date_col IS NULL;

这个查询会直接返回2，和示例的预期结果一致。

方案二：使用CONNECT BY（兼容Oracle 10g及更早版本） #

如果你的Oracle版本低于11g，无法使用递归CTE，可以用CONNECT BY来生成连续日期：

1. 查询缺失的具体日期 #

WITH date_range AS (
    SELECT 
        MIN(TO_DATE(date_col, 'MM/DD/YYYY')) AS start_date,
        MAX(TO_DATE(date_col, 'MM/DD/YYYY')) AS end_date
    FROM date_records
),
continuous_dates AS (
    -- 用LEVEL生成连续日期
    SELECT start_date + (LEVEL - 1) AS curr_date
    FROM date_range
    CONNECT BY LEVEL <= (end_date - start_date) + 1
)
SELECT curr_date AS missing_date
FROM continuous_dates
LEFT JOIN date_records 
    ON continuous_dates.curr_date = TO_DATE(date_records.date_col, 'MM/DD/YYYY')
WHERE date_records.date_col IS NULL
ORDER BY missing_date;

2. 统计缺失日期的数量 #

WITH date_range AS (
    SELECT 
        MIN(TO_DATE(date_col, 'MM/DD/YYYY')) AS start_date,
        MAX(TO_DATE(date_col, 'MM/DD/YYYY')) AS end_date
    FROM date_records
),
continuous_dates AS (
    SELECT start_date + (LEVEL - 1) AS curr_date
    FROM date_range
    CONNECT BY LEVEL <= (end_date - start_date) + 1
)
SELECT COUNT(*) AS missing_dates_count
FROM continuous_dates
LEFT JOIN date_records 
    ON continuous_dates.curr_date = TO_DATE(date_records.date_col, 'MM/DD/YYYY')
WHERE date_records.date_col IS NULL;

关键注意事项 #

• 如果你的日期字段已经是DATE类型，直接去掉所有TO_DATE()转换即可，避免不必要的类型转换开销。
• 确保日期格式匹配：TO_DATE(date_col, 'MM/DD/YYYY')中的格式符要和你存储的字符串日期格式一致，比如如果是DD/MM/YYYY就要修改格式符。

内容的提问来源于stack exchange，提问作者Aasem Shoshari