MySQL - 查找同一张表的行之间的差异

Question

我有一个包含带有时间戳的聚合结果的表 - 这意味着每次的每个结果都是到目前为止的总数:

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

我正在尝试制定一个查询，以获取每个时间范围之间的增量:

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)。有什么简单的方法可以做到这一点？谢谢。

Answer 1

这是一个没有变量的解决方案。我假设您的初始数据位于名为 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，因此初始结果会正确显示。

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

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