0Pricing
SQL Interview Prep · レッスン

ベンダー別PIVOTとクロス集計構文

SQL ServerのPIVOTとPostgresのcrosstab、およびそれぞれの制限を学びます。

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

条件付き集約のその先

移植性の高いCASEによるピボットはすでに理解しています。しかし面接官は、ベンダー固有のピボット演算子が利用できる場合に、それも使えるかどうかを確認したいと考えます。

SQL Serverには専用のPIVOT演算子が用意されています。PostgreSQLには、tablefunc拡張のcrosstab関数があります。両方の使い方と注意点を知っていれば、実務経験があることを示せます。

SQL Server PIVOTの構造

SQL ServerのPIVOTには、次の3つが必要です。

  • 値の列に対する集約関数。
  • 値が新しい列になる列を指定するFOR句。
  • 列に変換するリテラル値を列挙するINリスト。

これは、キー、展開列、値だけを正確に公開する派生テーブルに適用する必要があります。それ以外の列は含めません。

SELECT region, [Q1], [Q2]
FROM (SELECT region, quarter, amount FROM sales) AS src
PIVOT (
  SUM(amount)
  FOR quarter IN ([Q1], [Q2])
) AS p;

暗黙的なGROUP BY

面接で確認される、PIVOTの微妙な落とし穴があります。グループ化が暗黙的であることです。SQL Serverは、集約対象の列でもFOR列でもない、入力元のすべての列でグループ化します。

そのため、派生テーブルに誤ってorder_idのような余分な列を含めると、ピボットもその列でグループ化され、想定よりはるかに多くの行が生成されます。内側のクエリは、必ずキー、展開列、値だけに絞ってください。

-- WRONG: order_id leaks in and breaks grouping
FROM (SELECT region, quarter, amount, order_id FROM sales) AS src
PIVOT (SUM(amount) FOR quarter IN ([Q1],[Q2])) AS p;
-- The pivot now groups by region AND order_id

角括弧付きの列名

SQL Serverでは、ピボット後の列名はデータ内のリテラル値を角括弧で囲んだものになります。値が数字で始まる場合や空白を含む場合は、角括弧が必須です。

外側のSELECTでは、同じ角括弧付きの名前を使って選択します。これが、INリストがハードコードされているため、PIVOTで未知の値を動的SQLなしに扱えない理由でもあります。

SELECT region, [2023], [2024]
FROM (SELECT region, yr, amount FROM sales) AS s
PIVOT (SUM(amount) FOR yr IN ([2023], [2024])) AS p;

PostgreSQLのcrosstab

PostgreSQLにはPIVOTキーワードがありません。その代わり、tablefunc拡張がcrosstabを提供しています。これはSQL文字列を受け取り、その実行結果の形を変える関数です。

最初に拡張を有効にする必要があります。crosstabが受け取る元のクエリは、行識別子、カテゴリ、値の3列を、必ずこの順番で返さなければなりません。

CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT *
FROM crosstab(
  'SELECT region, quarter, amount FROM sales ORDER BY 1, 2'
) AS ct(region text, q1 numeric, q2 numeric);

列定義リスト

crosstabで最も間違いやすいのは、末尾に付けるAS ct(...)の列定義リストです。出力列の名前と型を自分で宣言する必要があり、カテゴリの数と順序に一致させなければなりません。

ある行にカテゴリが存在しない場合、crosstabは位置に基づいて値を埋めるため、下記の2引数形式を使わないとデータの対応がずれる可能性があります。

SELECT *
FROM crosstab(
  'SELECT region, quarter, amount FROM sales ORDER BY 1, 2'
) AS ct(region text, q1 numeric, q2 numeric);
-- ct(...) MUST list every output column and its type

2引数形式のcrosstab

一部の行に存在しないカテゴリがあっても対応がずれないようにするには、2引数形式を使います。2つ目のクエリがカテゴリ値の完全な順序付きリストを返すため、crosstabは各値がどの列に属するかを正確に判断できます。

