如何滞后MySQL中的列？
要在MySQL中滞后一列，首先让我们创建一个表。创建表的查询如下-
mysql> create table LagDemo -> ( -> UserId int, -> UserValue int -> );
示例
使用insert命令在表中插入一些记录。查询如下-
mysql> insert into LagDemo values(12,158); mysql> insert into LagDemo values(18,756); mysql> insert into LagDemo values(15,346); mysql> insert into LagDemo values(87,646); mysql> insert into LagDemo values(27,334); mysql> insert into LagDemo values(90,968); mysql> insert into LagDemo values(84,378); mysql> insert into LagDemo values(85,546);
使用select语句显示表中的所有记录。查询如下-
mysql> select *from LagDemo;
输出结果
+--------+-----------+ | UserId | UserValue | +--------+-----------+ | 12 | 158 | | 18 | 756 | | 15 | 346 | | 87 | 646 | | 27 | 334 | | 90 | 968 | | 84 | 378 | | 85 | 546 | +--------+-----------+ 8 rows in set (0.00 sec)
这是在MySQL中滞后一列的查询-
mysql> SET @f : = 0; mysql> SET @s : = 2; mysql> SELECT l1.UserId, l1.UserValue , l1.UserValue / l2.UserValue AS 'LAG' -> FROM -> (SELECT if(@f, @f: = @f+1, @f:=1) as RowNumber, UserId, UserValue FROM LagDemo) AS l1 -> LEFT JOIN -> (SELECT if(@s, @s: = @s+1, @s: = 1) as RowNumber, UserId, UserValue FROM LagDemo) AS l2 -> ON l1.RowNumber = l2.RowNumber;
输出结果
+--------+-----------+--------+ | UserId | UserValue | LAG | +--------+-----------+--------+ | 12 | 158 | NULL | | 18 | 756 | NULL | | 15 | 346 | 2.1899 | | 87 | 646 | 0.8545 | | 27 | 334 | 0.9653 | | 90 | 968 | 1.4985 | | 84 | 378 | 1.1317 | | 85 | 546 | 0.5640 | +--------+-----------+--------+ 8 rows in set (0.00 sec)