MySQL - Subtracting value from previous row, group by

Question

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

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

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

To me, this sounds like making the database present your denormalised application values. Is doing the subtraction in the presentation loop such a bad idea? — akaIDIOT, Nov 02 '12 at 13:42

score 82 · Answer 1 · edited Feb 24 '16 at 03:16

82

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.

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

edited Feb 24 '16 at 03:16

Community

1
1

answered Nov 02 '12 at 13:39

DRapp

47,638
12
72
142

1

For big tables this is probably the fastest for searching non-indexed columns. – Martin Hennings Jan 16 '14 at 15:15
2

Is it possible to also assign that `@lastSN := EL.SN, @lastValue := EL.Value` without actually returning it in the results? (I know you don't have to use it) – Yeti Oct 28 '15 at 13:20
6

@Yeti, no, if you don't want to see those columns in your FINAL result, just wrap this query up one more level and pull out only the columns you WANT (thus without the at-place-holder columns). – DRapp Oct 28 '15 at 14:32
Nice one! Order by is very important then but it can make the query slow. It is still much faster than doing sub query for each row. – Jerem Mar 03 '17 at 13:41
For me this only worked correctly if I changed lastSN := 0 to lastSN := null, otherwise they if would always be true. Is there any explanation for this behavior? It doesn't make much sense to me – Syberdoor Aug 24 '17 at 10:59
@Syberdoor, I only defaulted to zero as most IDs exist and would not be of a zero value. So it started with anyone who's SN was 0 as a valid person. You could also have started with null as you did, or like -1.. just SOME ID that would not be a legit ID within the system, but SHOULD be the same data type you expect to use it as. – DRapp Aug 25 '17 at 03:12
@DRapp I understand how the code works, the strange thing is that for me if I started with 0 (although no sn is 0) the if would always be true, if I started with -1 it would work exactly once (so the very first row would be calculated correctly but each one where the sn changed would be wrong), if I started with null it would work just fine. Too me it looked like if by using zero the datatype of the variable was automatically determined to be different than using null. But somehow this also makes no sense because wich datatype would default to true on any comparison no matter the content – Syberdoor Aug 26 '17 at 15:08
@Syberdoor, read second paragraph again at the... Only AFTER... Once the test is complete, you are now setting the lastSN to the value of the record it just processed. So it starts with 0, processes SN = 1, does its test/additions, whatever, then assigns the 1 to the lastSN. Then goes to the next record which MAY be SN = 1 OR... SN = 2 which it would detect difference and reset the counters to zero. – DRapp Aug 26 '17 at 17:26
Yeah I get all that (despite my limited understanding of SQL). My point is that with an initial of 0 every line where there is a change in the SN will be wrong on my system. Because the if will *always* evaluate to true and the strange thing is that the initial value can not even affect the value of lastSN after let's say 30 lines because it was already 30 times overwritten. However it still does exactly that. Seeing as I found a solution it is not really important, I was just interested if there maybe is some way SQL handles variable auto types that explains this or some behavior I don't get. – Syberdoor Aug 26 '17 at 17:55
@Syberdoor, FOR CONSUMPTION, The starting point is always zero. Think a bit like a checkbook register. You have zero balance, you add to it, now THAT is the balance. Now you write a check, new balance. Write another check, new balance. The at variables are like your running total and just get applied to each subsequent record until a new "ID" (customer, account, whatever) is identified. – DRapp Aug 27 '17 at 13:45
Thank you! I was confused for a bit how to use it when my query has Joins, turned out as simple as: `JOIN( SELECT @lastVALUE := 0) SQLVars` – Kash Mar 14 '19 at 19:59

score 16 · Answer 2 · answered Nov 02 '12 at 13:37

16

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

answered Nov 02 '12 at 13:37

bidifx

1,640
13
19

Ordering by date doesn't sound like a good idea in a sub-clause. – andig Jul 24 '13 at 11:37
@andig please elaborate – bidifx Jul 24 '13 at 14:22
For every row of energylog, the partial energylog table needs to be read again and the whole resultset sorted to find the first row. That means n sort operations for n records if I'm not mistaken. – andig Jul 25 '13 at 06:33
1

@andig - Old post I know, but... This is not necessarily true if there is an appropriate index. – MatBailie Mar 26 '14 at 13:51
1

@MatBailie not sure that the optimizer does. I'd prefer max(a) over sort(a) limit 1? – andig Mar 27 '14 at 21:26

score 13 · Answer 3 · answered Nov 02 '12 at 13:39

13

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).

answered Nov 02 '12 at 13:39

MatBailie

83,401
18
103
137

2

This is a fast solution if you can't or don't want to use @variables. – Martin Hennings Jan 21 '14 at 15:01
anyway to improve the query for large records? – neobie Mar 16 '17 at 03:35

score 3 · Answer 4 · edited Jun 19 '17 at 08:21

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
           )

score 0 · Answer 5 · answered Nov 02 '12 at 14:08

0

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;

answered Nov 02 '12 at 14:08

salona

59
6

MySQL - Subtracting value from previous row, group by

5 Answers5

Linked