Coding Interview Prep · レッスン

列を行に戻すアンピボット

Q: 「列を行に戻すアンピボット」で何を学びますか？

UNPIVOTまたはUNION ALLを使って横持ちテーブルを元に戻します。 ブラウザで直接実行するハンズオンコードでCoding Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

UNPIVOTまたはUNION ALLを使って横持ちテーブルを元に戻します。

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

逆方向の問題

アンピボットはピボットの逆です。横持ちのテーブルを受け取り、その列を行に戻します。スプレッドシートのような形で届いたデータを、分析用に正規化する必要がある場合に、面接で問われます。

たとえば、地域ごとにq1, q2, q3, q4の列を持つテーブルを、(region, quarter, amount)という行に変換する必要があります。この縦持ちの形式は、集約、結合、グラフ作成のいずれにも適しています。

-- Wide input we want to unpivot
region | q1  | q2  | q3  | q4
-------+-----+-----+-----+----
East   | 100 | 150 | 120 | 180
West   | 200 | 250 | 210 | 260

移植性の高いUNION ALLパターン

方言に依存しない答えはUNION ALLです。元の列ごとに1つのSELECTを記述し、それぞれで固定のラベルとその列の値を出力します。

重複除去のコストをかけず、2つのセルが同じ値を持つ場合でもすべての行を保持できるように、UNIONではなくUNION ALLを使います。

SELECT region, 'Q1' AS quarter, q1 AS amount FROM wide_sales
UNION ALL
SELECT region, 'Q2', q2 FROM wide_sales
UNION ALL
SELECT region, 'Q3', q3 FROM wide_sales
UNION ALL
SELECT region, 'Q4', q4 FROM wide_sales;

UNION ALLをUNIONにしない理由

これは面接でよくあるひっかけ問題です。UNIONは結果全体から重複行を削除します。EastとWestの両方にQ1の100がある場合、単純なUNIONでは同一の行が1つにまとめられ、データが失われます。

UNION ALLは重複除去を行わずに連結するため、アンピボットに適しています。また、重複除去のためのソートやハッシュ処理が不要なので、より高速です。

-- UNION would wrongly merge identical (region, quarter, amount) rows
-- UNION ALL keeps every row, always the correct choice here

列の型の整合

UNION ALLのすべての分岐は、同じ順序で、互換性のある型の列を同じ数だけ出力しなければなりません。列名は最初のSELECTから引き継がれます。

横持ちの列で型が異なる場合（たとえば一方がintで、もう一方がdecimalの場合）、エンジンは共通の型を選択します。本当に互換性がない場合は、UNIONが失敗しないように明示的にキャストしてください。

SELECT region, 'revenue' AS metric, CAST(revenue AS decimal(12,2)) AS val FROM t
UNION ALL
SELECT region, 'units',   CAST(units   AS decimal(12,2))        FROM t;

SQL ServerのUNPIVOT

SQL Serverには専用のUNPIVOT演算子があり、UNION ALLより簡潔に記述できます。新しい値の列と新しいラベルの列を指定し、まとめる元の列を列挙します。

重要な動作が1つあります。UNPIVOTは、値がNULLの行を削除します。面接官は、この副作用を知っているか確認します。

SELECT region, quarter, amount
FROM wide_sales
UNPIVOT (
  amount FOR quarter IN (q1, q2, q3, q4)
) AS u;

UNPIVOTはNULLを削除する

ある地域のq3がNULLの場合、SQL ServerのUNPIVOTはその行を出力から単純に省きます。NULLであってもすべての列に対応する行が必要なら、NULLを保持するUNION ALLに戻してください。

面接では、このトレードオフを説明してください。ネイティブのUNPIVOTは簡潔ですがNULLに対して情報が失われ、UNION ALLは冗長ですが完全です。

-- UNPIVOT: q3 NULL for East -> no (East, Q3) row produced
-- UNION ALL: (East, 'Q3', NULL) row IS produced

PostgreSQL：LATERAL VALUES

PostgreSQLにはUNPIVOTがありませんが、VALUESリストに対するCROSS JOIN LATERALというすっきりしたイディオムがあります。横持ちの各行を、(ラベル、値)の組からなる小さなインラインテーブルと結合して展開します。

