PIVOT・UNPIVOTをSQLで実装する方法｜CASE WHENで縦横変換する

-- monthly_category_sales（月次カテゴリ別売上：縦長形式）
-- category    : カテゴリ名
-- sales_month : 集計月（DATE型、月初の日付）
-- total_sales : 売上金額
-- order_count : 注文件数

-- STEP1: 3ヶ月分を横展開する基本ピボット

SELECT
    category,

    -- 10月の売上
    MAX(CASE WHEN sales_month = DATE '2024-10-01' THEN total_sales END)  AS "2024_10",

    -- 11月の売上
    MAX(CASE WHEN sales_month = DATE '2024-11-01' THEN total_sales END)  AS "2024_11",

    -- 12月の売上
    MAX(CASE WHEN sales_month = DATE '2024-12-01' THEN total_sales END)  AS "2024_12"

FROM monthly_category_sales
GROUP BY category
ORDER BY category;

-- STEP2: 元データから直接ピボット（SUM を使う）

SELECT
    oi.category,

    SUM(CASE
        WHEN DATE_TRUNC('month', o.order_date) = DATE '2024-10-01'
        THEN oi.quantity * oi.unit_price
        ELSE 0
    END)  AS "2024_10",

    SUM(CASE
        WHEN DATE_TRUNC('month', o.order_date) = DATE '2024-11-01'
        THEN oi.quantity * oi.unit_price
        ELSE 0
    END)  AS "2024_11",

    SUM(CASE
        WHEN DATE_TRUNC('month', o.order_date) = DATE '2024-12-01'
        THEN oi.quantity * oi.unit_price
        ELSE 0
    END)  AS "2024_12",

    -- 合計列も一緒に出す
    SUM(oi.quantity * oi.unit_price)  AS "合計"

FROM orders       o
JOIN order_items  oi  ON o.order_id = oi.order_id
WHERE
    o.status = 'completed'
    AND o.order_date >= DATE '2024-10-01'
    AND o.order_date <  DATE '2025-01-01'
GROUP BY oi.category
ORDER BY 合計 DESC;

-- STEP3: 売上と前月比を交互に並べた完成版ピボット

WITH monthly_pivot AS (
    SELECT
        oi.category,
        SUM(CASE WHEN DATE_TRUNC('month', o.order_date) = DATE '2024-10-01'
                 THEN oi.quantity * oi.unit_price ELSE 0 END)  AS m10,
        SUM(CASE WHEN DATE_TRUNC('month', o.order_date) = DATE '2024-11-01'
                 THEN oi.quantity * oi.unit_price ELSE 0 END)  AS m11,
        SUM(CASE WHEN DATE_TRUNC('month', o.order_date) = DATE '2024-12-01'
                 THEN oi.quantity * oi.unit_price ELSE 0 END)  AS m12
    FROM orders       o
    JOIN order_items  oi  ON o.order_id = oi.order_id
    WHERE o.status = 'completed'
    AND o.order_date >= DATE '2024-10-01'
    AND o.order_date <  DATE '2025-01-01'
    GROUP BY oi.category
)
SELECT
    category,
    m10                                                         AS "10月売上",
    m11                                                         AS "11月売上",
    ROUND((m11 - m10) * 100.0 / NULLIF(m10, 0), 1)            AS "11月前月比%",
    m12                                                         AS "12月売上",
    ROUND((m12 - m11) * 100.0 / NULLIF(m11, 0), 1)            AS "12月前月比%",
    m10 + m11 + m12                                             AS "3ヶ月合計"
FROM monthly_pivot
ORDER BY "3ヶ月合計" DESC;

-- STEP4: 横長テーブルを縦長に変換する（アンピボット）

-- 元の横長テーブル（monthly_pivot_wide）
-- category | 2024_10 | 2024_11 | 2024_12
-- ヘアケア  | 3,200,000 | 3,800,000 | 4,100,000

WITH wide_table AS (
    -- 上記のSTEP3のmonthly_pivotをそのまま利用
    SELECT
        oi.category,
        SUM(CASE WHEN DATE_TRUNC('month', o.order_date) = DATE '2024-10-01'
                 THEN oi.quantity * oi.unit_price ELSE 0 END)  AS m10,
        SUM(CASE WHEN DATE_TRUNC('month', o.order_date) = DATE '2024-11-01'
                 THEN oi.quantity * oi.unit_price ELSE 0 END)  AS m11,
        SUM(CASE WHEN DATE_TRUNC('month', o.order_date) = DATE '2024-12-01'
                 THEN oi.quantity * oi.unit_price ELSE 0 END)  AS m12
    FROM orders o JOIN order_items oi ON o.order_id = oi.order_id
    WHERE o.status = 'completed'
    AND o.order_date >= DATE '2024-10-01'
    AND o.order_date <  DATE '2025-01-01'
    GROUP BY oi.category
),
-- 月のリストをCROSS JOINで展開
month_list AS (
    SELECT 1 AS month_id, DATE '2024-10-01' AS sales_month, '2024_10' AS month_label
    UNION ALL
    SELECT 2, DATE '2024-11-01', '2024_11'
    UNION ALL
    SELECT 3, DATE '2024-12-01', '2024_12'
)
SELECT
    w.category,
    m.sales_month,
    m.month_label,
    -- month_idに応じて対応する列の値を取り出す
    CASE m.month_id
        WHEN 1 THEN w.m10
        WHEN 2 THEN w.m11
        WHEN 3 THEN w.m12
    END  AS total_sales
FROM wide_table   w
CROSS JOIN month_list  m
ORDER BY w.category, m.month_id;

-- 直近12ヶ月分のCASE WHENを書いておき、
-- 対象期間外はNULLになる（BIツール側でNULL列を非表示にする）

SELECT
    category,
    MAX(CASE WHEN sales_month = DATE_ADD('month', -11, DATE_TRUNC('month', CURRENT_DATE))
             THEN total_sales END)  AS m_minus_11,
    MAX(CASE WHEN sales_month = DATE_ADD('month', -10, DATE_TRUNC('month', CURRENT_DATE))
             THEN total_sales END)  AS m_minus_10,
    -- ... m_minus_9 〜 m_minus_1 ...
    MAX(CASE WHEN sales_month = DATE_TRUNC('month', CURRENT_DATE)
             THEN total_sales END)  AS m_current
FROM monthly_category_sales
GROUP BY category;

-- No.4のコホートピボット（再掲・今回の手法との共通点）
SELECT
    cohort_month,
    MAX(CASE WHEN month_number = 0 THEN retention_rate END)  AS "M+0",
    MAX(CASE WHEN month_number = 1 THEN retention_rate END)  AS "M+1",
    MAX(CASE WHEN month_number = 2 THEN retention_rate END)  AS "M+2",
    -- ...
FROM retention
GROUP BY cohort_month;