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:

表格能量日志

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