※この記事はAIによって作成されています

はじめに

データウェアハウスの設計において、スタースキーマは最も重要な概念の一つです。本記事では、NBA（アメリカプロバスケットボールリーグ）のデータを例に、スタースキーマの基本概念から実装まで、実践的に学んでいきます。

スタースキーマとは

スタースキーマは、データウェアハウスで使用される多次元データモデルの一種です。その名前の通り、中央にファクトテーブルがあり、周りにディメンションテーブルが星のように配置された構造を持ちます。

スタースキーマの特徴

シンプルな構造: 理解しやすく、クエリ性能が優秀
非正規化: データの重複を許容し、クエリ速度を優先
分析に最適: OLAP（Online Analytical Processing）に適している

NBAデータでスタースキーマを設計する

1. ビジネス要件の定義

まず、どのような分析をしたいかを明確にします：

選手の試合ごとのパフォーマンス分析
チーム別、シーズン別の成績比較
時系列での傾向分析
会場別のホームアドバンテージ分析

2. ファクトテーブルの設計

中核となるファクトテーブル game_stats を設計します：

CREATE TABLE game_stats (
    -- サロゲートキー
    game_stats_id BIGINT PRIMARY KEY,
    
    -- 外部キー（ディメンションテーブルへの参照）
    player_key INT NOT NULL,
    team_key INT NOT NULL,
    opponent_team_key INT NOT NULL,
    date_key INT NOT NULL,
    venue_key INT NOT NULL,
    game_key INT NOT NULL,
    
    -- メジャー（分析対象の数値データ）
    points INT,
    rebounds INT,
    assists INT,
    steals INT,
    blocks INT,
    turnovers INT,
    field_goals_made INT,
    field_goals_attempted INT,
    three_pointers_made INT,
    three_pointers_attempted INT,
    free_throws_made INT,
    free_throws_attempted INT,
    minutes_played DECIMAL(5,2),
    plus_minus INT,
    
    -- 計算済みメジャー
    field_goal_percentage DECIMAL(5,3),
    three_point_percentage DECIMAL(5,3),
    free_throw_percentage DECIMAL(5,3),
    efficiency_rating DECIMAL(6,2)
);

3. ディメンションテーブルの設計

選手ディメンション（Player Dimension）

CREATE TABLE dim_player (
    player_key INT PRIMARY KEY,
    player_id VARCHAR(20) NOT NULL, -- ナチュラルキー
    player_name VARCHAR(100) NOT NULL,
    position VARCHAR(20),
    height_cm INT,
    weight_kg INT,
    birth_date DATE,
    nationality VARCHAR(50),
    draft_year INT,
    draft_round INT,
    draft_pick INT,
    
    -- SCD Type 2 対応
    effective_date DATE NOT NULL,
    expiry_date DATE,
    is_current BOOLEAN DEFAULT TRUE,
    
    -- その他の属性
    college VARCHAR(100),
    years_of_experience INT
);

サンプルデータ例：

player_key	player_id	player_name	position	height_cm	weight_kg	birth_date	nationality	draft_year	effective_date	is_current
1001	lebron_001	LeBron James	SF	206	113	1984-12-30	USA	2003	2018-07-01	TRUE
1002	curry_001	Stephen Curry	PG	191	84	1988-03-14	USA	2009	2009-06-25	TRUE
1003	durant_001	Kevin Durant	SF	211	109	1988-09-29	USA	2007	2023-02-08	TRUE
1004	giannis_001	Giannis Antetokounmpo	PF	211	110	1994-12-06	Greece	2013	2013-06-27	TRUE

チームディメンション（Team Dimension）

CREATE TABLE dim_team (
    team_key INT PRIMARY KEY,
    team_id VARCHAR(10) NOT NULL,
    team_name VARCHAR(100) NOT NULL,
    team_city VARCHAR(50) NOT NULL,
    conference VARCHAR(20), -- Eastern/Western
    division VARCHAR(50),
    founded_year INT,
    arena_name VARCHAR(100),
    team_colors VARCHAR(100),
    
    -- SCD Type 2 対応
    effective_date DATE NOT NULL,
    expiry_date DATE,
    is_current BOOLEAN DEFAULT TRUE
);

