MySQL - 从前一行减去值，分组依据
我需要根据 SN 号获得基于前一个的消耗值.这是我的数据:
I need to have the consumption value base on previous one by SN number. This is my data:
表格能量日志
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
相关文章