MySQL tutorial: LAG [EN]

LAG(expr [, N[, default]]) [null_treatment] over_clause

Returns the value of expr from the row that lags (precedes) the current
row by N rows within its partition. If there is no such row, the return
value is default. For example, if N is 3, the return value is default
for the first two rows. If N or default are missing, the defaults are 1
and NULL, respectively.

N must be a literal nonnegative integer. If N is 0, expr is evaluated
for the current row.

Beginning with MySQL 8.0.22, N cannot be NULL. In addition, it must now
be an integer in the range 1 to 263, inclusive, in any of the following
forms:

o an unsigned integer constant literal

o a positional parameter marker (?)

o a user-defined variable

o a local variable in a stored routine

over_clause is as described in
https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html.
null_treatment is as described in the section introduction.

URL: https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html

Example

mysql> SELECT
t, val,
LAG(val) OVER w AS 'lag',
LEAD(val) OVER w AS 'lead',
val - LAG(val) OVER w AS 'lag diff',
val - LEAD(val) OVER w AS 'lead diff'
FROM series
WINDOW w AS (ORDER BY t);
+----------+------+------+------+----------+-----------+
| t | val | lag | lead | lag diff | lead diff |
+----------+------+------+------+----------+-----------+
| 12:00:00 | 100 | NULL | 125 | NULL | -25 |
| 13:00:00 | 125 | 100 | 132 | 25 | -7 |
| 14:00:00 | 132 | 125 | 145 | 7 | -13 |
| 15:00:00 | 145 | 132 | 140 | 13 | 5 |
| 16:00:00 | 140 | 145 | 150 | -5 | -10 |
| 17:00:00 | 150 | 140 | 200 | 10 | -50 |
| 18:00:00 | 200 | 150 | NULL | 50 | NULL |
+----------+------+------+------+----------+-----------+

mysql> SELECT n FROM fib ORDER BY n;
+------+
| n |
+------+
| 1 |
| 1 |
| 2 |
| 3 |
| 5 |
| 8 |
+------+

mysql> SELECT
n,
LAG(n, 1, 0) OVER w AS 'lag',
LEAD(n, 1, 0) OVER w AS 'lead',
n + LAG(n, 1, 0) OVER w AS 'next_n',
n + LEAD(n, 1, 0) OVER w AS 'next_next_n'
FROM fib
WINDOW w AS (ORDER BY n);
+------+------+------+--------+-------------+
| n | lag | lead | next_n | next_next_n |
+------+------+------+--------+-------------+
| 1 | 0 | 1 | 1 | 2 |
| 1 | 1 | 2 | 2 | 3 |
| 2 | 1 | 3 | 3 | 5 |
| 3 | 2 | 5 | 5 | 8 |
| 5 | 3 | 8 | 8 | 13 |
| 8 | 5 | 0 | 13 | 8 |
+------+------+------+--------+-------------+

MySQL:

LAG

Example