長いUNION ALLよりも簡潔で、元のテーブルも1回だけ読み取ります。

SELECT w.region, v.quarter, v.amount
FROM wide_sales w
CROSS JOIN LATERAL (VALUES
  ('Q1', w.q1),
  ('Q2', w.q2),
  ('Q3', w.q3),
  ('Q4', w.q4)
) AS v(quarter, amount);

テーブルを1回だけ読み取る

触れておく価値のあるパフォーマンス上のポイントがあります。素朴なUNION ALLでは、分岐ごとに横持ちのテーブルをスキャンします（四半期が4つなら4回のスキャンです）。LATERAL VALUES形式とSQL ServerのUNPIVOTでは、元のテーブルを1回だけ読み取ります。

大きなテーブルでは、この違いが重要になります。UNION ALLを使わなければならない場合でも、オプティマイザが繰り返しスキャンする可能性があるため、より効率的な方法としてLATERALやUNPIVOTに触れるとよいでしょう。

空のセルの除外

UNION ALLやLATERALでは、値がNULLの行も保持されます。値が入っているセルだけが必要な場合は、フィルタを追加してください。これは、SQL ServerのUNPIVOTが自動的に行う処理と同じです。

NULLを保持するか削除するかは要件次第なので、コーディングする前に面接官へ要件を確認してください。

SELECT region, quarter, amount
FROM (
  SELECT region, 'Q1' AS quarter, q1 AS amount FROM wide_sales
  UNION ALL SELECT region, 'Q2', q2 FROM wide_sales
) t
WHERE amount IS NOT NULL;

実例：アンピボット後の集計

よくある次の質問として、「横持ちの四半期テーブルから、全四半期を通した地域別の総売上を求めてください」というものがあります。いったん縦持ちにアンピボットすれば、集計は簡単です。地域ごとにグループ化した単一のSUMで済みます。

これが、先にアンピボットする本当の理由です。4つの列を別々に合計する方法は壊れやすい一方、縦持ち形式のSUM(amount) GROUP BY regionなら、四半期の数がいくつであっても対応できます。

WITH long_sales AS (
  SELECT region, 'Q1' AS quarter, q1 AS amount FROM wide_sales
  UNION ALL SELECT region, 'Q2', q2 FROM wide_sales
  UNION ALL SELECT region, 'Q3', q3 FROM wide_sales
  UNION ALL SELECT region, 'Q4', q4 FROM wide_sales
)
SELECT region, SUM(amount) AS total
FROM long_sales
GROUP BY region;

アンピボットするタイミング

問題文から、アンピボットすべきサインを見抜いてください。

入力に、実際には値であるもの（年月、指標など）が繰り返し列として存在している。
それらの値をまたいで集計、結合、またはグラフ化する必要がある。
非正規化されたスプレッドシートのデータを、インポート時に正規化したい。

その後のSQL処理では、ほとんどの場合、縦持ちが適した形です。そのため、アンピボットは最初の手順になることがよくあります。

簡単な確認

最もよくあるアンピボットの落とし穴を理解しているか確認しましょう。

まとめ

アンピボットすると、列が行になります。

移植性：列ごとに1つのSELECTを記述し、UNION ALLで結合します（通常のUNIONは使用しません）。
SQL Server：組み込みのUNPIVOTを使えます。簡潔ですが、NULL値は除外されます。
Postgres：CROSS JOIN LATERAL (VALUES ...)を使い、1回のスキャンで処理できます。
各分岐で列数と型を揃え、問題の要件に応じてNULLをフィルタリングします。

よくある質問

「列を行に戻すアンピボット」レッスンは無料ですか？

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

「列を行に戻すアンピボット」で何を学びますか？

UNPIVOTまたはUNION ALLを使って横持ちテーブルを元に戻します。ブラウザで直接実行するハンズオンコードでCoding Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

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

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

「列を行に戻すアンピボット」レッスンにはどのくらい時間がかかりますか？

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

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

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

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

← Coding Interview Prepに戻る