如何在MariaDB中按指定规则合并时间区间数据

已知时间点满足 A < B < C < D，需按照以下规则合并时间区间，最终均得到 A->D 的结果：

测试数据集 #

create table testing
(
    id       int,
    start_dt date,
    stop_dt  date
);
insert into testing
values (2, date '2010-02-14', date '2010-03-22'),  -- R1
       (3, date '2010-03-23', date '2010-04-12'),  -- R1
       (4, date '2010-03-23', date '2010-05-14'),  -- R1
       (5, date '2010-05-15', date '2010-06-07'),  -- R1
       -- 预期合并结果: 2010-02-14 | 2010-06-07
       (6, date '2011-01-01', date '2011-02-02'),  -- R2
       (7, date '2011-01-01', date '2011-03-04'),  -- R2
       (8, date '2011-02-03', date '2011-04-04'),  -- R2
       -- 预期合并结果: 2011-01-01 | 2011-04-04
       (14, date '2014-05-05', date '2014-06-06'), -- R3
       (15, date '2014-05-07', date '2014-06-06'), -- R3
       (16, date '2014-06-07', date '2014-12-12'); -- R3
       -- 预期合并结果: 2014-05-05 | 2014-12-12

当前错误结果 #

当前方法存在的问题 #

现有方法逻辑为：

• 若存在相同start_dt，通过min(start_dt)合并
• 若存在相同stop_dt，通过max(stop_dt)合并

该逻辑无法满足规则2（会错误移除A->B区间，导致后续区间无法正确合并），现有SQL代码如下：

select min(start_dt)                                             as start_dt,
       case when count(*) = count(stop_dt) then max(stop_dt) end as stop_dt,
       grp
from (select start_dt,
             stop_dt,
             count(flag) over (order by start_dt, stop_dt) as grp
      from (select start_dt,
                   stop_dt,
                   IF(lag(stop_dt) over (order by start_dt,stop_dt) =
                      start_dt - interval 1 day, null, 1) as flag
            from (select start_dt,
                         max(stop_dt) as stop_dt
                  from (select min(start_dt)             as start_dt,
                               stop_dt,
                               if(stop_dt is null, 1, 0) as grp
                        from testing
                        group by stop_dt
                        order by start_dt, grp) as minAndMaxDate
                  group by start_dt, grp) as sdsd) with_flags) grouped
group by grp
order by grp, start_dt;

正确实现方案 #

针对MariaDB 10.4.12，可使用窗口函数标记连续/重叠的区间分组，再聚合得到合并后的结果：

WITH sorted_intervals AS (
    SELECT 
        start_dt, 
        stop_dt,
        -- 标记新分组：当前区间的start_dt大于之前所有区间的最大stop_dt时，为新组
        SUM(CASE WHEN start_dt > COALESCE(MAX(stop_dt) OVER (ORDER BY start_dt ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), '1900-01-01') THEN 1 ELSE 0 END) OVER (ORDER BY start_dt) AS grp
    FROM testing
),
grouped_intervals AS (
    SELECT
        grp,
        MIN(start_dt) AS merged_start,
        MAX(stop_dt) AS merged_stop
    FROM sorted_intervals
    GROUP BY grp
)
SELECT merged_start AS start_dt, merged_stop AS stop_dt
FROM grouped_intervals
ORDER BY merged_start;

逻辑说明 #

1. sorted_intervals：按start_dt排序，用窗口函数计算每个区间所属的分组。如果当前区间的start_dt大于之前所有区间的最大stop_dt，则开启一个新分组（用COALESCE处理第一个区间的空值情况）。
2. grouped_intervals：按分组聚合，取每组最小的start_dt和最大的stop_dt，得到合并后的区间。

这个方案可以正确处理所有三个规则的区间合并，包括重叠、连续、包含的情况。

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