Hi David,
thanks for your quick answer. I tried to perform both queries but I failed.
Maybe it's because of the fact that I simplified my example and the one table
is actually a join of two tables. The actual tables look as follows:
monitorsensor=> select * from sensors;
sensorid | typename | locationid | min | max
----------+-------------+------------+-----+-----
4 | Particle | 3 | |
5 | Humidity | 4 | |
6 | Temperature | 4 | |
7 | Humidity | 5 | |
1 | Temperature | 1 | 24 | 35
2 | Humidity | 1 | 125 | 135
3 | Humidity | 2 | 55 | 66
(7 rows)
sensorid is primary key.
monitorsensor=> select * from sensordata limit 5;
sensorid | datetime | value
----------+------------------------------+--------
1 | 2010-01-01 01:01:01.23456+01 | 31
2 | 2010-01-01 01:02:01.23456+01 | 131.39
3 | 2010-01-01 01:03:01.23456+01 | 62.07
1 | 2010-01-01 01:04:01.23456+01 | 33.5
2 | 2010-01-01 01:05:01.23456+01 | 133.84
(5 rows)
sensorid is foreign key. Primary key is the combination of
(sensorid, datetime). Here the complete problem statement:
Select all rows that exceeded the alarm values within the last 10 min
except those where the last inserted entry (determined by datetime) didn't
exceeded the alarm value.
I feel that my solution is overcomplicated. I solved it as follows.
SELECT sensorid, min, value, max, datetime
FROM sensordata NATURAL JOIN sensors
WHERE
datetime
BETWEEN CURRENT_TIMESTAMP - INTERVAL '10 minutes'
AND CURRENT_TIMESTAMP
AND ( value<min OR value>max )
EXCEPT
(
SELECT sensorid, min, value, max, t.datetime
FROM
(
SELECT sensorid, datetime, min, value, max, MAX(datetime)
OVER (PARTITION BY sensorid) AS last
FROM sensordata NATURAL JOIN sensors
WHERE
datetime
BETWEEN CURRENT_TIMESTAMP - INTERVAL '10 minutes'
AND CURRENT_TIMESTAMP
) AS t
WHERE
t.datetime=t.last AND ( value>=min AND value<= max )
);
Marco
