35

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
Improve this question
3
  • 1
    is there an auto increment id? Commented Nov 2, 2012 at 13:31
  • For each SN number it wil start with 0 Commented Nov 2, 2012 at 13:34
  • 3
    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? Commented Nov 2, 2012 at 13:42

5 Answers 5

Reset to default
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
Improve this answer
Sign up to request clarification or add additional context in comments.

11 Comments

For big tables this is probably the fastest for searching non-indexed columns.
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, 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).
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.
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
|
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

Improve this answer

5 Comments

Ordering by date doesn't sound like a good idea in a sub-clause.
@andig please elaborate
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 - Old post I know, but... This is not necessarily true if there is an appropriate index.
@MatBailie not sure that the optimizer does. I'd prefer max(a) over sort(a) limit 1?
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).

Improve this answer

2 Comments

This is a fast solution if you can't or don't want to use @variables.
anyway to improve the query for large records?
3

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 )
Improve this answer

Comments

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;
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.