如何滞后MySQL中的列? [英] How do I lag columns in 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，我想相对于行id=27(334)的value设置行id=85(546)的value.因此，要为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;

这篇关于如何滞后MySQL中的列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！