SQL Interview Prep · レッスン

未知の列に対応する動的ピボット

Q: 「未知の列に対応する動的ピボット」で何を学びますか？

カテゴリが事前に分からない場合に、ピボット列を生成します。 ブラウザで直接実行するハンズオンコードでSQL Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

カテゴリが事前に分からない場合に、ピボット列を生成します。

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

難しいピボットの問題

CASEによる集計、SQL ServerのPIVOT、Postgresのcrosstabのいずれを使う静的なピボットにも、共通する制限があります。クエリを書く時点で、出力列を列挙しなければならないということです。

では、毎週変わる商品名や、稼働中の月ごとに1列が必要な場合のように、カテゴリが未知の場合はどうすればよいでしょうか。これは動的ピボットと呼ばれます。通常のSQLでは、実行時に列の一覧が決まる結果を返せないため、上級レベルの面接問題になります。

SQLだけでは実現できない理由

SQLは結果セットのレベルで静的型付けされます。プランナーは実行前に列とその型を把握していなければなりません。単一のクエリで、見つかった値ごとに列を1つ作ることはできません。

そのため、一般的な手法は2段階でSQLテキストを生成することです。まず重複しないカテゴリを問い合わせ、次にそのカテゴリからピボット用のクエリ文字列を組み立てて実行します。

手順1：カテゴリを収集する

最初の手順は、列になる重複しない値を一覧表示する通常のクエリです。列の並びを安定させるため、通常は順序も指定します。

この結果を、文字列を組み立てる手順への入力にします。実際のシステムでは、このクエリを実行して行を取得し、その内容から次のクエリを組み立てます。

SELECT DISTINCT quarter
FROM sales
ORDER BY quarter;
-- e.g. Q1, Q2, Q3, Q4

手順2：列リストを組み立てる

次に、それらの値を、カンマ区切りのCASE式のリスト（またはPIVOT用の角括弧付きの名前）に変換します。データベースには、SQL自体でこれを行うための文字列集約関数が用意されています。

Postgresではstring_agg、MySQLではGROUP_CONCAT、SQL ServerではSTRING_AGGまたは古いFOR XML PATHのテクニックを使います。

-- Postgres: build the SELECT-list fragment
SELECT string_agg(
  format('SUM(CASE WHEN quarter = %L THEN amount END) AS %I',
         quarter, quarter),
  ', '
)
FROM (SELECT DISTINCT quarter FROM sales ORDER BY 1) q;

手順3：組み立てて実行する

生成した断片を完全なクエリ文字列に連結し、動的実行で実行します。PL/pgSQLではEXECUTE、SQL Serverではsp_executesql、MySQLではPREPARE/EXECUTEを使います。

これが動的ピボットの核心です。SQLでSQLを生成し、それを実行します。

-- SQL Server pattern
DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);
SELECT @cols = STRING_AGG(QUOTENAME(quarter), ',')
FROM (SELECT DISTINCT quarter FROM sales) q;
SET @sql = N'SELECT region, ' + @cols + '
  FROM (SELECT region, quarter, amount FROM sales) s
  PIVOT (SUM(amount) FOR quarter IN (' + @cols + ')) p;';
EXEC sp_executesql @sql;

PostgreSQLの完全な例

Postgresでは、3つの手順をDOブロックまたは関数にまとめます。string_aggで列リストを作り、それをクエリに組み込んで、EXECUTEで実行します。

結果列は実行時まで分からないため、これを返す関数ではRETURNS SETOF recordを使うか、行をjsonとして返し、呼び出し側で展開することがよくあります。

DO $do$
DECLARE
  cols text;
  qry  text;
BEGIN
  SELECT string_agg(
    format('SUM(CASE WHEN quarter=%L THEN amount END) AS %I', quarter, quarter), ', ')
  INTO cols
  FROM (SELECT DISTINCT quarter FROM sales ORDER BY 1) q;
  qry := format('SELECT region, %s FROM sales GROUP BY region', cols);
  EXECUTE qry;
END $do$;

Prepared Statementを使うMySQL

MySQLにはピボット演算子がないため、GROUP_CONCATで条件付き集計の文字列を組み立て、Prepared Statementで実行します。

