Oracle数据库中重叠与非重叠区间合并为连续区间的高效查询

核心思路 #

通过提取所有区间的关键分界点（开始日期、替换空值后的结束日期），生成连续无重叠的细分区间，再统计每个细分区间内所有原始记录的COUNT总和。相比递归CTE，该方案借助窗口函数实现，性能更优，尤其适合大数据量场景。

处理逻辑与SQL实现 #

假设你的表名为your_table，以下是完整查询代码：

WITH date_points AS (
    -- 提取所有区间的开始/结束日期，空结束日期用Oracle最大日期替代（代表无限期）
    SELECT valid_from AS point_date FROM your_table
    UNION
    SELECT NVL(valid_until, DATE '9999-12-31') AS point_date FROM your_table
),
sorted_points AS (
    -- 对日期点去重排序，用LEAD函数生成连续无重叠区间
    SELECT 
        point_date AS start_date,
        LEAD(point_date) OVER (ORDER BY point_date) AS end_date
    FROM date_points
    GROUP BY point_date
    ORDER BY point_date
)
-- 关联原始表，统计每个细分区间的COUNT总和
SELECT 
    sp.start_date,
    sp.end_date,
    SUM(t.count) AS total_count
FROM sorted_points sp
JOIN your_table t 
    ON t.valid_from < sp.end_date 
    AND NVL(t.valid_until, DATE '9999-12-31') > sp.start_date
WHERE sp.end_date IS NOT NULL -- 排除无后续日期的孤立点
GROUP BY sp.start_date, sp.end_date
ORDER BY sp.start_date;

代码细节说明 #

1. date_points CTE：收集所有原始区间的开始和结束日期，将VALID_UNTIL为空的记录统一替换为DATE '9999-12-31'，确保无限期区间能被统一处理。
2. sorted_points CTE：对收集到的日期点去重、排序，通过LEAD窗口函数获取每个日期的下一个相邻日期，生成连续的无重叠细分区间（格式为[start_date, end_date)）。
3. 最终关联统计：通过区间覆盖判断条件，将原始记录与细分区间关联，对每个区间内的COUNT求和，得到最终结果。

示例验证 #

假设输入数据：

执行查询后输出：

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