时间数据的窗口函数： LEAD(), LAG()

现在我们的任务是——再往前走一步，学会用窗口函数分析时间数据。准备好了吗？希望你已经端起了咖啡，因为接下来会很有意思。

所以，像往常一样，先回答最重要的问题：为啥我们要用窗口函数（LEAD(), LAG()）？想象一下你在处理时间数据，比如事件日志、工时、时间序列，或者任何需要关注事件顺序的东西。

比如你想：

• 知道当前事件之后下一个事件是什么时候发生的。
• 算一下当前事件和上一个事件之间的时间差。
• 把数据排序，然后算一下每条记录之间的差异。

这时候就轮到两个超好用的函数登场了：LEAD() 和 LAG()。它们能让你在指定窗口里拿到上一行或者下一行的数据。就像你有一本魔法书，不翻页也能看到下一页写了啥。

LEAD() 和 LAG()：语法和基本原理

这两个函数的语法其实差不多：

LEAD(column_name, [offset], [default_value]) OVER (PARTITION BY column_name ORDER BY column_name)
LAG(column_name, [offset], [default_value]) OVER (PARTITION BY column_name ORDER BY column_name)

• column_name —— 你想取数据的那一列。
• offset（可选）—— 相对于当前行的偏移量，默认是 1。
• default_value（可选）—— 如果没有对应偏移的行（比如你在最后一行），就返回这个值。
• OVER() —— 这里定义“窗口”，也就是计算的范围。一般用 ORDER BY，有时候也会用 PARTITION BY 把数据分组。

例子：简单用 LEAD() 和 LAG()

我们先造个简单的 events 表来练手：

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_name TEXT NOT NULL,
    event_date TIMESTAMP NOT NULL
);

INSERT INTO events (event_name, event_date)
VALUES
    ('事件 A', '2023-10-01 10:00:00'),
    ('事件 B', '2023-10-01 11:00:00'),
    ('事件 C', '2023-10-01 12:00:00'),
    ('事件 D', '2023-10-01 13:00:00');

现在我们想看看每个事件的前一个和后一个事件分别是什么时候发生的：

SELECT
    id,
    event_name,
    event_date,
    LAG(event_date) OVER (ORDER BY event_date) AS 上一个事件,
    LEAD(event_date) OVER (ORDER BY event_date) AS 下一个事件
FROM events;

结果会是这样：

这里 LAG() 拿的是上一行的数据，LEAD() 拿的是下一行。第一个事件没前面可看，最后一个事件也没后面可看，所以它们会是 NULL。

例子：算事件之间的时间差

有时候我们想知道事件之间隔了多久。其实直接用时间相减就行：

SELECT
    id,
    event_name,
    event_date,
    event_date - LAG(event_date) OVER (ORDER BY event_date) AS 距离上次事件的时间
FROM events;

结果：

例子：用 PARTITION BY 分组

假设我们有好几个用户，每个人都有自己的事件。我们想算每个用户自己的事件之间的时间差。

先给表加个 user_id 列：

ALTER TABLE events ADD COLUMN user_id INT;

UPDATE events SET user_id = 1 WHERE id <= 2;
UPDATE events SET user_id = 2 WHERE id > 2;

现在有两个用户了。用 PARTITION BY 就能在每组里分别计算：

SELECT
    user_id,
    event_name,
    event_date,
    event_date - LAG(event_date) OVER (PARTITION BY user_id ORDER BY event_date) AS 距离上次事件的时间
FROM events;

结果：

实际场景下的用法举例

1. 事件日志：分析比如用户登录和退出之间的时间。
2. 时间追踪：算在某个任务上花了多久。
3. 行为分析：分析用户在网店里的操作顺序。
4. 累计指标计算：用窗口函数处理时间序列数据。

常见错误

用 LEAD() 和 LAG() 时，常见的坑有：

• 在 OVER() 里忘了写 ORDER BY。没它的话，函数根本不知道行的顺序。
• 时间区间或数据类型搞错了（TIMESTAMP 和 DATE）。
• 忽略了窗口开头和结尾出现的 NULL 值。

想避开这些坑，记得随时检查你的数据，确保窗口定义没问题。