MS SQL 2014 - 日期范围内的重复出现次数

Question

我有一个表，用于跟踪为特定Device_IDs创建的Incident_IDs的日期时间，我正在尝试找到一种方法来跟踪一系列日期的慢性问题。 慢性问题的定义是在过去 5 天内产生 3 次或更多Incident_IDs的任何Device_ID。 我需要能够搜索一系列不同的日期（主要是每月）。

给定表：

IF OBJECT_ID('tempdb.dbo.#temp') IS NOT NULL
DROP TABLE #temp
CREATE TABLE #temp
    (Device_ID INT,
    Incident_ID INT,
    Incident_Datetime DATETIME)
INSERT INTO #temp
VALUES
    (2,1001,'2016-02-01'),
    (3,1002,'2016-02-02'),
    (2,1003,'2016-02-09'),
    (2,1004,'2016-02-10'),
    (5,1005,'2016-02-12'),
    (2,1006,'2016-02-13'),
    (5,1007,'2016-02-14'),
    (5,1008,'2016-02-15'),
    (3,1009,'2016-02-18'),
    (3,1010,'2016-02-19'),
    (3,1011,'2016-02-20'),
    (5,1012,'2016-02-21'),
    (3,1013,'2016-03-18'),
    (3,1014,'2016-03-19'),
    (3,1015,'2016-03-20');

2016年2月慢性病的预期结果是：

Device_ID   Incident_ID Incident_Datetime
2           1003            2/9/16 0:00
2           1004            2/10/16 0:00
2           1006            2/13/16 0:00
3           1009            2/18/16 0:00
3           1010            2/19/16 0:00
3           1011            2/20/16 0:00
5           1005            2/12/16 0:00
5           1007            2/14/16 0:00
5           1008            2/15/16 0:00

我尝试了以下查询，它向我显示了事件的升序计数，并允许我找到那些有慢性问题的device_ids，但我很难隔离构成慢性问题的所有事件，同时排除那些发生在 3 天范围之外的异常值。

SELECT c.Device_ID, c.Incident_ID, c.Incident_Datetime,
    (SELECT COUNT(*)
    FROM #temp AS t
    WHERE
        c.Device_ID = t.Device_ID
        AND
        t.Incident_Datetime BETWEEN DATEADD(DAY,-5,c.Incident_Datetime) AND c.Incident_Datetime) AS Incident_Count
FROM #temp AS c
WHERE
    c.Incident_Datetime >= '2016-02-01'
    AND
    c.Incident_Datetime < '2016-03-01'
ORDER BY 
    Device_ID, Incident_Datetime

Answer 1

这可能不如杰克的回答那么好，但这里有一个可能有效的替代解决方案：

        WITH cte AS 
        (
          SELECT tmp.Device_ID, tmp.Incident_Datetime FROM #temp AS tmp
          CROSS APPLY
          ( 
            SELECT Device_ID 
            FROM #temp AS t 
            WHERE tmp.Device_ID = t.Device_ID AND t.Incident_Datetime BETWEEN DATEADD(d,-5,tmp.Incident_Datetime) AND tmp.Incident_Datetime
            GROUP BY Device_ID HAVING COUNT(Incident_ID) >= 3 
          ) p
          WHERE tmp.Incident_Datetime BETWEEN '02-01-2016' AND '03-01-2016'
        )        
        SELECT f.*
        FROM #temp f
        INNER JOIN cte
        ON f.Device_ID = cte.Device_ID
        WHERE f.Incident_Datetime BETWEEN DATEADD(d,-5,cte.Incident_Datetime) AND cte.Incident_Datetime 
        GROUP BY f.Device_ID, f.Incident_ID, f.Incident_Datetime
        ORDER BY f.Device_ID, f.Incident_Datetime

Answer 2

这个怎么样...

DECLARE @StartDate datetime, @EndDate datetime
SET @StartDate='2016-02-01'
SET @EndDate='2016-03-01'
SELECT c.Device_ID, c.Incident_ID, c.Incident_DateTime FROM #temp c
INNER JOIN (SELECT t.Device_ID, Count(*) FROM #temp
    WHERE t.Incident_DateTime BETWEEN DATEADD(dd, -3, c.Incident_DateTime) AND DATEADD(dd, +3, c.Incident_DateTime)
    GROUP BY t.Device_ID
    HAVING Count(*) > 2)) t ON c.Device_ID = t.Device_ID
AND c.Incident_DateTime BETWEEN @StartDate AND @EndDate
ORDER BY 
c.Device_ID, c.Incident_Datetime

Answer 3

以下是在

总共 n 天内导出正在运行的事件的方法：

with
incidents as (
    select * from #temp cross apply (
    select incident_datetime, 1 union all
    select incident_datetime + 5, -1) x(dt, delta)),
rolling as (
    select *, incidents_in_range = sum(delta)
        over (partition by device_id order by dt)
    from incidents)
select t.* from #temp t join rolling r
    on r.device_id=t.device_id
    and t.incident_datetime between r.incident_datetime - 5 and r.incident_datetime
where r.incidents_in_range >= 3

..基本上找到"5天内3个事件"达到的点，然后重新连接以包括5天内的事件。