Mysql – create difference column of time series in MySQL

MySQL

I have a table with 2 columns, the date and the value for the date, e.g.

date         value
2015-09-01   15
2015-09-02   17
2015-09-03   21

and I want to create a column with the difference between the value and the value of the last day, like

date         difference
2015-09-01   NULL
2015-09-02   2
2015-09-03   4

Can this be done with MySQL?

Best Answer

PROPOSED QUERY #1

SET @diff = NULL;
SET @prevval = -1;
SELECT date,
    (@diff:=IF(@prevval=-1,NULL,value - @prevval)) difference,
    (@prevval:=value) prevval
FROM mytable;

SAMPLE DATA

mysql> DROP DATABASE IF EXISTS tover;
Query OK, 1 row affected (0.03 sec)

mysql> CREATE DATABASE tover;
Query OK, 1 row affected (0.00 sec)

mysql> USE tover
Database changed
mysql> CREATE TABLE mytable
    -> (
    -> id INT NOT NULL AUTO_INCREMENT,
    -> date date,
    -> value int,
    -> PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO mytable (date,value) VALUES ('2015-09-01',15);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO mytable (date,value) VALUES ('2015-09-02',17);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO mytable (date,value) VALUES ('2015-09-03',21);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM mytable;
+----+------------+-------+
| id | date       | value |
+----+------------+-------+
|  1 | 2015-09-01 |    15 |
|  2 | 2015-09-02 |    17 |
|  3 | 2015-09-03 |    21 |
+----+------------+-------+
3 rows in set (0.00 sec)

mysql>

PROPOSED QUERY #1 EXECUTED

mysql> SET @diff = NULL;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @prevval = -1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT date,
    ->     (@diff:=IF(@prevval=-1,NULL,value - @prevval)) difference,
    ->     (@prevval:=value) prevval
    -> FROM mytable;
+------------+------------+---------+
| date       | difference | prevval |
+------------+------------+---------+
| 2015-09-01 |       NULL |      15 |
| 2015-09-02 |          2 |      17 |
| 2015-09-03 |          4 |      21 |
+------------+------------+---------+
3 rows in set (0.02 sec)

mysql>

PROPOSED QUERY #2

SET @diff = NULL;
SET @prevval = -1;
SELECT date,difference
FROM (SELECT date,
    (@diff:=IF(@prevval=-1,NULL,value - @prevval)) difference,
    (@prevval:=value) prevval
FROM mytable) A;

PROPOSED QUERY #2

This will give you the exact output you display in the question

mysql> SET @diff = NULL;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @prevval = -1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT date,difference
    -> FROM (SELECT date,
    ->     (@diff:=IF(@prevval=-1,NULL,value - @prevval)) difference,
    ->     (@prevval:=value) prevval
    -> FROM mytable) A;
+------------+------------+
| date       | difference |
+------------+------------+
| 2015-09-01 |       NULL |
| 2015-09-02 |          2 |
| 2015-09-03 |          4 |
+------------+------------+
3 rows in set (0.00 sec)

mysql>

GIVE IT A TRY !!!

Related Question