Introduction to PostgreSQL LEAD() function

PostgreSQLLEAD() function provide access to a row that follows the current row at a specified physical offset.

It means that from the current row, theLEAD() function can access data of the next row, the row after the next row, and so on.

TheLEAD() function is very useful for comparing the value of the current row with the value of the row that following the current row.

The following illustrates the syntax ofLEAD() function:

LEAD(expression [,offset [,default_value]])OVER (    [PARTITION BY partition_expression, ... ]    ORDER BY sort_expression [ASC | DESC], ...)

In this syntax:

expression

Theexpression is evaluated against the following row based on a specified offset from the current row. Theexpression can be a column, expression,subquery that must evaluate to a single value. And it cannot be a window function.

offset

Theoffset is a positive integer that specifies the number of rows forwarding from the current row from which to access data. Theoffset can be an expression, subquery, or column.

The offset defaults to 1 if you don’t specify it.

default_value

Thedefault_value is the return value if theoffset goes beyond the scope of the partition. Thedefault_value defaults to NULL if you omit it.

PARTITION BY clause

ThePARTITION BY clause divides rows into partitions to which theLEAD() function is applied.

By default, the whole result set is a single partition if you omit thePARTITION BY clause.

ORDER BY clause

TheORDER BY clause specifies the sort order of the rows in each partition to which theLEAD() function is applied.

PostgreSQL LEAD() function examples

Let’s set up a new table for the demonstration.

First,create a new table namedsales:

CREATE TABLE sales(year SMALLINT CHECK(year > 0),group_idINT NOT NULL,amountDECIMAL(10,2)NOT NULL,PRIMARY KEY(year,group_id));

Second,insert some rows into thesales table:

INSERT INTOsales(year, group_id, amount)VALUES(2018,1,1474),(2018,2,1787),(2018,3,1760),(2019,1,1915),(2019,2,1911),(2019,3,1118),(2020,1,1646),(2020,2,1975),(2020,3,1516);

Third, query data from thesales table:

SELECT * FROM sales;

1) Using PostgreSQL LEAD() function over a result set examples

The following query returns the total sales amount by year:

SELECTyear,SUM(amount)FROM salesGROUP BY yearORDER BY year;

This example uses theLEAD() function to return the sales amount of the current year and the next year:

WITH cteAS (SELECTyear,SUM(amount) amountFROM salesGROUP BY yearORDER BY year)SELECTyear,amount,LEAD(amount,1)OVER (ORDER BY year) next_year_salesFROMcte;

Here is the output:

In this example:

• First, theCTE returns the sales summarized by year.
• Then, the outer query uses theLEAD() function to return the sales of the following year for each row.

The following example uses two common table expressions to return the sales variance between the current year and the next:

WITH cteAS (SELECTyear,SUM(amount) amountFROM salesGROUP BY yearORDER BY year), cte2AS (SELECTyear,amount,LEAD(amount,1)OVER (ORDER BY year) next_year_salesFROMcte)SELECTyear,amount,next_year_sales,(next_year_sales- amount) varianceFROMcte2;

2) Using PostgreSQL LEAD() function over a partition example

The following statement uses theLEAD() function to compare the sales of the current year with sales of the next year for each product group:

SELECTyear,amount,group_id,LEAD(amount,1)OVER (PARTITION BY group_idORDER BY year) next_year_salesFROMsales;

This picture shows the output:

In this example:

• ThePARTITION BY clause distributes rows into product groups (or partitions) specified by group id.
• TheORDER BY clause sorts rows in each product group by years in ascending order.
• TheLEAD() function returns the sales of the next year from the current year for each product group.

In this tutorial, you have learned how to use the PostgreSQLLEAD() function to access a row at a specific physical offset which follows the current row.

Last updated onApril 11, 2020

On this page

• Introduction to PostgreSQL LEAD() function
• PostgreSQL LEAD() function examples