MySQL | LEAD() and LAG() Function

The LEAD() and LAG() function in MySQL are used to get preceding and succeeding value of any row within its partition. These functions are termed as nonaggregate Window functions.

The Window functions are those functions which perform operations for each row of the partition or window. These functions produce the result for each query row unlikely to the aggregate functions that group them and results in a single row.

  • The row on which operation occur is termed as current row.
  • The set of rows which are realted to current row or using which function opaerates on current row is termed as Window.

The LAG() function is used to get value from row that precedes the current row.



The LEAD() function is used to get value from row that succedes the current row.

Syntax:
For LEAD() function-

LEAD(expr, N, default) 
          OVER (Window_specification | Window_name)

For LAG() function-

LAG(expr, N, default) 
          OVER (Window_specification | Window_name)

The N and default argument in the function is optional.

Parameters used:

  1. expr: It can be a column or any bulit-in function.
  2. N: It is a positive value which determine number of rows preceding/succeeding the current row. If it is omitted in query then its default value is 1.
  3. default: It is the default value return by function in-case no row precedes/succeedes the current row by N rows. If it is missing then it is by default NULL.
  4. OVER(): It defines how rows are partitioned into groups. If OVER() is empty then function compute result using all rows.
  5. Window_specification: It consist of query partition clause which determines how the query rows are partitioned and ordered.
  6. Window_name: If window is specified elsewhere in the query then it is referenced using this Window_name.

Example:
Consider a “contest” table:-

c_id start_date end_date
1 2015-02-01 2015-02-04
2 2015-02-02 2015-02-05
3 2015-02-03 2015-02-07
4 2015-02-04 2015-02-06
5 2015-02-06 2015-02-09
6 2015-02-08 2015-02-10
7 2015-02-10 2015-02-11

In the above table, “c_id” represents contest id and “start_date” and “end_date” represents starting and ending date of contest respectively.

Problem description: We have to find number of days a contest will collapse with the next contest i.e no. of days on which both contests are held.

Query:


Select c_id, start_date, end_date, 
        end_date - lead (start_date) 
        over (order by start_date) 
               + 1 as 'no_of_days' 
                   from contest;

In the above query “end_date” return ending date of the current contest and lead(start_date) return next contest starting date. So, the difference between these dates plus 1 will return no. of days the contest will collide.

Here, window specification is given by “order by” clause, which represent that lead() function will operate on table order by their “start_date” in increasing order. Since their is no partition clause so, whole table is taken as a single window.

Output:

c_id start_date end_date no_of_days
1 2015-02-01 2015-02-04 3
2 2015-02-02 2015-02-05 3
3 2015-02-03 2015-02-07 4
4 2015-02-04 2015-02-06 1
5 2015-02-06 2015-02-09 2
6 2015-02-08 2015-02-10 1
7 2015-02-10 2015-02-11 NULL

Since, there is no contest after contest 7 i.e (c_id=7). So, lead(start_date) returns NULL value.

Note:The LEAD() and LAG() function are always used with OVER(). Missing of an over clause will raise an error.



My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.



Improved By : nidhi_biet



Article Tags :
Practice Tags :


1


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.