時間資料的視窗函數： LEAD(), LAG()

現在我們要再進一步，學會怎麼用視窗函數來分析時間資料。準備好了嗎？希望你已經拿好一杯咖啡，因為這真的蠻有趣的。

那麼，照慣例，先來回答最重要的問題：我們為什麼要用視窗函數（LEAD(), LAG()）？想像一下你在處理時間資料，不管是事件日誌、工時記錄、時間序列，還是任何需要關注事件順序的東西。

比如說，你可能想要：

• 知道每個事件之後發生的是什麼事件。
• 計算當前事件跟前一個事件之間的時間差。
• 排序資料並計算紀錄之間的差異。

這時候就輪到兩個超好用的函數登場了：LEAD() 跟 LAG()。它們可以讓你在一個特定的 window 裡，抓到前一筆或下一筆的資料。就像你有一本魔法書，不用翻頁就能偷看到下一頁的內容一樣。

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() — 這裡設定「window」，也就是要怎麼計算。最常見的是 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 來在每個 group 裡計算：

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 值，這些值會出現在 window 的頭尾。

為了避免這些錯誤，記得隨時檢查你的資料，還有 window 設定對不對。