INSERT INTO t1 VALUES
(1,3), (1,5), (8,2), (5,7), (5,6),
(10,1), (6,4), (3,9), (3,9), (7,2),
(7,5), (2,6), (9,10), (9,5), (4,8);
SELECT a, b,
LAG(b) OVER w AS 'lag',
LEAD(b) OVER w AS 'lead',
a - LAG(b) OVER w AS 'lag diff',
a - LEAD(b) OVER w AS 'lead diff'
FROM t1
WINDOW w AS (ORDER BY a, b);
+------+------+------+------+----------+-----------+
| a | b | lag | lead | lag diff | lead diff |
+------+------+------+------+----------+-----------+
| 1 | 3 | NULL | 5 | NULL | -4 |
| 1 | 5 | 3 | 6 | -2 | -5 |
| 2 | 6 | 5 | 9 | -3 | -7 |
| 3 | 9 | 6 | 9 | -3 | -6 |
| 3 | 9 | 9 | 8 | -6 | -5 |
| 4 | 8 | 9 | 6 | -5 | -2 |
| 5 | 6 | 8 | 7 | -3 | -2 |
| 5 | 7 | 6 | 4 | -1 | 1 |
| 6 | 4 | 7 | 2 | -1 | 4 |
| 7 | 2 | 4 | 5 | 3 | 2 |
| 7 | 5 | 2 | 2 | 5 | 5 |
| 8 | 2 | 5 | 5 | 3 | 3 |
| 9 | 5 | 2 | 10 | 7 | -1 |
| 9 | 10 | 5 | 1 | 4 | 8 |
| 10 | 1 | 10 | NULL | 0 | NULL |
+------+------+------+------+----------+-----------+
© 2024 MariaDB. All rights reserved.
