how to calculate minus value between rows and between time

Question

I have a table contains id, server_time and value:

id   server_time      value
---  ---------------  -----
100  04/10/2016 0:00  3
101  04/10/2016 0:15  6
102  04/10/2016 0:30  8
103  04/10/2016 0:45  9
104  04/10/2016 1:00  10
105  04/10/2016 1:15  10
106  04/10/2016 1:30  12
107  04/10/2016 1:45  13
108  04/10/2016 2:00  14

This data will be inserted every 15 minutes, and the value is a cumulative value.

I need to output columns min15, hour1 and hour2:

• column min15 will be the difference between this row's value and the one 15 minutes ago.
• column hour1 will be the difference between this row's value and the one 1 hour ago.
• column hour2 will be the difference between this row's value and the one 2 hours ago.

id   server_time      value  min15  hour1  hour2
---  ---------------  -----  -----  -----  -----
100  04/10/2016 0:00  3      3      null   null
101  04/10/2016 0:15  6      3      null   null
102  04/10/2016 0:30  8      2      null   null
103  04/10/2016 0:45  9      1      null   null
104  04/10/2016 1:00  10     1      7      null
105  04/10/2016 1:15  10     0      null   null
106  04/10/2016 1:30  12     2      null   null
107  04/10/2016 1:45  13     1      null   null
108  04/10/2016 2:00  14     1      4      11

Or should I display these columns (min15, hour1, hour2) as separate results?

Any suggestion for the query would be appreciated.

Answer 1

I see no guarantee that there are no gaps on the table and all rows follow every 15 minutes.

But if and only if the data is like that - and you don't have anything outside these specs - then you could simply use the LAG() function:

SELECT 
    d.id, d.server_time, d.value,
    min15 = d.value - LAG(value) OVER (ORDER BY d.server_time),
    hour1 = d.value - LAG(value, 4) OVER (ORDER BY d.server_time),
    hour2 = d.value - LAG(value, 8) OVER (ORDER BY d.server_time)
FROM server_data AS d
ORDER BY server_time ;