基于T-SQL按最大时间间隔生成<from>-<to>时间列表的问题

需求说明 #

将DATETIME列表转换为<from_dt>-<to_dt>时间对，规则是：同一时间对中的相邻时间间隔不得超过指定的@maxDiff分钟；若相邻时间间隔超过阈值，则各自单独成为一组（from和to均为自身）。

正常场景示例 #

当@maxDiff = 100时，输入时间列表：

2022-01-01 13:00:00.000
2022-01-01 14:00:00.000
2022-01-01 15:00:00.000

期望输出：

from                        to
2022-01-01 13:00:00.000     2022-01-01 15:00:00.000

边缘场景问题 #

当所有时间间隔均超过@maxDiff时，原代码输出错误：

测试数据与原代码

DECLARE @maxDiff INT = 100;

DECLARE @datesTmp TABLE (   
    t DATETIME
)

INSERT INTO @datesTmp 
VALUES 
('2022-01-01 13:00:00'),
('2022-01-02 14:00:00'),
('2022-01-03 15:00:00')

DECLARE @dates TABLE (
    rn INT
    ,t DATETIME
    ,d INT
    )

INSERT INTO @dates
SELECT ROW_NUMBER() OVER (
        ORDER BY t
        ) rn
    ,t
    ,datediff(minute, t, lead(t) OVER (
            ORDER BY t
            )) d
FROM @datesTmp

;WITH cte
AS (
    SELECT *
    FROM @dates
    WHERE d IS NULL
        OR d > @maxDiff
    
    UNION
    
    SELECT *
    FROM @dates
    WHERE rn IN (
            SELECT rn + 1
            FROM @dates
            WHERE d > @maxDiff
            )
    
    UNION
    
    SELECT TOP 1 *
    FROM @dates
    WHERE rn = 1
    )
    ,cte2
AS (
    SELECT ROW_NUMBER() OVER (
            ORDER BY t
            ) rn
        ,t
    FROM cte
    )
SELECT min(t) tFrom
    ,max(t) tTo
FROM cte2
GROUP BY (rn - 1) / 2

原代码错误输出

from                        to
2022-01-01 13:00:00.000     2022-01-02 14:00:00.000
2022-01-03 15:00:00.000     2022-01-03 15:00:00.000

期望正确输出

from                        to
2022-01-01 13:00:00.000     2022-01-01 13:00:00.000 
2022-01-02 14:00:00.000     2022-01-02 14:00:00.000 
2022-01-03 15:00:00.000     2022-01-03 15:00:00.000

问题根源分析 #

原代码的CTE组合逻辑存在缺陷：

1. 强制加入第一条记录的逻辑（第三个UNION）会导致第一条时间被错误地和第二条时间归为一组，即使两者间隔超过阈值；
2. 依赖(rn-1)/2的分组方式仅适用于成对出现的边界记录，当所有间隔都超限时，CTE中的记录重复或错位，导致分组错误。

优化后的解决方案 #

通过计算分组标识的方式，为每个时间标记所属的分组：

• 第一条时间默认属于分组1；
• 后续时间若与前一个时间的间隔超过@maxDiff，则分组编号+1，否则沿用前一个分组编号；
• 最后按分组编号取最小和最大时间，得到正确的时间对。

优化代码 #

DECLARE @maxDiff INT = 100;

DECLARE @datesTmp TABLE (   
    t DATETIME
)

INSERT INTO @datesTmp 
VALUES 
('2022-01-01 13:00:00'),
('2022-01-02 14:00:00'),
('2022-01-03 15:00:00');

WITH ranked_dates AS (
    SELECT 
        t,
        ROW_NUMBER() OVER (ORDER BY t) AS rn
    FROM @datesTmp
),
grouped_dates AS (
    SELECT
        t,
        -- 计算分组标识：当前时间与前一个时间间隔超过阈值则新建分组
        SUM(CASE WHEN DATEDIFF(minute, LAG(t) OVER (ORDER BY rn), t) > @maxDiff THEN 1 ELSE 0 END) 
            OVER (ORDER BY rn ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) + 1 AS group_id
    FROM ranked_dates
)
SELECT 
    MIN(t) AS [from],
    MAX(t) AS [to]
FROM grouped_dates
GROUP BY group_id
ORDER BY [from];

代码验证 #

• 边缘场景下输出符合期望；
• 正常场景（三个时间间隔均≤100分钟）下，会输出单个时间对2022-01-01 13:00:00到2022-01-01 15:00:00，符合需求。

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