我需要找到从第一次出现到接下来的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个则列出该设备名称。
对下一个记录重复相同的操作,直到下一个5分钟为止34个。因此,在上述情况下,记录99将在18-12-2016 18:11第一次拥有34条记录,但是接下来的5分钟我们没有得到超过34条记录的3条记录,但是在18-12-2016我们又得到了34条记录18:21,并且在接下来的5分钟内获得了3个条目,共34个
因此,上表的预期输出将是设备ID 99。
我对仅找到值34感兴趣。因此,不需要在5分钟内找到所有这些重复值的额外复杂性。只是想知道我们在5分钟的时间间隔内对34个设备重复了3次以上(这应该是可更改的,我也可以将其硬编码为10次)。
最有效的方法是使用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
当前行的比较。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句