MySQL delta from previous value

Question

I have a table like this one:

And I need to add a column with incremental value from previous value based on Data order like this:

How can I achieve this task ?

Answer 1

You can make use of the function LAG or LEAD.

A simple example on a table which only has integers values from 0 to 9:

select 
   i, 
   lead(i) over (order by i) as "LEAD", 
   lag(i) over (order by i) as "LAG" 
from numm 
order by i;

will output this:

+---+------+------+
| i | LEAD | LAG  |
+---+------+------+
| 0 |    1 | NULL |
| 1 |    2 |    0 |
| 2 |    3 |    1 |
| 3 |    4 |    2 |
| 4 |    5 |    3 |
| 5 |    6 |    4 |
| 6 |    7 |    5 |
| 7 |    8 |    6 |
| 8 |    9 |    7 |
| 9 | NULL |    8 |
+---+------+------+