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屋！