PostgreSQL中如何查询指定日期范围内各ID的缺失日期并输出

现有数据表 #

假设你的表名为your_table，数据如下：

需求说明 #

需要找出2022-09-01至2022-09-10日期范围内，每个ID对应的缺失日期，输出格式如下：

预期结果 #

实现方案 #

通过生成完整日期序列、关联所有ID、排除已存在的日期组合来实现，具体SQL语句如下：

WITH date_range AS (
    -- 生成指定范围内的所有日期
    SELECT generate_series(
        '2022-09-01'::DATE,
        '2022-09-10'::DATE,
        '1 day'::INTERVAL
    ) AS missing_date
),
distinct_ids AS (
    -- 获取所有唯一的ID
    SELECT DISTINCT ID FROM your_table
),
all_possible AS (
    -- 生成每个ID对应所有日期的笛卡尔积
    SELECT di.ID, dr.missing_date::DATE
    FROM distinct_ids di
    CROSS JOIN date_range dr
)
-- 排除已存在的(ID, Date)组合，剩下的就是缺失数据
SELECT ap.ID, ap.missing_date AS "缺失日期"
FROM all_possible ap
LEFT JOIN your_table t ON ap.ID = t.ID AND ap.missing_date = t.Date
WHERE t.Date IS NULL
ORDER BY ap.ID, ap.missing_date;

代码说明 #

• date_range：用generate_series生成目标日期范围内的每一天，得到完整的日期序列。
• distinct_ids：提取表中所有不重复的ID，确保每个ID都被处理。
• all_possible：将唯一ID和日期序列做交叉连接，得到每个ID在目标范围内的所有可能日期组合。
• 最后通过左连接原表，筛选出原表中不存在的组合，即为对应的缺失日期。

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