カテゴリがまばらに存在する場合、面接官が期待するのはこの堅牢な形式です。

SELECT *
FROM crosstab(
  'SELECT region, quarter, amount FROM sales ORDER BY 1, 2',
  'SELECT DISTINCT quarter FROM sales ORDER BY 1'
) AS ct(region text, q1 numeric, q2 numeric);

MySQLにはどちらもない

面接官からMySQLについて尋ねられた場合、答えは明確です。MySQLにはPIVOTもcrosstabもありません。使える方法は、CASEによる条件付き集約（またはSUM(... ) とIF()を組み合わせた簡略記法）だけです。

だからこそ、移植性の高いCASEパターンが重視されます。どの環境でも動作する、最も広く使える方法だからです。

-- MySQL: only conditional aggregation works
SELECT
  region,
  SUM(IF(quarter = 'Q1', amount, 0)) AS q1,
  SUM(IF(quarter = 'Q2', amount, 0)) AS q2
FROM sales
GROUP BY region;

実例：SQL Serverでのステータス別件数

レポート作成の要件として、「地域ごとに1行とし、各ステータスの注文数を列にする」というものがあります。SQL Serverでは、COUNTを使うPIVOTに、必要な列だけを持つ派生テーブルを渡します。

ステータス列そのものを数えるため、バケット内のNULLではないステータス行がすべて集計されます。外側のSELECTでは、各ステータスを角括弧付きの列として列挙します。これは、COUNT(CASE ...)を3つ記述するより簡潔な方法です。

SELECT region, [pending], [shipped], [delivered]
FROM (SELECT region, status FROM orders) AS src
PIVOT (
  COUNT(status)
  FOR status IN ([pending], [shipped], [delivered])
) AS p;

共通する制限事項

PIVOTとcrosstabには、条件付き集約と同じ基本的な制限があります。クエリを書く時点で、出力列がわかっていなければなりません。

  • SQL Serverでは、INリストがリテラルです。
  • Postgresのcrosstabでは、列定義リストがリテラルです。

どちらも実行時にカテゴリを検出することはできません。そのためには、SQL文字列を動的に組み立てる必要があります。

どれを使うべきか

面接では、次のように正直に比較するとよいでしょう。

  • CASEによる集約：移植性と可読性が高く、どのデータベースエンジンでも動作します。基本的にはこれを選びます。
  • SQL ServerのPIVOT：列が多い場合は簡潔ですが、暗黙的なグループ化が思わぬ結果を招くことがあります。
  • Postgresのcrosstab：強力ですが記述が冗長で、拡張機能と列定義リストが必要です。

迷ったら条件付き集約を使い、代替手段としてベンダー固有の演算子にも触れてください。

理解度チェック

面接官が確認するSQL Server PIVOTの動作をしっかり理解しましょう。

まとめ

ベンダー固有のピボット構文を1画面でまとめます。

  • SQL Server：PIVOT (SUM(x) FOR col IN ([a],[b]))を使い、残りの列に対する暗黙的なGROUP BYが行われます。
  • Postgres：tablefuncのcrosstab()を使います。列定義リストが必要で、まばらなデータには2引数形式を使います。
  • MySQL：どちらも存在しないため、CASEを使います。
  • 3つの方法すべてで、クエリを書く時点で列がわかっている必要があります。

よくある質問

「ベンダー別PIVOTとクロス集計構文」レッスンは無料ですか？

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

「ベンダー別PIVOTとクロス集計構文」で何を学びますか？

SQL ServerのPIVOTとPostgresのcrosstab、およびそれぞれの制限を学びます。 ブラウザで直接実行するハンズオンコードでSQL Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

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

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

「ベンダー別PIVOTとクロス集計構文」レッスンにはどのくらい時間がかかりますか？

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

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

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

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

  1. 条件付き集計によるピボット
  2. ベンダー別PIVOTとクロス集計構文
  3. 列を行に戻すアンピボット
  4. 未知の列に対応する動的ピボット
← SQL Interview Prepに戻る