SCDをSQLで実装する方法｜履歴を保持するディメンション設計

-- member_rank_history（会員ランク履歴テーブル：Type 2 SCD）
-- surrogate_key  : サロゲートキー（このテーブル固有のID）
-- customer_id    : 顧客ID（自然キー）
-- rank           : 会員ランク
-- valid_from     : このランクが有効になった日
-- valid_to       : このランクが無効になった日（現在有効なレコードはNULLまたは9999-12-31）
-- is_current     : 現在有効なレコードかどうかのフラグ（1=現在、0=過去）

-- STEP1: 現在有効なレコードを取得する

-- 方法1: is_current フラグを使う（シンプルで高速）
SELECT
    customer_id,
    rank   AS current_rank,
    valid_from
FROM member_rank_history
WHERE is_current = 1
ORDER BY customer_id;

-- 方法2: valid_to IS NULL を使う（is_currentカラムがない場合）
SELECT
    customer_id,
    rank   AS current_rank,
    valid_from
FROM member_rank_history
WHERE valid_to IS NULL
ORDER BY customer_id;

-- STEP2: 特定時点（2023-01-01）でのランクを取得する

SELECT
    customer_id,
    rank        AS rank_at_that_date,
    valid_from,
    valid_to
FROM member_rank_history
WHERE
    valid_from <= DATE '2023-01-01'  -- その日より前から有効だった
    AND (
        valid_to >  DATE '2023-01-01'  -- その日より後まで有効だった
        OR valid_to IS NULL             -- または現在も有効（有効期限なし）
    )
ORDER BY customer_id;

-- STEP3: 注文時点の会員ランクを取得する

SELECT
    o.order_id,
    o.customer_id,
    o.order_date,
    o.total_amount,
    r.rank          AS rank_at_order_time,  -- 注文時点のランク
    r.valid_from,
    r.valid_to
FROM orders              o
JOIN member_rank_history r
    ON  o.customer_id = r.customer_id
    -- 注文日がランクの有効期間内に収まっていること
    AND r.valid_from <= o.order_date
    AND (
        r.valid_to   >  o.order_date
        OR r.valid_to IS NULL
    )
WHERE o.status = 'completed'
ORDER BY o.customer_id, o.order_date;

-- STEP4: ランク変更時の更新処理（C001 が 2024-04-01 にプラチナに昇格）

-- Step1: 現在有効なレコードの valid_to を更新する（終了させる）
UPDATE member_rank_history
SET
    valid_to   = DATE '2024-03-31',  -- 昇格日の前日
    is_current = 0
WHERE
    customer_id = 'C001'
    AND is_current = 1;

-- Step2: 新しいランクのレコードを追加する
INSERT INTO member_rank_history
    (customer_id, rank, valid_from, valid_to, is_current)
VALUES
    ('C001', 'プラチナ', DATE '2024-04-01', NULL, 1);

-- 代替：全件を再構築するアプローチ

CREATE TABLE member_rank_history_new AS

-- 既存レコードで、C001の現在レコードを終了させて新しい履歴を追加
SELECT
    customer_id,
    rank,
    valid_from,
    -- C001の現在レコードのvalid_toを昇格日前日に更新
    CASE
        WHEN customer_id = 'C001' AND is_current = 1
        THEN DATE '2024-03-31'
        ELSE valid_to
    END  AS valid_to,
    CASE
        WHEN customer_id = 'C001' AND is_current = 1
        THEN 0
        ELSE is_current
    END  AS is_current
FROM member_rank_history

UNION ALL

-- 新しいプラチナレコードを追加
SELECT
    'C001'          AS customer_id,
    'プラチナ'      AS rank,
    DATE '2024-04-01' AS valid_from,
    NULL            AS valid_to,
    1               AS is_current;

-- STEP5: valid_fromだけの履歴からLEADでvalid_toを計算する

-- 元テーブル（valid_from のみ）
-- member_rank_changes
-- customer_id, rank, changed_at（ランクが変わった日）

WITH rank_with_valid_to AS (
    SELECT
        customer_id,
        rank,
        changed_at                          AS valid_from,

        -- 次のレコードのchanged_atの前日がvalid_to
        DATE_ADD('day', -1,
            LEAD(changed_at) OVER (
                PARTITION BY customer_id
                ORDER BY changed_at ASC
            )
        )                                   AS valid_to,

        -- 次のレコードがない（最新）かどうか
        CASE
            WHEN LEAD(changed_at) OVER (
                PARTITION BY customer_id
                ORDER BY changed_at ASC
            ) IS NULL
            THEN 1 ELSE 0
        END                                 AS is_current
    FROM member_rank_changes
)
SELECT *
FROM rank_with_valid_to
ORDER BY customer_id, valid_from;

-- 注文時点のランク別の年次購買金額集計

WITH rank_history AS (
    -- LEAD で valid_to を自動計算
    SELECT
        customer_id,
        rank,
        changed_at  AS valid_from,
        DATE_ADD('day', -1,
            LEAD(changed_at) OVER (
                PARTITION BY customer_id ORDER BY changed_at ASC
            )
        )            AS valid_to
    FROM member_rank_changes
)
SELECT
    EXTRACT(YEAR FROM o.order_date)  AS order_year,
    r.rank                           AS rank_at_order_time,
    COUNT(DISTINCT o.customer_id)    AS customer_count,
    COUNT(o.order_id)                AS order_count,
    SUM(o.total_amount)              AS total_sales,
    ROUND(AVG(o.total_amount), 0)    AS avg_order_value
FROM orders        o
JOIN rank_history  r
    ON  o.customer_id = r.customer_id
    AND r.valid_from  <= o.order_date
    AND (r.valid_to   >  o.order_date OR r.valid_to IS NULL)
WHERE o.status = 'completed'
GROUP BY EXTRACT(YEAR FROM o.order_date), r.rank
ORDER BY order_year, r.rank;