CODEWITHSUNDEEP
sqlsql-server-2008
Dan Bechard
Dan Bechard

Reputation: 5291

Value over threshold for n consecutive minutes

Assume I have a table containing a set of data points, each consisting of a timestamp and a value. How would I write a query that returns true (1) if there are at least N consecutive records (ordered by timestamp) that are above a given value X, false (0) otherwise?

The following does this for 3 consecutive records, but does not work for the general case without building a dynamic query and adding more "and exists" cases:

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))

Ideas?

Upvotes: 2

Views: 953

Answers (2)

Andomar
Andomar

Reputation: 238048

You can use an over ... rows N preceding clause to calculate the minimum over the last N rows. For example, this displays 1 if this row and the last 3 rows have a value larger than 10:

select  *
,       case when min(val) over (order by dt rows 3 preceding) > 10 then 1 else 0 end
from    tblData

Example at SQL Fiddle.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Basically, you want a cumulative sum of a flag saying that the column exceeds the value @x.

It turns out that you can do this with some tricks using row_number(). Enumerate all the rows using row_number() (in time order). Then, enumerate all the rows, partitioning by the flag. The difference between these will be a constant that identifies a group of consecutive rows. Then, with aggregation, we can get the longest length of consecutive values where the flag is true (or false):

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);

This returns each sequence and the length of it. This should give you the information you need to proceed.

You can see it work on SQL Fiddle.

Upvotes: 3

Related Questions