PostgreSQL行列変換完全ガイド：ピボット・クロス集計を使いこなす方法

データベース・SQL

「売上データを月別の横並び表にしたい」 「縦長のデータを見やすい表形式に変換したい」 「ExcelのピボットテーブルみたいなことをSQLでやりたい」

データベースのデータは通常、縦長（行形式）で保存されています。でも、レポートや分析では横長（列形式）の方が見やすいことが多いですよね。

PostgreSQLでは、この「行を列に」「列を行に」変換する方法がいくつかあります。最初は難しく感じるかもしれませんが、パターンを覚えてしまえば簡単です。

この記事では、PostgreSQLでの行列変換について、基本的なCASE文から、強力なCROSSTAB関数まで、実例を交えながら分かりやすく解説します。これを読めば、あなたもデータを自在に変形できるようになりますよ！

スポンサーリンク

行列変換の基本概念

そもそも行列変換とは？

行から列への変換（ピボット）：

変換前（縦長）:           変換後（横長）:
月    | 商品  | 売上      商品  | 1月  | 2月  | 3月
------|-------|------     ------|------|------|------
1月   | A     | 100       A     | 100  | 150  | 120
2月   | A     | 150       B     | 200  | 180  | 220
3月   | A     | 120
1月   | B     | 200
2月   | B     | 180
3月   | B     | 220

縦に並んでいたデータが、横に展開されます。

なぜ行列変換するの？

メリット：

  • レポートが見やすくなる
  • Excelとの連携が簡単
  • 比較分析がしやすい
  • グラフ化しやすい
  • データの傾向が分かりやすい

特に時系列データや、カテゴリー別の集計で威力を発揮します。

サンプルデータの準備

テストテーブルの作成

まず、実例で使うサンプルデータを用意しましょう。

-- 売上テーブルの作成
CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    sales_date DATE,
    product VARCHAR(50),
    category VARCHAR(50),
    amount DECIMAL(10,2)
);

-- サンプルデータの挿入
INSERT INTO sales (sales_date, product, category, amount) VALUES
('2024-01-15', 'ノートPC', '電子機器', 150000),
('2024-01-20', 'マウス', '周辺機器', 3000),
('2024-02-10', 'ノートPC', '電子機器', 180000),
('2024-02-15', 'キーボード', '周辺機器', 8000),
('2024-03-05', 'ノートPC', '電子機器', 160000),
('2024-03-20', 'マウス', '周辺機器', 3500),
('2024-01-25', 'モニター', '電子機器', 45000),
('2024-02-28', 'モニター', '電子機器', 48000),
('2024-03-15', 'キーボード', '周辺機器', 8500);

このデータを使って、様々な変換方法を学んでいきます。

CASE文を使った基本的なピボット

シンプルなピボット

最も基本的な方法は、CASE文を使うことです。

-- 月別・商品別の売上集計（横展開）
SELECT 
    product AS "商品",
    SUM(CASE WHEN EXTRACT(MONTH FROM sales_date) = 1 
        THEN amount ELSE 0 END) AS "1月",
    SUM(CASE WHEN EXTRACT(MONTH FROM sales_date) = 2 
        THEN amount ELSE 0 END) AS "2月",
    SUM(CASE WHEN EXTRACT(MONTH FROM sales_date) = 3 
        THEN amount ELSE 0 END) AS "3月",
    SUM(amount) AS "合計"
FROM sales
WHERE EXTRACT(YEAR FROM sales_date) = 2024
GROUP BY product
ORDER BY product;

結果：

商品        | 1月    | 2月    | 3月    | 合計
------------|--------|--------|--------|--------
キーボード  | 0      | 8000   | 8500   | 16500
ノートPC    | 150000 | 180000 | 160000 | 490000
マウス      | 3000   | 0      | 3500   | 6500
モニター    | 45000  | 48000  | 0      | 93000

複数条件でのピボット

カテゴリー別の集計も同時に行う場合：

-- カテゴリー別・月別の売上集計
SELECT 
    category AS "カテゴリー",
    COUNT(CASE WHEN EXTRACT(MONTH FROM sales_date) = 1 
        THEN 1 END) AS "1月件数",
    SUM(CASE WHEN EXTRACT(MONTH FROM sales_date) = 1 
        THEN amount ELSE 0 END) AS "1月売上",
    COUNT(CASE WHEN EXTRACT(MONTH FROM sales_date) = 2 
        THEN 1 END) AS "2月件数",
    SUM(CASE WHEN EXTRACT(MONTH FROM sales_date) = 2 
        THEN amount ELSE 0 END) AS "2月売上",
    COUNT(CASE WHEN EXTRACT(MONTH FROM sales_date) = 3 
        THEN 1 END) AS "3月件数",
    SUM(CASE WHEN EXTRACT(MONTH FROM sales_date) = 3 
        THEN amount ELSE 0 END) AS "3月売上"
