lundi 23 mars 2015

SQL hourly log , show all matching rows that have a value below threshold for n hours

I have a simple SQL log table (named market_history in SQLite) for US markets it looks something like this:


Sample table (market_history)



id datetime market percent
1 9/5/2014 7:50 ARIZONA 50.0
2 9/5/2014 7:50 ATLANTA 97.4
3 9/5/2014 7:50 AUSTIN 78.8
4 9/5/2014 7:50 BOSTON 90.9
6 9/5/2014 7:50 CHARLOTTE 100.0
7 9/5/2014 7:50 CHICAGO 90.3


This table is an hourly snapshot of network capacity in various systems in each market. What I would like to do is set up an alert system that if any one particular market is below a threshold percent (say 50) for more than 2 consecutive hours (each row is recorded every hour), it triggers an alert email.. So the query should show me a a unique list of Market names where the percents is < 50.0 for more than the last 2 consecutive entries


Here's the SQL I'm trying, but it's not working:


Sample SQL (not working):



SELECT
mh.datetime, mh.market, mh.percent
FROM markets_history mh
WHERE
(SELECT mh1.precent FROM markets_history mh1 WHERE mh1.datetime BETWEEN "2015-03-23 00:00:00" AND "2015-03-23 00:59:59" AND mh.market=mh1.market ) < 50 AND (SELECT mh2.precent FROM markets_history mh2 WHERE mh2.datetime BETWEEN "2015-03-23 01:00:00" AND "2015-03-23 01:59:59" AND mh.market=mh2.market ) < 50
ORDER by mh.datetime


I know I'm missing something.. any sugggestions


Aucun commentaire:

Enregistrer un commentaire