LEAD() and LAG() Function in Postgresql

LEAD() and LAG() Function returns the values from next row and previous row in the table respectively. These functions allow you to access the data from a subsequent row without using any SELF JOIN. LEAD and LAG function along with PARTITION BY gets the next and previous rows of the group within the table.

LEAD() Function in Postgresql: LEAD() Function returns the values from next row in the table

LAG() Function in Postgresql: LAG() Function returns the values from previous row in the table

The table BASKET which we use for depiction is shown below

Lead and Lag function in postgresql 0

 

 

LEAD() in postgresql:

LEAD in postresql returns the next row in the table

SELECT *,LEAD (price,1) OVER (ORDER BY price) AS next_price
FROM basket;

So the resultant table

Lead and Lag function in postgresql 1

 

LEAD with PARTITION BY:

LEAD with PARTITION BY in postresql returns the next row in a group


SELECT *,LEAD (price,1) OVER (PARTITION BY ITEM_GROUP ORDER BY price) AS groups_next_price
FROM basket;

Lead and Lag function in postgresql 2

 

LAG() in postgresql:

LAG in postresql returns the previous row in the table


SELECT *,LAG (price,1) OVER (ORDER BY price) AS previous_price
FROM basket;

So the resultant table

Lead and Lag function in postgresql 3

 

LAG with PARTITION BY:

LAG with PARTITION BY in postresql returns the previous row in a group


SELECT *,LAG (price,1) OVER (PARTITION BY ITEM_GROUP ORDER BY price) AS 
groups_previous_price FROM basket;

So the resultant table will be

Lead and Lag function in postgresql 4

 

                                                                                                   

Author

  • Sridhar Venkatachalam

    With close to 10 years on Experience in data science and machine learning Have extensively worked on programming languages like R, Python (Pandas), SAS, Pyspark.