価格弾力性をSQLで計算する方法｜値上げによる販売数変化を定量化する

-- product_price_history（価格改定履歴）
-- product_id, price, valid_from, valid_to

-- order_items（注文明細）
-- order_id, product_id, quantity, unit_price

-- orders（注文テーブル）
-- order_id, order_date, status

-- STEP1: 価格帯別・月次の販売数量を集計する

WITH price_history AS (
    -- SCDテーブル。valid_toがNULLなら現在有効
    SELECT
        product_id,
        price,
        valid_from,
        COALESCE(valid_to, TIMESTAMP '2099-12-31 23:59:59')  AS valid_to_safe
    FROM product_price_history
),
monthly_sales AS (
    SELECT
        oi.product_id,
        DATE_TRUNC('month', o.order_date)  AS sales_month,
        SUM(oi.quantity)                    AS total_quantity,
        COUNT(DISTINCT o.order_id)          AS order_count,
        -- その月の適用価格（月初に有効だった価格）
        MAX(ph.price)                       AS price_in_month
    FROM orders       o
    JOIN order_items  oi ON o.order_id   = oi.order_id
    JOIN price_history ph
        ON  oi.product_id  = ph.product_id
        -- 月初日が価格の有効期間内にある
        AND DATE_TRUNC('month', o.order_date) >= CAST(ph.valid_from AS DATE)
        AND DATE_TRUNC('month', o.order_date) <  CAST(ph.valid_to_safe AS DATE)
    WHERE o.status = 'completed'
    AND o.order_date >= DATE '2023-01-01'
    AND o.order_date <  DATE '2025-01-01'
    GROUP BY oi.product_id, DATE_TRUNC('month', o.order_date)
)
SELECT *
FROM monthly_sales
ORDER BY product_id, sales_month;

-- STEP2: 弧弾力性（中点法）で価格改定前後を比較する

WITH price_history AS (
    SELECT product_id, price, valid_from,
           COALESCE(valid_to, TIMESTAMP '2099-12-31 23:59:59') AS valid_to_safe
    FROM product_price_history
),
monthly_sales AS (
    SELECT
        oi.product_id,
        DATE_TRUNC('month', o.order_date)  AS sales_month,
        SUM(oi.quantity)                    AS total_quantity,
        MAX(ph.price)                       AS price_in_month
    FROM orders o
    JOIN order_items  oi ON o.order_id   = oi.order_id
    JOIN price_history ph
        ON oi.product_id = ph.product_id
        AND DATE_TRUNC('month', o.order_date) >= CAST(ph.valid_from AS DATE)
        AND DATE_TRUNC('month', o.order_date) <  CAST(ph.valid_to_safe AS DATE)
    WHERE o.status = 'completed'
    AND o.order_date >= DATE '2023-01-01'
    AND o.order_date <  DATE '2025-01-01'
    GROUP BY oi.product_id, DATE_TRUNC('month', o.order_date)
),
price_change_periods AS (
    -- 価格改定が起きた前後の月を特定する
    SELECT
        product_id,
        price_in_month,
        sales_month,
        total_quantity,
        LAG(price_in_month) OVER (PARTITION BY product_id ORDER BY sales_month)
            AS prev_price,
        LAG(total_quantity) OVER (PARTITION BY product_id ORDER BY sales_month)
            AS prev_quantity,
        -- 価格が変化したか
        CASE WHEN price_in_month
             <> LAG(price_in_month) OVER (PARTITION BY product_id ORDER BY sales_month)
             THEN 1 ELSE 0 END  AS is_price_change
    FROM monthly_sales
),
arc_elasticity AS (
    SELECT
        product_id,
        sales_month                                           AS change_month,
        prev_price                                            AS price_before,
        price_in_month                                        AS price_after,
        prev_quantity                                         AS qty_before,
        total_quantity                                        AS qty_after,

        -- 価格変化率（中点法）
        ROUND(
            (price_in_month - prev_price) * 1.0
            / ((price_in_month + prev_price) / 2.0)
        , 4)  AS price_change_pct,

        -- 数量変化率（中点法）
        ROUND(
            (total_quantity - prev_quantity) * 1.0
            / ((total_quantity + prev_quantity) / 2.0)
        , 4)  AS qty_change_pct,

        -- 弧弾力性（価格弾力性）
        ROUND(
            ((total_quantity - prev_quantity) * 1.0
             / ((total_quantity + prev_quantity) / 2.0))
            /
            NULLIF(
                (price_in_month - prev_price) * 1.0
                / ((price_in_month + prev_price) / 2.0)
            , 0)
        , 4)  AS arc_elasticity

    FROM price_change_periods
    WHERE is_price_change = 1  -- 価格改定が発生した月のみ
)
SELECT
    product_id,
    change_month,
    price_before,
    price_after,
    qty_before,
    qty_after,
    ROUND(price_change_pct * 100, 2)  AS price_change_pct,
    ROUND(qty_change_pct * 100, 2)    AS qty_change_pct,
    arc_elasticity,
    -- 弾力性の解釈
    CASE
        WHEN arc_elasticity < -1   THEN '弾力的（価格敏感）'
        WHEN arc_elasticity BETWEEN -1 AND 0 THEN '非弾力的（価格鈍感）'
        WHEN arc_elasticity > 0    THEN '正の弾力性（ギッフェン財の可能性）'
        ELSE                            '計算不可'
    END  AS elasticity_type
