基于7天窗口（含间隔）的行分组优化：替代低效WHILE语句的方案

需求描述 #

我有一个存储日期的表，需要将日期按7天为一个区块进行分组，规则如下：任何在前一个区块结束后出现的日期，都作为新7天周期的起始。

期望输出示例：

Date            Block  
---------------------
2023-03-02      1
2023-03-03      1
2023-03-04      1
2023-03-10      2
2023-03-16      2
2023-04-04      3
2023-05-02      4
2023-05-05      4

目前我使用WHILE语句实现了该分组算法，但在批量处理场景下运行速度过慢，请问是否有其他更高效的实现方式？

原WHILE循环实现代码：

create table #dates_to_assign (
    [Date] date
)
insert into #dates_to_assign values 
('2023-03-02'), ('2023-03-03'), ('2023-03-04'), ('2023-03-10'),
('2023-03-16'), ('2023-04-04'), ('2023-05-02'), ('2023-05-05')

create table #dates_assigned (
    [Date] date,
    [Block] int
)

declare @curr_date date = (select min([Date]) from #dates_to_assign)
declare @block int = 1

WHILE EXISTS(SELECT TOP 1 * from #dates_to_assign)
BEGIN
    insert into #dates_assigned
    select [Date], [Block] = @block
    from #dates_to_assign 
    where DATEDIFF(DAY, @curr_date, [Date]) < 7

    delete from #dates_to_assign
    where DATEDIFF(DAY, @curr_date, [Date]) < 7

    set @curr_date = (select min([Date]) from #dates_to_assign)
    set @block = @block + 1
END

select *
from #dates_assigned

高效实现方案：窗口函数集合式操作 #

可以通过累积最大值判断+累积求和实现纯集合式分组，彻底避免循环带来的性能损耗，适合大数据量批量处理场景。

实现代码 #

WITH date_groups AS (
    SELECT 
        [Date],
        -- 标记当前日期是否触发新区块：如果当前日期与前一个区块的起始日差≥7，或为第一个日期，则标记为新起点
        CASE 
            WHEN DATEDIFF(DAY, 
                          COALESCE(LAG(current_block_start) OVER (ORDER BY [Date]), [Date]), 
                          [Date]) >=7
            THEN [Date]
            ELSE LAG(current_block_start) OVER (ORDER BY [Date])
        END AS current_block_start
    FROM (
        -- 初始化第一个日期的区块起始日
        SELECT [Date], [Date] AS current_block_start
        FROM #dates_to_assign
    ) t
),
block_numbers AS (
    SELECT 
        [Date],
        -- 对新区块起始日的变化计数，生成区块编号
        DENSE_RANK() OVER (ORDER BY current_block_start) AS Block
    FROM date_groups
)
SELECT [Date], Block
FROM block_numbers
ORDER BY [Date];

逻辑说明 #

1. 内层子查询初始化每个日期的初始区块起始日为自身日期。
2. date_groups CTE：用LAG函数获取前一个日期的区块起始日，判断当前日期与该起始日的间隔是否≥7：
   • 若≥7，当前日期成为新的区块起始日；
   • 否则沿用前一个区块的起始日。
3. block_numbers CTE：对所有区块起始日进行DENSE_RANK排名，得到每个日期对应的区块编号。

性能优势 #

• 全程基于SQL集合操作，避免了循环中多次插入、删除、查询的IO开销；
• 数据库引擎可利用日期索引进行优化，处理百万级以上数据时性能远优于WHILE循环；
• 不需要创建额外临时表，代码更简洁易维护。

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