LAG
Returns the value from the row that is offset rows before the current one in the window partition.
PostgreSQL 17.9
LAG(column[, offset[, default]])- [ ]optional part
- column
- Column or expression whose value is taken from the previous row
- offset
- How many rows back to look, 1 by default; a negative value looks ahead, like LEADintegerOptional
- default
- Value of the same type as column used if there is no row at that offset, NULL by defaultOptional
Return value
A value of the same type as column; default or NULL if there is no row at that offset, for example for the first row of a partition
Examples
Shift one and two rows back
PostgreSQL 17.9
SELECT member_name,
birthday,
LAG(member_name) OVER (
ORDER BY birthday
) AS previous_member,
LAG(member_name, 2, 'none') OVER (
ORDER BY birthday
) AS two_rows_back
FROM FamilyMembers;Time since the previous purchase
PostgreSQL 17.9
SELECT family_member,
date,
LAG(date) OVER (
PARTITION BY family_member
ORDER BY date
) AS previous_date,
date - LAG(date) OVER (
PARTITION BY family_member
ORDER BY date
) AS since_previous
FROM Payments
ORDER BY family_member,
date;Details
The difference from the previous row is column - LAG(column) OVER (...): for the first row of a partition it is NULL. default must be of the same type as column.