最新レコードをSQLで取得する方法｜ROW_NUMBERとDELETE INSERTの使い分け

-- order_status_history（注文ステータス履歴テーブル）
-- history_id    : 履歴ID（主キー、AUTO INCREMENT）
-- order_id      : 注文ID
-- status        : ステータス（'pending','processing','shipped','delivered','cancelled'）
-- changed_at    : ステータス変更日時
-- changed_by    : 変更者（'system' / 'operator'）
-- note          : メモ

-- ROW_NUMBERで各注文の最新ステータスを取得する

WITH ranked AS (
    SELECT
        history_id,
        order_id,
        status,
        changed_at,
        changed_by,
        note,
        -- 注文ごとに変更日時の降順で番号を振る（1 = 最新）
        ROW_NUMBER() OVER (
            PARTITION BY order_id
            ORDER BY changed_at DESC
        )  AS rn
    FROM order_status_history
)
SELECT
    history_id,
    order_id,
    status        AS current_status,
    changed_at    AS last_changed_at,
    changed_by
FROM ranked
WHERE rn = 1  -- 最新の1件だけを残す
ORDER BY order_id;

-- MAX(changed_at)で最新日時を取り、再度JOINして全カラムを取得する

WITH latest_time AS (
    SELECT
        order_id,
        MAX(changed_at)  AS latest_changed_at
    FROM order_status_history
    GROUP BY order_id
)
SELECT
    h.order_id,
    h.status        AS current_status,
    h.changed_at    AS last_changed_at,
    h.changed_by
FROM order_status_history  h
JOIN latest_time           l
    ON  h.order_id   = l.order_id
    AND h.changed_at = l.latest_changed_at
ORDER BY h.order_id;

-- ステータスを更新するときの処理（例：O001 を delivered に更新）

-- Step 1: 既存レコードを削除
DELETE FROM order_current_status
WHERE order_id = 'O001';

-- Step 2: 新しいレコードを挿入
INSERT INTO order_current_status
    (order_id, status, changed_at, changed_by)
VALUES
    ('O001', 'delivered', '2024-12-04 15:00:00', 'system');

-- PostgreSQL / BigQuery の UPSERT（MERGE文）相当
MERGE INTO order_current_status AS target
USING (
    SELECT 'O001' AS order_id, 'delivered' AS status,
           TIMESTAMP '2024-12-04 15:00:00' AS changed_at, 'system' AS changed_by
) AS source
ON target.order_id = source.order_id
WHEN MATCHED THEN
    UPDATE SET status = source.status,
               changed_at = source.changed_at,
               changed_by = source.changed_by
WHEN NOT MATCHED THEN
    INSERT (order_id, status, changed_at, changed_by)
    VALUES (source.order_id, source.status, source.changed_at, source.changed_by);

-- SELECT は単純に全件取るだけ
SELECT *
FROM order_current_status
ORDER BY order_id;

-- 実務でよく使う「現在と1つ前」を同時に取得するパターン

WITH ranked AS (
    SELECT
        order_id,
        status,
        changed_at,
        ROW_NUMBER() OVER (
            PARTITION BY order_id ORDER BY changed_at DESC
        )  AS rn,
        LAG(status) OVER (
            PARTITION BY order_id ORDER BY changed_at DESC
        )  AS prev_status  -- 1つ前のステータス
    FROM order_status_history
)
SELECT
    order_id,
    status       AS current_status,
    prev_status,  -- 直前のステータス（例：shipped → delivered の遷移確認）
    changed_at
FROM ranked
WHERE rn = 1;

[OLTP DB] order_current_status（最新のみ・高速参照）
    ↓ ETL/CDC（変更データキャプチャ）
[DWH]     order_status_history（全履歴・分析用）

-- Treasure Dataでの実務パターン：
-- 毎朝バッチで「最新ステータスのみ」のスナップショットを作り直す

CREATE TABLE order_latest_status AS
WITH ranked AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY order_id
            ORDER BY changed_at DESC
        )  AS rn
    FROM order_status_history
    WHERE changed_at >= DATE_ADD('day', -90, CURRENT_DATE)  -- 直近90日分で十分な場合
)
SELECT
    order_id,
    status        AS current_status,
    changed_at    AS last_changed_at,
    changed_by
FROM ranked
WHERE rn = 1;

-- ❌ 間違い：MAX(changed_at) と status が対応していない

SELECT
    order_id,
    MAX(changed_at)  AS last_changed_at,
    status           -- これは「最新日時のstatusとは限らない」
FROM order_status_history
GROUP BY order_id, status;  -- status を GROUP BY に入れると全ステータスが出てしまう