サンプルデータ例：

team_key	team_id	team_name	team_city	conference	division	founded_year	arena_name	effective_date	is_current
2001	LAL	Los Angeles Lakers	Los Angeles	Western	Pacific	1947	Crypto.com Arena	1999-10-17	TRUE
2002	GSW	Golden State Warriors	San Francisco	Western	Pacific	1946	Chase Center	2019-09-06	TRUE
2003	PHX	Phoenix Suns	Phoenix	Western	Pacific	1968	Footprint Center	1992-06-01	TRUE
2004	MIL	Milwaukee Bucks	Milwaukee	Eastern	Central	1968	Fiserv Forum	2018-08-26	TRUE

日付ディメンション（Date Dimension）

CREATE TABLE dim_date (
    date_key INT PRIMARY KEY, -- YYYYMMDD形式
    full_date DATE NOT NULL,
    day_of_week VARCHAR(20),
    day_of_month INT,
    day_of_year INT,
    week_of_year INT,
    month_name VARCHAR(20),
    month_number INT,
    quarter INT,
    year INT,
    
    -- NBA固有の属性
    nba_season VARCHAR(10), -- '2023-24'
    season_type VARCHAR(20), -- 'Regular Season', 'Playoffs', 'Preseason'
    is_holiday BOOLEAN,
    is_weekend BOOLEAN
);

サンプルデータ例：

date_key	full_date	day_of_week	month_name	quarter	year	nba_season	season_type	is_weekend
20231215	2023-12-15	Friday	December	2	2023	2023-24	Regular Season	FALSE
20240225	2024-02-25	Sunday	February	1	2024	2023-24	Regular Season	TRUE
20240415	2024-04-15	Monday	April	2	2024	2023-24	Playoffs	FALSE
20240620	2024-06-20	Thursday	June	2	2024	2023-24	Playoffs	FALSE

会場ディメンション（Venue Dimension）

CREATE TABLE dim_venue (
    venue_key INT PRIMARY KEY,
    venue_id VARCHAR(20) NOT NULL,
    venue_name VARCHAR(100) NOT NULL,
    city VARCHAR(50),
    state VARCHAR(50),
    country VARCHAR(50),
    capacity INT,
    opened_year INT,
    surface_type VARCHAR(20),
    
    -- 地理情報
    latitude DECIMAL(10,8),
    longitude DECIMAL(11,8),
    timezone VARCHAR(50)
);

サンプルデータ例：

venue_key	venue_id	venue_name	city	state	capacity	opened_year	timezone
3001	crypto_arena	Crypto.com Arena	Los Angeles	CA	20000	1999	America/Los_Angeles
3002	chase_center	Chase Center	San Francisco	CA	18064	2019	America/Los_Angeles
3003	footprint_center	Footprint Center	Phoenix	AZ	18422	1992	America/Phoenix
3004	fiserv_forum	Fiserv Forum	Milwaukee	WI	17500	2018	America/Chicago

試合ディメンション（Game Dimension）

CREATE TABLE dim_game (
    game_key INT PRIMARY KEY,
    game_id VARCHAR(20) NOT NULL,
    season VARCHAR(10),
    game_type VARCHAR(20), -- 'Regular Season', 'Playoffs'
    playoff_round VARCHAR(30), -- 'First Round', 'Finals'等
    game_number INT, -- シリーズ内でのゲーム番号
    
    -- 試合結果
    home_team_score INT,
    away_team_score INT,
    overtime_periods INT,
    attendance INT,
    
    -- 試合状況
    game_status VARCHAR(20), -- 'Final', 'In Progress'
    broadcast_networks VARCHAR(200)
);

サンプルデータ例：

game_key	game_id	season	game_type	home_team_score	away_team_score	overtime_periods	attendance
4001	0022300456	2023-24	Regular Season	128	123	1	20000
4002	0022300457	2023-24	Regular Season	115	108	0	18064
4003	0042300101	2023-24	Playoffs	118	112	0	18422
4004	0042300102	2023-24	Playoffs	105	98	0	17500

ファクトテーブルのサンプルデータ

game_stats テーブルの例：

