mercredi 2 septembre 2015

SQL how to select the most recent timestamp by values and get the difference?

Let's say I have a table like this:

create table sensors (sensor_type integer not null, 
                      value integer not null, time timestamp not null, 
                      unique(sensor_type, time));

Write a sql query that for each sensor_type that has been registered more than once, returns the difference between the latest, meaning the most recent in terms of time and the second latest value. The table should be ordered by sensor_type in ascending order.

Below is an example:

    sensor_type | value | time
    ---------------------------------------
        2       | 5     | 2015-05-09 12:42:00
        4       | -42   | 2015-05-09 13:19:57
        2       | 2     | 2015-05-09 14:48:30
        2       | 7     | 2015-05-09 12:54:39
        3       | 16    | 2015-05-09 13:19:57
        3       | 20    | 2015-05-09 15:01:09

Output should come out as:

sensor_type | value
 ------------------
    2       | -5    
    3       | 4  

So I decided to came up with below query but not able to understand how to get the value right by doing minus here?

SELECT sensor_type,value
FROM sensors s1
WHERE time = (
  SELECT MAX(time) FROM sensors s2 WHERE s1.sensor_type = s2.sensor_type
)
ORDER BY time ASC;

Aucun commentaire:

Enregistrer un commentaire