FROM sales
WHERE EXTRACT(YEAR FROM sales_date) = 2024
GROUP BY category
ORDER BY category;

件数と金額を同時に集計できます。

CROSSTAB関数による高度なピボット

tablefunc拡張のインストール

CROSSTABを使うには、まず拡張機能をインストールします。

-- tablefunc拡張のインストール（一度だけ実行）
CREATE EXTENSION IF NOT EXISTS tablefunc;

基本的なCROSSTABの使い方

-- CROSSTAB関数での月別商品売上
SELECT * FROM crosstab(
    'SELECT product, 
            EXTRACT(MONTH FROM sales_date)::INT, 
            SUM(amount)
     FROM sales
     WHERE EXTRACT(YEAR FROM sales_date) = 2024
     GROUP BY product, EXTRACT(MONTH FROM sales_date)
     ORDER BY 1, 2',
    'SELECT DISTINCT EXTRACT(MONTH FROM sales_date)::INT
     FROM sales
     WHERE EXTRACT(YEAR FROM sales_date) = 2024
     ORDER BY 1'
) AS ct(
    product VARCHAR(50),
    "1月" DECIMAL(10,2),
    "2月" DECIMAL(10,2),
    "3月" DECIMAL(10,2)
);

CROSSTABは高速で、複雑な変換にも対応できます。

動的な列名での変換

列名を動的に生成する場合：

-- 動的SQLでCROSSTABを生成
DO $$
DECLARE
    sql_query TEXT;
    columns_list TEXT;
