価格改定履歴をSQLで扱う方法｜注文時点の正しい単価を取得するクエリ

-- order_items テーブルに unit_price_at_order カラムを持つ
-- order_id, product_id, quantity, unit_price_at_order（注文時点の単価）

-- product_price_history（価格改定履歴：Type 2 SCD）
-- product_id   : 商品ID
-- price        : 価格
-- valid_from   : この価格が有効になった日時
-- valid_to     : この価格が無効になった日時（NULLは現在有効）

-- products（商品マスタ）
-- product_id   : 商品ID
-- product_name : 商品名
-- category     : カテゴリ

-- product_price_history（価格改定履歴）
-- price_id     : 価格履歴ID（主キー）
-- product_id   : 商品ID
-- price        : 価格（円）
-- valid_from   : 有効開始日時（TIMESTAMP）
-- valid_to     : 有効終了日時（TIMESTAMP、NULLは現在有効）
-- changed_by   : 変更者

-- orders（注文テーブル）
-- order_id     : 注文ID
-- customer_id  : 顧客ID
-- ordered_at   : 注文日時（TIMESTAMP）
-- status       : 'completed' / 'cancelled'

-- order_items（注文明細）
-- order_id     : 注文ID
-- product_id   : 商品ID
-- quantity     : 数量
-- （unit_price カラムがない、または信頼できない前提）

-- STEP1: valid_fromだけの履歴からvalid_toをLEADで補完する

WITH price_history_with_valid_to AS (
    SELECT
        price_id,
        product_id,
        price,
        valid_from,

        -- 次のレコードのvalid_fromの1秒前がvalid_to
        DATE_ADD('second', -1,
            LEAD(valid_from) OVER (
                PARTITION BY product_id
                ORDER BY valid_from ASC
            )
        )  AS valid_to,

        -- 最新レコードか
        CASE
            WHEN LEAD(valid_from) OVER (
                PARTITION BY product_id
                ORDER BY valid_from ASC
            ) IS NULL
            THEN 1 ELSE 0
        END  AS is_current
    FROM product_price_history
)
SELECT *
FROM price_history_with_valid_to
ORDER BY product_id, valid_from;

-- STEP2: 注文時点の単価を取得する（AS-OF JOIN）

WITH price_history AS (
    SELECT
        product_id,
        price,
        valid_from,
        DATE_ADD('second', -1,
            LEAD(valid_from) OVER (
                PARTITION BY product_id
                ORDER BY valid_from ASC
            )
        )  AS valid_to
    FROM product_price_history
)
SELECT
    oi.order_id,
    oi.product_id,
    p.product_name,
    o.ordered_at,
    oi.quantity,
    ph.price                                    AS unit_price_at_order,
    oi.quantity * ph.price                      AS line_total
FROM order_items   oi
JOIN orders        o   ON oi.order_id    = o.order_id
JOIN products      p   ON oi.product_id  = p.product_id
-- 注文日時が価格の有効期間内に収まるレコードを結合
JOIN price_history ph
    ON  oi.product_id  = ph.product_id
    AND o.ordered_at   >= ph.valid_from
    AND (
        o.ordered_at   <  ph.valid_to      -- valid_toが存在する場合
        OR ph.valid_to IS NULL              -- または現在有効（valid_toがない）
    )
WHERE o.status = 'completed'
ORDER BY oi.order_id, oi.product_id;

-- STEP3: 価格改定前後の売上・数量比較

WITH price_history AS (
    SELECT
        product_id,
        price,
        valid_from,
        DATE_ADD('second', -1,
            LEAD(valid_from) OVER (
                PARTITION BY product_id
                ORDER BY valid_from ASC
            )
        )  AS valid_to,
        ROW_NUMBER() OVER (
            PARTITION BY product_id
            ORDER BY valid_from ASC
        )  AS price_version  -- 価格改定の世代番号
    FROM product_price_history
),
order_with_price AS (
    SELECT
        oi.product_id,
        ph.price                    AS unit_price,
        ph.price_version,
        ph.valid_from               AS price_valid_from,
        ph.valid_to                 AS price_valid_to,
        oi.quantity,
        oi.quantity * ph.price      AS line_total
    FROM order_items   oi
    JOIN orders        o   ON oi.order_id   = o.order_id
    JOIN price_history ph
        ON  oi.product_id  = ph.product_id
        AND o.ordered_at   >= ph.valid_from
        AND (o.ordered_at  <  ph.valid_to OR ph.valid_to IS NULL)
    WHERE o.status = 'completed'
)
SELECT
    product_id,
    price_version,
    unit_price,
    price_valid_from,
    price_valid_to,

    -- 集計
    COUNT(*)                        AS order_line_count,
    SUM(quantity)                   AS total_quantity,
    SUM(line_total)                 AS total_sales,
    ROUND(AVG(quantity), 2)         AS avg_quantity_per_order,

    -- 前バージョンとの数量比較（LAG で前世代を参照）
    LAG(SUM(quantity)) OVER (
        PARTITION BY product_id
        ORDER BY price_version ASC
    )  AS prev_version_quantity,

    -- 数量の変化率
    ROUND(
        (SUM(quantity) - LAG(SUM(quantity)) OVER (
            PARTITION BY product_id ORDER BY price_version ASC
        )) * 100.0
        / NULLIF(LAG(SUM(quantity)) OVER (
            PARTITION BY product_id ORDER BY price_version ASC
        ), 0)
    , 1)  AS quantity_change_pct
FROM order_with_price
GROUP BY product_id, price_version, unit_price, price_valid_from, price_valid_to
ORDER BY product_id, price_version;

-- STEP4: 価格履歴が存在しない注文明細を検知する

WITH price_history AS (
    SELECT
        product_id, price, valid_from,
        DATE_ADD('second', -1,
            LEAD(valid_from) OVER (
                PARTITION BY product_id ORDER BY valid_from ASC
            )
        )  AS valid_to
    FROM product_price_history
)
SELECT
    oi.order_id,
    oi.product_id,
    o.ordered_at,
    '価格履歴なし'  AS issue
FROM order_items   oi
JOIN orders        o   ON oi.order_id   = o.order_id
LEFT JOIN price_history ph
    ON  oi.product_id = ph.product_id
    AND o.ordered_at  >= ph.valid_from
    AND (o.ordered_at <  ph.valid_to OR ph.valid_to IS NULL)
WHERE
    o.status  = 'completed'
    AND ph.product_id IS NULL  -- JOINできなかった行
ORDER BY o.ordered_at;