SQL Reference Guide

LAG windowing function

Declaration

LAG (scalar_expression [ , offset ] [ , default ] ) [ IGNORE NULLS | RESPECT NULLS ] OVER ( [ partition_by_clause ] order_by_clause )

Description

LAG accesses data from a previous row in the same result set without the use of a self-join.

Use this analytic function in a SELECT statement to compare values in the current row with values in a previous row:

expression - Defines the value the function returns from the specified row.
offset - a positive or negative value depending on before or after current row.
default - default is the value used when there is no row at the specified position.

order_by (required)
ROWS BETWEEN xxx AND yyy - ROWS and RANGE BETWEEN clauses may change the first row in the partition.

RANGE BETWEEN xxx AND yyy - ROWS and RANGE BETWEEN clauses may change the first row in the partition.

lag() and lead() return a value from a row that is before or after the current row in the current partition. lag() and lead() are synonyms except they treat the direction of the offset oppositely. That is, a negative offset value in lag() returns the same result as a positive offset in lead().

lag(expr, offset, default) == lead(expr, -offset, default)

A positive offset in lag( offset ) is the number of rows before the current row.
A negative offset in lag( offset ) is the number of rows after the current row.
A positive offset in lead( offset ) is the number of rows after the current row.
A negative offset in lead( offset ) is the number of rows before the current row.

Example

SELECT

Sale_Date,

Product_ID,

Price,

LAG(Price, 1, 0) OVER (

PARTITION BY Product_ID

ORDER BY Sale_Date

) AS Previous_Price,

Price - LAG(Price, 1, 0) OVER (

PARTITION BY Product_ID

ORDER BY Sale_Date

) AS Price_Change

FROM

Sales_History

WHERE

Product_ID = 'Product_A'

ORDER BY

Sale_Date;

Sale_Date Product_ID Price Previous_Price Price_Change
2024-01-01 Product_A 10.00 0.00 10.00
2024-01-15 Product_A 12.00 10.00 2.00
2024-01-20 Product_A 11.00 12.00 -1.00
2024-02-05 Product_A 11.00 11.00 0.00