「売上データを月ごとの列で表示したい」
「アンケート結果を質問項目ごとに横に並べたい」
「縦持ちのデータを横持ちに変換したい！」
データベースを扱っていると、こんな要望がよく出てきますよね。
これは「ピボット（pivot）」や「クロスタブ（crosstab）」と呼ばれる操作で、行データを列に変換してデータを見やすくする技術です。ExcelのピボットテーブルをSQLで実現するようなものと考えると分かりやすいでしょう。
PostgreSQLには、この行列変換を実現する様々な方法があります。標準SQLのCASE文を使う方法から、専用のcrosstab関数まで、それぞれに特徴があるんです。
この記事では、PostgreSQLで行を列に変換するあらゆる方法を、実際のビジネスシーンを想定した具体例とともに、初心者の方でも理解できるように詳しく解説していきます。
データの可視化や分析がグッと楽になる技術を、一緒にマスターしていきましょう！
行列変換（ピボット）の基本概念
そもそもピボットとは？
データの行と列を入れ替える操作を理解しましょう。
縦持ちデータ（変換前）：
年月 | 商品 | 売上
---------|--------|-------
2025-01 | A商品 | 100
2025-01 | B商品 | 200
2025-02 | A商品 | 150
2025-02 | B商品 | 250横持ちデータ（変換後）：
年月 | A商品 | B商品
---------|-------|-------
2025-01 | 100 | 200
2025-02 | 150 | 250このように、特定の列の値を新しい列名として展開する操作がピボットです。
なぜ行列変換が必要なのか
ピボット変換が必要になる典型的なシーンを紹介します。
ビジネスでの活用例：
- 月別売上を商品ごとに横並びで比較
- 社員の勤怠データを日付ごとに表示
- アンケート結果を質問項目別に集計
- 在庫数を倉庫別に一覧表示
- 成績を科目別に横に並べる
メリット：
- データが見やすくなる
- Excelなどへの出力が簡単
- グラフ作成に適した形式
- 比較分析がしやすい
レポート作成には欠かせない技術です。
PostgreSQLでの実現方法
PostgreSQLで行列変換を実現する主な方法は4つあります。
1. CASE文を使った方法
- 標準SQLで実装可能
- どのバージョンでも動作
- 列数が固定の場合に有効
2. crosstab関数
- tablefunc拡張機能を使用
- 高速で効率的
- 動的な列には対応しづらい
3. FILTER句を使った方法
- PostgreSQL 9.4以降で使用可能
- CASE文より簡潔
- 集計と組み合わせやすい
4. 動的SQL
- 列数が可変の場合に対応
- PL/pgSQLで実装
- 複雑だが柔軟性が高い
それぞれの方法を詳しく見ていきましょう。
CASE文を使った基本的な方法
シンプルな例から始める
最も基本的なCASE文を使った方法を、売上データを例に説明します。
サンプルテーブルの作成：
-- 売上テーブルの作成
CREATE TABLE sales (
sale_date DATE,
product VARCHAR(50),
amount DECIMAL(10,2)
);
-- サンプルデータの挿入
INSERT INTO sales VALUES
('2025-01-01', 'A商品', 100),
('2025-01-01', 'B商品', 200),
('2025-01-01', 'C商品', 150),
('2025-01-02', 'A商品', 120),
('2025-01-02', 'B商品', 180),
('2025-01-02', 'C商品', 160);CASE文でピボット変換：
SELECT
sale_date,
SUM(CASE WHEN product = 'A商品' THEN amount ELSE 0 END) AS "A商品",
SUM(CASE WHEN product = 'B商品' THEN amount ELSE 0 END) AS "B商品",
SUM(CASE WHEN product = 'C商品' THEN amount ELSE 0 END) AS "C商品"
FROM sales
GROUP BY sale_date
ORDER BY sale_date;結果：
sale_date | A商品 | B商品 | C商品
-----------|-------|-------|-------
2025-01-01 | 100 | 200 | 150
2025-01-02 | 120 | 180 | 160各商品の売上が列として展開されました！
複数の集計を組み合わせる
合計だけでなく、平均や最大値も同時に取得できます。
SELECT
DATE_TRUNC('month', sale_date) AS month,
-- 合計
SUM(CASE WHEN product = 'A商品' THEN amount END) AS "A商品_合計",
SUM(CASE WHEN product = 'B商品' THEN amount END) AS "B商品_合計",
-- 平均
AVG(CASE WHEN product = 'A商品' THEN amount END) AS "A商品_平均",
AVG(CASE WHEN product = 'B商品' THEN amount END) AS "B商品_平均",
-- 件数
COUNT(CASE WHEN product = 'A商品' THEN 1 END) AS "A商品_件数",
COUNT(CASE WHEN product = 'B商品' THEN 1 END) AS "B商品_件数"
FROM sales
GROUP BY DATE_TRUNC('month', sale_date)
ORDER BY month;複数の統計値を一度に取得できるのが便利です。
NULL値の扱い
NULL値を適切に処理する方法です。
-- NULL値を0として扱う
SELECT
sale_date,
COALESCE(SUM(CASE WHEN product = 'A商品' THEN amount END), 0) AS "A商品",
COALESCE(SUM(CASE WHEN product = 'B商品' THEN amount END), 0) AS "B商品"
FROM sales
GROUP BY sale_date;
-- NULL値を文字列で表示
SELECT
sale_date,
COALESCE(
SUM(CASE WHEN product = 'A商品' THEN amount END)::TEXT,
'データなし'
) AS "A商品"
FROM sales
GROUP BY sale_date;用途に応じてNULL値の表示を制御できます。
crosstab関数の使い方
tablefunc拡張機能のインストール
crosstab関数を使うには、まず拡張機能をインストールします。
-- 拡張機能のインストール（管理者権限が必要）
CREATE EXTENSION IF NOT EXISTS tablefunc;
-- インストール確認
SELECT * FROM pg_extension WHERE extname = 'tablefunc';一度インストールすれば、そのデータベースで永続的に使用できます。
基本的なcrosstab使用例
crosstab関数の基本的な使い方を説明します。
-- crosstabを使ったピボット変換
SELECT * FROM crosstab(
-- ソースクエリ（3列：行キー、列キー、値）
'SELECT
sale_date,
product,
amount
FROM sales
ORDER BY sale_date, product',
-- 列の値のリスト
'SELECT DISTINCT product FROM sales ORDER BY product'
) AS ct (
sale_date DATE,
"A商品" DECIMAL(10,2),
"B商品" DECIMAL(10,2),
"C商品" DECIMAL(10,2)
);ポイント：
- 第1引数：データを取得するクエリ（必ず3列）
- 第2引数：列として展開する値のリスト
- AS句で結果の型を定義する必要がある
集計を含むcrosstab
集計処理を含む、より実践的な例です。
-- 月別・商品別の売上集計
SELECT * FROM crosstab(
'SELECT
DATE_TRUNC(''month'', sale_date)::DATE AS month,
product,
SUM(amount) AS total
FROM sales
GROUP BY month, product
ORDER BY month, product',
'VALUES (''A商品''), (''B商品''), (''C商品'')'
) AS ct (
month DATE,
"A商品" DECIMAL(10,2),
"B商品" DECIMAL(10,2),
"C商品" DECIMAL(10,2)
);複数の集計値を扱う場合：
-- 売上と件数を同時に取得
SELECT * FROM crosstab(
'SELECT
sale_date,
product || ''_売上'' AS category,
SUM(amount)
FROM sales
GROUP BY sale_date, product
UNION ALL
SELECT
sale_date,
product || ''_件数'',
COUNT(*)
FROM sales
GROUP BY sale_date, product
ORDER BY 1, 2'
) AS ct (
sale_date DATE,
"A商品_件数" BIGINT,
"A商品_売上" DECIMAL,
"B商品_件数" BIGINT,
"B商品_売上" DECIMAL,
"C商品_件数" BIGINT,
"C商品_売上" DECIMAL
);crosstabは高速ですが、列の定義が必要な点に注意が必要です。
FILTER句を使った方法
PostgreSQL 9.4以降の新機能
FILTER句を使うと、CASE文より簡潔に書けます。
-- FILTER句を使ったピボット変換
SELECT
sale_date,
SUM(amount) FILTER (WHERE product = 'A商品') AS "A商品",
SUM(amount) FILTER (WHERE product = 'B商品') AS "B商品",
SUM(amount) FILTER (WHERE product = 'C商品') AS "C商品",
SUM(amount) AS "合計"
FROM sales
GROUP BY sale_date
ORDER BY sale_date;FILTER句のメリット：
- CASE文より読みやすい
- 条件が明確
- 複雑な条件も書きやすい
複雑な条件での使用
FILTER句は複雑な条件でも威力を発揮します。
-- 複数条件でのフィルタリング
SELECT
DATE_TRUNC('month', sale_date)::DATE AS month,
-- 特定金額以上の売上
SUM(amount) FILTER (WHERE product = 'A商品' AND amount >= 100) AS "A商品_100以上",
SUM(amount) FILTER (WHERE product = 'A商品' AND amount < 100) AS "A商品_100未満",
-- 件数と合計を同時に
COUNT(*) FILTER (WHERE product = 'B商品') AS "B商品_件数",
SUM(amount) FILTER (WHERE product = 'B商品') AS "B商品_合計",
-- 平均値
AVG(amount) FILTER (WHERE product = 'C商品') AS "C商品_平均"
FROM sales
GROUP BY DATE_TRUNC('month', sale_date);条件を自由に組み合わせられるのが強みです。
パフォーマンスの比較
CASE文とFILTER句のパフォーマンスを比較してみましょう。
-- 実行計画の確認（CASE文）
EXPLAIN ANALYZE
SELECT
sale_date,
SUM(CASE WHEN product = 'A商品' THEN amount END) AS "A商品"
FROM sales
GROUP BY sale_date;
-- 実行計画の確認（FILTER句）
EXPLAIN ANALYZE
SELECT
sale_date,
SUM(amount) FILTER (WHERE product = 'A商品') AS "A商品"
FROM sales
GROUP BY sale_date;一般的に、FILTER句の方がわずかに高速です。
動的な列数に対応する方法
PL/pgSQLで動的SQL生成
列数が事前に分からない場合の対処法です。
-- 動的ピボット関数の作成
CREATE OR REPLACE FUNCTION dynamic_pivot(
table_name TEXT,
row_column TEXT,
category_column TEXT,
value_column TEXT,
aggregate_func TEXT DEFAULT 'SUM'
)
RETURNS TABLE (result JSON) AS $$
DECLARE
columns TEXT;
query TEXT;
BEGIN
-- 動的に列名を生成
EXECUTE format(
'SELECT STRING_AGG(
DISTINCT format(
''%s(%s) FILTER (WHERE %s = ''''%%s'''') AS "%%s"'',
''%s'',
''%s'',
''%s'',
%s,
%s
), '', ''
) FROM %s',
aggregate_func,
value_column,
category_column,
category_column,
category_column,
table_name
) INTO columns;
-- 動的クエリの生成と実行
query := format(
'SELECT row_to_json(t) FROM (
SELECT %s, %s
FROM %s
GROUP BY %s
ORDER BY %s
) t',
row_column,
columns,
table_name,
row_column,
row_column
);
RETURN QUERY EXECUTE query;
END;
$$ LANGUAGE plpgsql;動的ピボットの使用例
作成した関数を使ってみましょう。
-- 動的ピボットの実行
SELECT * FROM dynamic_pivot(
'sales', -- テーブル名
'sale_date', -- 行として使う列
'product', -- 列として展開する列
'amount', -- 値の列
'SUM' -- 集計関数
);
-- 結果をJSON形式で取得
SELECT
result->>'sale_date' AS sale_date,
result->>'A商品' AS "A商品",
result->>'B商品' AS "B商品",
result->>'C商品' AS "C商品"
FROM dynamic_pivot('sales', 'sale_date', 'product', 'amount', 'SUM');商品が増えても自動的に対応できます。
より実用的な動的ピボット
実際のビジネスで使える、より高機能な実装です。
CREATE OR REPLACE FUNCTION create_pivot_view(
view_name TEXT,
source_table TEXT,
row_column TEXT,
category_column TEXT,
value_column TEXT
)
RETURNS VOID AS $$
DECLARE
columns TEXT;
query TEXT;
BEGIN
-- カテゴリーを取得して列定義を生成
EXECUTE format(
'SELECT STRING_AGG(
format(
''SUM(CASE WHEN %I = %%L THEN %I END) AS %%I'',
category,
category
), '', ''
ORDER BY category
) FROM (
SELECT DISTINCT %I AS category
FROM %I
ORDER BY 1
) t',
category_column,
value_column,
category_column,
source_table
) INTO columns;
-- ビューを作成
query := format(
'CREATE OR REPLACE VIEW %I AS
SELECT %I, %s
FROM %I
GROUP BY %I
ORDER BY %I',
view_name,
row_column,
columns,
source_table,
row_column,
row_column
);
EXECUTE query;
RAISE NOTICE 'View % created successfully', view_name;
END;
$$ LANGUAGE plpgsql;
-- 使用例
SELECT create_pivot_view(
'sales_pivot_view',
'sales',
'sale_date',
'product',
'amount'
);
-- 作成されたビューを確認
SELECT * FROM sales_pivot_view;ビューとして保存することで、再利用が簡単になります。
実践的な使用例
月別売上レポート
実際のビジネスでよく使うレポート形式です。
-- 月別・商品別売上レポート
WITH monthly_sales AS (
SELECT
TO_CHAR(sale_date, 'YYYY-MM') AS month,
product,
SUM(amount) AS total_amount,
COUNT(*) AS transaction_count,
AVG(amount) AS avg_amount
FROM sales
WHERE sale_date >= CURRENT_DATE - INTERVAL '6 months'
GROUP BY TO_CHAR(sale_date, 'YYYY-MM'), product
)
SELECT
month AS "月",
-- 売上金額
SUM(total_amount) FILTER (WHERE product = 'A商品') AS "A商品_売上",
SUM(total_amount) FILTER (WHERE product = 'B商品') AS "B商品_売上",
SUM(total_amount) FILTER (WHERE product = 'C商品') AS "C商品_売上",
-- 取引件数
SUM(transaction_count) FILTER (WHERE product = 'A商品') AS "A商品_件数",
SUM(transaction_count) FILTER (WHERE product = 'B商品') AS "B商品_件数",
SUM(transaction_count) FILTER (WHERE product = 'C商品') AS "C商品_件数",
-- 合計
SUM(total_amount) AS "合計売上",
SUM(transaction_count) AS "合計件数"
FROM monthly_sales
GROUP BY month
ORDER BY month;社員の勤怠管理表
勤怠データを日付ごとに横並びで表示します。
-- 勤怠テーブルの例
CREATE TABLE attendance (
employee_id INT,
attendance_date DATE,
status VARCHAR(10) -- '出勤', '休暇', '欠勤' など
);
-- 月間勤怠表の作成
SELECT
employee_id AS "社員ID",
MAX(CASE WHEN EXTRACT(DAY FROM attendance_date) = 1 THEN status END) AS "1日",
MAX(CASE WHEN EXTRACT(DAY FROM attendance_date) = 2 THEN status END) AS "2日",
MAX(CASE WHEN EXTRACT(DAY FROM attendance_date) = 3 THEN status END) AS "3日",
-- ... 31日まで続く
COUNT(CASE WHEN status = '出勤' THEN 1 END) AS "出勤日数",
COUNT(CASE WHEN status = '休暇' THEN 1 END) AS "休暇日数"
FROM attendance
WHERE DATE_TRUNC('month', attendance_date) = '2025-01-01'
GROUP BY employee_id
ORDER BY employee_id;アンケート結果の集計
アンケート結果を見やすく整形します。
-- アンケート回答テーブル
CREATE TABLE survey_responses (
respondent_id INT,
question_id INT,
answer INT -- 1-5の評価
);
-- 質問別の回答分布
SELECT
question_id AS "質問ID",
COUNT(*) FILTER (WHERE answer = 1) AS "評価1",
COUNT(*) FILTER (WHERE answer = 2) AS "評価2",
COUNT(*) FILTER (WHERE answer = 3) AS "評価3",
COUNT(*) FILTER (WHERE answer = 4) AS "評価4",
COUNT(*) FILTER (WHERE answer = 5) AS "評価5",
AVG(answer)::DECIMAL(3,2) AS "平均評価",
COUNT(*) AS "回答数"
FROM survey_responses
GROUP BY question_id
ORDER BY question_id;
-- 回答者別の質問ごとの評価
SELECT
respondent_id,
MAX(answer) FILTER (WHERE question_id = 1) AS "Q1",
MAX(answer) FILTER (WHERE question_id = 2) AS "Q2",
MAX(answer) FILTER (WHERE question_id = 3) AS "Q3",
AVG(answer)::DECIMAL(3,2) AS "平均評価"
FROM survey_responses
GROUP BY respondent_id;パフォーマンス最適化
インデックスの活用
ピボット処理を高速化するインデックス戦略です。
-- 複合インデックスの作成
CREATE INDEX idx_sales_date_product ON sales(sale_date, product);
CREATE INDEX idx_sales_product_amount ON sales(product, amount);
-- パーティションテーブルでの最適化
CREATE TABLE sales_partitioned (
sale_date DATE,
product VARCHAR(50),
amount DECIMAL(10,2)
) PARTITION BY RANGE (sale_date);
-- 月ごとのパーティション作成
CREATE TABLE sales_2025_01 PARTITION OF sales_partitioned
FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');マテリアライズドビューの活用
頻繁に使うピボット結果を事前計算しておきます。
-- マテリアライズドビューの作成
CREATE MATERIALIZED VIEW sales_pivot_mv AS
SELECT
sale_date,
SUM(amount) FILTER (WHERE product = 'A商品') AS "A商品",
SUM(amount) FILTER (WHERE product = 'B商品') AS "B商品",
SUM(amount) FILTER (WHERE product = 'C商品') AS "C商品"
FROM sales
GROUP BY sale_date;
-- インデックスの作成
CREATE UNIQUE INDEX ON sales_pivot_mv (sale_date);
-- 定期的な更新
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_pivot_mv;大量データでの考慮事項
データ量が多い場合の対策です。
-- バッチ処理での実行
DO $$
DECLARE
start_date DATE := '2025-01-01';
end_date DATE;
BEGIN
FOR i IN 0..11 LOOP
end_date := start_date + INTERVAL '1 month';
-- 月ごとに処理
INSERT INTO sales_summary_pivot
SELECT
DATE_TRUNC('month', sale_date)::DATE AS month,
SUM(amount) FILTER (WHERE product = 'A商品') AS "A商品",
SUM(amount) FILTER (WHERE product = 'B商品') AS "B商品"
FROM sales
WHERE sale_date >= start_date
AND sale_date < end_date
GROUP BY DATE_TRUNC('month', sale_date);
start_date := end_date;
END LOOP;
END $$;トラブルシューティング
よくあるエラーと対処法
ピボット処理でよく遭遇するエラーです。
crosstab関数のエラー：
-- エラー: 関数 crosstab(unknown) が存在しません
-- 解決法: 拡張機能をインストール
CREATE EXTENSION tablefunc;
-- エラー: 列の数が一致しません
-- 解決法: AS句の列定義を確認
SELECT * FROM crosstab(
'SELECT ...', -- 必ず3列
'SELECT ...' -- カテゴリーのリスト
) AS ct (
-- すべての列を正しく定義
col1 TYPE1,
col2 TYPE2,
...
);メモリ不足エラー：
-- work_memを一時的に増やす
SET work_mem = '256MB';
-- または設定ファイルで永続的に変更
-- postgresql.conf
-- work_mem = 256MBNULL値の処理
NULL値が含まれる場合の対処法です。
-- NULL値を特定の値に置換
SELECT
sale_date,
COALESCE(
SUM(amount) FILTER (WHERE product = 'A商品'),
0
) AS "A商品"
FROM sales
GROUP BY sale_date;
-- NULL値を別の表現に変更
SELECT
sale_date,
CASE
WHEN SUM(amount) FILTER (WHERE product = 'A商品') IS NULL
THEN 'N/A'
ELSE SUM(amount) FILTER (WHERE product = 'A商品')::TEXT
END AS "A商品"
FROM sales
GROUP BY sale_date;データ型の不一致
異なるデータ型を扱う場合の注意点です。
-- 文字列と数値の混在を避ける
SELECT
sale_date,
-- すべて文字列に統一
COALESCE(
SUM(amount) FILTER (WHERE product = 'A商品')::TEXT,
'-'
) AS "A商品",
COALESCE(
COUNT(*) FILTER (WHERE product = 'B商品')::TEXT,
'0'
) AS "B商品_件数"
FROM sales
GROUP BY sale_date;よくある質問
Q: crosstabとCASE文、どちらを使うべき？
A: 状況によって使い分けましょう。
CASE文を使う場合：
- 列数が少ない（10列程度まで）
- 標準SQLで実装したい
- 複雑な条件がある
crosstabを使う場合：
- 大量のデータを高速処理したい
- 列数が多い
- シンプルな変換
Q: 列数が動的に変わる場合はどうすれば？
A: 動的SQLかアプリケーション側での処理を検討してください。
PostgreSQLだけで完結させたい場合は、PL/pgSQL関数で動的SQLを生成します。ただし、複雑になるため、アプリケーション側（Python、JavaScriptなど）で処理する方が簡単な場合もあります。
Q: 大量データでパフォーマンスが悪い
A: 以下の対策を試してください。
- 適切なインデックスの作成
- マテリアライズドビューの使用
- パーティショニング
- work_memの調整
- 並列処理の有効化
Q: ExcelのピボットテーブルのようにGUIで操作できない？
A: PostgreSQL単体ではGUIはありませんが、以下のツールが便利です。
- pgAdmin 4：基本的なクエリビルダー
- DBeaver：ピボット機能あり
- Metabase：ビジュアル分析ツール
- Tableau：高機能なBIツール
Q: 複数の値を同時にピボットできる？
A: はい、可能です。
UNION ALLを使って複数の値を結合するか、列名に値の種類を含めることで実現できます。例えば「商品名売上」「商品名件数」のような形式にします。
まとめ
PostgreSQLで行を列に変換する方法は、用途に応じて選択できる豊富な選択肢があります。
方法の選び方：
- シンプルで標準的：CASE文
- 初心者にも分かりやすい
- どこでも動作する
- 列数が固定の場合
- 高速で効率的：crosstab関数
- 大量データに最適
- 拡張機能が必要
- 列定義が必要
- 簡潔で読みやすい：FILTER句
- PostgreSQL 9.4以降
- CASE文より見やすい
- 複雑な条件も書きやすい
- 柔軟性重視：動的SQL
- 列数が可変
- 複雑だが万能
- メンテナンスに注意
成功のポイント：
- まずはCASE文から始める
- データ量に応じて最適化
- NULL値の処理を忘れない
- 適切なインデックスを設定
行列変換は、データ分析やレポート作成に欠かせない技術です。
この記事で紹介した方法を参考に、あなたのデータを見やすく、分析しやすい形に変換してください。適切なピボット処理で、データの価値を最大限に引き出しましょう！
コメント