如何在MySQL中延迟列？

Question

考虑下面的表格：

SELECT id, value FROM table ORDER BY id ASC;
+-----+---------+
| id  | value   |
+-----+---------+
| 12  | 158     |
| 15  | 346     |
| 27  | 334     |
| 84  | 378     |
| 85  | 546     |
+-----+---------+

id列是自增的，但存在间隔。 value列是数字。

我想通过将value与前两行的value进行比较来观察随时间增加的value。也就是说，对于id=85的行，我想将id=85（546）的value设置为相对于id=27（334）的value。因此，要计算id=85行的值为546/334=1.63473。

这就是我想要实现的结果：

SELECT id, value, ...;
+-----+---------+---------------------+
| id  | value   | value/lag(value, 2) | (the syntax value/lag(value, 2) is made up)
+-----+---------+---------------------+
| 12  | 158     | NULL                |
| 15  | 346     | NULL                |
| 27  | 334     | 2.11392             | (334/158=2.11392)
| 84  | 378     | 1.09248             | (378/346=1.09248)
| 85  | 546     | 1.63473             | (546/334=1.63473)
+-----+---------+---------------------+

如何在MySQL中执行这样的滞后操作？

请注意，id列包含间隔，因此仅使用t1.id = t2.id - 2在同一表上连接将无法正常工作。

- knorv

---

SQL标准中有一个lag()函数，但MySQL不支持该函数。 - user330315

Answer 1

这里有一个MySQL的解决方案，可以返回您想要的结果。

SET @a :=0;
SET @b :=2;
SELECT r.id, r.value, r.value/r2.value AS 'lag'
FROM
(SELECT if(@a, @a:=@a+1, @a:=1) as rownum, id, value FROM results) AS r
LEFT JOIN
(SELECT if(@b, @b:=@b+1, @b:=1) as rownum, id, value FROM results) AS r2
ON r.rownum = r2.rownum

MySQL 5.1对子查询的自连接不友好，所以你需要计算两次行数，这样就不那么整洁和可扩展了，但是这确实使得指定滞后很简单。

对于使用Oracle的读者来说，这更加容易。

SELECT id, value, value/lag(value, 2) over (order by id) as lag from results;

- user672739

---

针对使用 Oracle 的读者：或者 PostgreSQL 或 DB2 或 Teradata :) - user330315

Answer 2

由于当前行和您想要获取“历史”数据的行之间只有两行，因此您可以使用变量暂时存储数据，例如：

set @oldid0=999999;
set @oldid1=999999;
set @oldid2=999999;
select @oldid0:=@oldid1,@oldid1:=@oldid2,@oldid2:=id, value/@oldid0 from table order by id asc;

虽然这是一个非常不整洁的解决方案，但我认为它能够完成任务。也许有一些方法可以防止变量被显示出来，但我还没有深入研究。

- blankabout

---

Lag(2)只是一个例子。我正在寻找一般解决方案，比如允许Lag(365) :-) - knorv

啊，我看了你的问题，愚蠢地认为你只是指lag(2)。 - blankabout