GROUP_CONCATには長さの上限（group_concat_max_len）があります。面接で言及されることがあるため、カテゴリが多い場合は上限を引き上げてください。

SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT
  CONCAT('SUM(CASE WHEN quarter=''', quarter,
         ''' THEN amount END) AS ', QUOTE(quarter))
) INTO @sql FROM sales;
SET @sql = CONCAT('SELECT region, ', @sql,
                  ' FROM sales GROUP BY region');
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;

SQLインジェクションのリスク

データ値を実行可能なSQLに連結するため、動的ピボットにはインジェクションのリスクがあります。カテゴリ値に引用符や悪意のある文字列が含まれていると、生成されたクエリが壊れたり、乗っ取られたりする可能性があります。

識別子とリテラルは、必ずデータベースエンジンの安全なヘルパーでエスケープしてください。Postgresではformat('%I', ...)と%L、SQL ServerではQUOTENAMEを使います。生の値をそのまま文字列に貼り付けてはいけません。

-- Safe quoting prevents injection / breakage
-- Postgres: %I identifier, %L literal
format('SUM(CASE WHEN k=%L THEN v END) AS %I', cat, cat)
-- SQL Server: QUOTENAME(cat)

未知の列を返す

もう1つの難所は、呼び出し側が結果の形を事前に把握できないことです。面接で認められる一般的な戦略には、次のようなものがあります。

行をJSONとして返し、アプリケーション層でキーを展開する。
プロシージャにクエリを出力または生成させ、それを2段階目として実行する。
カテゴリが分かった時点で、アプリケーションコード（pandasやBIツール）で最終的なピボットを行う。

1回の静的な呼び出しで任意の列を返す、すっきりした方法はありません。

実例：商品別にピボットする

商品が入れ替わり、レポートではsalesに現在存在する商品ごとに1つの売上列が必要だとします。列の一覧をハードコードすることはできないため、生成します。Postgresなら、string_aggと安全なクォートを使ってCASEの断片を作り、それをクエリに組み込んでからEXECUTEする流れを読みやすく記述できます。

面接では、商品の特定、各商品をクォート付きの列に変換、組み立て、実行という流れを説明してください。どのデータベースエンジンでも同じ構成を適用できます。変わるのはヘルパーだけです。

DO $do$
DECLARE cols text; qry text;
BEGIN
  SELECT string_agg(
    format('SUM(CASE WHEN product=%L THEN amount END) AS %I',
           product, product), ', ')
  INTO cols
  FROM (SELECT DISTINCT product FROM sales ORDER BY 1) p;
  qry := format('SELECT region, %s FROM sales GROUP BY region', cols);
  EXECUTE qry;
END $do$;

動的ピボットを避けるタイミング

優れた候補者は、SQLでこれを行わないべきタイミングを理解しています。動的SQLは、読みやすく、テストしやすく、安全に扱いやすく、キャッシュしやすいものではありません。多くの場合、より良い回答は次のとおりです。

SQLから縦持ち形式で返し、アプリケーションまたはレポート層でピボットする。
カテゴリの集合が小さく、変化も少ない場合は、静的ピボットを使い、必要に応じて更新する。

動的ピボットは、実際に開かれていて常に変化するカテゴリ集合に限って使用してください。

簡単な確認

動的ピボットが必要になる核心的な理由を確認しましょう。

まとめ

動的ピボットは、未知の列集合に対応します。

実行前に結果列を固定しなければならないため、静的ピボットでは対応できません。
基本パターンは、重複しないカテゴリを問い合わせ、ピボット用のSQL文字列を作り、それを動的に実行することです。
string_agg/GROUP_CONCAT/STRING_AGGを使って列リストを作ります。
SQLインジェクションを防ぐため、値（%I/%L、QUOTENAME）をエスケープします。
多くの場合、縦持ち形式で返し、アプリケーション層でピボットする方がすっきりします。

よくある質問

「未知の列に対応する動的ピボット」レッスンは無料ですか？

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

「未知の列に対応する動的ピボット」で何を学びますか？

カテゴリが事前に分からない場合に、ピボット列を生成します。ブラウザで直接実行するハンズオンコードでSQL Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

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

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

「未知の列に対応する動的ピボット」レッスンにはどのくらい時間がかかりますか？

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

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

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

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

← SQL Interview Prepに戻る