时间戳

首页 > 解决方案 > SQL Server - 在时间范围内选择频繁的记录

sql - SQL Server - 在时间范围内选择频繁的记录

问题描述

我正在寻找一种方法来选择一列具有相同记录且日期时间在 5 分钟内的所有记录。我管理一个票务系统，并试图查看在 5 分钟时间内创建的所有类似票证。请参阅下面的示例数据集：

|ID |Subject    |CreatedDateTime    |
-------------------------------------
|1  |A          |2020-09-28 11:01:00|
|2  |A          |2020-09-28 11:02:00|
|3  |A          |2020-09-28 11:03:00|
|4  |A          |2020-09-28 11:03:09|
|5  |A          |2020-09-28 11:04:52|
|6  |A          |2020-09-28 11:15:00|
|7  |B          |2020-09-28 11:20:00|
|8  |B          |2020-09-28 11:20:00|
|9  |B          |2020-09-28 11:20:00|

我的目标是只选择记录 1-5，因为 5 个相同的主题并且它们都是在 5 分钟内创建的。6-10不应选择，因为Subject数量不够多，或者超出了规定的时间范围。

下面是我到目前为止的一个测试查询，但它没有考虑 5 分钟的范围（我只回顾 1 周，因此该条款）：

SELECT Subject,COUNT(*)
FROM TableName
WHERE CreatedDateTime > DATEADD(day, -7, GETDATE())
GROUP BY Subject
HAVING COUNT(*) > 5 
ORDER BY COUNT(*) DESC;

有没有办法只在接近的时间范围内看到类似的记录？谢谢大家！

标签： sqlsql-server

解决方案

您可以为每条记录前后达到峰值，找出以分钟为单位的时间差，然后仅保留在 5 分钟内与至少一条其他记录相连的记录：

WITH cte AS (
    SELECT *,
           DATEDIFF(minute,
               LAG(CreatedDateTime) OVER (PARTITION BY Subject
                                          ORDER BY CreatedDateTime),
               CreatedDateTime) AS LagCreatedDateTime,
           DATEDIFF(minute,
               CreatedDateTime,
               LEAD(CreatedDateTime) OVER (PARTITION BY Subject
                                           ORDER BY CreatedDateTime))
                AS LeadCreatedDateTime
    FROM TableName
)

SELECT *
FROM cte
WHERE LagCreatedDateTime <= 5 OR LeadCreatedDateTime <= 5;

有关使用示例数据运行的示例，请参阅下面的演示链接。

演示

推荐阅读