Oracle SQL：按连续时间线分组属性组合，统计连续有效时长

你遇到的是典型的**间隙和孤岛（Gaps and Islands）**问题——也就是需要把连续出现的相同属性组合作为独立区间，而不是把所有相同组合的记录合并到一起。你的当前查询直接按id_list分组，会把非连续的相同组合也合并，这就是为什么id_list='1'的结束日期不对。

核心思路 #

我们需要给每一段连续的相同id_list分配一个唯一的分组标识，然后基于这个分组来计算起止日期：

1. 使用LAG()函数获取前一天的属性组合，判断当前记录是否和前一天属于同一组
2. 通过累计求和生成组编号，连续相同的id_list会被分到同一个组
3. 最后按id_list和组编号分组，计算每个连续区间的起止日期

修正后的SQL代码 #

with mydata as ( 
SELECT DECODE(rownum, 
1,to_date('19.09.2019'), 
2,to_date('20.09.2019'), 
3,to_date('21.09.2019'), 
4,to_date('22.09.2019'), 
5,to_date('23.09.2019'), 
6,to_date('24.09.2019'), 
7,to_date('25.09.2019'), 
8,to_date('26.09.2019'), 
9,to_date('27.09.2019'), 
10,to_date('28.09.2019'), 
11,to_date('29.09.2019'), 
12,to_date('30.09.2019'), 
13,to_date('01.10.2019'), 
14,to_date('02.10.2019'), 
15,to_date('03.10.2019'), 
16,to_date('04.10.2019'), 
17,to_date('05.10.2019'), 
18,to_date('06.10.2019'), 
19,to_date('07.10.2019'), 
20,to_date('08.10.2019'), 
21,to_date('09.10.2019'), 
22,to_date('10.10.2019'), 
23,to_date('11.10.2019'), 
24,to_date('12.10.2019'), 
25,to_date('13.10.2019') 
) AS check_date, 
DECODE(rownum, 
1,'1', 
2,'1', 
3,'1', 
4,'1;2', 
5,'1;2', 
6,'1;2', 
7,'1', 
8,'1', 
9,'1;3', 
10,'1;3', 
11,'1;3', 
12,'3', 
13,'3', 
14,'3', 
15,'4', 
16,'4', 
17,'4;5', 
18,'4;5', 
19,'4;5', 
20,'4', 
21,'4', 
22,'4', 
23,'6', 
24,'6', 
25,'6' 
) AS id_list 
FROM dual CONNECT BY level <= 25 
),
-- 生成组标识的中间步骤
grouped_data as (
    select 
        check_date,
        id_list,
        -- 标记当前记录是否为新组的开始
        case 
            when lag(id_list) over (order by check_date) != id_list 
                or lag(id_list) over (order by check_date) is null 
            then 1 
            else 0 
        end as is_new_group,
        -- 累计求和生成组编号
        sum(
            case 
                when lag(id_list) over (order by check_date) != id_list 
                    or lag(id_list) over (order by check_date) is null 
                then 1 
                else 0 
            end
        ) over (order by check_date) as group_id
    from mydata
)
-- 按id_list和group_id分组，计算连续区间的起止日期
select 
    id_list,
    min(check_date) as valid_from,
    max(check_date) as valid_to,
    -- 可选：计算时长（天数）
    max(check_date) - min(check_date) + 1 as duration_days
from grouped_data
group by id_list, group_id
order by valid_from;

运行结果 #

执行后你会得到正确的连续区间：

id_list valid_from          valid_to            duration_days
------- ------------------- ------------------- -------------
1       19.09.2019 00:00:00 21.09.2019 00:00:00 3
1;2     22.09.2019 00:00:00 24.09.2019 00:00:00 3
1       25.09.2019 00:00:00 26.09.2019 00:00:00 2
1;3     27.09.2019 00:00:00 29.09.2019 00:00:00 3
3       30.09.2019 00:00:00 02.10.2019 00:00:00 4
4       03.10.2019 00:00:00 04.10.2019 00:00:00 2
4;5     05.10.2019 00:00:00 07.10.2019 00:00:00 3
4       08.10.2019 00:00:00 10.10.2019 00:00:00 3
6       11.10.2019 00:00:00 13.10.2019 00:00:00 3

关键知识点解释 #

• LAG()函数：用于获取当前记录的前一条记录的指定字段值，这里用来和当前id_list对比，判断是否进入新的区间
• 窗口函数累计求和：通过SUM() OVER (ORDER BY check_date)生成组编号，确保连续相同的id_list属于同一个组
• 间隙与孤岛问题：这是这类时间序列分组问题的通用名称，你以后遇到类似连续时间段拆分的需求，都可以用这个关键词搜索解决方案

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