sql - 连续 n 分钟超过阈值

Question

假设我有一个包含一组数据点的表，每个数据点由一个时间戳和一个值组成。如果至少有 N 个连续记录(按时间戳排序)高于给定值 X，我将如何编写返回 true (1) 的查询，否则返回 false (0)？

以下对 3 个连续记录执行此操作，但不适用于一般情况，而无需构建动态查询并添加更多“并存在”情况:

truncate table tblData
insert into tblData values ('1-jul-2013 13:01:00', 64)
insert into tblData values ('1-jul-2013 13:02:00', 13)
insert into tblData values ('1-jul-2013 13:03:00', 7)
insert into tblData values ('1-jul-2013 13:04:00', 17)
insert into tblData values ('1-jul-2013 13:05:00', 7)
insert into tblData values ('1-jul-2013 13:06:00', 18)
insert into tblData values ('1-jul-2013 13:07:00', 9)
insert into tblData values ('1-jul-2013 13:08:00', 20)
insert into tblData values ('1-jul-2013 13:09:00', 12)
insert into tblData values ('1-jul-2013 13:10:00', 21)
insert into tblData values ('1-jul-2013 13:11:00', 22)
insert into tblData values ('1-jul-2013 13:12:00', 3)
insert into tblData values ('1-jul-2013 13:13:00', 22)
insert into tblData values ('1-jul-2013 13:14:00', 20)

declare @x as int = 10

select count(*)
from tblData a
where a.dt in
 (select dt from tblData b where b.value > @x and b.dt >= a.dt and b.dt < DATEADD(minute,1,a.dt)) and exists
 (select dt from tblData b where b.value > @x and b.dt >= DATEADD(minute,1,a.dt) and b.dt < DATEADD(minute,2,a.dt)) and exists
 (select dt from tblData b where b.value > @x and b.dt >= DATEADD(minute,2,a.dt) and b.dt < DATEADD(minute,3,a.dt))

想法？

Answer 1

基本上，您需要一个标志的累积总和，表明该列超过值 @x .

事实证明，您可以使用 row_number() 使用一些技巧来做到这一点。 .使用 row_number() 枚举所有行(按时间顺序)。然后，枚举所有行，按标志分区。它们之间的差异将是标识一组连续行的常量。然后，通过聚合，我们可以获得标志为真(或假)的连续值的最长长度:

select seqnum - seqnum_flag, count(*)
from (select d.*,
             row_number() over (order by dt) as seqnum,
             row_number() over (partition by (case when val > @x then 1 else 0 end)
                                order by dt) as seqnum_flag
      from tblData d
     ) d
where val > @x
group by (seqnum - seqnum_flag);

这将返回每个序列及其长度。这应该为您提供继续操作所需的信息。

你可以看到它在 SQL Fiddle 上工作.