NBAで学ぶSCD（Slowly Changing Dimension）

NBA SQL データ分析基盤

※この記事はAIによって執筆されています。

はじめに

データウェアハウスにおいて、時間の経過とともに変化するデータをどのように管理するかは重要な課題です。SCD（Slowly Changing Dimension）は、このような変化するディメンションデータを効果的に処理するための手法です。

本記事では、NBA選手のデータを具体例として、SCD Type1からType7までの各手法を詳しく解説します。

SCDとは？

SCD（Slowly Changing Dimension）とは、時間の経過とともにゆっくりと変化するディメンションテーブルのデータを管理する手法です。例えば、選手の所属チーム、ポジション、住所などは時間とともに変化します。

前提：NBA選手ディメンションテーブルの初期状態

以下のNBA選手テーブルを例に各SCDタイプを説明します：

Players テーブル（初期状態）

player_key player_id name team position salary created_date updated_date
1 P001 LeBron James Lakers SF 44000000 2023-01-01 2023-01-01
2 P002 Stephen Curry Warriors PG 48000000 2023-01-01 2023-01-01

SCD Type 1: 上書き更新

概要: 既存のレコードを新しい値で上書きする最もシンプルな方法

特徴: - 履歴を保持しない - ストレージ効率が良い - 常に最新の状態のみ保持

適用例: LeBron Jamesの年俸が4400万ドルから4600万ドルに変更

更新後のテーブル:

player_key player_id name team position salary updated_date
1 P001 LeBron James Lakers SF 46000000 2024-01-15
2 P002 Stephen Curry Warriors PG 48000000 2023-01-01

SQL実装例:

UPDATE Players 
SET salary = 46000000, updated_date = '2024-01-15'
WHERE player_id = 'P001';

メリット: シンプル、ストレージ効率が良い
デメリット: 履歴が失われる、監査証跡なし

SCD Type 2: 履歴管理（新レコード追加）

概要: 変更時に新しいレコードを追加し、古いレコードを無効化

特徴: - 完全な履歴を保持 - 有効期間で管理 - 最も一般的なSCD手法

適用例: Stephen CurryがWarriorsからNetsに移籍

更新後のテーブル:

player_key player_id name team position salary start_date end_date is_current
1 P001 LeBron James Lakers SF 46000000 2023-01-01 9999-12-31 Y
2 P002 Stephen Curry Warriors PG 48000000 2023-01-01 2024-02-01 N
3 P002 Stephen Curry Nets PG 52000000 2024-02-01 9999-12-31 Y

SQL実装例:

-- 既存レコードを無効化
UPDATE Players 
SET end_date = '2024-02-01', is_current = 'N'
WHERE player_id = 'P002' AND is_current = 'Y';

-- 新レコード追加
INSERT INTO Players 
(player_id, name, team, position, salary, start_date, end_date, is_current)
VALUES 
('P002', 'Stephen Curry', 'Nets', 'PG', 52000000, '2024-02-01', '9999-12-31', 'Y');

メリット: 完全な履歴保持、時系列分析可能
デメリット: ストレージ使用量増加、クエリ複雑化

SCD Type 3: 限定履歴管理

概要: 現在値と以前の値を同一レコード内の異なるカラムで管理

特徴: - 直前の値のみ保持 - ファクトテーブルのキー変更不要 - 限定的な履歴管理

テーブル構造変更:

Column Type Description
previous_team VARCHAR(50) 直前の所属チーム
team_change_date DATE チーム変更日 
ALTER TABLE Players 
ADD COLUMN previous_team VARCHAR(50),
ADD COLUMN team_change_date DATE;

適用例: LeBron JamesがLakersからHeatに移籍

更新後のテーブル:

player_key player_id name current_team previous_team team_change_date position
1 P001 LeBron James Heat Lakers 2024-03-01 SF
3 P002 Stephen Curry Nets NULL NULL PG

SQL実装例:

UPDATE Players 
SET previous_team = current_team,
    current_team = 'Heat',
    team_change_date = '2024-03-01'
WHERE player_id = 'P001';

メリット: シンプルなクエリ、ファクトテーブル影響なし
デメリット: 限定的な履歴、複数変更に対応困難

SCD Type 4: 履歴テーブル分離

概要: 現在データと履歴データを別テーブルで管理

特徴: - 現在テーブルと履歴テーブルに分離 - パフォーマンス最適化 - 明確な責務分離

テーブル設計:

Players_Current テーブル（現在データ）:

player_key player_id name team position salary updated_date
1 P001 LeBron James Heat SF 46000000 2024-03-01
3 P002 Stephen Curry Nets PG 52000000 2024-02-01

Players_History テーブル（履歴データ）:

history_key player_id name team position salary start_date end_date
1 P001 LeBron James Lakers SF 44000000 2023-01-01 2024-03-01
2 P002 Stephen Curry Warriors PG 48000000 2023-01-01 2024-02-01

更新処理例:

