如何在 MySQL 中滞后列?
考虑下表:
SELECT id, value FROM table ORDER BY id ASC;
+-----+---------+
| id | value |
+-----+---------+
| 12 | 158 |
| 15 | 346 |
| 27 | 334 |
| 84 | 378 |
| 85 | 546 |
+-----+---------+
id 列是自动递增的，但包含间隙.value 列是数字.
The id column is auto-incremented but contains gaps. The value column is numeric.
我想通过设置与上面两行 value 相关的 value 来查看 value 随时间的增加.那是对于行 id=85 我想设置与 value 相关的行 id=85 (546) 的 value 行 id=27 (334).因此，要为 id=85 行计算的值是 546/334=1.63473.
I want to look at the increase in value over time by setting value in relation to the value two rows above. That is for row id=85 I want to set the value of row id=85 (546) in relation to the value of row id=27 (334). The value to be computed for row id=85 is hence 546/334=1.63473.
这是我想要达到的结果:
This is the result I want to achieve:
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 中执行这种滞后?
How do I perform such lagging in MySQL?
请注意，id 列包含间隙，因此仅使用 t1.id = t2.id - 2 加入同一个表是行不通的.
Please note that the id column contains gaps, so simply joining on the same table with t1.id = t2.id - 2 will not work.
推荐答案
这里有一个解决方案，在MySQL中返回你想要的内容
Here is a solution that returns what you want in 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 不喜欢针对子查询的自联接，因此您必须对行进行两次计数，因此不像它可能的那样整洁或可扩展，但它确实使指定滞后变得简单.
MySQL 5.1 doesn't like a self join against a subquery so you have to count rows twice, so not as tidy or scalable as it might be, but it does make specifying the lag simple.
对于使用 Oracle 的读者来说，这更容易
For readers that use Oracle instead this is much easier
SELECT id, value, value/lag(value, 2) over (order by id) as lag from results;
相关文章