MySQL - 从上一行减去值，分组依据

I need to have the consumption value base on previous one by SN number. This is my data:

我需要通过SN号将消耗值基于前一个消耗值。这是我的数据：

TABLE EnergyLog

表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

This is the result I need:

这是我需要的结果：

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

5 个解决方案

#1

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.

使用MySQL变量非常棒，就像内联程序变量赋值一样。首先，FROM子句为你“声明”@变量，默认为空白。然后按照您希望的顺序查询记录。它通过数据而不是通过重复的子查询进行单次传递，这可能是时间密集的。

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.

对于读取的每一行，将@lastSN与当前记录的SN进行比较。如果不同，则始终返回0.如果相同，则计算简单差异。只有完成比较后，将@lastSN和@lastValue设置为等于当前记录的值，以便进行下一次记录比较。

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

#2

A near universal solution is to join the data on to itself, to find the previous record, by including a correlated sub-query in the join condition...

一种近乎通用的解决方案是通过在连接条件中包含相关的子查询来将数据连接到自身，以查找先前的记录...

SELECT
  ThisLog.*,
  COALESCE(ThisLog.Value - PrevLog.Value, 0) AS consumption
FROM
  EnergyLog    AS ThisLog
LEFT JOIN
  EnergyLog    AS PrevLog
    ON  PrevLog.SN   = ThisLog.SN
    AND PrevLog.Date = (SELECT MAX(Date)
                          FROM EnergyLog
                         WHERE SN   = ThisLog.SN
                           AND Date < ThisLog.Date)

This performs best with one index covering both (SN, Date).

这表现最佳，一个索引覆盖（SN，日期）。

#3

This should do the trick:

这应该是诀窍：

SELECT l.sn,
       l.date, 
       l.value,
       l.value - (SELECT value 
                  FROM energylog x
                  WHERE x.date < l.date
                  AND x.sn = l.sn
                  ORDER BY date DESC
                  LIMIT 1) consumption
FROM energylog l;

See SQLFiddle: http://sqlfiddle.com/#!2/b9eb1/8

请参阅SQLFiddle：http：//sqlfiddle.com/#！2/b9eb1/8

#4

You can join two rows of the same table like this:

您可以像这样连接同一个表的两行：

    SELECT this.*, prev.*
      FROM tbl this
INNER JOIN tbl prev ON prev.id =
           ( 
               SELECT max(t.id) 
               FROM tbl t  
               WHERE t.id < this.id
           )     
     WHERE ...

So your case will look like:

所以你的情况看起来像：

    SELECT this.SN, this.Date, this.Value, (this.Value - prev.Value) AS consumption
      FROM EnergyLog this
INNER JOIN EnergyLog prev ON prev.Date =
           ( 
               SELECT max(t.Date) 
               FROM EnergyLog t  
               WHERE t.Date < this.Date
           )

#5

Can you please try the below query once.

可以请一次尝试以下查询。

SELECT e1.*,
   (SELECT Value
    FROM EnergyLog e2
    WHERE e2.sn = e1.sn AND e2.date < e1.date
    ORDER BY date DESC
    LIMIT 1)-l.Value consumption
FROM EnergyLog e1;

#1

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

#2