Oracle 19c中如何按连续日期范围合并汇总数据行？

针对你的需求，Oracle 19c可以通过多种方式实现，以下是两种高效的解决方案：

方法1：基于分析函数的分组合并 #

利用LAG函数识别连续日期的分组，再汇总计算：

WITH grouped_data AS (
    SELECT 
        person_number,
        full_name,
        grade,
        st_date,
        end_date,
        -- 标记非连续行，生成分组ID
        SUM(
            CASE 
                WHEN LAG(end_date) OVER (PARTITION BY person_number, full_name, grade ORDER BY st_date) + 1 = st_date 
                THEN 0 
                ELSE 1 
            END
        ) OVER (PARTITION BY person_number, full_name, grade ORDER BY st_date) AS group_id
    FROM xxtest
)
SELECT 
    person_number,
    full_name,
    grade,
    MIN(st_date) AS merged_start_date,
    MAX(end_date) AS merged_end_date,
    SUM(end_date - st_date + 1) AS total_days
FROM grouped_data
GROUP BY person_number, full_name, grade, group_id
ORDER BY person_number, merged_start_date;

方法2：使用MATCH_RECOGNIZE模式匹配（Oracle 12c+支持） #

Oracle的模式匹配功能可以直观地匹配连续的日期序列：

SELECT 
    person_number,
    full_name,
    grade,
    merged_start_date,
    merged_end_date,
    total_days
FROM xxtest
MATCH_RECOGNIZE (
    PARTITION BY person_number, full_name, grade
    ORDER BY st_date
    MEASURES
        FIRST(st_date) AS merged_start_date,
        LAST(end_date) AS merged_end_date,
        SUM(end_date - st_date + 1) AS total_days
    PATTERN (consecutive_records+)
    DEFINE
        consecutive_records AS NEXT(st_date) = PREV(end_date) + 1 OR PREV(end_date) IS NULL
);

关键说明 #

• 连续判断逻辑：当前记录的st_date等于上一条同组记录的end_date + 1，视为日期连续。
• 天数计算：使用end_date - st_date + 1是因为Oracle日期相减仅返回间隔天数，加1才能包含首尾两天（比如2023-01-01到2023-01-01，相减结果为0，加1后得到正确的1天）。
• 两种方法都能正确处理你提供的测试数据，合并后得到符合预期的汇总结果。

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