MySQL-查找同一表的行之间的差异 [英] MySQL - find difference between rows of the same table

问题描述

我有一个表，其中包含带有时间戳的汇总结果-表示每个时间的每个结果都是到目前为止的总数:

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
...

因此，每个日期/ip分组的每一行都减去前一行(或0). 有任何简单的方法吗?谢谢.

So each row per date / ip grouping subtracts the one before it (or 0). Any simple way to do this? thanks.

推荐答案

这是没有变量的解决方案.我假设您的初始数据在名为thetable的表中.

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

在这里，我们通过子选择获得了先前的值(来自同一ip的先前时间戳的最大result). IFNULL如果这是第一个值，则为我们提供0，因此可以正确显示初始结果.

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.

我还建议将以下索引添加到thetable:

I also recommend adding the following index to thetable:

CREATE INDEX sort1 ON thetable (ip, date, time);

这篇关于MySQL-查找同一表的行之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！