如何在Oracle中提取指定日期范围内缺失日期的主键信息

问题说明 #

你需要从表a中找出每个job_id在2024-01-01至2024-10-01范围内缺失的每月1号记录。注意：原表定义中job_id设为主键会导致插入重复job_id的记录失败，正确的表定义应将job_id和"DATE"设为联合主键，修正后的建表及插入语句如下：

CREATE TABLE a
(
    job_id INT NOT NULL,
    "DATE" DATE NOT NULL,
    PRIMARY KEY (job_id, "DATE")
);

INSERT INTO a 
VALUES (1, '2024-01-01'),
       (1, '2024-02-01'),
       (1, '2024-03-01'),
       (1, '2024-05-01'),
       (1, '2024-06-01'),
       (1, '2024-07-01'),
       (1, '2024-09-01'),
       (2, '2024-01-01'),
       (2, '2024-02-01'),
       (2, '2024-03-01'),
       (2, '2024-04-01'),
       (2, '2024-06-01');

解决方案SQL #

WITH full_dates AS (
    -- 生成指定范围内每个月的第一天
    SELECT TRUNC(TO_DATE('2024-01-01', 'YYYY-MM-DD'), 'MM') + NUMTOYMINTERVAL(LEVEL-1, 'MONTH') AS month_start
    FROM dual
    CONNECT BY LEVEL <= 10 -- 覆盖1月到10月共10个月份
),
all_job_date_pairs AS (
    -- 生成所有job_id与完整日期的组合
    SELECT j.job_id, fd.month_start AS "DATE"
    FROM (SELECT DISTINCT job_id FROM a) j
    CROSS JOIN full_dates fd
)
-- 筛选出表a中不存在的组合，即缺失记录
SELECT ajdp.job_id, ajdp."DATE"
FROM all_job_date_pairs ajdp
LEFT JOIN a 
    ON ajdp.job_id = a.job_id 
    AND ajdp."DATE" = a."DATE"
WHERE a."DATE" IS NULL
ORDER BY ajdp.job_id, ajdp."DATE";

语句解释 #

1. full_dates：利用Oracle的CONNECT BY生成连续的月份起始日期，确保覆盖2024年1月到10月的所有每月第一天。
2. all_job_date_pairs：将所有唯一的job_id与完整日期序列做交叉连接，得到每个job_id在指定范围内应该存在的所有日期组合。
3. 主查询：通过左连接表a，筛选出那些在完整组合中存在但表a里没有的记录，这些就是缺失的日期记录，最后按job_id和日期排序。

执行结果 #

JOB_ID | DATE
-------+-------------
 1     | 2024-04-01
 1     | 2024-08-01
 1     | 2024-10-01
 2     | 2024-05-01
 2     | 2024-07-01
 2     | 2024-08-01
 2     | 2024-09-01
 2     | 2024-10-01

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