game_stats_id	player_key	team_key	date_key	venue_key	game_key	points	rebounds	assists	minutes_played	field_goal_percentage
100001	1001	2001	20231215	3001	4001	35	8	12	42.5	0.562
100002	1002	2002	20231215	3002	4002	28	5	7	38.2	0.478
100003	1003	2003	20240225	3003	4003	31	9	6	40.1	0.521
100004	1004	2004	20240225	3004	4004	29	14	8	41.8	0.545

実際のデータ例で理解するスタースキーマ

上記のサンプルデータを使って、スタースキーマがどのように機能するかを具体的に見てみましょう。

データの関連性の確認

ファクトテーブルの1行目のデータ（game_stats_id: 100001）を例に取ると：

SELECT 
    gs.points,
    gs.rebounds,
    gs.assists,
    p.player_name,
    t.team_name,
    d.full_date,
    v.venue_name,
    g.home_team_score,
    g.away_team_score
FROM game_stats gs
JOIN dim_player p ON gs.player_key = p.player_key
JOIN dim_team t ON gs.team_key = t.team_key  
JOIN dim_date d ON gs.date_key = d.date_key
JOIN dim_venue v ON gs.venue_key = v.venue_key
JOIN dim_game g ON gs.game_key = g.game_key
WHERE gs.game_stats_id = 100001;

結果：

points	rebounds	assists	player_name	team_name	full_date	venue_name	home_team_score	away_team_score
35	8	12	LeBron James	Los Angeles Lakers	2023-12-15	Crypto.com Arena	128	123

この結果から、「LeBron Jamesが2023年12月15日にCrypto.com Arenaで行われた試合で35点、8リバウンド、12アシストを記録し、Lakers が128-123で勝利した」という情報が分かります。

集計クエリの例

同じサンプルデータを使用した分析例：

-- 選手別の平均スタッツ（サンプルデータから）
SELECT 
    p.player_name,
    AVG(gs.points) as avg_points,
    AVG(gs.rebounds) as avg_rebounds,
    AVG(gs.assists) as avg_assists
FROM game_stats gs
JOIN dim_player p ON gs.player_key = p.player_key
GROUP BY p.player_name;

結果（サンプルデータベース）：

player_name	avg_points	avg_rebounds	avg_assists
LeBron James	35.0	8.0	12.0
Stephen Curry	28.0	5.0	7.0
Kevin Durant	31.0	9.0	6.0
Giannis Antetokounmpo	29.0	14.0	8.0

スタースキーマの実装ベストプラクティス

1. サロゲートキーの使用

各ディメンションテーブルには、ビジネスキーとは独立したサロゲートキーを使用します：

-- 良い例：サロゲートキーを使用
SELECT 
    p.player_name,
    SUM(gs.points) as total_points
FROM game_stats gs
JOIN dim_player p ON gs.player_key = p.player_key
WHERE p.is_current = TRUE;

-- 避けるべき例：ナチュラルキーの直接使用
-- ナチュラルキーは変更される可能性があるため非推奨

2. Slowly Changing Dimensions (SCD) の実装

選手の所属チーム変更などを追跡するためのSCD Type 2の実装例：

-- 選手のトレード処理例
-- 1. 現在のレコードを無効化
UPDATE dim_player 
SET expiry_date = '2024-02-08',
    is_current = FALSE
WHERE player_id = 'lebron_james_001' 
    AND is_current = TRUE;

-- 2. 新しいレコードを挿入
INSERT INTO dim_player (
    player_key, player_id, player_name, 
    current_team, effective_date, is_current
) VALUES (
    10001, 'lebron_james_001', 'LeBron James',
    'MIA', '2024-02-09', TRUE
);

3. 階層構造の実装

日付ディメンションでの階層構造の例：

-- 年→四半期→月→日の階層での集計
SELECT 
    d.year,
    d.quarter,
    d.month_name,
    AVG(gs.points) as avg_points
FROM game_stats gs
JOIN dim_date d ON gs.date_key = d.date_key
WHERE d.nba_season = '2023-24'
GROUP BY d.year, d.quarter, d.month_name
ORDER BY d.year, d.quarter, d.month_number;

実践的なクエリ例

1. 選手の月別パフォーマンス分析

