Geek Answers Handbook

MySQL - find the difference between the rows of one table

I have a table containing aggregated results with timestamps - each result in time is shared:

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 am trying to formulate a query that will receive a delta 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 ...

Thus, each line in the date / ip group subtracts one in front of it (or 0). Any easy way to do this? thanks.

+6
mysql
source share
2 answers

Here is a solution without variables. I assume that you have initail data in a table named 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 subquery (maximum result previous timestamps from the same ip ). IFNULL gives us 0 if that was the first value, so the original results are displayed correctly.

I also recommend adding the following index to thetable :

 CREATE INDEX sort1 ON thetable (ip, date, time);
+10
source share

Try this option -

 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 | +------------+----------+----------+------+
+4
source share

More articles:


All Articles