LEAD() and LAG() Function in PostgreSQL

LEAD() Function returns the values from next row and previous row in the table. LAG() function returns the values from previous row  in PostgreSQL table 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 in PostgreSQL.

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:

Syntax of LEAD() function :

• column: The column from which you want to retrieve the value.
• Offset: (optional): The number of rows forward from the current row from which to get the value. The default is 1.
• default: (optional): The value to return if the lead operation goes past the end of the result set. The default is NULL.
• PARTITION BY (optional): Divides the result set into partitions to which the function is applied.
• ORDER BY: Specifies the order of rows in each partition.

LEAD() in PostgreSQL:

LEAD of 1 Example :

LEAD in PostgreSQL returns the next row in the table. LEAD() function takes column name and lead value as argument

 

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

So the resultant table

Output:

LEAD with PARTITION BY: LEAD with PARTITION BY in PostgreSQL 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;

Output:

Syntax of LAG() function:

• column: The column from which you want to retrieve the value.
• Offset: (optional): The number of rows back from the current row from which to get the value. The default is 1.
• default: (optional): The value to return if the lag operation goes past the beginning of the result set. The default is NULL.
• PARTITION BY (optional): Divides the result set into partitions to which the function is applied.
• ORDER BY: Specifies the order of rows in each partition.

LAG() in PostgreSQL:

LAG in PostgreSQL returns the previous row in the table

 

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

In the above code we have lag of 1 , So the resultant table will be

Output:

LAG with PARTITION BY: LAG with PARTITION BY in PostgreSQL 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

Output: