mysql 日期滞后,在MySQL上模拟滞后函数

I'm trying to get lagged values (current row - last row) from the following table:

+-------+------------+-------------+---------------------+

| index | codigowine | preconormal | timestamp |

+-------+------------+-------------+---------------------+

| 2 | 10088 | 65.60 | 2014-11-18 23:39:08 |

| 1005 | 10088 | 62.60 | 2014-11-20 11:00:14 |

| 2028 | 10088 | 62.60 | 2014-11-21 09:00:13 |

| 3092 | 10088 | 62.60 | 2014-11-22 09:00:12 |

| 5083 | 10088 | 69.00 | 2014-11-24 09:00:13 |

+-------+------------+-------------+---------------------+

The expected output should be:

+-------+------------+-------------+---------------------+-----------+

| index | codigowine | preconormal | timestamp | lag_price |

+-------+------------+-------------+---------------------+-----------+

| 2 | 10088 | 65.60 | 2014-11-18 23:39:08 | 0.00 |

| 1005 | 10088 | 62.60 | 2014-11-20 11:00:14 | -3.00 |

| 2028 | 10088 | 62.60 | 2014-11-21 09:00:13 | 0.00 |

| 3092 | 10088 | 62.60 | 2014-11-22 09:00:12 | 0.00 |

| 5083 | 10088 | 69.00 | 2014-11-24 09:00:13 | 6.40 |

+-------+------------+-------------+---------------------+-----------+

I've seen some examples emulating the lag function on MySQL by setting a variable and calculating the difference between rows, but I just can't get it to work:

SET @price=0;

select index,codigowine,@price price_lag, @price:=preconormal curr_price from precos order by codigowine, timestamp;

Other possibilities would be to calculate using php or adding a new table and doing the programming via python, but I'm trying to avoid these possibilities if I'm able to perform this through SQL.

解决方案

Ok, so after some more digging, and the suggestion by @Marc B, I finally got to where I wanted. In case anyone else needs help, here's my answer:

set @price = (select preconormal from precos where codigowine='10088' order by timestamp asc limit 1);

On the line above I'm setting @price to the first record instead of zero, to avoid getting a lag_price of 65.60 on the first row.

Below is the actual result:

select codigowine, preconormal - @price as lag_price, @price:=preconormal curr_price from precos WHERE codigowine='10088' order by timestamp;

Here's the fiddle working.