对于一张表的一行数据而言,在其之上的是Lag, 在其之下的是Lead
+---------+------------+
LAG()
------------------------
current_row
------------------------
LEAD()
+-----------------------+
LEAD函数用法:
LEAD(col, offset, default)
col - 指你要操作的那一列
offset - 偏移几行,如果是1就是下1行,以此类推
default - 如果下一行不存在,用什么值填充
例题:
1709. 访问日期之间最大的空档期https://leetcode-cn.com/problems/biggest-window-between-visits/
题解:
SELECT
user_id,
MAX(DATEDIFF(next_day, visit_date)) AS biggest_window
FROM (
SELECT
user_id,
visit_date,
LEAD(visit_date, 1, '2021-1-1') OVER (PARTITION BY user_id ORDER BY visit_date) AS next_day
FROM UserVisits
) tmp
GROUP BY user_id
ORDER BY user_id