SQL Server中基于time_connects_flag对有序数据集分组的实现

示例数据创建 #

首先通过以下SQL创建临时表并插入示例数据：

drop table if exists #test;
create table #test (id char(4), product char(1), begin_date date, end_date date);
insert into #test (id, product, begin_date, end_date) values('0001', 'T','2009-02-26','2010-02-26');
insert into #test (id, product, begin_date, end_date) values('0001', 'T','2011-02-26','2012-02-26');
insert into #test (id, product, begin_date, end_date) values('0001', 'T','2012-02-26','2013-02-26');
insert into #test (id, product, begin_date, end_date) values('0002', 'T','2013-02-26','2014-02-26');
insert into #test (id, product, begin_date, end_date) values('0002', 'T','2014-02-26','2015-01-02');
insert into #test (id, product, begin_date, end_date) values('0002', 'T','2015-01-02','2015-08-01');
insert into #test (id, product, begin_date, end_date) values('0002', 'T','2015-08-01','2016-08-01');
insert into #test (id, product, begin_date, end_date) values('0002', 'T','2016-08-01','2017-08-01');
insert into #test (id, product, begin_date, end_date) values('0002', 'T','2017-08-01','2018-08-01');
insert into #test (id, product, begin_date, end_date) values('0002', 'T','2019-08-01','2020-08-01');

原查询生成time_connects_flag字段，标识当前行的end_date是否与下一行的begin_date对齐：

select
    *,
    case 
        when lead(begin_date,1) over (partition by id, product order by begin_date) = end_date then 1
        else 0
    end as time_connects_flag
from
    #test;

解决方案 #

要生成分组标识，核心是识别每个分组的起始行，再通过累计求和得到唯一组ID，具体步骤如下：

1. 标记分组起始行：当某行是分区内的第一行，或者上一行的time_connects_flag为0（说明与当前行时间不连续），则标记为新组起始。
2. 累计生成组ID：对起始行标记进行分区内的累计求和，同一连续时间区间的行会得到相同的组ID。

完整SQL代码：

with base_data as (
    select
        *,
        case 
            when lead(begin_date,1) over (partition by id, product order by begin_date) = end_date then 1
            else 0
        end as time_connects_flag
    from #test
),
group_starts as (
    select
        *,
        case
            when row_number() over (partition by id, product order by begin_date) = 1 then 1
            when lag(time_connects_flag,1) over (partition by id, product order by begin_date) = 0 then 1
            else 0
        end as is_new_group
    from base_data
)
select
    id,
    product,
    begin_date,
    end_date,
    time_connects_flag,
    sum(is_new_group) over (partition by id, product order by begin_date) as group_id
from group_starts
order by id, begin_date;

结果说明 #

执行上述SQL后，会得到带有group_id的结果：

• id='0001'：第一行与下一行时间不连续，单独为组1；第二、三行时间连续，归为组2。
• id='0002'：前五行时间连续（2013-02-26至2018-08-01），归为组1；第六行与前一行时间断开，单独为组2。

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