Using LEAD and LAG Functions in PostgreSQL

Overview

The LEAD and LAG functions are a part of the window function family provided by PostgreSQL. Window functions allow users to perform calculations across sets of rows that are related to the current query row.

History and Purpose

The LEAD and LAG functions were introduced as part of the SQL:2003 standard, and they were implemented in PostgreSQL in version 8.4, which was released in 2009. These functions are designed to access data from a subsequent row (LEAD) or a previous row (LAG) without the need for a self-join, which can greatly improve query performance and readability.

Syntax, Parameters, and Return Value

LAG:

LAG(value_expression [, offset [, default_value]]) OVER ([partition_by_clause] order_by_clause)

Lead:

LEAD(value_expression [, offset [, default_value]]) OVER ([partition_by_clause] order_by_clause)

Parameters (of both functions):

• value_expression – the value to be returned from the leading or lagging row.
• offset – the number of rows forward (for LEAD) or backward (for LAG) the current row to obtain the value. If not specified, defaults to 1.
• default_value – the value to return when the LEAD or LAG operation goes beyond the bounds of the window frame. If not specified, defaults to NULL.
• The OVER clause defines the window over which the LEAD or LAG function operates and includes ORDER BY mandatory and PARTITION BY optional clauses.

Return Value: The value of the requested row’s column if it exists, otherwise, the default_value.

Examples

Example 1: Basic Usage of LAG

The following query demonstrates how LAG can be used to access the value of the previous row.

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    sale_date DATE NOT NULL,
    amount NUMERIC NOT NULL
);

INSERT INTO sales (sale_date, amount)
VALUES
    ('2023-01-01', 100),
    ('2023-01-02', 150),
    ('2023-01-03', 120);

SELECT
    sale_date,
    amount,
    LAG(amount) OVER (ORDER BY sale_date) AS previous_amount
FROM
    sales;

Example 2: Using LEAD with Custom Offset and Default

This snippet shows how to use LEAD to get the value 2 rows ahead, with a custom default.

SELECT
    sale_date,
    amount,
    LEAD(amount, 2, 0) OVER (ORDER BY sale_date) AS lead_amount
FROM
    sales;

Example 3: LAG with PARTITION BY Clause

This query illustrates the LAG function’s use within different partitions.

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    department VARCHAR NOT NULL,
    name VARCHAR NOT NULL,
    salary NUMERIC NOT NULL
);

INSERT INTO employees (department, name, salary)
VALUES
    ('HR', 'John', 70000),
    ('HR', 'Jane', 72000),
    ('Engineering', 'Alice', 75000),
    ('Engineering', 'Bob', 73000);

SELECT
    department,
    name,
    salary,
    LAG(salary) OVER (PARTITION BY department ORDER BY name) AS previous_salary_in_department
FROM
    employees;

Conclusion

LEAD and LAG functions are powerful tools for accessing nonadjacent rows in a result set. They can simplify complex queries and improve performance by avoiding self-joins. Used effectively in conjunction with the OVER clause, they allow for elegant solutions to common SQL problems.