使用MATCH_RECOGNIZE实现Oracle SQL日期范围合并与聚合计算求助

将数据合并为最小的日期范围，并针对同一id下的各个name对象，拼接各对象的P_MAX值（格式如150+300），同时计算该区间内所有对象的MIN(P_MIN)。

原始数据 #

期望结果 #

提示信息 #

• 每个name对象的MIN(date_from)和MAX(date_to)始终相同。
• MAX(date_to)可为NULL，表示对象持续到“无限期”。
• 同一name对象的date_from始终等于上一条记录的date_to。
• 一个id下可包含2个以上对象。
• 需按最小日期节点划分范围，可能存在多个最小日期节点。

尝试情况 #

曾尝试用MATCH_RECOGNIZE实现但未得到预期结果，优先倾向使用该方法，也接受其他SQL方案。

数据脚本（Oracle SQL） #

CREATE TABLE my_table (id         number
                     ,name       varchar2(100)
                     ,date_from  date
                     ,date_to    date
                     ,p_max      number
                     ,p_min      number);   
                     
INSERT INTO my_table VALUES (1, 'OBJECT 1', TO_DATE('10/11/2021', 'DD/MM/YYYY'), TO_DATE('10/10/2022', 'DD/MM/YYYY'), 150, 20);
INSERT INTO my_table VALUES (1, 'OBJECT 1', TO_DATE('10/10/2022', 'DD/MM/YYYY'), TO_DATE('02/02/2023', 'DD/MM/YYYY'), 200, 40);
INSERT INTO my_table VALUES (1, 'OBJECT 1', TO_DATE('02/02/2023', 'DD/MM/YYYY'), TO_DATE('18/06/2027', 'DD/MM/YYYY'), 100, 70);
INSERT INTO my_table VALUES (1, 'OBJECT 2', TO_DATE('10/11/2021', 'DD/MM/YYYY'), TO_DATE('01/05/2022', 'DD/MM/YYYY'), 300, 60);
INSERT INTO my_table VALUES (1, 'OBJECT 2', TO_DATE('01/05/2022', 'DD/MM/YYYY'), TO_DATE('01/12/2022', 'DD/MM/YYYY'),  50, 40);
INSERT INTO my_table VALUES (1, 'OBJECT 2', TO_DATE('01/12/2022', 'DD/MM/YYYY'), TO_DATE('18/06/2027', 'DD/MM/YYYY'), 350, 40);

解决方案 #

方法一：日期节点拆分+关联聚合 #

逻辑清晰，适用于所有Oracle版本：

1. 提取所有唯一日期节点，按id排序
2. 将相邻节点组合成最小日期区间
3. 关联原始表匹配区间内的对象
4. 聚合拼接p_max并计算min(p_min)

WITH date_nodes AS (
    SELECT id, date_val
    FROM (
        SELECT id, date_from AS date_val FROM my_table
        UNION
        SELECT id, date_to AS date_val FROM my_table
    )
    WHERE date_val IS NOT NULL
),
date_ranges AS (
    SELECT 
        id,
        date_val AS date_from,
        LEAD(date_val) OVER (PARTITION BY id ORDER BY date_val) AS date_to
    FROM date_nodes
)
SELECT 
    dr.id,
    TO_CHAR(dr.date_from, 'DD/MM/YYYY') AS date_from,
    TO_CHAR(dr.date_to, 'DD/MM/YYYY') AS date_to,
    LISTAGG(mt.p_max, '+') WITHIN GROUP (ORDER BY mt.name) AS sum_p_max,
    MIN(mt.p_min) AS p_min
FROM date_ranges dr
JOIN my_table mt 
    ON dr.id = mt.id
    AND mt.date_from <= dr.date_from
    AND (mt.date_to >= dr.date_to OR mt.date_to IS NULL)
WHERE dr.date_to IS NOT NULL
GROUP BY dr.id, dr.date_from, dr.date_to
ORDER BY dr.id, dr.date_from;

方法二：MATCH_RECOGNIZE实现 #

利用模式匹配识别跨对象的日期区间，同时完成聚合：

WITH all_events AS (
    SELECT 
        id,
        name,
        date_from AS event_date,
        p_max,
        p_min,
        'START' AS event_type
    FROM my_table
    UNION ALL
    SELECT 
        id,
        name,
        date_to AS event_date,
        p_max,
        p_min,
        'END' AS event_type
    FROM my_table
    WHERE date_to IS NOT NULL
)
SELECT 
    id,
    TO_CHAR(start_date, 'DD/MM/YYYY') AS date_from,
    TO_CHAR(end_date, 'DD/MM/YYYY') AS date_to,
    LISTAGG(p_max, '+') WITHIN GROUP (ORDER BY name) AS sum_p_max,
    MIN(p_min) AS p_min
FROM all_events
MATCH_RECOGNIZE (
    PARTITION BY id
    ORDER BY event_date
    MEASURES
        FIRST(event_date) AS start_date,
        NEXT(event_date) AS end_date,
        MIN(p_min) AS p_min
    PATTERN (A B*)
    DEFINE
        A AS 1=1,
        B AS event_date = PREV(event_date)
)
WHERE end_date IS NOT NULL
GROUP BY id, start_date, end_date
ORDER BY id, start_date;

若存在date_to为NULL的情况，可将NULL替换为极大值（如DATE '9999-12-31'），确保无限期对象被包含到最后一个区间。

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