SQLで縦持ちから横持ちへデータ変換する方法とパフォーマンス最適化

SQLによるデータ構造の変換と最適化手法

SQLによる縦持ちから横持ちへの基本変換

縦持ちデータを横持ちに変換する基本的なSQLの書き方を見ていきましょう。最も一般的な方法は、CASE式とMAX関数を組み合わせる方法です。

SELECT 
  name,
  MAX(CASE WHEN subject = '数学' THEN score END) as math_score,
  MAX(CASE WHEN subject = '国語' THEN score END) as japanese_score,
  MAX(CASE WHEN subject = '英語' THEN score END) as english_score
FROM scores
GROUP BY name

このクエリでは、GROUP BY句でグループ化し、CASE式で条件分岐を行い、MAX関数で値を取得します。

パフォーマンスを考慮したデータ構造設計

データ構造の選択は、クエリのパフォーマンスに大きな影響を与えます。特に以下の場合は縦持ちが有利です：

• カラム数が非常に多い（500以上）
• ほとんどのカラムがNULL値
• レコードごとにカラムが異なる

実際のパフォーマンス比較では、縦持ちデータ構造の方が約14倍高速なケースも報告されています。

大規模データセットでの変換テクニック

大規模データを扱う際は、以下の点に注意が必要です：

WITH base_data AS (
  SELECT 
    row_id,
    column_id,
    value
  FROM vertical_table
  WHERE created_at > CURRENT_DATE - 30
)
SELECT 
  row_id,
  MAX(CASE WHEN column_id = 1 THEN value END) as col1,
  MAX(CASE WHEN column_id = 2 THEN value END) as col2
FROM base_data
GROUP BY row_id

このアプローチでは、WITH句を使用してデータを事前にフィルタリングし、メモリ使用量を抑えています。

列指向データベースでの最適化戦略

Snowflakeなどの列指向データベースでは、縦持ちデータ構造が特に効果的です。その理由は：

• 列単位でのデータ圧縮が効率的
• 必要な列のみを読み込むため、I/O負荷が低減
• 分析クエリの実行が高速

SQLによる動的な構造変換の実装

動的にカラムを生成する必要がある場合は、以下のようなアプローチが有効です：

SELECT 
  dt,
  string_agg(
    CASE 
      WHEN metric_type = 'impression' THEN value::text 
    END, 
    ',' ORDER BY created_at DESC
  ) as impressions,
  string_agg(
    CASE 
      WHEN metric_type = 'click' THEN value::text 
    END, 
    ',' ORDER BY created_at DESC
  ) as clicks
FROM metrics
GROUP BY dt

このテクニックでは、string_agg関数を使用して動的に列を生成します。