質問の歴史。
この要件は、レガシーなスプレッドシステムやフラットファイルのエクスポートから移行するデータ取り込みパイプラインに由来し、時系列メトリックやカテゴリ属性が、正規化された行ではなく、ワイドフォーマットの列見出し（例：Jan_Sales、Feb_Sales）に非正規化されています。このようなスキーマは、ETLで関係データウェアハウスに取り込まれる前のExcel主導のビジネスプロセスで一般的であり、時系列分析と次元JOINを可能にするためにナローファクトテーブルに変換する必要があります。課題は、これらの静的な列投影を、命令的な行ごとの処理に頼ることなく動的なタプルストリームに転送することです。
問題。
標準のSELECT文は、解析時に投影された列のアイデンティティを固定するため、単一の投影が異なる出力行で異なるソース列を出力することを妨げます。目標は、各ソース行と属性名を列挙した仮想次元テーブルとの間に直積を生成し、条件ロジックを使用して、正しいソース値を一般的な結果列にマルチプレックスすることです。これは、ANSI SQL:1999以降で利用可能な標準の結合構文とスカラー表現のみを使用して実行する必要があります。
解決策。
VALUES行構築子を使用して、カテゴリキー（例：月の名前）を行として列挙した派生テーブルに対してCROSS JOINを利用します。SELECTリスト内で、各キーをその対応するソース列にマッピングする検索されたCASE式を使用し、効果的に非正規化された値を正規化された行構造に投影します。特定のキーに対してソース属性が欠落しているときに生成されるNULL値を除外するため、結果をフィルタリングし、最終的な出力に有効な測定値のみを含めることを保証します。
SELECT s.cost_center_id, m.fiscal_month, CASE m.fiscal_month WHEN 'M01' THEN s.m01_amt WHEN 'M02' THEN s.m02_amt WHEN 'M03' THEN s.m03_amt -- ... 追加の月 WHEN 'M12' THEN s.m12_amt END AS amount FROM budget_wide s CROSS JOIN ( VALUES ('M01'), ('M02'), ('M03'), ('M04'), ('M05'), ('M06'), ('M07'), ('M08'), ('M09'), ('M10'), ('M11'), ('M12') ) AS m(fiscal_month) WHERE CASE m.fiscal_month WHEN 'M01' THEN s.m01_amt -- ... NULLを避けるために全て繰り返す END IS NOT NULL;
財務部門が企業のExcelモデルから会計年度の予算配分をエクスポートし、12の月ごとの列（M01_AmtからM12_Amt）が各コストセンターの非正規化された時間を表しているステージングテーブルに貼り付けました。ターゲットのSAPデータウェアハウスは、（CostCenter_ID、Fiscal_Month、Amount）のナローファクトテーブルスキーマを必要とし、中間Python処理を回避するためにANSI SQLロードスクリプト内でのアンピボット変換が求められました。5千万件のレコードのボリュームにより、手動変換やマルチパスローディング戦略は妨げられました。
解決策1：列ごとのUnion All。
初期のアプローチは、各月の列を異なる回路としてハードコーディングした12個の別々のSELECTクエリを使用し、一般的なAmountとMonth_Name列にまとめられました。利点：この方法は普遍的な互換性があり、レガシーのメインフレームデータベースや古いSQLエンジンでも機能します。欠点：このアプローチは、ソースデータで12回のフルテーブルスキャンを実行し、線形I/Oの劣化を引き起こす; クエリプランは膨大で、キャッシュが難しく、スキーマの変更（13番目の期間を追加すること）が必要であるため、12の異なる投影リストを変更する必要があります。
解決策2：動的SQL生成。
もう1つの代替案は、メタデータテーブルを反復処理して、必要なCASEブランチまたはUNIONのアームをランタイムで生成することによって、アプリケーション層でクエリテキストをダイナミックに構築することでした。利点：これは進化するスキーマに対して柔軟性を提供し、数百の列を扱う際の手動SQL作成の負担を軽減します。欠点：これは手続きロジックの禁忌に違反し、SQLインジェクション攻撃ベクトルおよびコンパイルオーバーヘッドを導入し、生成されたステートメントを静的データベースビューまたはストアドプロシージャ定義にカプセル化できません。
解決策3：Valuesを使用したCROSS JOIN。
受け入れられた実装は、12の会計期間を定義するVALUESコンストラクタを使用したCROSS JOINを採用し、仮想期間識別子に基づいてCASE式を介して正しい金額をマルチプレックスしました。利点：これはソーステーブルの単一パスを実行し、効率的な結合アルゴリズムを利用し、すべてのOracle、SQL Server、PostgreSQL、およびDb2で宣言的で移植性があります。欠点：これは、古いシステムでは利用できない行構築子へのSQL:1999サポートが必要であり、CASE式の冗長さがメンテナンスのオーバーヘッドを増加させる、生成されたテンプレートでない限り。
結果。
この変換のレイテンシーは、UNION ALLパターンに内在する冗長なテーブルスキャンを排除することにより25分から90秒未満に減少しました。ローディングプロセスは、スキーマの拡張に対して弾力性があり、新しい会計期間が導入されたときはVALUES構築子に行を追加するだけで済みます。さらに、ロジックは標準ビューにカプセル化されており、Tableauユーザーによる直接のアドホッククエリが可能で、中間ETLステップなしに済みます。
どうすれば、ソース列のNULL値がアンピボット結果の行として現れるのを防ぎつつ、実行プランでCASE式が2回評価されるのを防げるでしょうか？
候補者はしばしば、WHERE CASE ... END IS NOT NULLのようなWHERE句条件内にCASE式を埋め込みますが、これはオプティマイザにプロジェクションを2回計算させることを強制します—1回はフィルタリングのため、もう1回は出力のためです。効率的なANSI SQLパターンは、派生テーブルまたは共通テーブル式（CTE）内に結果を具現化します：SELECT * FROM (SELECT ..., CASE ... END AS val FROM ... CROSS JOIN ...) sub WHERE val IS NOT NULL。これはCASEを一度計算し、行をフィルタリングし、クエリオプティマイザの関心の分離を維持します。
異種データ型（例：VARCHARコメント列とDECIMAL金額列）を持つ列をアンピボットする場合、データ損失なしに単一結果値列で型の整合性を保証する特定のANSI SQLキャスティング戦略は何ですか？
多くの候補者は、暗黙の型変換に依存し、文字列が切り捨てられたり、小数精度が失われたり、プラットフォームによって型強制ルールが異なることに気づかずにUNION ALLを試みます。ロバストなソリューションは、各WHENブランチ内ですべてのソース列を共通のスーパタイプ—典型的にはVARCHAR—に明示的にキャストします：CASE WHEN key='Comment' THEN CAST(text_col AS VARCHAR(500)) ELSE CAST(num_col AS VARCHAR(500)) END。これにより、すべての戻り値が結果列定義と互換性のある単一データ型を共有し、必要に応じて数値データのテキスト表現が保持されます。
CROSS JOIN with VALUESアプローチが表面的に直積爆発を生成しているように見えるのはなぜで、オプティマイザはCPNULL排除の動作と比較してこれを通常どのように緩和しますか？
CROSS JOINは、フィルタリングの前にM×N行（ソース行×属性数）を論理的に生成します。候補者は、これが大規模なデータセットでのパフォーマンスを悪化させると心配します。しかし、現代のコストベースオプティマイザは、CASE式のデータ依存性を小さな定数テーブルに基づいて認識し、計画を単純な投影または内部でのUNPIVOT物理演算子に変換することがよくあります。ネイティブのUNPIVOTとは異なり、通常はNULL結果を自動的に排除しますが、この方法では、ソース属性がNULLだった行を破棄するために明示的なWHERE句が必要です。そうでないと、結果セットにスパー空のファクトが含まれ、集計計算を下流で腐敗させます。