FROM arc_elasticity
ORDER BY product_id, change_month;

-- STEP3: 対数回帰による弾力性（複数時点を使った安定推定）

WITH price_history AS (
    SELECT product_id, price, valid_from,
           COALESCE(valid_to, TIMESTAMP '2099-12-31 23:59:59') AS valid_to_safe
    FROM product_price_history
),
monthly_sales AS (
    SELECT
        oi.product_id,
        DATE_TRUNC('month', o.order_date)  AS sales_month,
        SUM(oi.quantity)                    AS total_quantity,
        MAX(ph.price)                       AS price_in_month
    FROM orders o
    JOIN order_items  oi ON o.order_id   = oi.order_id
    JOIN price_history ph
        ON oi.product_id = ph.product_id
        AND DATE_TRUNC('month', o.order_date) >= CAST(ph.valid_from AS DATE)
        AND DATE_TRUNC('month', o.order_date) <  CAST(ph.valid_to_safe AS DATE)
    WHERE o.status = 'completed'
    AND o.order_date >= DATE '2023-01-01'
    AND o.order_date <  DATE '2025-01-01'
    GROUP BY oi.product_id, DATE_TRUNC('month', o.order_date)
),
log_transformed AS (
    SELECT
        product_id,
        sales_month,
        total_quantity,
        price_in_month,
        LN(price_in_month)  AS ln_price,
        LN(total_quantity)  AS ln_quantity
    FROM monthly_sales
    WHERE total_quantity > 0  -- ゼロは対数変換不可
    AND price_in_month  > 0
)
SELECT
    product_id,
    COUNT(*)  AS data_points,

    -- CORR() で対数回帰の傾き（= 価格弾力性）を計算
    ROUND(CORR(ln_price, ln_quantity), 4)  AS log_elasticity,

    -- 方法1（手動計算）：最小二乗法の公式
    ROUND(
        (COUNT(*) * SUM(ln_price * ln_quantity) - SUM(ln_price) * SUM(ln_quantity))
        / NULLIF(
            COUNT(*) * SUM(ln_price * ln_price) - SUM(ln_price) * SUM(ln_price)
        , 0)
    , 4)  AS log_elasticity_ols,

    -- 切片 α の計算
    ROUND(
        (SUM(ln_quantity) - (
            (COUNT(*) * SUM(ln_price * ln_quantity) - SUM(ln_price) * SUM(ln_quantity))
            / NULLIF(COUNT(*) * SUM(ln_price * ln_price) - SUM(ln_price) * SUM(ln_price), 0)
        ) * SUM(ln_price)
        ) / COUNT(*)
    , 4)  AS log_intercept,

    -- 推定の決定係数（R²）
    ROUND(POWER(CORR(ln_price, ln_quantity), 2), 4)  AS r_squared,

    -- 価格の範囲
    ROUND(MIN(price_in_month), 0)  AS min_price,
    ROUND(MAX(price_in_month), 0)  AS max_price,

    -- 弾力性の解釈
    CASE
        WHEN CORR(ln_price, ln_quantity) < -1 THEN '弾力的（価格敏感）'
        WHEN CORR(ln_price, ln_quantity) BETWEEN -1 AND 0 THEN '非弾力的'
        ELSE '正の弾力性（要確認）'
    END  AS elasticity_type