BEGIN
    -- 月のリストを動的に生成
    SELECT STRING_AGG(
        '"' || month_num || '月" DECIMAL(10,2)', 
        ', '
        ORDER BY month_num
    ) INTO columns_list
    FROM (
        SELECT DISTINCT EXTRACT(MONTH FROM sales_date)::INT as month_num
        FROM sales
        WHERE EXTRACT(YEAR FROM sales_date) = 2024
    ) months;
    
    -- 動的クエリの生成と実行
    sql_query := format('
        CREATE TEMP TABLE pivot_result AS
        SELECT * FROM crosstab(
            ''SELECT product, 
                    EXTRACT(MONTH FROM sales_date)::INT, 
                    SUM(amount)
             FROM sales
             WHERE EXTRACT(YEAR FROM sales_date) = 2024
             GROUP BY product, EXTRACT(MONTH FROM sales_date)
             ORDER BY 1, 2'',
            ''SELECT DISTINCT EXTRACT(MONTH FROM sales_date)::INT
             FROM sales
             WHERE EXTRACT(YEAR FROM sales_date) = 2024
             ORDER BY 1''
        ) AS ct(product VARCHAR(50), %s)', columns_list);
    
    EXECUTE sql_query;
END $$;

-- 結果の確認
SELECT * FROM pivot_result;

FILTER句を使った集計（PostgreSQL 9.4以降）

FILTER句でスッキリ記述

PostgreSQL 9.4以降では、FILTER句でより読みやすく書けます。

-- FILTER句を使った月別集計
SELECT 
    product AS "商品",
    SUM(amount) FILTER (WHERE EXTRACT(MONTH FROM sales_date) = 1) AS "1月",
    SUM(amount) FILTER (WHERE EXTRACT(MONTH FROM sales_date) = 2) AS "2月",
    SUM(amount) FILTER (WHERE EXTRACT(MONTH FROM sales_date) = 3) AS "3月",
    COUNT(*) FILTER (WHERE amount > 10000) AS "高額取引数",
    AVG(amount)::DECIMAL(10,2) AS "平均売上"
FROM sales
WHERE EXTRACT(YEAR FROM sales_date) = 2024
GROUP BY product
ORDER BY product;

CASE文よりも直感的で、パフォーマンスも良好です。

複数の集計関数との組み合わせ

-- 様々な集計を同時に実行
SELECT 
    category AS "カテゴリー",
    -- 件数
    COUNT(*) FILTER (WHERE EXTRACT(MONTH FROM sales_date) = 1) AS "1月件数",
    COUNT(*) FILTER (WHERE EXTRACT(MONTH FROM sales_date) = 2) AS "2月件数",
    COUNT(*) FILTER (WHERE EXTRACT(MONTH FROM sales_date) = 3) AS "3月件数",
    -- 合計
    SUM(amount) FILTER (WHERE EXTRACT(MONTH FROM sales_date) = 1) AS "1月合計",
    SUM(amount) FILTER (WHERE EXTRACT(MONTH FROM sales_date) = 2) AS "2月合計",
    SUM(amount) FILTER (WHERE EXTRACT(MONTH FROM sales_date) = 3) AS "3月合計",
    -- 最大値
    MAX(amount) FILTER (WHERE EXTRACT(MONTH FROM sales_date) = 1) AS "1月最大",
    MAX(amount) FILTER (WHERE EXTRACT(MONTH FROM sales_date) = 2) AS "2月最大",
    MAX(amount) FILTER (WHERE EXTRACT(MONTH FROM sales_date) = 3) AS "3月最大"
FROM sales
WHERE EXTRACT(YEAR FROM sales_date) = 2024
GROUP BY category;

アンピボット（列から行への変換）

UNION ALLを使った方法

横長のデータを縦長に戻す場合：

-- まず横長のテーブルを作成
CREATE TEMP TABLE monthly_sales AS
SELECT 
    product,
    SUM(amount) FILTER (WHERE EXTRACT(MONTH FROM sales_date) = 1) AS jan,
    SUM(amount) FILTER (WHERE EXTRACT(MONTH FROM sales_date) = 2) AS feb,
    SUM(amount) FILTER (WHERE EXTRACT(MONTH FROM sales_date) = 3) AS mar
FROM sales
GROUP BY product;

-- UNION ALLでアンピボット
SELECT product, '1月' as month, jan as amount FROM monthly_sales
UNION ALL
SELECT product, '2月' as month, feb as amount FROM monthly_sales
UNION ALL
SELECT product, '3月' as month, mar as amount FROM monthly_sales
ORDER BY product, month;

LATERAL JOINを使った方法

より効率的なアンピボット：

-- LATERAL JOINでアンピボット
SELECT 
    ms.product,
    v.month,
    v.amount
FROM monthly_sales ms
CROSS JOIN LATERAL (
    VALUES 
        ('1月', ms.jan),
        ('2月', ms.feb),
        ('3月', ms.mar)
) AS v(month, amount)
WHERE v.amount IS NOT NULL
ORDER BY ms.product, v.month;

LATERALを使うと、コードがスッキリします。

JSON関数を活用した変換

JSONでの柔軟な変換

PostgreSQL 9.3以降では、JSON関数で柔軟な変換ができます。

-- JSON形式での集計
SELECT 
    product,
    JSON_OBJECT_AGG(
        TO_CHAR(sales_date, 'YYYY-MM'), 
        amount
    ) AS monthly_sales
FROM sales
GROUP BY product;

-- 結果をJSON形式で取得
SELECT 
    product,
    monthly_sales->>'2024-01' AS "2024年1月",
    monthly_sales->>'2024-02' AS "2024年2月",
    monthly_sales->>'2024-03' AS "2024年3月"
FROM (
    SELECT 
        product,
        JSON_OBJECT_AGG(
            TO_CHAR(sales_date, 'YYYY-MM'), 
            SUM(amount)
        ) AS monthly_sales
    FROM sales
    GROUP BY product, TO_CHAR(sales_date, 'YYYY-MM')
) sub
GROUP BY product, monthly_sales;

JSONB_AGGでの配列集計

-- 商品ごとの取引履歴を配列で集計
SELECT 
    product,
    JSONB_AGG(
        JSONB_BUILD_OBJECT(
            'date', sales_date,
            'amount', amount,
            'category', category
        ) ORDER BY sales_date
    ) AS transaction_history
FROM sales
GROUP BY product;

JSONを使うと、階層的なデータ構造も表現できます。

パフォーマンス最適化

インデックスの活用

-- ピボット用のインデックス作成
CREATE INDEX idx_sales_date_product ON sales(sales_date, product);
CREATE INDEX idx_sales_product_amount ON sales(product, amount);

-- 部分インデックスで特定期間を高速化
CREATE INDEX idx_sales_2024 ON sales(sales_date, product, amount)
WHERE EXTRACT(YEAR FROM sales_date) = 2024;

マテリアライズドビューの活用

頻繁に使うピボットテーブルは、マテリアライズドビューにします。

-- マテリアライズドビューの作成
CREATE MATERIALIZED VIEW mv_monthly_sales AS
SELECT 
    product,
    EXTRACT(YEAR FROM sales_date) AS year,
    EXTRACT(MONTH FROM sales_date) AS month,
    SUM(amount) AS total_amount,
    COUNT(*) AS transaction_count,
    AVG(amount) AS avg_amount
FROM sales
GROUP BY product, EXTRACT(YEAR FROM sales_date), EXTRACT(MONTH FROM sales_date);

-- インデックスの作成
CREATE INDEX idx_mv_monthly_sales ON mv_monthly_sales(year, month, product);

-- 定期的な更新
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_sales;

実践的な応用例

前年同月比の計算

-- 前年同月比を含むピボットテーブル
WITH monthly_data AS (
    SELECT 
        EXTRACT(YEAR FROM sales_date) AS year,
        EXTRACT(MONTH FROM sales_date) AS month,
        product,
        SUM(amount) AS amount
    FROM sales
    GROUP BY EXTRACT(YEAR FROM sales_date), 
             EXTRACT(MONTH FROM sales_date), 
             product
)
SELECT 
    product,
    SUM(CASE WHEN year = 2023 AND month = 1 THEN amount END) AS "2023年1月",
    SUM(CASE WHEN year = 2024 AND month = 1 THEN amount END) AS "2024年1月",
    ROUND(
        (SUM(CASE WHEN year = 2024 AND month = 1 THEN amount END) - 
         SUM(CASE WHEN year = 2023 AND month = 1 THEN amount END)) * 100.0 / 
        NULLIF(SUM(CASE WHEN year = 2023 AND month = 1 THEN amount END), 0), 
        2
    ) AS "1月成長率%"
FROM monthly_data
GROUP BY product;

ランキング付きピボット

-- 月別売上ランキング付きピボット
WITH ranked_sales AS (
    SELECT 
        product,
        EXTRACT(MONTH FROM sales_date) AS month,
        SUM(amount) AS total,
        RANK() OVER (
            PARTITION BY EXTRACT(MONTH FROM sales_date) 
            ORDER BY SUM(amount) DESC
        ) AS rank
    FROM sales
    WHERE EXTRACT(YEAR FROM sales_date) = 2024
    GROUP BY product, EXTRACT(MONTH FROM sales_date)
)
SELECT 
    product,
    MAX(CASE WHEN month = 1 THEN total END) AS "1月売上",
    MAX(CASE WHEN month = 1 THEN rank END) AS "1月順位",
    MAX(CASE WHEN month = 2 THEN total END) AS "2月売上",
    MAX(CASE WHEN month = 2 THEN rank END) AS "2月順位",
    MAX(CASE WHEN month = 3 THEN total END) AS "3月売上",
    MAX(CASE WHEN month = 3 THEN rank END) AS "3月順位"
FROM ranked_sales
GROUP BY product
ORDER BY product;

よくある質問と解決策

Q: 列数が動的に変わる場合は？

A: 動的SQLを使用するか、JSON形式で出力してアプリケーション側で処理します。PL/pgSQLでプロシージャを作成するのも有効です。

Q: NULLを0として表示したい

A: COALESCE関数を使用します：

COALESCE(SUM(amount) FILTER (WHERE month = 1), 0) AS "1月"

Q: CROSSTABとCASE文、どちらが速い？

A: 一般的にCROSSTABの方が高速ですが、単純な変換ならCASE文でも十分です。データ量とクエリの複雑さで判断しましょう。

Q: 大量データでメモリ不足になる

A: パーティション化、インデックスの最適化、マテリアライズドビューの活用を検討してください。

Q: 日付の範囲を動的に指定したい

A: パラメータ化クエリやプリペアドステートメントを使用します。

まとめ：状況に応じた最適な方法を選ぼう

PostgreSQLでの行列変換は、様々な方法があります。

方法の選び方：

  • シンプルな変換：CASE文またはFILTER句
  • 複雑な変換：CROSSTAB関数
  • 動的な列：動的SQLまたはJSON関数
  • 逆変換：UNION ALLまたはLATERAL JOIN
  • 高頻度アクセス：マテリアライズドビュー

ベストプラクティス：

  • まずシンプルな方法から試す
  • パフォーマンスを測定して比較
  • 可読性とメンテナンス性を重視
  • 適切なインデックスを設定
  • 定期的にVACUUMとANALYZEを実行

この記事で紹介した技術を使えば、どんなデータ変換要求にも対応できるはずです。最初は簡単なCASE文から始めて、徐々に高度な技術にチャレンジしてみてください。

データの見せ方を変えるだけで、新しい発見があるかもしれません。さあ、あなたのデータを自在に変換して、価値ある情報を引き出しましょう！

Youtube

神話・歴史・伝承の解説チャンネル

スポンサーリンク

コメント

タイトルとURLをコピーしました