LAG
Returns the value from the row that is offset rows before the current one in the window partition.
MySQL 8.4
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, a non-negative integer, 1 by defaultINTOptional
- default
- Value 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
MySQL 8.4
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.4
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.