SQL:从第一次出现到接下来五分钟计算某个值的出现次数，然后再次对下一次出现重复相同的操作

Question

我需要找到一个值说 34 从第一次出现到接下来的 5 分钟发生的次数。

然后在 5 分钟后再次执行相同的操作，再次获取值为 20 的记录，查看每个设备在接下来的 5 分钟内发生了多少次

假设我有下表：

DevID      value             DateTime
--------------------------------------------------
99       20               18-12-2016 18:10
99       34               18-12-2016 18:11
99       34               18-12-2016 18:12
99       20               18-12-2016 18:15
23       15               18-12-2016 18:16
28       34               18-12-2016 18:17
23       15               18-12-2016 18:18
23       12               18-12-2016 18:19
99       20               18-12-2016 18:20
99       34               18-12-2016 18:21
99       34               18-12-2016 18:22
99       34               18-12-2016 18:23
99       34               18-12-2016 18:24
99       34               18-12-2016 18:25

我对34号感兴趣。我想检查数字 34 的第一次出现，获取它的时间，然后计算这个数字 (34( 在接下来的 5 分钟内发生了多少次。基本上获取从第一次发生到发生+5分钟的记录，并计算其中有多少有34个，如果超过3个列出该设备名称。

对下一条记录重复相同的操作，直到接下来的 5 分钟。 所以在上面的情况下，记录 99 Will 在 18-12-2016 18：11 第一次有 34 次，但随后我们在接下来的 3 分钟内没有得到超过 5 个 34个记录，但是我们再次在 18-12-2016 18：21 得到 34 个，并在接下来的 3 分钟内获得了超过 3 个条目 34

个因此，上表的预期输出将是设备 ID 99。

已编辑

我只对找到值 34 感兴趣。因此，在 5 分钟的间隙内找到所有这些重复值不需要额外的复杂性。 只是想知道我们在 34 分钟的时间间隔之间有 5 次重复超过 3 次的设备(这应该是可更改的，我也可以将其硬编码为 10 次(。

Answer 1

最有效的方法是使用lag()/lead()：

select t.*
from (select t.*,
lead(datetime, 2) over (partition by devid order by datetime) as next2_dt
from t
where value = 34
) t
where next2_dt <= dateadd(minute, 5, datetime);

这会提前达到第二个值，并且仅将该值的datetime与当前行上的datetime进行比较。

Answer 2

这可以按如下方式完成：

SELECT DevID
FROM t
WHERE Value = 34
AND 2 <= (
SELECT COUNT(*)
FROM t AS x
WHERE x.DevID = t.DevID
AND x.Value = t.Value
AND x.DateTime > t.DateTime
AND x.DateTime < DATEADD(MINUTE, 5, t.DateTime)
)
GROUP BY DevID

您可能希望将<替换为<=，具体取决于您如何计算 5 分钟。

Answer 3

请调整到你的RDBMS，但它应该看起来像这样：

select b.*
from (
select value, min(DateTime) as md
from the_table
group by value
) as a
join the_table as b
on a.value = b.value
and b.DateTime between a.md and a.md + interval'5'minute