Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How do I lag columns in MySQL?
To lag a column in MySQL, first, let us create a table. The query to create a table is as follows −
mysql> create table LagDemo -> ( -> UserId int, -> UserValue int -> ); Query OK, 0 rows affected (1.74 sec)
Example
Insert some records in the table using insert command. The query is as follows −
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)
Display all records from the table using a select statement. The query is as follows −
mysql> select *from LagDemo;
Output
+--------+-----------+ | 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)
Here is the query to lag a column in 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;
Output
+--------+-----------+--------+ | 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)
Updated on: 26-Jun-2020
111 Views
Advertisements