极客教程MySQL 如何进行列延迟
要在MySQL中进行列延迟，首先需要创建一张表。创建表的查询语句如下 –
mysql> create table LagDemo
-> (
-> UserId int,
-> UserValue int
-> );
Query OK, 0 rows affected (1.74 sec)
阅读更多：MySQL 教程
示例
使用insert命令在表中插入一些记录。查询如下 –
mysql> insert into LagDemo values(12,158);
Query OK, 1 row affected (0.61 sec)
mysql> insert into LagDemo values(18,756);
Query OK, 1 row affected (0.21 sec)
mysql> insert into LagDemo values(15,346);
Query OK, 1 row affected (0.25 sec)
mysql> insert into LagDemo values(87,646);
Query OK, 1 row affected (0.14 sec)
mysql> insert into LagDemo values(27,334);
Query OK, 1 row affected (0.11 sec)
mysql> insert into LagDemo values(90,968);
Query OK, 1 row affected (0.08 sec)
mysql> insert into LagDemo values(84,378);
Query OK, 1 row affected (0.10 sec)
mysql> insert into LagDemo values(85,546);
Query OK, 1 row affected (0.56 sec)
使用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;
Query OK, 0 rows affected (0.00 sec)
mysql> SET @s : = 2;
Query OK, 0 rows affected (0.00 sec)
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)