Oracle中如何批量查询多name分组下的缺失日期？

需求说明 #

现有一张Oracle表，包含name和my_date字段，表内存在重复日期数据，需要生成每个name分组内的缺失日期列表。目前已实现单个name的缺失日期查询SQL，现需调整为同时处理多个name的情况。

输入输出示例 #

输入数据 #

name,my_date
A,04-JAN-2000
A,05-JAN-2000
A,08-JAN-2000
A,08-JAN-2000  -- 允许重复数据
A,10-JAN-2000
B,09-FEB-2001
B,10-FEB-2001
B,05-FEB-2001

输出结果 #

A,06-JAN-2000
A,07-JAN-2000
A,09-JAN-2000
B,06-FEB-2001
B,07-FEB-2001
B,08-FEB-2001

原单个name查询SQL #

WITH all_dates_wo_boundary_values as
(SELECT oldest + level my_date
    FROM (SELECT MIN(my_date) oldest
                ,MAX(my_date) recent
             FROM mytable my
             WHERE my.name = 'A'
         )
 connect by level <= recent - oldest - 1
)
 SELECT my_date
FROM all_dates_wo_boundary_values
MINUS
SELECT my_date
FROM mytable my
WHERE my.name = 'A'

批量处理多name的解决方案 #

要支持多分组查询，核心是先按name分组获取每个组的日期边界，再通过分层查询生成每个组内的所有中间日期，最后排除已存在的日期。以下是修改后的SQL：

WITH name_date_bounds AS (
    -- 按name分组，获取每个组的最小和最大日期
    SELECT 
        name,
        MIN(my_date) AS oldest,
        MAX(my_date) AS recent
    FROM mytable
    GROUP BY name
),
all_group_dates AS (
    -- 为每个name生成其日期范围内的所有中间日期（不含边界）
    SELECT 
        ndb.name,
        ndb.oldest + LEVEL AS my_date
    FROM name_date_bounds ndb
    CONNECT BY 
        LEVEL <= ndb.recent - ndb.oldest - 1
        -- 关键：确保分层查询按name分组生成，避免跨组混乱
        AND PRIOR ndb.name = ndb.name
        AND PRIOR SYS_GUID() IS NOT NULL
),
existing_dates AS (
    -- 去重后的已存在日期（因为原表有重复，去重后对比更高效）
    SELECT DISTINCT name, my_date
    FROM mytable
)
-- 找出每个name分组内生成的日期中，不在已存在列表里的记录
SELECT agd.name, agd.my_date
FROM all_group_dates agd
LEFT JOIN existing_dates ed 
    ON agd.name = ed.name AND agd.my_date = ed.my_date
WHERE ed.my_date IS NULL
ORDER BY agd.name, agd.my_date;

关键改动说明 #

• 分组获取日期边界：新增name_date_bounds子查询，按name分组获取每个组的最小和最大日期，替代原SQL中固定单个name的查询。
• 分层查询分组控制：在CONNECT BY中添加PRIOR ndb.name = ndb.name和PRIOR SYS_GUID() IS NOT NULL，确保分层查询是针对每个name独立生成日期，避免出现跨组的笛卡尔积问题。
• 去重已存在日期：新增existing_dates子查询对原表日期去重，减少后续关联对比的开销。
• 关联筛选缺失日期：用左连接替代原有的MINUS，更直观地筛选出每个分组内的缺失日期，同时保留name字段，符合输出要求。

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