SQL服务器-在时间范围内选择频繁记录

Question

我正在寻找一种方法来选择所有记录，其中一列具有相同的记录，并且日期时间彼此之间的间隔不超过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，因为受试者数量不够多，或者超出了规定的时间范围。

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

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

有没有办法只在很短的时间内看到类似的记录？提前谢谢大家！

Answer 1

如果您希望对彼此在5分钟内的记录进行分组，那么您可以使用递归查询对日期范围进行“分组”，如下所示

with data
  as (select *,row_number() over(partition by subject order by createddatetime) as rnk
        from t
        )
    ,cte
    as(select id,subject,createddatetime as begin_date, createddatetime,cast(1 as int) as grp
         from data
       where rnk=1  
       union all
       select b.id
             ,b.subject
             ,b.createddatetime
             ,case when datediff(minute,a.createddatetime,b.createddatetime) > 5 then 
                    b.createddatetime
                   else
                    a.createddatetime
               end as createddatetime
             ,case when datediff(minute,a.createddatetime,b.createddatetime) > 5 then 
                    a.grp+1
                   else
                    a.grp
               end as grp  
         from cte a
         join t b
           on a.id+1=b.id
          and a.subject=b.subject 
       )
     select * from cte  order by 1

 +----+---------+-------------------------+-------------------------+-----+
| id | subject |       begin_date        |     createddatetime     | grp |
+----+---------+-------------------------+-------------------------+-----+
|  1 | A       | 2020-09-28 11:01:00.000 | 2020-09-28 11:01:00.000 |   1 |
|  2 | A       | 2020-09-28 11:02:00.000 | 2020-09-28 11:01:00.000 |   1 |
|  3 | A       | 2020-09-28 11:03:00.000 | 2020-09-28 11:01:00.000 |   1 |
|  4 | A       | 2020-09-28 11:03:09.000 | 2020-09-28 11:01:00.000 |   1 |
|  5 | A       | 2020-09-28 11:04:52.000 | 2020-09-28 11:01:00.000 |   1 |
|  6 | A       | 2020-09-28 11:15:00.000 | 2020-09-28 11:15:00.000 |   2 |
|  6 | A       | 2020-09-28 11:17:00.000 | 2020-09-28 11:17:00.000 |   2 |
|  7 | B       | 2020-09-28 11:20:00.000 | 2020-09-28 11:20:00.000 |   1 |
|  8 | B       | 2020-09-28 11:20:00.000 | 2020-09-28 11:20:00.000 |   1 |
|  9 | B       | 2020-09-28 11:20:00.000 | 2020-09-28 11:20:00.000 |   1 |
+----+---------+-------------------------+-------------------------+-----+

DB小提琴链接

https://dbfiddle.uk/?rdbms=sqlserver_2019

Answer 2

您可以在每个记录前面和后面达到峰值，以分钟为单位查找时差，然后仅保留在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;

请参阅下面的演示链接，以获取使用示例数据运行的示例。