条件付き集計によるピボット
行を列に変換する、移植性の高いCASEをSUM内で使うパターンです。
「条件付き集計によるピボット」はCoddyKit上の無料SQL Interview Prepレッスンです。 これはレッスン1/4です。 下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。 これはSQL Interview Prep学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 SQL Interview Prepコースには全4レッスンが含まれています。
面接での設定
レポート関連の面接で非常によく出る課題の1つが、行を列に変換することです。sales(region, quarter, amount)のような縦長のテーブルがあり、面接官は四半期ごとに1列を持つ横長のレポートを求めています。
求められる移植性の高い、方言に依存しない回答は条件付き集約です。これは、SUMなどの集約関数の中にCASE式を配置する方法です。これを習得すれば、PIVOTキーワードがないデータベースでも、どのデータベースでもピボットできます。
縦長形式と横長形式
ピボットする前に、データの形を確認しましょう。縦長形式では、1行に1つの事実を格納します。地域と四半期の組み合わせごとに、独立した行になります。横長形式では、カテゴリを列に展開します。
- 縦長形式：挿入しやすい一方、横に並べて読むのは困難です。
- 横長形式：人が読むレポートに適しています。
ピボットは縦長形式を横長形式に変換します。これは単なる構文ではなく、集約を理解しているかを確認できるため、面接官に好まれます。
-- Long form (the input)
region | quarter | amount
-------+---------+-------
East | Q1 | 100
East | Q2 | 150
West | Q1 | 200
West | Q2 | 250基本パターン
ポイントは、出力する各列について、その列に該当する行では値を返し、それ以外ではNULLを返すCASEを記述することです。それを集約関数でラップし、グループを1つの行にまとめます。
これは「金額を合計するが、Q1の行だけを対象にする」と読めます。SUMはNULLを無視するため、該当しない行は何も加算しません。
SELECT
region,
SUM(CASE WHEN quarter = 'Q1' THEN amount END) AS q1,
SUM(CASE WHEN quarter = 'Q2' THEN amount END) AS q2
FROM sales
GROUP BY region;SUMがNULLを無視する理由
このパターンが機能するのは、面接官が確認してくる次の事実があるためです。集約関数はNULLをスキップします。ELSEのないCASEは、どの分岐にも一致しない場合にNULLを返します。そのため、SUM(CASE WHEN ... THEN amount END)は選択した行だけを加算します。
代わりにELSE 0と書いても、0を加えても結果は変わらないためSUMでは機能します。しかし、AVG、MIN、COUNTでは正しい結果になりません。
-- Both produce the same SUM result:
SUM(CASE WHEN quarter = 'Q1' THEN amount END)
SUM(CASE WHEN quarter = 'Q1' THEN amount ELSE 0 END)実例：四半期レポート
サンプルデータに対する完全なクエリを示します。各地域が1行になり、各四半期が1列になります。
GROUP BY regionによって、入力された4行が出力される2行にまとめられます。これがないと、入力された各行に対して1行ずつ生成され、ほとんどの値がNULLになります。
SELECT
region,
SUM(CASE WHEN quarter = 'Q1' THEN amount END) AS q1,
SUM(CASE WHEN quarter = 'Q2' THEN amount END) AS q2
FROM sales
GROUP BY region;
-- Result:
-- region | q1 | q2
-- East | 100 | 150
-- West | 200 | 250適切な集約関数の選択
CASEを包む集約関数は、求めている内容に合わせる必要があります。
- 各セルで値を合計する場合は
SUMを使います。 - 各地域と四半期の組み合わせに値が1つだけあり、それを取り出して表示したい場合は
MAXまたはMINを使います。 - 各セルで条件に一致する行数を数える場合は
COUNTを使います。
面接では、COUNTを使うパターンがよく出題されます。たとえば、月ごと、ステータスごとの注文数はいくつか、という問題です。
SELECT
month,
COUNT(CASE WHEN status = 'shipped' THEN 1 END) AS shipped,
COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled
FROM orders
GROUP BY month;1つの値を持つセルに対するMAX
各キーとカテゴリの組み合わせが1つの値だけを持つ場合（合計ではない、真のクロス集計の場合）は、MAXまたはMINを使います。どちらもNULLではない唯一の値を返し、一致しない分岐から生じるNULLは無視します。
これは、金額を合計するのではなく属性を組み替える場合に安全な方法です。たとえば、キーと値で構成された設定テーブルを、エンティティごとに1行へ変換する場合に使います。
-- Turn key/value rows into one wide row per user
SELECT
user_id,
MAX(CASE WHEN attr = 'city' THEN value END) AS city,
MAX(CASE WHEN attr = 'plan' THEN value END) AS plan
FROM user_attributes
GROUP BY user_id;NULLになる出力セルの処理
ある地域にQ2の売上がなければ、そのq2セルはNULLになります。面接では、代わりに0を表示するよう求められることがあります。集約全体をCOALESCEで包んでください。
COALESCEはCASEの内側ではなく、集約関数の外側に置きます。そうすれば、グループ全体に一致する行がない場合だけ値を置き換えられます。
SELECT
region,
COALESCE(SUM(CASE WHEN quarter = 'Q1' THEN amount END), 0) AS q1,
COALESCE(SUM(CASE WHEN quarter = 'Q2' THEN amount END), 0) AS q2
FROM sales
GROUP BY region;総計列の追加
よくある追加質問として、ピボットしたすべての列の合計を追加する方法があります。列名を1つずつ指定して足し合わせる必要はありません。同じグループに対して単純なSUM(amount)を使えば、CASEによる絞り込みを一切受けないため、行の合計が得られます。
これにより、SELECT内の各集約が同じグループに対して独立して計算されることを理解していると面接官に示せます。
SELECT
region,
SUM(CASE WHEN quarter = 'Q1' THEN amount END) AS q1,
SUM(CASE WHEN quarter = 'Q2' THEN amount END) AS q2,
SUM(amount) AS total
FROM sales
GROUP BY region;FILTER付き集約という簡略記法
PostgreSQLとSQL標準はFILTER (WHERE ...)をサポートしています。これは条件付き集約をよりすっきり記述する方法です。読みやすく、CASEの定型的な記述も避けられます。
面接でこれに触れると、幅広い知識を示せます。ただし、MySQLとSQL Serverはサポートしていないため、移植性のある答えとしてはCASEが引き続き適しています。
-- Postgres / standard SQL
SELECT
region,
SUM(amount) FILTER (WHERE quarter = 'Q1') AS q1,
SUM(amount) FILTER (WHERE quarter = 'Q2') AS q2
FROM sales
GROUP BY region;大きな制限事項
条件付き集約には、面接官が詳しく確認する重要な注意点があります。出力する列をすべて手作業で列挙しなければなりません。四半期やカテゴリが事前にわからない場合、この静的なクエリでは対応できません。
この問題は動的ピボットと呼ばれ、生成されたSQLが必要になります。ただし、カテゴリの集合が固定されていて既知であれば、条件付き集約が簡潔で移植性にも優れた最適な方法です。
理解度チェック
条件付き集約のパターンをどの程度理解できているか確認しましょう。
まとめ
条件付き集約は、面接官にも受け入れられる移植性の高いピボット方法です。
- 出力列ごとに1つの
CASEを記述し、集約関数で包みます。 - 合計には
SUM、1つの値を持つセルにはMAX/MIN、件数にはCOUNTを使います。 - 一致しない分岐から生じる
NULLを集約関数が無視するため機能します。 - 空のセルを0に変換するには
COALESCEを使います。 - 制限事項として、列をハードコードする必要があります。次はこの制限につながる動的ピボットを扱います。
よくある質問
「条件付き集計によるピボット」レッスンは無料ですか？
はい。「条件付き集計によるピボット」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、SQL Interview Prepコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 SQL Interview Prepコースには全4レッスンが含まれています。
「条件付き集計によるピボット」で何を学びますか？
行を列に変換する、移植性の高いCASEをSUM内で使うパターンです。 ブラウザで直接実行するハンズオンコードでSQL Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。
SQL Interview Prepを始めるのに経験は必要ですか？
事前経験は必要ありません。CoddyKitのSQL Interview Prepは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン1/4です。
「条件付き集計によるピボット」レッスンにはどのくらい時間がかかりますか？
ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。
このSQL Interview Prepレッスンでコードを書いて実行できますか？
はい。すべてのSQL Interview Prepレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。
このコースのすべてのレッスン
- 条件付き集計によるピボット
- ベンダー別PIVOTとクロス集計構文
- 列を行に戻すアンピボット
- 未知の列に対応する動的ピボット