用于检索具有相同ID和类型但仅相隔3分钟的记录的SQL查询

Question

我正试图编写一个查询来从一个表中检索ID，该表如下所示：

021-11-01 21:43:48.0000000021-09-02 20:12:40.0000000

ID
1234	开始
1234	开始	2021-11-01 21:44:40.0000000000
1234	end	2021-11-04 15:27:50.0000000000
4567	开始
4567	开始	2021-09-02 23:01:11.00000000

假设您的表被称为DATA，这应该有效：

SELECT DISTINCT t1.ID
FROM 
DATA t1 JOIN
DATA t2 ON t1.ID = t2.ID 
AND t1.TYPE = t2.TYPE 
AND t1.CREATED_TIME <> t2.CREATED_TIME 
AND (ABS(DATEDIFF(MINUTE, t1.CREATED_TIME, t2.CREATED_TIME))  < 3)

自加入效率低下您应该使用这个的窗口功能

SELECT ID
FROM (
SELECT *,
PrevVal = LAG(CREATED_TIME, 1, '19000101') OVER (PARTITION BY ID, Type ORDER BY CREATED_TIME)
FROM YourTable t
) t
WHERE DATEADD(minute, 3, t.PrevVal) >= t.CREATED_TIME
GROUP BY ID;

db<gt；小提琴