如何在SQL表指定时间范围内查找缺失日期与缺失时段

问题说明 #

现有一张名为RandomPriceSummary的表，日期范围覆盖2022年10月1日到2022年10月3日（时区为GMT+0100，英国夏令时），时段范围是1到3。表中现有数据如下：

需要编写包含WHERE SETTLEMENT_DATE BETWEEN TIMESTAMP '10-01-2022' AND TIMESTAMP '10-03-2022'的SQL查询，找出1-3时段内缺失的日期和时段，预期返回结果如下：

解决方法 #

要找出缺失的日期-时段组合，核心是先生成所有应该存在的配对，再和现有表的数据对比，筛选出未出现的记录。

完整SQL查询 #

WITH date_range AS (
    -- 生成指定范围内的所有日期
    SELECT TIMESTAMP '2022-10-01' AS settlement_date
    UNION ALL
    SELECT settlement_date + INTERVAL '1 day'
    FROM date_range
    WHERE settlement_date < TIMESTAMP '2022-10-03'
),
period_range AS (
    -- 生成1到3的所有时段
    SELECT 1 AS period
    UNION ALL
    SELECT period + 1
    FROM period_range
    WHERE period < 3
),
expected_combinations AS (
    -- 生成所有可能的日期-时段组合
    SELECT dr.settlement_date, pr.period
    FROM date_range dr
    CROSS JOIN period_range pr
)
-- 对比现有数据，找出缺失项
SELECT 
    ec.settlement_date AS missing_date,
    ec.period AS missing_period
FROM expected_combinations ec
LEFT JOIN RandomPriceSummary rps
    ON ec.settlement_date = rps.日期
    AND ec.period = rps.period
WHERE 
    rps.日期 IS NULL  -- 筛选原表中无匹配的记录
    AND ec.settlement_date BETWEEN TIMESTAMP '2022-10-01' AND TIMESTAMP '2022-10-03';

逻辑解释 #

1. 生成日期序列：用递归CTEdate_range生成2022-10-01到2022-10-03的所有日期；
2. 生成时段序列：用递归CTEperiod_range生成1到3的所有时段；
3. 生成预期组合：通过CROSS JOIN（笛卡尔积）将日期和时段两两配对，得到所有应存在的组合；
4. 筛选缺失项：用左连接关联预期组合与原表，原表中无匹配的记录即为缺失项，再通过BETWEEN限定日期范围。

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