PostgreSQL – LAG Function

Last Updated : 01 Nov, 2024

In PostgreSQL, the LAG() function is a powerful window function that allows you to access data from a previous row within the same result set. It’s particularly useful for comparing values in the current row with values in the preceding row, making it ideal for analytical queries in PostgreSQL.

For example, we can use LAG() to analyze trends, such as comparing sales data over different time periods. In this article, we will explain how to use the PostgreSQL LAG function, starting with the syntax and practical Examples.

What is PostgreSQL LAG Function ?

The LAG() function operates on partitions created by the PARTITION BY clause. If no partition is specified, the function treats the entire result set as a single partition. The ORDER BY clause determines the order of rows within each partition to which the LAG() function is applied. The LAG() function is a window function that retrieves the value of a column from a prior row in the same partition, based on a specified offset. If the offset extends beyond the start of the partition, a default value can be returned, preventing null results in the analysis.

Syntax

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

Key Terms:

expression: This sets the basis for the comparison between the current row and the row at the specified offset. It can be a column, an expression, or a subquery.
offset: A positive integer specifying the number of rows before the current row. If not specified, it defaults to 1.
default_value: This value is returned if the offset goes beyond the scope of the partition.

Examples of the PostgreSQL LAG() Function

Below are some practical examples that demonstrate how the LAG() function works in PostgreSQL, with each example showcasing a unique use case.

Example 1: Comparing Current Year Sales with Previous Year Sales

Suppose we have a table named sales that tracks sales for different years and product groups. Here’s the structure. Let’s set up a new table for the demonstration named ‘sales‘:

Query:

CREATE TABLE sales(
    year SMALLINT CHECK(year > 0),
    group_id INT NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY(year, group_id)
);

Insert some data into the ‘sales' table:

INSERT INTO sales (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);

To use the LAG() function to return the sales amount of the current year and the previous year, you can write the following query:

WITH cte AS (
    SELECT 
        year, 
        SUM(amount) AS amount
    FROM sales
    GROUP BY year
) 
SELECT
    year, 
    amount,
    LAG(amount, 1) OVER (ORDER BY year) AS last_year_sales
FROM cte;

Output

Example 1: Output

Explanation:

The LAG(amount, 1) retrieves the amount from the previous row.
ORDER BY year ensures that the comparison is made by year in ascending order.

Example 2: Comparing Sales of Current Year with Previous Year for Each Product Group

This example uses the LAG() function to compare the sales of the current year with the sales of the previous year of each product group:

Query:

SELECT
    year, 
    amount,
    group_id,
    LAG(amount, 1) OVER (
        PARTITION BY group_id
        ORDER BY year
    ) AS last_year_sales
FROM sales;

Output

Example 2: Output

Explanation:

Here, PARTITION BY group_id divides the data by group_id, ensuring that LAG() operates separately within each product group.
ORDER BY year orders rows within each product group, enabling comparison by year.

Important Points About LAG() Function in PostgreSQL

The LAG() function is used to compare the values of the current row with the previous row within a specified window of rows.
The LAG() function is a window function, meaning it operates over a set of table rows that are somehow related to the current row.
Similar to the LEAD() function, which accesses data from rows that come after the current row, LAG() function accesses data from rows before the current row.
By using the PARTITION BY clause, you can divide the result set into partitions and apply the LAG() function to each partition independently.
The ORDER BY clause specifies the order of rows within each partition to which the LAG() function is applied.

Conclusion

The LAG() function in PostgreSQL is essential for data comparison within ordered datasets, providing analysts and developers with a means to retrieve prior row values. Whether we are working with financial data, inventory levels, or time-based analysis, the LAG() function offers a structured way to view relationships in sequential data. By Using PARTITION BY and ORDER BY clauses, you can use LAG() across distinct groups, gaining insights into historical trends and performance over time.

PostgreSQL- LPAD Function

RajuKumar19

Improve

Article Tags :

PostgreSQL – LAG Function

What is PostgreSQL LAG Function ?

Key Terms:

Examples of the PostgreSQL LAG() Function

Example 1: Comparing Current Year Sales with Previous Year Sales

Explanation:

Example 2: Comparing Sales of Current Year with Previous Year for Each Product Group

Explanation:

Important Points About LAG() Function in PostgreSQL

Conclusion

Similar Reads