SQL Server实现时间差小于2分钟的相邻任务分组聚合

问题说明 #

现有任务明细表[dbo].[tasks]存储单条任务明细，表结构如下：

CREATE TABLE [dbo].[tasks](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [task_id] [int] NULL,
    [task_start_date] [datetime] NULL,
    [task_end_date] [datetime] NULL,
    [duration_sec] [int] NULL,
    [owner_name] [varchar](150) NULL,
PRIMARY KEY CLUSTERED ([id] ASC)

测试数据如下：

insert into tasks
values
 (1125, '2022-06-09 14:56:58',  '2022-06-09 15:02:26',  328,        'John Parsons')
,(1126, '2022-06-09 15:03:43',  '2022-06-09 15:33:50',  1807,       'John Parsons')
,(1127, '2022-06-09 15:34:55',  '2022-06-09 16:05:02',  1807,       NULL)
,(1128, '2022-06-09 16:06:06',  '2022-06-09 16:21:03',  897,        'John Parsons')
,(1129, '2022-06-09 16:39:56',  '2022-06-09 16:47:34',  458,        'Sarah Mitchell')
,(1129, '2022-06-09 16:50:57',  '2022-06-09 16:55:59',  302,        'Sarah Mitchell')
,(1130, '2022-06-09 17:11:26',  '2022-06-09 17:24:07',  761,        'John Parsons')
,(1131, '2022-06-10 21:11:34',  '2022-06-10 21:23:21',  707,        'Sarah Mitchell')
,(1131, '2022-06-10 21:24:35',  '2022-06-10 21:39:37',  902,        'Sarah Mitchell')
,(1132, '2022-06-10 21:45:37',  '2022-06-10 23:02:05',  4588,       NULL)

需要将任务按规则分组后写入目标表[dbo].[grouped_tasks]，目标表结构：

create table dbo.grouped_tasks(
id int identity (1,1) primary key
,task_id int
,task_start_date datetime
,task_end_date datetime
,duration_sec int
,break_duration int
,owner_name varchar(150)
)

分组规则 #

• 所有任务按task_start_date升序排序
• 相邻两条任务，若前一条的task_end_date和后一条的task_start_date间隔≤120秒，则归为同一组
• 分组后聚合规则：
  • task_id取组内最小值
  • task_start_date取组内最小值
  • task_end_date取组内最大值
  • duration_sec取组内求和值
  • break_duration为组内所有相邻任务间隔秒数之和
  • owner_name匹配对应任务归属人

预期输出结果 #

insert into grouped_tasks
values
(1125   ,'2022-06-09 14:56:58', '2022-06-09 16:21:03',  4839    ,206,   'John Parsons')
,(1129  ,'2022-06-09 16:39:56', '2022-06-09 16:47:34',  458     ,0, 'Sarah Mitchell')
,(1129  ,'2022-06-09 16:50:57', '2022-06-09 16:55:59',  302     ,0, 'Sarah Mitchell')
,(1130  ,'2022-06-09 17:11:26', '2022-06-09 17:24:07',  761     ,0, 'John Parsons')
,(1131  ,'2022-06-10 21:11:34', '2022-06-10 21:39:37',  1609    ,74,    'Sarah Mitchell')
,(1132  ,'2022-06-10 21:45:37', '2022-06-10 23:02:05',  4588    ,0, NULL)

问题原因 #

原有写法仅筛选了间隔≤120秒的相邻记录，没有生成连续的分组标识，无法正确聚合连续符合间隔规则的任务链。

实现步骤 #

1. 按task_start_date排序，用LAG窗口函数计算当前任务和上一条任务的间隔秒数
2. 标记分组边界：当前任务和上一条间隔>120秒、或是第一条任务时，记为新分组起点
3. 用窗口累加求和的方式给每条记录生成分组ID，同一连续任务链的分组ID相同
4. 按分组ID聚合，计算各目标字段，组内存在NULL归属人时自动取非空值匹配

完整实现代码 #

WITH task_with_gap AS (
    SELECT
        task_id,
        task_start_date,
        task_end_date,
        duration_sec,
        owner_name,
        -- 计算和上一条任务的间隔秒数
        DATEDIFF(SECOND, LAG(task_end_date) OVER (ORDER BY task_start_date), task_start_date) AS gap_sec
    FROM dbo.tasks
),
task_with_group_flag AS (
    SELECT
        *,
        -- 间隔超过120秒/第一条任务 标记为新分组起点
        CASE WHEN gap_sec IS NULL OR gap_sec > 120 THEN 1 ELSE 0 END AS is_new_group
    FROM task_with_gap
),
task_with_group_id AS (
    SELECT
        *,
        -- 累加新分组标记，生成连续的分组ID
        SUM(is_new_group) OVER (ORDER BY task_start_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS group_id
    FROM task_with_group_flag
)
-- 按分组ID聚合写入目标表
INSERT INTO dbo.grouped_tasks (task_id, task_start_date, task_end_date, duration_sec, break_duration, owner_name)
SELECT
    MIN(task_id) AS task_id,
    MIN(task_start_date) AS task_start_date,
    MAX(task_end_date) AS task_end_date,
    SUM(duration_sec) AS duration_sec,
    -- 第一条记录无上一条任务，间隔值替换为0
    SUM(ISNULL(gap_sec, 0)) AS break_duration,
    -- 自动取组内非空归属人
    MAX(owner_name) AS owner_name
FROM task_with_group_id
GROUP BY group_id
ORDER BY task_start_date;

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