SELECT 
    p.player_name,
    d.month_name,
    COUNT(*) as games_played,
    AVG(gs.points) as avg_points,
    AVG(gs.rebounds) as avg_rebounds,
    AVG(gs.assists) as avg_assists,
    AVG(gs.efficiency_rating) as avg_efficiency
FROM game_stats gs
JOIN dim_player p ON gs.player_key = p.player_key
JOIN dim_date d ON gs.date_key = d.date_key
WHERE d.nba_season = '2023-24'
    AND d.season_type = 'Regular Season'
    AND p.is_current = TRUE
GROUP BY p.player_name, d.month_name, d.month_number
ORDER BY p.player_name, d.month_number;

2. ホームアドバンテージ分析

SELECT 
    t.team_name,
    CASE 
        WHEN ht.team_key = gs.team_key THEN 'Home'
        ELSE 'Away'
    END as home_away,
    COUNT(*) as games,
    AVG(gs.points) as avg_points_scored,
    AVG(CASE WHEN g.home_team_score > g.away_team_score 
             AND ht.team_key = gs.team_key THEN 1
             WHEN g.away_team_score > g.home_team_score 
             AND at.team_key = gs.team_key THEN 1
             ELSE 0 END) as win_percentage
FROM game_stats gs
JOIN dim_team t ON gs.team_key = t.team_key
JOIN dim_game g ON gs.game_key = g.game_key
JOIN dim_team ht ON g.home_team_key = ht.team_key
JOIN dim_team at ON g.away_team_key = at.team_key
WHERE t.is_current = TRUE
GROUP BY t.team_name, home_away
ORDER BY t.team_name, home_away;

3. プレイオフパフォーマンス比較

SELECT 
    p.player_name,
    d.season_type,
    COUNT(*) as games,
    AVG(gs.points) as avg_points,
    AVG(gs.field_goal_percentage) as avg_fg_pct,
    AVG(gs.three_point_percentage) as avg_3p_pct
FROM game_stats gs
JOIN dim_player p ON gs.player_key = p.player_key
JOIN dim_date d ON gs.date_key = d.date_key
WHERE d.nba_season = '2023-24'
    AND p.is_current = TRUE
    AND gs.minutes_played >= 20  -- 最低出場時間でフィルタ
GROUP BY p.player_name, d.season_type
HAVING COUNT(*) >= 5  -- 最低試合数でフィルタ
ORDER BY p.player_name, d.season_type;

パフォーマンス最適化

1. インデックス戦略

-- ファクトテーブルのインデックス
CREATE INDEX idx_game_stats_player ON game_stats(player_key);
CREATE INDEX idx_game_stats_team ON game_stats(team_key);
CREATE INDEX idx_game_stats_date ON game_stats(date_key);
CREATE INDEX idx_game_stats_composite ON game_stats(date_key, team_key);

-- ディメンションテーブルのインデックス
CREATE INDEX idx_player_natural_key ON dim_player(player_id, is_current);
CREATE INDEX idx_team_natural_key ON dim_team(team_id, is_current);
CREATE INDEX idx_date_season ON dim_date(nba_season, season_type);

2. パーティショニング

大量データに対するパーティショニング戦略：

-- 日付によるパーティショニング
CREATE TABLE game_stats_partitioned (
    -- 同じカラム定義
) PARTITION BY RANGE (date_key);

-- 年度別パーティション作成
CREATE TABLE game_stats_2023 PARTITION OF game_stats_partitioned
    FOR VALUES FROM (20230101) TO (20240101);

CREATE TABLE game_stats_2024 PARTITION OF game_stats_partitioned
    FOR VALUES FROM (20240101) TO (20250101);

3. 集約テーブル

よく使用される集計データのマテリアライズドビュー：

CREATE MATERIALIZED VIEW mv_player_season_stats AS
SELECT 
    gs.player_key,
    d.nba_season,
    d.season_type,
    COUNT(*) as games_played,
    SUM(gs.points) as total_points,
    AVG(gs.points) as avg_points,
    SUM(gs.rebounds) as total_rebounds,
    AVG(gs.rebounds) as avg_rebounds,
    SUM(gs.assists) as total_assists,
    AVG(gs.assists) as avg_assists,
    AVG(gs.field_goal_percentage) as avg_fg_pct
