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() 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 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;
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
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