LAG
Returns the value from the row that is offset rows before the current one in the window partition.
MySQL 8.1
LAG(column[, offset[, default]]) OVER ([PARTITION BY ...] [ORDER BY ...])Parameters
- column
- Column or expression whose value is taken from the previous row
- offsetINToptional
- How many rows back to look, a non-negative integer, 1 by default
- defaultoptional
- Value used if there is no row at that offset, NULL by default
- PARTITION BYoptional
- Columns that split rows into partitions, the offset does not cross partition bounds
- ORDER BYoptional
- Order of rows that defines which row is the previous one; without it the order is undefined
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
MySQL 8.1
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;Days since the previous payment of each family member
MySQL 8.1
SELECT family_member,
date,
DATEDIFF(
date,
LAG(date) OVER (
PARTITION BY family_member
ORDER BY date
)
) AS days_since_previous
FROM Payments;Details
A typical task is to compare a row with the previous one: the difference x - LAG(x) OVER (...) or DATEDIFF(date, LAG(date) OVER (...)) shows the change since the previous row. default is used only when there is no row at that offset; if the row exists but holds NULL, the result is NULL.