CODEWITHSUNDEEP
mysqlsql
Electronic Circuit
Electronic Circuit

Reputation: 335

How to get a result from substracting date

Hi guys i want to query out the current date subtract previous date and return the changes of the length (current length - previous length) using SQL. See the sample of the changes as in the table below current minus previous length

I couldn't think of query how to do it SELECT date, length FROM length_data

date                 length          changes
-------------------  --------------  ------------
2000-08-29 10:30:00          147.98  147.98
2000-08-30 00:00:00          147.98  0
2000-09-02 10:30:00          156.51  8.53
2000-09-04 00:00:00          156.51  ....
2000-09-04 04:30:00          156.51  ....
2000-09-04 06:30:00          156.51  ....
2000-09-05 21:00:00          156.51
2000-09-06 03:00:00          156.51
2000-09-07 09:30:00          204.06
2000-09-07 10:30:00          204.06
2000-09-08 00:00:00          339.09
2000-09-08 12:30:00          395.78
2000-09-09 09:30:00          477.77
2000-09-10 02:30:00          737.77

Upvotes: 1

Views: 63

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175586

You could use variable to simulate LAG windowed function:

SELECT `date`, `length`,
       `length` - @prev AS changes,
       @prev := `length`
FROM length_data, (SELECT @prev := 0) AS a
ORDER BY `date`;

SqlFiddleDemo

I've assumed that date is PRIMARY KEY/UNIQUE otherwise it won't be stable.

EDIT:

To remove additional column you could use subquery.

SELECT `date`, `length`, `changes`
FROM (SELECT `date`, `length`,
        `length` - @prev AS changes,
        @prev := `length`
      FROM length_data,(SELECT @prev := 0) as a
      ORDER BY `date`) AS sub
ORDER BY `date`;

SqlFiddleDemo2

Output:

╔══════════════════════════════╦═════════╦═════════╗
║            date              ║ length  ║ changes ║
╠══════════════════════════════╬═════════╬═════════╣
║ August, 29 2000 10:30:00     ║ 147.98  ║ 147.98  ║
║ August, 30 2000 00:00:00     ║ 147.98  ║ 0       ║
║ September, 02 2000 10:30:00  ║ 156.51  ║ 8.53    ║
║ September, 04 2000 00:00:00  ║ 156.51  ║ 0       ║
║ September, 04 2000 04:30:00  ║ 156.51  ║ 0       ║
║ September, 04 2000 06:30:00  ║ 156.51  ║ 0       ║
║ September, 05 2000 21:00:00  ║ 156.51  ║ 0       ║
║ September, 06 2000 03:00:00  ║ 156.51  ║ 0       ║
║ September, 07 2000 09:30:00  ║ 204.06  ║ 47.55   ║
║ September, 07 2000 10:30:00  ║ 204.06  ║ 0       ║
║ September, 08 2000 00:00:00  ║ 339.09  ║ 135.03  ║
║ September, 08 2000 12:30:00  ║ 395.78  ║ 56.69   ║
║ September, 09 2000 09:30:00  ║ 477.77  ║ 81.99   ║
║ September, 10 2000 02:30:00  ║ 737.77  ║ 260     ║
╚══════════════════════════════╩═════════╩═════════╝

EDIT 2:

Using Jean Doux's idea to calculate it using only one column:

SELECT `date`, length,
    CAST((-IF(@prev IS NULL,0,@prev) + (@prev := length)) AS DECIMAL(10,4)) AS changes 
FROM length_data,(SELECT @prev := NULL ) AS a
ORDER BY `date`;

SqlFiddleDemo2

or the easiest way is to skip additional column in application layer.

Upvotes: 3

Daniel E.
Daniel E.

Reputation: 2480

SELECT
date, 
length,
@prevLength - (@prevLength:= length) as changes
FROM 
test, (SELECT @prevLength:= 0) as a
ORDER BY account_id ASC, value ASC

Upvotes: 1

Related Questions