Introduction to PostgreSQL LAG() function

PostgreSQLLAG() function allows you to access data of the previous row from the current row. It can be very useful for comparing the value of the current row with the value of the previous row.

Here’s the basic syntax of theLAG() function:

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

In this syntax:

expression

Theexpression is evaluated against the previous row at a specified offset. It can be a column, expression, orsubquery.

Theexpression must return a single value, and cannot be a window function.

offset

Theoffset is a positive integer that specifies the number of rows that come before the current row from which to access data. Theoffset can be an expression, subquery, or column. It defaults to 1 if you don’t specify it.

default_value

TheLAG() function will return thedefault_value in case theoffset goes beyond the scope of the partition. The function will return NULL if you omit thedefault_value.

PARTITION BY clause

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

By default, the function will treat the whole result set as a single partition if you omit thePARTITION BY clause.

ORDER BY clause

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

PostgreSQL LAG() function examples

We’ll use thesales table from theLEAD() function tutorial for the demonstration:

CREATE TABLE sales(year SMALLINT CHECK(year > 0),group_idINT NOT NULL,amountDECIMAL(10,2)NOT NULL,PRIMARY KEY(year,group_id));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)RETURNING*;

Here is the data from thesales function:

year | group_id | amount------+----------+--------- 2018 |        1 | 1474.00 2018 |        2 | 1787.00 2018 |        3 | 1760.00 2019 |        1 | 1915.00 2019 |        2 | 1911.00 2019 |        3 | 1118.00 2020 |        1 | 1646.00 2020 |        2 | 1975.00 2020 |        3 | 1516.00(9 rows)

1) Using PostgreSQL LAG() function over a result set example

This example uses theLAG() function to return the sales amount of the current year and the previous year of the group id 1:

SELECT  year,  amount,  LAG(amount, 1) OVER (    ORDER BY      year  ) previous_year_salesFROM  salesWHERE group_id = 1;

Output:

year | amount  | previous_year_sales------+---------+--------------------- 2018 | 1474.00 |                null 2019 | 1915.00 |             1474.00 2020 | 1646.00 |             1915.00(3 rows)

In this example:

• TheWHERE clause retrieves only the rows with the group id 1.
• TheLAG() function returns the sales amount of the previous year from the current year.

Since the sales table has no data for the year before 2018, theLAG() function returns NULL.

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

The following example uses theLAG() function to compare the sales of the current year with the sales of the previous year of each product group:

SELECT  year,  amount,  group_id,  LAG(amount,1)OVER (    PARTITION BY group_id    ORDER BY      year  ) previous_year_salesFROM  sales;

Output:

year | amount  | group_id | previous_year_sales------+---------+----------+--------------------- 2018 | 1474.00 |        1 |                null 2019 | 1915.00 |        1 |             1474.00 2020 | 1646.00 |        1 |             1915.00 2018 | 1787.00 |        2 |                null 2019 | 1911.00 |        2 |             1787.00 2020 | 1975.00 |        2 |             1911.00 2018 | 1760.00 |        3 |                null 2019 | 1118.00 |        3 |             1760.00 2020 | 1516.00 |        3 |             1118.00(9 rows)

In this example:

• ThePARTITION BY clause divides the rows into partitions by the group id.
• TheORDER BY clause sorts rows in each product group by years in ascending order.
• TheLAG() function is applied to each partition to return the sales of the previous year.

Summary

• Use the PostgreSQLLAG() function to access the data of the previous row from the current row.

Last updated onFebruary 12, 2024

On this page

• Introduction to PostgreSQL LAG() function
• PostgreSQL LAG() function examples
• Summary