I have a table containing aggregated results with timestamps - meaning each result per time is the total so far:
date | time | ip | result
---------------------------------------
2011-03-01 | 10:00 | 10.0.0.1 | 200
2011-03-01 | 11:00 | 10.0.0.1 | 303
2011-03-01 | 12:00 | 10.0.0.1 | 415
2011-03-01 | 13:00 | 10.0.0.1 | 628
2011-03-01 | 10:00 | 10.0.0.2 | 198
2011-03-01 | 11:00 | 10.0.0.2 | 234
2011-03-01 | 12:00 | 10.0.0.2 | 373
2011-03-01 | 13:00 | 10.0.0.2 | 512
I'm trying to formulate a query that'll get the deltas between each time range:
date | time | ip | diff
---------------------------------------
2011-03-01 | 10:00 | 10.0.0.1 | 200
2011-03-01 | 11:00 | 10.0.0.1 | 103
2011-03-01 | 12:00 | 10.0.0.1 | 112
2011-03-01 | 13:00 | 10.0.0.1 | 213
2011-03-01 | 10:00 | 10.0.0.2 | 198
2011-03-01 | 11:00 | 10.0.0.2 | 36
2011-03-01 | 12:00 | 10.0.0.2 | 139
2011-03-01 | 13:00 | 10.0.0.2 | 139
...
So each row per date / ip grouping subtracts the one before it (or 0). Any simple way to do this? thanks.
Here is a solution without variables. I assume you have your initail data in a table called thetable.
SELECT date, time, ip,
result - IFNULL( (
SELECT MAX( result )
FROM thetable
WHERE ip = t1.ip
AND ( date < t1.date
OR date = t1.date AND time < t1.time )
) , 0) AS diff
FROM thetable AS t1
ORDER BY ip, date, time
Here we get the previous value with a subselect (the maximal result of the preceding timestamps from the same ip). IFNULL gives us a 0 if this was the first value, so initial results are displayed correctly.
I also recommend adding the following index to thetable:
CREATE INDEX sort1 ON thetable (ip, date, time);