时间戳

首页 > 解决方案 > 如何按日期容差对记录进行分组？

sql-server - 如何按日期容差对记录进行分组？

问题描述

DROP TABLE IF EXISTS  #Groups;

CREATE TABLE #Groups 
(
     [entity] nvarchar(30), 
     [workItem] nvarchar(255), 
     [CreatedDate] datetime
)

INSERT INTO #Groups ([entity], [workItem], [CreatedDate])
VALUES
( N'5002', N'AG', N'2020-09-04T13:24:00.823' ), 
( N'5002', N'AG', N'2020-09-04T13:23:05.103' ), 
( N'5002', N'AG', N'2020-09-04T14:23:05.103' ), 
( N'5002', N'SH', N'2020-09-04T13:26:42.367' ), 
( N'5002', N'SH', N'2020-09-04T13:27:17.25'  ), 
( N'5003', N'SH', N'2020-10-04T15:36:42.367' ), 
( N'5003', N'SH', N'2020-10-04T15:37:17.25'  );

预期结果：

entity  workItem    CreatedDate
--------------------------------------------
5002    AG          2020-09-04 13:24:00.823
5002    AG          2020-09-04 14:23:05.103
5002    SH          2020-09-04 13:27:17.250
5003    SH          2020-10-04 15:37:17.250

基本上，我需要按实体、工作项和日期进行分组，容差为一分钟。

这是我的尝试（虽然我没有走得太远）：

SELECT
    t1.entity,
    t1.workItem,
    t1.CreatedDate,
    LAG(t1.CreatedDate, 1) OVER (PARTITION BY   t1.entity, t1.workItem ORDER BY t1.CreatedDate) AS CreateDate_new
FROM 
    #Groups t1
LEFT JOIN 
    #Groups t2 ON t2.entity = t1.entity
                AND t2.workItem = t1.workItem
                AND t2.CreatedDate >= t1.CreatedDate
                AND (DATEDIFF(MINUTE, t1.CreatedDate, t2.CreatedDate) < 1)
GROUP BY 
    t1.entity, t1.workItem, t1.CreatedDate
ORDER BY 
    t1.workItem

标签： sql-servertsql

解决方案

我认为这被称为“差距和孤岛”问题，我们在 SO 上有很多这样的问题。

下面的方法是

  • 找到并把所有在 1 分钟内的人分组
  • 然后从这些组中找到相关值。

这是执行此操作的一种方法

WITH Groups_Flagged AS
    (SELECT [Entity], [workItem], [CreatedDate],
            CASE WHEN DATEDIFF(second, LAG([CreatedDate], 1) OVER (PARTITION BY [Entity], [workItem] ORDER BY [CreatedDate]), [CreatedDate]) < 60 THEN 0 ELSE 1 END AS NewGrp_Flag
     FROM #Groups
    ),
Groups_Grouped AS
    (SELECT  [Entity], [workItem], [CreatedDate], SUM(NewGrp_Flag) OVER (ORDER BY [Entity], [workItem], [CreatedDate]) AS GrpNum
     FROM Groups_Flagged
     )
SELECT [Entity], [workItem], MAX([CreatedDate]) AS [CreatedDate]
FROM Groups_Grouped
GROUP BY [Entity], [workItem], [GrpNum]
ORDER BY [Entity], [workItem];

Groups_FlaggedCTE 标记哪些记录是“新的”，例如，不在该实体/工作项组合的先前记录的 1 分钟（60 秒）内。它将新记录标记为“1”，其他记录为“0”。

然后，Groups_GroupedCTE 使用这些 1 和 0 的总和来为这些集群中的每一个创建组编号。

最终选择按 Entity、WorkItem 和新组号分组，并获得这些的最大 CreatedDate。

请注意，尽管这会将一系列活动视为一个组（例如，如果您有 5 个创建者，所有 50 人相隔，它算作一个）。如果您不希望发生这种情况，则需要明确指定如何处理这些链。

（PS 感谢您拥有创建数据的脚本 - 它使回答这些问题变得更加容易！）

推荐阅读