Lead and lag are the window functions which are used fetch subsequent and previous rows based on over clause expressions provided. In this blog we will see how these work and at the end we have a video with same example.
LAG()
This is the window function used to pull previous row value of mentioned column for mentioned partition and order.
Syntax of Lag function: below is syntax of lead function, inside it we will write column which we want and how much lead do we want then inside OVER clause we can write partition by and order by clause on which we want lead. Again like all window functions partition by clause is optional.
LAG(column, lag_value) OVER (PARTITION BY expression ORDER BY expression)
Illustration of lag function: here we have a table with revenue by quarter by year imagine we want a new column with revenue of previous quarter printed beside. this is when we use lag function.
Below is a query with lag function. where we are pulling revenue of previous row (one row before) when it is ordered by year and quarter in ascending manner.
project_1=# select *, lag(rev,1) over (order by year,qrtr) from rev;
year | qrtr | rev | lag
------+------+-----+-----
2020 | Q1 | 10 |
2020 | Q2 | 20 | 10
2020 | Q3 | 30 | 20
2020 | Q4 | 40 | 30
2021 | Q1 | 15 | 40
2021 | Q2 | 25 | 15
2021 | Q3 | 35 | 25
2021 | Q4 | 45 | 35
(8 rows)LEAD()
This is the window function used to pull subsequent row value of mentioned column for mentioned partition and order.
Syntax of Lead function: below is syntax of lead function, inside lag function we will write column which we want and how much lead do we want then inside OVER clause we can write partition by and order by clause on which we want lag. and do not forget, like all window functions partition by is optional.
LEAD(column, lag_value) OVER (PARTITION BY expression ORDER BY expression)
Example of lead function: similar to previous example, here we have a table with revenue by quarter by year imagine we want a new column with revenue of next quarter printed beside for a comparative study. this is when we use lead function.
below is query with lead function. where we are pulling revenue of next row (one row after) when it is ordered by year and quarter in ascending manner.
project_1=# select *, lead(rev,1) over (order by year,qrtr) from rev;
year | qrtr | rev | lead
------+------+-----+------
2020 | Q1 | 10 | 20
2020 | Q2 | 20 | 30
2020 | Q3 | 30 | 40
2020 | Q4 | 40 | 15
2021 | Q1 | 15 | 25
2021 | Q2 | 25 | 35
2021 | Q3 | 35 | 45
2021 | Q4 | 45 |
(8 rows)Here is a video by crackdataanalytics explaining lead and lag functions using same examples as this blog