非正規化とピボットを使用してデータを変換する

データ構造の再構築は、効果的な分析ソリューションを構築する上で重要な部分です。 非正規化、 ピボット、ピボット 解除 は、正規化された形式と集計された形式の間でデータを再構成するのに役立つ変換手法であり、それぞれ異なる分析とモデリングのニーズに対応します。

このユニットでは、より高速なクエリのために正規化されたテーブルをフラット化し、クロステーブル分析のために行を列にローテーションし、ワイド データセットを正規化する必要がある場合にそのプロセスを反転する方法について説明します。

非正規化について

正規化されたデータベースでは、複数の関連テーブルに情報を分割することで、データの冗長性が低下します。 この設計はトランザクション システムに適していますが、分析の課題を生み出します。 クエリは多数のテーブルを結合する必要があり、パフォーマンスが低下し、複雑さが増します。

非正規化は、関連するテーブルをより少ない幅のテーブルにフラット化することで、これらの課題に対処します。 ディメンション テーブルのデータをファクト テーブルに直接結合することで、クエリ時に結合する必要がなくなります。

注文、顧客、および製品のテーブルを個別に含む販売データベースを検討します。 正規化されたクエリには、複数の結合が必要です。

-- Normalized query with multiple joins
SELECT o.order_id,
       o.order_date,
       c.customer_name,
       c.region,
       p.product_name,
       p.category,
       o.quantity,
       o.total_price
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON o.product_id = p.product_id;

非正規化テーブルは、この情報を一緒に格納します。

-- Create a denormalized orders table
CREATE OR REPLACE TABLE sales.orders_denormalized AS
SELECT o.order_id,
       o.order_date,
       c.customer_name,
       c.region,
       p.product_name,
       p.category,
       o.quantity,
       o.total_price
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON o.product_id = p.product_id;

非正規化テーブルに対するクエリは、結合が不要なため、高速に実行されます。 このアプローチは、ビジネス インテリジェンス ツール用のスター スキーマとデータ マートを構築する場合に一般的です。 トレードオフにより、ストレージが増加し、ソース データが変更されたときに非正規化テーブルを更新する必要があります。

行を列にピボットする

ピボットでは、列の一意の値が個別の列に変換され、"ロング" テーブル形式が "ワイド" のテーブル形式に変換されます。 この変換は、表間レポートまたはダッシュボードのデータを準備する場合に特に便利です。

複数のソース行がピボット結果の同じセルにマップされる可能性があるため、 PIVOT 句には集計関数が必要です。 FOR句とIN句を使用して、新しい列名になる列の値を指定します。

長い形式で格納されている四半期ごとの売上データを検討します。

-- Create sample sales data
CREATE OR REPLACE TEMP VIEW quarterly_sales(year, quarter, region, revenue) AS
VALUES (2024, 1, 'East', 150000),
       (2024, 2, 'East', 175000),
       (2024, 3, 'East', 160000),
       (2024, 4, 'East', 200000),
       (2024, 1, 'West', 180000),
       (2024, 2, 'West', 165000),
       (2024, 3, 'West', 190000),
       (2024, 4, 'West', 210000),
       (2025, 1, 'East', 165000),
       (2025, 2, 'East', 185000);

-- View data in long format
SELECT * FROM quarterly_sales;

このクエリは、四半期ごとに個別の行として収益を返します。 四半期を並べて比較するには、データをピボットします。

-- Pivot quarters into columns
SELECT year, region, Q1, Q2, Q3, Q4
FROM quarterly_sales
PIVOT (
  SUM(revenue)
  FOR quarter IN (1 AS Q1, 2 AS Q2, 3 AS Q3, 4 AS Q4)
);

その結果、各リージョンの四半期の収益が列として表示され、前年比の比較は簡単になります。

1 つのピボット操作で複数の集計関数を適用できます。

-- Pivot with multiple aggregations
SELECT year, Q1_total, Q1_avg, Q2_total, Q2_avg
FROM (SELECT year, quarter, revenue FROM quarterly_sales)
PIVOT (
  SUM(revenue) AS total,
  AVG(revenue) AS avg
  FOR quarter IN (1 AS Q1, 2 AS Q2)
);

これにより、各四半期の収益の合計と平均の両方の列が作成されます。

列を行にピボット解除する

ピボット解除では逆変換が実行され、列が行に変換されます。 分析に正規化が必要なワイド形式のデータを受け取る場合や、長い形式が必要なシステム用にデータを準備する場合は、ピボット解除を使用します。

UNPIVOT句は、指定した列を値と名前のペアに変換します。 値を保持する列と、元の列名を保持する列を定義します。

-- Create wide-format data
CREATE OR REPLACE TEMP VIEW regional_targets(region, jan, feb, mar, apr) AS
VALUES ('North', 50000, 55000, 52000, 58000),
       ('South', 45000, 48000, 51000, 53000),
       ('East',  60000, 62000, 65000, 68000);

-- Unpivot monthly columns into rows
SELECT *
FROM regional_targets
UNPIVOT (
  target_amount FOR month IN (jan, feb, mar, apr)
);

結果は、各月の列を行に変換します。

既定では、 UNPIVOT は null 値を除外します。 それらを含めるには、 INCLUDE NULLSを指定します。

-- Unpivot while keeping null values
SELECT *
FROM regional_targets
UNPIVOT INCLUDE NULLS (
  target_amount FOR month IN (jan, feb, mar, apr)
);

ピボットされていない列名にカスタム エイリアスを割り当てることもできます。

-- Unpivot with custom month labels
SELECT *
FROM regional_targets
UNPIVOT (
  target_amount FOR month IN (
    jan AS `January`,
    feb AS `February`,
    mar AS `March`,
    apr AS `April`
  )
);

適切な変換を選択する

各変換は、特定の分析ニーズに対応します。 ダッシュボードとレポートの高速クエリ パフォーマンスが必要な場合は、非正規化を使用します。 アナリストがカテゴリ間で並べて比較する必要がある場合は、ピボットを選択します。 集計パイプラインまたは機械学習パイプラインのワイド データを正規化する必要がある場合は、ピボット解除を適用します。

多くの場合、これらの変換はデータ パイプラインで連携します。 ソース データをピボット解除して、その形式を標準化し、変換を適用し、最終的なプレゼンテーションの結果をピボットするか、ダウンストリーム コンシューマー用に最適化されたテーブルに出力を非正規化することができます。