FROM game_stats gs
JOIN dim_date d ON gs.date_key = d.date_key
GROUP BY gs.player_key, d.nba_season, d.season_type;

-- 定期的な更新
REFRESH MATERIALIZED VIEW mv_player_season_stats;

ETLプロセスの考慮事項

1. データ品質チェック

-- データ整合性チェックの例
-- 1. 参照整合性チェック
SELECT COUNT(*) as orphaned_records
FROM game_stats gs
LEFT JOIN dim_player p ON gs.player_key = p.player_key
WHERE p.player_key IS NULL;

-- 2. ビジネスルールチェック
SELECT game_key, SUM(minutes_played) as total_minutes
FROM game_stats
GROUP BY game_key
HAVING SUM(minutes_played) > 240 * 2; -- 1チーム最大240分 × 2チーム

2. 増分ロード戦略

-- 増分ロードのためのタイムスタンプ管理
ALTER TABLE game_stats ADD COLUMN 
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

-- 変更データのみ処理
SELECT * FROM source_game_stats 
WHERE last_modified > (
    SELECT MAX(last_updated) 
    FROM game_stats
);

まとめ

スタースキーマは、データウェアハウス設計の基本でありながら、強力な分析基盤を提供します。NBAデータの例を通じて見てきたように、以下の点が重要です：

設計の原則 - ビジネス要件を明確にしてからスキーマ設計を開始する - ファクトテーブルは分析対象のメジャーを中心に設計する - ディメンションテーブルは分析の軸となる属性を豊富に持つ

実装のベストプラクティス - サロゲートキーの一貫した使用 - SCD（Slowly Changing Dimensions）への適切な対応 - パフォーマンスを考慮したインデックスとパーティショニング

運用面での考慮事項 - データ品質チェックの自動化 - 効率的なETLプロセスの構築 - 集約テーブルによるクエリパフォーマンスの向上

スタースキーマの理解は、現代のデータ分析基盤において必須のスキルです。本記事で紹介した概念を基に、実際のプロジェクトでスタースキーマを活用してみてください。データの力を最大限に引き出し、価値ある洞察を得ることができるでしょう。

[asin:B08L39ZQ2N:detail]

[asin:B07H3TQ2XX:detail]

はじめに
スタースキーマとは
- スタースキーマの特徴
NBAデータでスタースキーマを設計する
実際のデータ例で理解するスタースキーマ
- データの関連性の確認
- 集計クエリの例
スタースキーマの実装ベストプラクティス
実践的なクエリ例
パフォーマンス最適化
ETLプロセスの考慮事項
- 1. データ品質チェック
- 2. 増分ロード戦略
まとめ
関連記事
タグ
データウェアハウス #スタースキーマ #SQL #NBA #データ分析 #OLAP

データウェアハウス #スタースキーマ #SQL #NBA #データ分析 #OLAP

めもちょー

NBAで学ぶスタースキーマ

はじめに

スタースキーマとは

スタースキーマの特徴

NBAデータでスタースキーマを設計する

1. ビジネス要件の定義

2. ファクトテーブルの設計

3. ディメンションテーブルの設計

選手ディメンション（Player Dimension）

チームディメンション（Team Dimension）

日付ディメンション（Date Dimension）

会場ディメンション（Venue Dimension）

試合ディメンション（Game Dimension）

ファクトテーブルのサンプルデータ

実際のデータ例で理解するスタースキーマ

データの関連性の確認

集計クエリの例

スタースキーマの実装ベストプラクティス

1. サロゲートキーの使用

2. Slowly Changing Dimensions (SCD) の実装

3. 階層構造の実装

実践的なクエリ例

1. 選手の月別パフォーマンス分析

2. ホームアドバンテージ分析

3. プレイオフパフォーマンス比較

パフォーマンス最適化

1. インデックス戦略

2. パーティショニング

3. 集約テーブル

ETLプロセスの考慮事項

1. データ品質チェック

2. 増分ロード戦略

まとめ

関連記事

タグ

データウェアハウス #スタースキーマ #SQL #NBA #データ分析 #OLAP