如何获取指定日期范围内数据表中的所有缺失日期？

嘿，这个场景我太熟悉了——月度数据导入总会碰到漏导或者部分缺失的情况！要解决这个问题，核心思路其实很直白：先生成一段包含你需要的所有日期的完整序列，再把它和你的数据表做对比，找出那些在完整序列里但没出现在你的数据表中的日期就搞定了。

下面我针对几种常用数据库给出具体实现，你可以直接套用到自己的场景里：

1. PostgreSQL 实现 #

PostgreSQL自带的generate_series函数可以轻松生成日期序列，非常方便：

-- 第一步：生成你指定范围内的所有日期
WITH date_range AS (
    SELECT generate_series(
        '2024-01-01'::DATE,  -- 替换成你的起始日期
        '2024-08-31'::DATE,  -- 替换成你的结束日期
        '1 day'::INTERVAL    -- 按天生成，要是只找每月第一天就改成'1 month'
    ) AS full_date
)
-- 第二步：对比现有表，找出缺失日期
SELECT full_date AS missing_date
FROM date_range
LEFT JOIN your_table ON date_range.full_date = your_table.date_column  -- 替换表名和日期字段名
WHERE your_table.date_column IS NULL
ORDER BY full_date;

2. MySQL 实现 #

MySQL没有直接的日期序列生成函数，不过可以用递归CTE来实现：

-- 第一步：递归生成完整日期序列
WITH RECURSIVE date_range AS (
    SELECT '2024-01-01' AS full_date  -- 起始日期
    UNION ALL
    SELECT DATE_ADD(full_date, INTERVAL 1 DAY)
    FROM date_range
    WHERE full_date < '2024-08-31'  -- 结束日期
)
-- 第二步：对比找出缺失日期
SELECT full_date AS missing_date
FROM date_range
LEFT JOIN your_table ON date_range.full_date = your_table.date_column  -- 替换表名和字段名
WHERE your_table.date_column IS NULL
ORDER BY full_date;

3. SQL Server 实现 #

和MySQL类似，SQL Server也可以用递归CTE，注意如果日期范围超过100天需要加MAXRECURSION选项：

-- 第一步：递归生成完整日期序列
WITH date_range AS (
    SELECT CAST('2024-01-01' AS DATE) AS full_date  -- 起始日期
    UNION ALL
    SELECT DATEADD(DAY, 1, full_date)
    FROM date_range
    WHERE full_date < CAST('2024-08-31' AS DATE)  -- 结束日期
)
-- 第二步：对比找出缺失日期
SELECT full_date AS missing_date
FROM date_range
LEFT JOIN your_table ON date_range.full_date = your_table.date_column  -- 替换表名和字段名
WHERE your_table.date_column IS NULL
ORDER BY full_date
OPTION (MAXRECURSION 0);  -- 解除递归次数限制（默认100次）

额外提示：如果只找每月第一天的缺失 #

你提到每月第一天会导入数据，要是你只需要检查这些月度起始日期的缺失，只需要调整日期序列的生成步长就行。比如PostgreSQL里把'1 day'改成'1 month'，生成的就是每个月的第一天，再和表对比就能找出漏导的月份起始日了。

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