-- 履歴テーブルに移動
INSERT INTO Players_History 
SELECT player_key, player_id, name, team, position, salary, 
       updated_date AS start_date, CURRENT_DATE AS end_date
FROM Players_Current 
WHERE player_id = 'P001';

-- 現在テーブル更新
UPDATE Players_Current 
SET team = 'Heat', updated_date = CURRENT_DATE
WHERE player_id = 'P001';

メリット: 高パフォーマンス、明確な分離
デメリット: 複雑な管理、データ整合性確保が必要

SCD Type 5: ミニディメンション

概要: 頻繁に変化する属性を別ディメンションテーブルに分離

特徴: - 頻繁変化属性の分離 - ファクトテーブルの肥大化防止 - パフォーマンス向上

テーブル設計:

Players テーブル（メイン・安定属性）:

player_key player_id name position height weight
1 P001 LeBron James SF 206 113
2 P002 Stephen Curry PG 191 84

Player_Contracts テーブル（ミニディメンション・変化頻繁）:

contract_key contract_band salary_range team_type
1 High 40M-50M Large Market
2 Super Max 50M+ Large Market

Player_Contract_Bridge テーブル（ブリッジテーブル）:

player_key contract_key start_date end_date
1 1 2023-01-01 2024-03-01
1 2 2024-03-01 9999-12-31
2 2 2023-01-01 9999-12-31

メリット: パフォーマンス向上、属性管理の柔軟性
デメリット: 設計複雑化、結合処理増加

SCD Type 6: ハイブリッド手法（Type 1 + Type 2 + Type 3）

概要: Type 1、Type 2、Type 3を組み合わせた包括的手法

特徴: - 完全な履歴保持（Type 2） - 現在値への高速アクセス（Type 1） - 直前値の比較（Type 3）

テーブル構造:

Players_Type6 テーブル:

player_key player_id name historical_team previous_team current_team start_date end_date is_current salary current_salary
1 P001 LeBron James Lakers Lakers Heat 2023-01-01 2024-03-01 N 44000000 46000000
2 P001 LeBron James Heat Lakers Heat 2024-03-01 9999-12-31 Y 46000000 46000000
3 P002 Stephen Curry Warriors Warriors Nets 2023-01-01 2024-02-01 N 48000000 52000000
4 P002 Stephen Curry Nets Warriors Nets 2024-02-01 9999-12-31 Y 52000000 52000000

メリット: 最大の柔軟性、多様な分析ニーズに対応
デメリット: 最も複雑、高いストレージコスト

SCD Type 7: ハイブリッド手法（Type 1 + Type 2 の統合キー）

概要: Type 1とType 2を組み合わせ、代理キーと自然キーの両方を提供

特徴: - 履歴管理（Type 2） - 現在データへの直接アクセス（Type 1） - 統合キーによる柔軟な結合

テーブル構造:

Players_Type7 テーブル:

surrogate_key natural_key current_key player_id name team start_date end_date is_current
1 1 2 P001 LeBron James Lakers 2023-01-01 2024-03-01 N
2 1 2 P001 LeBron James Heat 2024-03-01 9999-12-31 Y
3 3 4 P002 Stephen Curry Warriors 2023-01-01 2024-02-01 N
4 3 4 P002 Stephen Curry Nets 2024-02-01 9999-12-31 Y

クエリ例:

-- 履歴分析（natural_keyを使用）
SELECT * FROM Players_Type7 WHERE natural_key = 1;

-- 現在データのみ（current_keyを使用）
SELECT * FROM Players_Type7 WHERE surrogate_key = current_key;

メリット: 柔軟性と効率性のバランス
デメリット: 複雑なキー管理

まとめ：各SCDタイプの選択指針

SCDタイプ 適用場面 メリット デメリット
Type 1 履歴不要、最新データのみ必要 シンプル、高効率 履歴なし
Type 2 完全な履歴が必要 完全履歴、時系列分析可 ストレージ大、複雑
Type 3 直前値との比較が重要 シンプル、比較容易 限定履歴
Type 4 パフォーマンス重視 高速クエリ 管理複雑
Type 5 頻繁変化属性の分離 効率的、柔軟 設計複雑
Type 6 包括的な要件 最大柔軟性 最も複雑
Type 7 履歴と現在データ両方 バランス良好 キー管理複雑

実装時の考慮点

パフォーマンス：Type 2は大量データでクエリが重くなる可能性があります。適切なインデックス設計が重要です。

ストレージ：履歴を保持するタイプ（2,4,6,7）はストレージコストを考慮する必要があります。

ビジネス要件：法的要件や監査要件により履歴保持期間が決まる場合があります。

データ更新頻度：更新頻度が高い場合は、Type 5のミニディメンション手法を検討しましょう。

適切なSCDタイプの選択により、効率的で保守性の高いデータウェアハウスを構築できます。NBA選手データのような実世界のデータを通じて各手法を理解し、プロジェクトの要件に最適な手法を選択してください。