sql - 如何按 DATEDIFF 对行进行分组？

Question

希望你能帮助我。

如果 10 分钟内有 2 名或更多员工进入房间，我需要显示 HH_Solution_Audit 表中的记录。要求如下:

1. 仅显示时间戳 (LAST_UPDATED) 间隔小于或等于 10 分钟的事件。因此，我必须将当前行与下一行和上一行进行比较，以检查它们的 DATEDIFF 是否小于或等于 10 分钟。我已经完成了这部分。
2. 仅显示房间内持续时间小于或等于 10 分钟的不同 STAFF_GUID 数量至少为 2 条的记录。

HH_Solution_Audit 表详细信息:

1. ID - PK
2. STAFF_GUID - 员工 ID
3. LAST_UPDATED - 员工进入房间的日期时间

这是我到目前为止所得到的。这仅满足要求#1。

CREATE TABLE HH_Solution_Audit (
ID INT PRIMARY KEY,
STAFF_GUID NVARCHAR(1),
LAST_UPDATED DATETIME
)
GO
INSERT INTO HH_Solution_Audit VALUES (1, 'b', '2013-04-25 9:01')
INSERT INTO HH_Solution_Audit VALUES (2, 'b', '2013-04-25 9:04')
INSERT INTO HH_Solution_Audit VALUES (3, 'b', '2013-04-25 9:13')
INSERT INTO HH_Solution_Audit VALUES (4, 'a', '2013-04-25 10:15')
INSERT INTO HH_Solution_Audit VALUES (5, 'a', '2013-04-25 10:30')
INSERT INTO HH_Solution_Audit VALUES (6, 'a', '2013-04-25 10:33')
INSERT INTO HH_Solution_Audit VALUES (7, 'a', '2013-04-25 10:41')
INSERT INTO HH_Solution_Audit VALUES (8, 'a', '2013-04-25 11:02')
INSERT INTO HH_Solution_Audit VALUES (9, 'a', '2013-04-25 11:30')
INSERT INTO HH_Solution_Audit VALUES (10, 'a', '2013-04-25 11:45')
INSERT INTO HH_Solution_Audit VALUES (11, 'a', '2013-04-25 11:46')
INSERT INTO HH_Solution_Audit VALUES (12, 'a', '2013-04-25 11:51')
INSERT INTO HH_Solution_Audit VALUES (13, 'a', '2013-04-25 12:24')
INSERT INTO HH_Solution_Audit VALUES (14, 'b', '2013-04-25 12:27')
INSERT INTO HH_Solution_Audit VALUES (15, 'b', '2013-04-25 13:35')

<小时/>

    DECLARE @numOfPeople INT = 2,   
              --minimum number of people that must be inside 
              --the room for @lengthOfStay minutes
            @lengthOfStay INT = 10, 
              --number of minutes of stay
            @dateFrom DATETIME = '04/25/2013 00:00',
            @dateTo DATETIME = '04/25/2013 23:59';
    WITH cteSource AS
    (
         SELECT ID, STAFF_GUID, LAST_UPDATED, 
              ROW_NUMBER() OVER (ORDER BY LAST_UPDATED) AS row_num
         FROM HH_SOLUTION_AUDIT 
              WHERE LAST_UPDATED >= @dateFrom AND LAST_UPDATED <= @dateTo
    )
    SELECT [current].ID, [current].STAFF_GUID, [current].LAST_UPDATED
    FROM
         cteSource AS [current]
    LEFT OUTER JOIN
         cteSource AS [previous] ON [current].row_num = [previous].row_num + 1
    LEFT OUTER JOIN
         cteSource AS [next] ON [current].row_num = [next].row_num - 1
    WHERE
         DATEDIFF(MINUTE, [previous].LAST_UPDATED, [current].LAST_UPDATED) 
         <= @lengthOfStay
         OR
         DATEDIFF(MINUTE, [current].LAST_UPDATED, [next].LAST_UPDATED) 
         <= @lengthOfStay  
    ORDER BY [current].ID, [current].LAST_UPDATED    

<小时/>

运行查询返回 ID:
1、2、3、5、6、7、10、11、12、13、14
这满足前一行、当前行和下一行之间的间隔小于或等于 10 分钟的要求#1。

你能帮我满足第二个要求吗？如果应用，返回的 ID 应该仅为:
13, 14

Answer 1

这是一个想法。您不需要 ROW_NUMBER 以及上一条和下一条记录。您只需要联合查询 - 一个查找所有有人在 X 分钟后检查的人，另一个查找提前 X 分钟的人。每个都使用相关子查询和 COUNT(*) 来查找匹配人数。如果数字大于您的@numOfPeople - 就是这样。

编辑:新版本:我们不再执行前后 10 分钟的两次查询，而是仅检查后面 10 分钟的情况 - 选择与 cteLastOnes 中匹配的查询。之后将进入查询的另一部分来搜索那些在这 10 分钟内实际存在的内容。最终他们和“最后的人”再次结合

WITH cteSource AS
(
    SELECT ID, STAFF_GUID, LAST_UPDATED
    FROM HH_SOLUTION_AUDIT 
    WHERE LAST_UPDATED >= @dateFrom AND LAST_UPDATED <= @dateTo
)
,cteLastOnes AS 
(
    SELECT * FROM cteSource c1
    WHERE @numOfPeople -1 <= (SELECT COUNT(DISTINCT STAFF_GUID) 
                              FROM cteSource c2 
                              WHERE DATEADD(MI,@lengthOfStay,c2.LAST_UPDATED) > c1.LAST_UPDATED 
                                AND C2.LAST_UPDATED <= C1.LAST_UPDATED 
                                AND c1.STAFF_GUID <> c2.STAFF_GUID) 
)
SELECT * FROM cteLastOnes
UNION
SELECT * FROM cteSource s
WHERE EXISTS (SELECT * FROM cteLastOnes l 
               WHERE DATEADD(MI,@lengthOfStay,s.LAST_UPDATED) > l.LAST_UPDATED 
                AND s.LAST_UPDATED <= l.LAST_UPDATED 
                AND s.STAFF_GUID <> l.STAFF_GUID)

<强> SQLFiddle DEMO - new version

SQLFiddle DEMO - old version