時系列データのためのウィンドウ関数: LEAD(), LAG()

さて、今回はもう一歩進んで、時系列データを分析するためにウィンドウ関数を使う方法をマスターしよう。準備はいい？コーヒー片手に読んでくれると嬉しいな、面白い内容になるから！

じゃあ、いつものようにまず一番大事な質問から：なんでウィンドウ関数（LEAD(), LAG()）が必要なの？ってこと。例えば、君が時系列データを扱ってるとしよう。イベントログだったり、勤務時間だったり、時系列データだったり、何でもいいけど、イベントの順番が大事なやつね。

例えば、こんなことがしたい時：

• 今のイベントの後に次のイベントがいつ起きたか知りたい。
• 今のイベントと前のイベントの時間差を計算したい。
• データを並べ替えて、レコード同士の差分を計算したい。

ここで登場するのが超便利な2つの関数：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になるよ。

例：イベント間の差分

時々、イベント間でどれくらい時間が経ったか知りたいことがあるよね。そんな時は単純に時間を引き算すればOK：

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;

これでユーザーが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を無視しちゃう。

こういうミスを防ぐには、必ずデータをチェックして、どんなウィンドウで計算するかちゃんと決めてからやろう！