FROM log_transformed
GROUP BY product_id
HAVING COUNT(*) >= 6  -- 最低6時点のデータがある商品のみ
ORDER BY log_elasticity ASC;

-- STEP4: 価格弾力性から最適価格を推定する

WITH elasticity_data AS (
    -- STEP3の結果を使う（商品ごとの弾力性と切片）
    SELECT
        product_id,
        log_elasticity   AS beta,    -- 弾力性
        log_intercept    AS alpha,   -- 切片
        -- 現在価格（最新の価格）
        MAX(price_in_month)  AS current_price,
        -- 現在の月次販売数（最新2ヶ月平均）
        AVG(CASE WHEN sales_month >= DATE '2024-08-01' THEN total_quantity END)
            AS current_qty
    FROM (
        -- STEP3のlog_transformedとlog_elasticityの結果を結合（実際はCTEとして連結する）
        SELECT ms.product_id, ms.sales_month, ms.total_quantity, ms.price_in_month,
               ols.log_elasticity, ols.log_intercept
        FROM monthly_sales ms
        JOIN (SELECT product_id,
                ROUND((COUNT(*) * SUM(ln_price * ln_quantity) - SUM(ln_price) * SUM(ln_quantity))
                    / NULLIF(COUNT(*) * SUM(ln_price * ln_price) - SUM(ln_price) * SUM(ln_price), 0), 4) AS log_elasticity,
                ROUND((SUM(ln_quantity) - ((COUNT(*) * SUM(ln_price * ln_quantity) - SUM(ln_price) * SUM(ln_quantity))
                    / NULLIF(COUNT(*) * SUM(ln_price * ln_price) - SUM(ln_price) * SUM(ln_price), 0)) * SUM(ln_price)) / COUNT(*), 4) AS log_intercept
              FROM log_transformed GROUP BY product_id) ols ON ms.product_id = ols.product_id
    )
    GROUP BY product_id, log_elasticity, log_intercept
)
SELECT
    product_id,
    ROUND(beta, 3)          AS price_elasticity,
    ROUND(current_price, 0) AS current_price,
    ROUND(current_qty, 0)   AS current_monthly_qty,

    -- 現在の月次売上推定
    ROUND(current_price * current_qty, 0)  AS current_monthly_revenue,

    -- 価格別シナリオ分析（-20% / -10% / +10% / +20%）
    -- 数量予測：Q_new = Q_current × (P_new / P_current)^beta
    ROUND(current_price * 0.80, 0)  AS price_minus_20pct,
    ROUND(current_qty * POWER(0.80, beta), 0)  AS qty_at_minus_20pct,
    ROUND(current_price * 0.80 * current_qty * POWER(0.80, beta), 0)  AS rev_at_minus_20pct,

    ROUND(current_price * 0.90, 0)  AS price_minus_10pct,
    ROUND(current_qty * POWER(0.90, beta), 0)  AS qty_at_minus_10pct,
    ROUND(current_price * 0.90 * current_qty * POWER(0.90, beta), 0)  AS rev_at_minus_10pct,

    ROUND(current_price * 1.10, 0)  AS price_plus_10pct,
    ROUND(current_qty * POWER(1.10, beta), 0)  AS qty_at_plus_10pct,
    ROUND(current_price * 1.10 * current_qty * POWER(1.10, beta), 0)  AS rev_at_plus_10pct,

    ROUND(current_price * 1.20, 0)  AS price_plus_20pct,
    ROUND(current_qty * POWER(1.20, beta), 0)  AS qty_at_plus_20pct,
    ROUND(current_price * 1.20 * current_qty * POWER(1.20, beta), 0)  AS rev_at_plus_20pct

FROM elasticity_data
ORDER BY ABS(beta) DESC;