0Pricing
SQL Academy · レッスン

クロス集計パターン（PostgreSQL crosstab()）

tablefunc拡張のcrosstab()関数で本格的なピボットテーブルを生成します。

「クロス集計パターン（PostgreSQL crosstab()）」はCoddyKit上の無料SQL Academyレッスンです。 これはレッスン4/4です。 下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。 これはSQL Academy学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 SQL Academyコースには全4レッスンが含まれています。

本格的なクロス集計が必要な理由

CASE によるピボットでは、対象となる各列を列挙する必要があります。本当に横幅の広いピボット（たとえば商品ごとに1列）には、tablefunc 拡張機能の crosstab() が適しています。

拡張機能を有効にする

tablefunc は PostgreSQL の contrib に含まれています。

CREATE EXTENSION IF NOT EXISTS tablefunc;

crosstab の基本シグネチャ

crosstab は3列（row_key、category、value）を返す SQL 文字列を受け取り、row_key とカテゴリごとの1列を返します。

SELECT * FROM crosstab(
  $$
    SELECT user_id, status, COUNT(*)::INT
    FROM orders
    GROUP BY user_id, status
    ORDER BY user_id, status
  $$
) AS ct (
  user_id BIGINT,
  paid    INT,
  pending INT,
  cancelled INT
);

出力列を宣言する理由

SQL は静的型付けなので、プランナーは解析時に出力列を把握する必要があります。そのため、AS 句でデータ型を含むスキーマを指定します。

2引数の crosstab（カテゴリ集合を指定）

データが疎な場合は、カテゴリの一覧を別に渡してください。存在しない値が列のずれではなく NULL になります。

SELECT * FROM crosstab(
  $$
    SELECT user_id, status, COUNT(*)::INT
    FROM orders GROUP BY user_id, status
    ORDER BY user_id
  $$,
  $$ VALUES ('paid'), ('pending'), ('cancelled') $$
) AS ct (
  user_id BIGINT, paid INT, pending INT, cancelled INT
);

CASE が crosstab より適している場合

カテゴリが少数で既知の場合は、CASE / FILTER のほうがシンプルです。拡張機能が不要で、2引数版特有の注意点もありません。次のような場合は crosstab を使用してください。

  • カテゴリが多数ある
  • カテゴリが動的に読み込まれる
  • 外部のピボット利用ツール向けにデータを生成する

動的なピボット

実行時までカテゴリが分からない場合は、アプリケーションで SQL を生成するか、format() + EXECUTE を使った PL/pgSQL を使用してください。

-- Build the SQL dynamically:
SELECT string_agg(format('SUM(CASE WHEN status = %L THEN 1 END) AS %I',
                          status, status), ', ')
FROM (SELECT DISTINCT status FROM orders) s;

スプレッドシート向けの横長ピボット

アナリスト向けのレポートでは、横長形式が求められることがよくあります。SQL で生成するか、縦長形式のまま BI ツールに渡してピボットさせてください。

アンピボット：逆の変換

横長形式から縦長形式に変換するには、UNION ALL または PostgreSQL の jsonb_each_text() を使用します。

SELECT id, key AS month, (value)::NUMERIC AS revenue
FROM monthly_wide,
     jsonb_each_text(to_jsonb(monthly_wide) - 'id');

パフォーマンス

crosstab() は内部 SQL を1回実行し、メモリ上でピボットします。ボトルネックは通常の GROUP BY クエリと同じです。

crosstab の制限

PostgreSQL には、Oracle や SQL Server とは異なり、ネイティブの PIVOT キーワードがありません。crosstab() がその代替手段です。

まとめ

ほとんどのピボットでは、CASE / FILTER がすっきりした解決策です。カテゴリが多い場合や、事前にカテゴリが分からない場合は crosstab() を使用してください。

クイックチェック

PostgreSQL の crosstab() 関数を提供する拡張機能はどれですか？

よくある質問

「クロス集計パターン（PostgreSQL crosstab()）」レッスンは無料ですか？

はい。「クロス集計パターン（PostgreSQL crosstab()）」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、SQL Academyコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 SQL Academyコースには全4レッスンが含まれています。

「クロス集計パターン（PostgreSQL crosstab()）」で何を学びますか？

tablefunc拡張のcrosstab()関数で本格的なピボットテーブルを生成します。 ブラウザで直接実行するハンズオンコードでSQL Academyを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

SQL Academyを始めるのに経験は必要ですか？

事前経験は必要ありません。CoddyKitのSQL Academyは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン4/4です。

「クロス集計パターン（PostgreSQL crosstab()）」レッスンにはどのくらい時間がかかりますか？

ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。

このSQL Academyレッスンでコードを書いて実行できますか？

はい。すべてのSQL Academyレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。

このコースのすべてのレッスン

  1. UNION、INTERSECT、EXCEPT
  2. UNION ALLとUNION（重複排除のコスト）
  3. CASE式とピボットクエリ
  4. クロス集計パターン（PostgreSQL crosstab()）
← SQL Academyに戻る