PostgreSQL – LEAD Function
In PostgreSQL, the LEAD() function is used to access a row that follows the current row, at a specific physical offset and is generally used for comparing the value of the current row with the value of the next row following the current row.
The syntax of LEAD() function looks like below:
Syntax: LEAD(expression [, offset [, default_value]]) OVER ( [PARTITION BY partition_expression, ... ] ORDER BY sort_expression [ASC | DESC], ... )
Let’s analyze the above syntax:
- The expression can be a column, expression, subquery that must evaluate to a single value.
- The offset is a positive integer that specifies the number of rows forwarding from the current row. It is generally an expression, subquery, or column. If offset is not set, it defaults to 1.
- The PARTITION BY clause divides rows into partitions. By default, it takes the query result as a single partition.
- The ORDER BY clause is used to sort the query result rows in each partition.
Example 1:
Let’s set up a new table for the demonstration named Match:
CREATE TABLE Match( year SMALLINT CHECK(year > 0), match_id INT NOT NULL, overs DECIMAL(10,2) NOT NULL, PRIMARY KEY(year,match_id) );
Now insert some data to it:
INSERT INTO Match(year, match_id, overs) VALUES (2018, 1, 140), (2018, 2, 174), (2018, 3, 130), (2019, 1, 90), (2019, 2, 100), (2019, 3, 120), (2020, 1, 50), (2020, 2, 70), (2020, 3, 20);
The below query uses the LEAD() function to return the overs of the current year and the average overs per year:
WITH cte AS ( SELECT year, SUM(overs) overs FROM Match GROUP BY year ORDER BY year ) SELECT year, overs, LEAD(overs, 1) OVER ( ORDER BY year ) year_average FROM cte;
Output:
Example 2:
The following statement uses the LEAD() function to compare the overs of the current year with overs of the next year for each group:
SELECT year, overs, match_id, LEAD(overs, 1) OVER ( PARTITION BY match_id ORDER BY year ) next_year_overs FROM Match;
Output:
Contact Us