MySQL-从上一行中减去值,按组 [英] MySQL - Subtracting value from previous row, group by
问题描述
我需要以SN号为基础来获取消耗值. 这是我的数据:
I need to have the consumption value base on previous one by SN number. This is my data:
TABLE EnergyLog
TABLE EnergyLog
SN Date Value
2380 2012-10-30 00:15:51 21.01
2380 2012-10-31 00:31:03 22.04
2380 2012-11-01 00:16:02 22.65
2380 2012-11-02 00:15:32 23.11
20100 2012-10-30 00:15:38 35.21
20100 2012-10-31 00:15:48 37.07
20100 2012-11-01 00:15:49 38.17
20100 2012-11-02 00:15:19 38.97
20103 2012-10-30 10:27:34 57.98
20103 2012-10-31 12:24:42 60.83
这是我需要的结果:
SN Date Value consumption
2380 2012-10-30 00:15:51 21.01 0
2380 2012-10-31 00:31:03 22.04 1.03
2380 2012-11-01 00:16:02 22.65 0.61
2380 2012-11-02 00:15:32 23.11 0.46
20100 2012-10-30 00:15:38 35.21 0
20100 2012-10-31 00:15:48 37.07 1.86
20100 2012-11-01 00:15:49 38.17 1.1
20100 2012-11-02 00:15:19 38.97 0.8
20103 2012-10-30 10:27:34 57.98 0
20103 2012-10-31 12:24:42 60.83 2.85
推荐答案
使用MySQL变量很棒,就像内联程序变量分配一样.首先,FROM子句为您声明" @变量,默认为空白.然后以所需的顺序查询记录.它只遍历数据,而不是通过重复的子查询来进行,这可能会占用大量时间.
Working with MySQL variables is great, its like inline program variable assignments. First, the FROM clause "declares" the @ variables for you, defaulting to blank. Then query the records in the expected order you want them. It makes a single pass through the data instead of via repeated subqueries which can be time intensive.
对于读取的每一行,将@lastSN与当前记录的SN进行比较.如果不同,则始终返回0.如果相同,则计算简单差异.只有在完成比较之后,才将@lastSN和@lastValue设置为等于当前记录的值,以进行下一个记录比较.
For each row read, compare the @lastSN with the SN of the current record. If different, always return 0. If it IS the same, compute the simple difference. Only AFTER that compare is done, set the @lastSN and @lastValue equal to that of the current record for the next records comparison.
select
EL.SN,
EL.Date,
EL.Value, --remove duplicate alias
if( @lastSN = EL.SN, EL.Value - @lastValue, 0000.00 ) as Consumption,
@lastSN := EL.SN,
@lastValue := EL.Value
from
EnergyLog EL,
( select @lastSN := 0,
@lastValue := 0 ) SQLVars
order by
EL.SN,
EL.Date
这篇关于MySQL-从上一行中减去值,按组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!