CODEWITHSUNDEEP
mysql
Roman Rdgz
Roman Rdgz

Reputation: 13254

Checking consecutive values at a MySQL query

I have a MySQL table like this:

ID - Time - Value

And I'm getting every pair of ID, Time (grouped by ID) where Value is greater than a certain threshold. So basicaly, I'm getting every ID which has at least one time a value greater than the threshold. The query looks like this:

SELECT ID, Time FROM mydb.MYTABLE
WHERE Value>%s AND Time>=%s AND Time<=%s
GROUP BY ID

EDIT: The Time checks allow to operate in a time range of my choice between all the data which is into the table; it has nothing else to do with what I am asking.

It works perfectly, but now I want to add some filtering: I want it to avoid those times the value is greater than the threshold (let's call it alarms) if the alarm hasn't happened also the Time just before or just after. I mean: if the alarm accurs at a single, isolated instant of time instead of two consecutive instants of time, I'll consider it is a false alarm and avoid it to be returned at the query response.

Of course I can do this with a call for each Id to check for this, but I'd like to do this in a single query to make it faster. I guess I could use conditionals, but I don't have that expertise at MySQL.

Any help?

EDIT2: Example for Threshold = 10

ID - Time - Value
1  - 2004 -   9
1  - 2005 -  11
1  - 2006 -   8
2  - 2107 -  12
2  - 2109 -  13
3  - 3402 -  11
3  - 3403 -  12

In this example, only ID 3 should be a valid alarm, since 2 consecutive time values for this ID have their value > threshold. ID 1 has a single, isolated alarm, so it should be filteres. For ID 2 there are 2 alarms, but not consecutive, so it should be also filtered.

Upvotes: 1

Views: 1722

Answers (2)

valex
valex

Reputation: 24144

Something like this:

10 - is a threshold
0 - minimum of the time period
100000 - maximum of the time period


select ID, min(Time)

from
(
SELECT ID, Time,

       (select max(time) from t 
           where Time<t1.Time
           and Id=t1.Id
           and Value>10) LAG_G,
       (select max(time) from t 
           where Time<t1.Time
           and Id=t1.Id
           and Value<=10) LAG_L,
       (select min(time) from t 
           where Time>t1.Time
           and Id=t1.Id
           and Value>10) LEAD_G,
       (select min(time) from t 
           where Time>t1.Time
           and Id=t1.Id
           and Value<=10) LEAD_L

FROM t as t1
WHERE Value>10 AND Time>=0 AND Time<=100000
) t3
where ifnull(LAG_G,0)>ifnull(LAG_L,0)
      OR
      ifnull(LEAD_G,100000)<ifnull(LEAD_L,100000)

GROUP BY ID

SQLFiddle demo

This query works for searching near records.

If you need to search records by Time (+1, -1 ) as you've mentioned in the comment try this query:

select ID, min(Time) from t as t1
where Value>10
      AND Time>=%s2 AND Time<=%s1
  and 
   ( 
      Exists(select 1 from t where Value>10 
                              and Id=t1.Id
                              and Time=t1.Time-1) 
      OR 

      Exists(select 1 from t where Value>10 
                              and Id=t1.Id
                              and Time=t1.Time+1) 
   )  
group by ID

SQLFiddle demo

Upvotes: 1

jaczes
jaczes

Reputation: 1404

such alarm ?

SELECT ID, Time , count(if(value>%treshold ,1,0)) alert_active
FROM mydb.MYTABLE
WHERE Value>%s3 AND Time>=%s2 AND Time<=%s1
GROUP BY ID;

i don't understand exactly:

In this example, only ID 3 should be a valid alarm, since 2 consecutive time values for this ID have their value > threshold. ID 1 has a single, isolated alarm, so it should be filteres. For ID 2 there are 2 alarms, but not consecutive, so it should be also filtered.

I guess that You want filter alerts:

SELECT ID, Time 
FROM mydb.MYTABLE
WHERE Value>%s3 AND Time>=%s2 AND Time<=%s1
GROUP BY ID
having value<%treshold;

Upvotes: 0

Related Questions