在MySQL上模拟滞后函数 [英] Simulating lag function on 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 |
+-------+------------+-------------+---------------------+

预期输出应为:

+-------+------------+-------------+---------------------+-----------+
| 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 |
+-------+------------+-------------+---------------------+-----------+

我已经看到一些通过设置变量并计算行之间的差来在MySQL上模拟lag函数的示例，但我只是无法使它起作用:

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;

这是 SQL提琴.

其他可能性是使用php进行计算或添加新表并通过python进行编程，但是如果我能够通过SQL执行此操作，那么我将尽力避免这些可能性.

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.

推荐答案

好，所以在进行了进一步的挖掘以及@Marc B的建议之后，我终于到达了想要的地方.万一其他人需要帮助，这是我的答案:

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);

在上面的行中，我将@price设置为第一条记录而不是零，以避免第一行的lag_price为65.60.

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.

以下是实际结果:

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

小提琴正在工作.

这篇关于在MySQL上模拟滞后函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！