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

Question

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

然后在 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 则列出该设备名称。

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

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

已编辑

我只对找到值 34 感兴趣。因此不需要在 5 分钟间隔内找到所有此类重复值的额外复杂性。
只是想知道在 5 分钟的时间间隔之间我们有 34 次重复超过 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在当前行。