MySQL - find difference between rows of the same table

Question

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.

You might want to elaborate on how the date change is handled. Does the result start from zero on every day, or starts from zero only on the begginning of the file? — vbence, Mar 23 '11 at 10:53
What if there is a 11:00 data point and there's no 10:00, only 09:00 or none before that day? — Clodoaldo Neto, Mar 23 '11 at 11:38

vbence · Accepted Answer · 2011-03-23T11:15:00.857

10

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);

edited Mar 23 '11 at 11:15

answered Mar 23 '11 at 10:47

vbence

20,084
9
69
118

Good solution, more efficient. – Devart Mar 23 '11 at 11:01
Thanks. It had a little problem with the handling of `date` field. Now it's OK. – vbence Mar 23 '11 at 11:17
I had some troubles too, with variables ;) – Devart Mar 23 '11 at 11:30

score 4 · Answer 2 · answered Mar 23 '11 at 09:50

Try this one -

SET @f_rank = 0;
SET @s_rank = 0;
SET @f_date = NULL;
SET @f_ip = NULL;
SET @s_date = NULL;
SET @s_ip = NULL;
SELECT t1.date, t1.time, t1.ip, IF(t2.result IS NULL, t1.result, t1.result - t2.result) diff FROM
    (SELECT *, IF(@f_date = date AND @f_ip = ip, @f_rank:=@f_rank + 1, @f_rank:=1) AS rank, @f_date := date, @f_ip := ip FROM table1 ORDER BY date, ip, time) t1
  LEFT JOIN 
    (SELECT *, IF(@s_date = date AND @s_ip = ip, @s_rank:=@s_rank + 1, @s_rank:=1) AS rank, @s_date := date, @s_ip := ip FROM table1 ORDER BY date, ip, time) t2
  ON t1.date = t2.date AND t1.ip = t2.ip AND (t1.rank = t2.rank + 1);

+------------+----------+----------+------+
| date       | time     | ip       | diff |
+------------+----------+----------+------+
| 2011-03-01 | 10:00:00 | 10.0.0.1 |  200 |
| 2011-03-01 | 11:00:00 | 10.0.0.1 |  103 |
| 2011-03-01 | 12:00:00 | 10.0.0.1 |  112 |
| 2011-03-01 | 13:00:00 | 10.0.0.1 |  213 |
| 2011-03-01 | 10:00:00 | 10.0.0.2 |  198 |
| 2011-03-01 | 11:00:00 | 10.0.0.2 |   36 |
| 2011-03-01 | 12:00:00 | 10.0.0.2 |  139 |
| 2011-03-01 | 13:00:00 | 10.0.0.2 |  139 |
+------------+----------+----------+------+

MySQL - find difference between rows of the same table

2 Answers2

Linked