🎯 STEP 15: スタースキーマ実践

ECサイトの売上分析用DWHを実際に設計してみよう

📋 このステップで学ぶこと

売上分析用DWHの設計プロセス
ディメンションテーブルの詳細設計
ファクトテーブルの粒度決定
実践的な分析クエリの作成
CREATE TABLE文の作成と実装

学習時間の目安： 2.5時間　｜　前提知識： STEP 14（データウェアハウス設計基礎）を理解していること

🎯 1. プロジェクト概要

STEP 14からの続き：スタースキーマを実際に構築する

STEP 14ではデータウェアハウスの基礎概念として、OLTP/OLAPの違い、ファクトテーブルとディメンションテーブル、スタースキーマの考え方を学びました。このSTEP 15では、それらの知識を使って実際にECサイトの売上分析用DWHを設計します。

📋 プロジェクト：ECサイト売上分析DWH

目的：ECサイトの売上データを分析し、経営判断をサポートする

分析したい内容：

商品カテゴリ別の売上推移
地域別・都道府県別の売上分析
顧客セグメント別の購買傾向
時系列での売上トレンド（日次、月次、四半期）
プロモーション効果の測定

データソース：ECサイトの業務DB（OLTP）から日次でETL処理

📐 2. 設計ステップ1：要件整理と粒度の決定

ファクトテーブルの粒度を決める

💡 粒度（Granularity）とは？

ファクトテーブルの「1行が何を表すか」のことです。

粒度の例：

トランザクション粒度：1行 = 1つの注文明細（最も詳細）
日次粒度：1行 = 1日の商品別売上
月次粒度：1行 = 1ヶ月の商品別売上

粒度の選択基準

📋 粒度の選び方

粒度	メリット	デメリット
トランザクション粒度	✅ 最も詳細 ✅ 柔軟な分析が可能 ✅ 後から集計できる	⚠️ データ量が多い ⚠️ 集計に時間がかかる
日次粒度	✅ 適度なデータ量 ✅ 集計が速い ✅ 日次分析に十分	⚠️ 時間単位の分析不可 ⚠️ 詳細な分析は困難
月次粒度	✅ 非常に軽量 ✅ 超高速	⚠️ 粗すぎる ⚠️ 詳細分析不可

🎯 今回の選択：トランザクション粒度

決定理由：
✅ 最も柔軟な分析が可能（時間帯別、商品別、顧客別など）
✅ 後から日次・月次に集計できる
✅ 現代のデータベースは大量データにも対応可能

1行 = 1つの注文明細（商品1つの販売記録）

📊 3. 設計ステップ2：ディメンションテーブルの設計

必要なディメンションを洗い出す

📋 今回設計するディメンション

日付ディメンション（dim_date）：時系列分析の軸
商品ディメンション（dim_product）：商品別分析の軸
顧客ディメンション（dim_customer）：顧客別分析の軸
プロモーションディメンション（dim_promotion）：キャンペーン効果測定

ディメンション1：日付ディメンション

— dim_date（日付ディメンション）
CREATE TABLE dim_date (
    date_id INT PRIMARY KEY,              — 20250115 形式
    full_date DATE NOT NULL,               — 2025-01-15
    year INT NOT NULL,                     — 2025
    quarter INT NOT NULL,                  — 1 (Q1)
    month INT NOT NULL,                    — 1
    month_name VARCHAR(20) NOT NULL,       — ‘1月’ または ‘January’
    day_of_month INT NOT NULL,             — 15
    day_of_week INT NOT NULL,              — 1=月曜日, 7=日曜日
    day_name VARCHAR(20) NOT NULL,         — ‘水曜日’ または ‘Wednesday’
    week_of_year INT NOT NULL,             — 3
    is_weekend BOOLEAN NOT NULL,           — 土日かどうか
    is_holiday BOOLEAN NOT NULL,           — 祝日かどうか
    holiday_name VARCHAR(100),             — 祝日名
    fiscal_year INT NOT NULL,              — 会計年度
    fiscal_quarter INT NOT NULL,           — 会計年度の四半期
    season VARCHAR(20) NOT NULL            — ‘春’, ‘夏’, ‘秋’, ‘冬’
);

    💡 日付ディメンションのポイント
    ✅ 事前に全日付を登録：過去5年分＋未来2年分など
✅ 豊富な属性：年、月、四半期、曜日、祝日など
✅ 集計が簡単：「月別」「四半期別」「曜日別」などがWHEREだけで可能
✅ 会計年度にも対応：企業の会計年度が4月開始などでもOK

ディメンション2：商品ディメンション

— dim_product（商品ディメンション）
CREATE TABLE dim_product (
    product_id INT PRIMARY KEY,
    product_code VARCHAR(50) NOT NULL UNIQUE,  — 元システムの商品コード
    product_name VARCHAR(200) NOT NULL,
    category_level1 VARCHAR(100) NOT NULL,     — 大カテゴリ（電子機器、衣料品など）
    category_level2 VARCHAR(100),              — 中カテゴリ（ノートPC、Tシャツなど）
    category_level3 VARCHAR(100),              — 小カテゴリ
    brand VARCHAR(100),
    supplier VARCHAR(100),
    unit_price DECIMAL(10, 2) NOT NULL,        — 標準価格
    cost_price DECIMAL(10, 2),                 — 原価
    is_active BOOLEAN NOT NULL DEFAULT true,   — 販売中かどうか
    created_date DATE NOT NULL,
    discontinued_date DATE,                    — 販売終了日
    — SCD Type 2 用（ゆっくり変化するディメンション対応）
    effective_date DATE NOT NULL,              — 有効開始日
    expiration_date DATE,                      — 有効終了日
    is_current BOOLEAN NOT NULL DEFAULT true   — 現在有効なレコードか
);

📝 SCD Type 2（Slowly Changing Dimension）とは？

商品情報が変更された場合の対応方法です：

問題：商品の価格やカテゴリが変更された場合、過去データはどうする？
SCD Type 1：上書き（履歴を残さない）
SCD Type 2：新しい行を追加（履歴を保持）← 推奨

例：

product_id | product_code | product_name | unit_price | effective_date | expiration_date | is_current
1          | P001         | ノートPC     | 80000      | 2024-01-01     | 2025-01-14      | false
2          | P001         | ノートPC     | 75000      | 2025-01-15     | NULL            | true

— 価格変更時、古いレコードを終了させ、新しいレコードを追加

ディメンション3：顧客ディメンション

— dim_customer（顧客ディメンション）
CREATE TABLE dim_customer (
    customer_id INT PRIMARY KEY,
    customer_code VARCHAR(50) NOT NULL UNIQUE,
    customer_name VARCHAR(100) NOT NULL,
    customer_segment VARCHAR(50),              — ‘VIP’, ‘Regular’, ‘New’
    gender VARCHAR(20),
    age_range VARCHAR(20),                     — ’20-29′, ’30-39′ など
    prefecture VARCHAR(50),                    — 都道府県
    city VARCHAR(100),                         — 市区町村
    region VARCHAR(50),                        — 地域（関東、関西など）
    registration_date DATE NOT NULL,           — 登録日
    first_purchase_date DATE,                  — 初回購入日
    total_purchases INT DEFAULT 0,             — 累計購入回数
    total_amount DECIMAL(15, 2) DEFAULT 0,     — 累計購入金額
    rfm_score INT,                             — RFMスコア（Recency, Frequency, Monetary）
    is_active BOOLEAN NOT NULL DEFAULT true,
    — SCD Type 2 用
    effective_date DATE NOT NULL,
    expiration_date DATE,
    is_current BOOLEAN NOT NULL DEFAULT true
);

ディメンション4：プロモーションディメンション

— dim_promotion（プロモーションディメンション）
CREATE TABLE dim_promotion (
    promotion_id INT PRIMARY KEY,
    promotion_name VARCHAR(200) NOT NULL,
    promotion_type VARCHAR(50),                — ‘Discount’, ‘Free Shipping’, ‘Bundle’
    discount_rate DECIMAL(5, 2),               — 割引率（10.00 = 10%）
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT true
);

— プロモーションなしのデフォルトレコード
INSERT INTO dim_promotion (promotion_id, promotion_name, promotion_type) 
VALUES (0, ‘プロモーションなし’, ‘None’);

📈 4. 設計ステップ3：ファクトテーブルの設計

売上ファクトテーブル

— fact_sales（売上ファクトテーブル）
CREATE TABLE fact_sales (
    sales_fact_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    
    — ディメンションへの外部キー
    date_id INT NOT NULL,
    product_id INT NOT NULL,
    customer_id INT NOT NULL,
    promotion_id INT NOT NULL DEFAULT 0,
    
    — メジャー（集計対象の数値）
    quantity INT NOT NULL,                     — 販売数量
    unit_price DECIMAL(10, 2) NOT NULL,        — 販売時単価
    discount_amount DECIMAL(10, 2) DEFAULT 0,  — 割引額
    sales_amount DECIMAL(12, 2) NOT NULL,      — 売上金額（単価×数量 – 割引）
    cost_amount DECIMAL(12, 2),                — 原価（数量×原価単価）
    profit_amount DECIMAL(12, 2),              — 利益（売上金額 – 原価）
    
    — 追加情報
    order_number VARCHAR(50),                  — 元システムの注文番号
    order_line_number INT,                     — 注文内の行番号
    created_timestamp TIMESTAMP NOT NULL,      — ETL処理日時
    
    — 外部キー制約
    FOREIGN KEY (date_id) REFERENCES dim_date(date_id),
    FOREIGN KEY (product_id) REFERENCES dim_product(product_id),
    FOREIGN KEY (customer_id) REFERENCES dim_customer(customer_id),
    FOREIGN KEY (promotion_id) REFERENCES dim_promotion(promotion_id),
    
    — インデックス（パフォーマンス最適化）
    INDEX idx_date (date_id),
    INDEX idx_product (product_id),
    INDEX idx_customer (customer_id),
    INDEX idx_date_product (date_id, product_id),
    INDEX idx_date_customer (date_id, customer_id)
);

💡 ファクトテーブル設計のポイント

✅ 外部キーのみ：ディメンションへの参照だけ（商品名などは含めない）
✅ 加法性のある数値：売上金額、数量など、合計できる値を保持
✅ 計算済みの値も保持：利益額などを事前計算して保存（集計高速化）
✅ 適切なインデックス：頻繁に使うディメンションの組み合わせにインデックス
✅ BIGINT の主キー：数億行にも対応できるよう大きめのデータ型

🔍 5. 実践的な分析クエリ

クエリ1：月別売上推移

— 2024年の月別売上推移
SELECT 
    d.year AS 年,
    d.month AS 月,
    d.month_name AS 月名,
    COUNT(DISTINCT f.sales_fact_id) AS 販売件数,
    SUM(f.quantity) AS 販売数量,
    SUM(f.sales_amount) AS 売上金額,
    SUM(f.profit_amount) AS 利益,
    ROUND(SUM(f.profit_amount) / SUM(f.sales_amount) * 100, 2) AS 利益率
FROM fact_sales f
JOIN dim_date d ON f.date_id = d.date_id
WHERE d.year = 2024
GROUP BY d.year, d.month, d.month_name
ORDER BY d.year, d.month;

年 | 月 | 月名 | 販売件数 | 販売数量 | 売上金額 | 利益 | 利益率 2024| 1 | 1月 | 15234 | 28456 | 125680000 | 25136000 | 20.00 2024| 2 | 2月 | 14876 | 27123 | 118950000 | 23790000 | 20.00 2024| 3 | 3月 | 18234 | 34567 | 156780000 | 31356000 | 20.00

クエリ2：商品カテゴリ別売上（上位10件）

— 2025年1月の商品カテゴリ別売上トップ10
SELECT 
    p.category_level1 AS カテゴリ,
    COUNT(DISTINCT f.sales_fact_id) AS 販売件数,
    SUM(f.quantity) AS 販売数量,
    SUM(f.sales_amount) AS 売上金額,
    ROUND(AVG(f.unit_price), 0) AS 平均単価
FROM fact_sales f
JOIN dim_product p ON f.product_id = p.product_id
JOIN dim_date d ON f.date_id = d.date_id
WHERE d.year = 2025 AND d.month = 1
  AND p.is_current = true
GROUP BY p.category_level1
ORDER BY 売上金額 DESC
LIMIT 10;

クエリ3：都道府県別売上分析

— 2025年の都道府県別売上
SELECT 
    c.region AS 地域,
    c.prefecture AS 都道府県,
    COUNT(DISTINCT f.customer_id) AS 購入顧客数,
    COUNT(DISTINCT f.sales_fact_id) AS 販売件数,
    SUM(f.sales_amount) AS 売上金額,
    ROUND(SUM(f.sales_amount) / COUNT(DISTINCT f.customer_id), 0) AS 顧客単価
FROM fact_sales f
JOIN dim_customer c ON f.customer_id = c.customer_id
JOIN dim_date d ON f.date_id = d.date_id
WHERE d.year = 2025
  AND c.is_current = true
GROUP BY c.region, c.prefecture
ORDER BY 売上金額 DESC;

クエリ4：プロモーション効果測定

— プロモーション別の売上効果
SELECT 
    pr.promotion_name AS プロモーション名,
    pr.promotion_type AS タイプ,
    COUNT(DISTINCT f.sales_fact_id) AS 販売件数,
    SUM(f.sales_amount) AS 売上金額,
    SUM(f.discount_amount) AS 割引総額,
    SUM(f.profit_amount) AS 利益,
    ROUND(SUM(f.profit_amount) / SUM(f.sales_amount) * 100, 2) AS 利益率
FROM fact_sales f
JOIN dim_promotion pr ON f.promotion_id = pr.promotion_id
JOIN dim_date d ON f.date_id = d.date_id
WHERE d.year = 2025 AND d.month = 1
GROUP BY pr.promotion_id, pr.promotion_name, pr.promotion_type
ORDER BY 売上金額 DESC;

クエリ5：顧客セグメント別分析

— 顧客セグメント別の購買傾向
SELECT 
    c.customer_segment AS 顧客セグメント,
    c.age_range AS 年齢層,
    COUNT(DISTINCT f.customer_id) AS 顧客数,
    SUM(f.sales_amount) AS 売上金額,
    ROUND(AVG(f.sales_amount), 0) AS 平均購入金額,
    COUNT(f.sales_fact_id) AS 購入回数,
    ROUND(COUNT(f.sales_fact_id) / COUNT(DISTINCT f.customer_id), 2) AS 一人当たり購入回数
FROM fact_sales f
JOIN dim_customer c ON f.customer_id = c.customer_id
JOIN dim_date d ON f.date_id = d.date_id
WHERE d.year = 2025 AND d.quarter = 1
  AND c.is_current = true
GROUP BY c.customer_segment, c.age_range
ORDER BY 売上金額 DESC;

クエリ6：曜日別売上分析

— 曜日別の売上傾向（直近3ヶ月）
SELECT 
    d.day_of_week AS 曜日コード,
    d.day_name AS 曜日,
    d.is_weekend AS 週末,
    COUNT(DISTINCT f.sales_fact_id) AS 販売件数,
    SUM(f.sales_amount) AS 売上金額,
    ROUND(AVG(f.sales_amount), 0) AS 平均購入金額
FROM fact_sales f
JOIN dim_date d ON f.date_id = d.date_id
WHERE d.full_date >= DATE_SUB(CURDATE(), INTERVAL 3 MONTH)
GROUP BY d.day_of_week, d.day_name, d.is_weekend
ORDER BY d.day_of_week;

📊 6. ETL処理の実装例

業務DBからDWHへのデータロード

— ステップ1: 日付ディメンションの事前作成（初回のみ）
INSERT INTO dim_date (date_id, full_date, year, quarter, month, …)
SELECT 
    DATE_FORMAT(date_value, ‘%Y%m%d’) AS date_id,
    date_value AS full_date,
    YEAR(date_value) AS year,
    QUARTER(date_value) AS quarter,
    MONTH(date_value) AS month,
    …
FROM 
    (SELECT DATE_ADD(‘2020-01-01’, INTERVAL seq DAY) AS date_value
     FROM sequence_table
     WHERE seq < DATEDIFF('2030-12-31', '2020-01-01')) dates;

-- ステップ2: 商品ディメンションの更新（日次）
-- 新規商品の追加
INSERT INTO dim_product (
    product_code, product_name, category_level1, ..., effective_date, is_current
)
SELECT 
    p.product_code,
    p.product_name,
    p.category_name AS category_level1,
    ...,
    CURDATE() AS effective_date,
    true AS is_current
FROM oltp_products p
LEFT JOIN dim_product dp ON p.product_code = dp.product_code AND dp.is_current = true
WHERE dp.product_id IS NULL;

-- ステップ3: ファクトテーブルへのデータロード（日次）
INSERT INTO fact_sales (
    date_id, product_id, customer_id, promotion_id,
    quantity, unit_price, discount_amount, sales_amount, cost_amount, profit_amount,
    order_number, order_line_number
)
SELECT 
    DATE_FORMAT(o.order_date, '%Y%m%d') AS date_id,
    dp.product_id,
    dc.customer_id,
    COALESCE(dpr.promotion_id, 0) AS promotion_id,
    od.quantity,
    od.unit_price,
    od.discount_amount,
    od.quantity * od.unit_price - od.discount_amount AS sales_amount,
    od.quantity * p.cost_price AS cost_amount,
    (od.quantity * od.unit_price - od.discount_amount) - (od.quantity * p.cost_price) AS profit_amount,
    o.order_number,
    od.line_number
FROM oltp_orders o
JOIN oltp_order_details od ON o.order_id = od.order_id
JOIN dim_product dp ON od.product_code = dp.product_code AND dp.is_current = true
JOIN dim_customer dc ON o.customer_code = dc.customer_code AND dc.is_current = true
LEFT JOIN dim_promotion dpr ON o.promotion_code = dpr.promotion_code
JOIN oltp_products p ON od.product_code = p.product_code
WHERE o.order_date = DATE_SUB(CURDATE(), INTERVAL 1 DAY)
  AND o.order_status = 'completed';

💡 ETL処理のポイント

✅ 増分ロード：前日分のデータだけをロード（全件ロードは避ける）
✅ SCD対応：変更があった場合は新しいレコードを追加
✅ トランザクション：エラー時はロールバック
✅ ログ記録：ETL処理の実行ログを保存
✅ データ検証：ロード前後で件数チェック

🎯 7. パフォーマンス最適化

集計テーブル（サマリーテーブル）の作成

📊 頻繁に使う集計は事前計算

毎回ファクトテーブルから集計するのではなく、よく使う集計は事前計算して保存します。

— 日次売上サマリーテーブル
CREATE TABLE summary_daily_sales (
    date_id INT NOT NULL,
    total_sales_amount DECIMAL(15, 2) NOT NULL,
    total_orders INT NOT NULL,
    total_customers INT NOT NULL,
    average_order_amount DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY (date_id),
    FOREIGN KEY (date_id) REFERENCES dim_date(date_id)
);

— 商品カテゴリ別日次サマリー
CREATE TABLE summary_daily_category_sales (
    date_id INT NOT NULL,
    category_level1 VARCHAR(100) NOT NULL,
    total_sales_amount DECIMAL(15, 2) NOT NULL,
    total_quantity INT NOT NULL,
    total_orders INT NOT NULL,
    PRIMARY KEY (date_id, category_level1),
    FOREIGN KEY (date_id) REFERENCES dim_date(date_id)
);

パーティショニング

— ファクトテーブルを年月でパーティション分割
ALTER TABLE fact_sales 
PARTITION BY RANGE (date_id) (
    PARTITION p202401 VALUES LESS THAN (20240201),
    PARTITION p202402 VALUES LESS THAN (20240301),
    PARTITION p202403 VALUES LESS THAN (20240401),
    …
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

— メリット:
— – 古いデータのクエリが高速化
— – パーティション単位でデータ削除が可能
— – メンテナンスが容易

📝 8. スタースキーマ実践のまとめ

✅ このステップで学んだこと

粒度の決定：トランザクション粒度が最も柔軟
ディメンション設計：日付、商品、顧客、プロモーションの4つ
SCD Type 2：変更履歴を保持する設計パターン
ファクトテーブル：外部キーと集計可能な数値のみ
分析クエリ：月別、カテゴリ別、地域別など多角的な分析
ETL処理：増分ロードとSCD対応
パフォーマンス最適化：サマリーテーブル、インデックス、パーティショニング

💡 実務での適用

このスタースキーマ設計パターンは、様々な業種に応用可能です：

✅ 製造業：生産実績、在庫分析
✅ 金融：取引分析、顧客ポートフォリオ分析
✅ 医療：診療実績、患者動向分析
✅ 教育：受講実績、成績分析

基本的な考え方は同じです。「何を測りたいか（ファクト）」と「どう分析したいか（ディメンション）」を明確にすることが重要です。

📝 練習問題

問題 1 基礎

ファクトテーブルの「粒度」とは何ですか？また、粒度を決める際に考慮すべきポイントを3つ挙げてください。

【解答】

粒度とは：

ファクトテーブルの「1行が何を表すか」を定義したもの。例えば、1行 = 1つの注文明細、1行 = 1日の商品別売上、など。

粒度を決める際の考慮ポイント：

分析の詳細度：どの程度細かい分析が必要か（時間帯別？日次？月次？）
データ量：細かい粒度ほどデータ量が増大し、ストレージとクエリ時間に影響
柔軟性：トランザクション粒度は後から集計可能だが、月次粒度は日次に戻せない
パフォーマンス：粗い粒度ほどクエリが高速
ビジネス要件：経営層が求める分析の単位

問題 2 基礎

SCD Type 2とは何ですか？どのような場面で使用しますか？

【解答】

SCD Type 2とは：

Slowly Changing Dimension Type 2の略で、ディメンションの属性が変更された際に、古いレコードを終了させて新しいレコードを追加することで変更履歴を保持する設計パターンです。

使用場面：

商品の価格が変更されたが、過去の売上は変更前の価格で分析したい
顧客のセグメントが変わったが、いつから変わったかを追跡したい
過去の分析結果を正確に再現したい

実装要素：

effective_date: 有効開始日
expiration_date: 有効終了日
is_current: 現在有効なレコードかどうか

問題 3 標準

以下の日付ディメンションテーブルを使って、「2025年第1四半期の週末と平日の売上を比較する」SQLを書いてください。

dim_date(date_id, full_date, year, quarter, month, day_of_week, is_weekend)
fact_sales(sales_fact_id, date_id, product_id, customer_id, quantity, sales_amount)

【解答】

SELECT 
    CASE 
        WHEN d.is_weekend = true THEN ‘週末’
        ELSE ‘平日’
    END AS 日タイプ,
    COUNT(DISTINCT d.date_id) AS 日数,
    COUNT(f.sales_fact_id) AS 販売件数,
    SUM(f.sales_amount) AS 売上合計,
    ROUND(SUM(f.sales_amount) / COUNT(DISTINCT d.date_id), 0) AS 日平均売上
FROM fact_sales f
JOIN dim_date d ON f.date_id = d.date_id
WHERE d.year = 2025 AND d.quarter = 1
GROUP BY d.is_weekend
ORDER BY d.is_weekend;

実行結果例：

日タイプ | 日数 | 販売件数 | 売上合計 | 日平均売上平日 | 63 | 45234 | 425680000 | 6757778 週末 | 27 | 28456 | 312950000 | 11590741

問題 4 標準

オンライン学習サービスの受講分析用DWHを設計してください。以下の分析要件を満たすスタースキーマを設計し、ファクトテーブル1つとディメンションテーブル3つ以上を定義してください。

分析要件：

コース別の受講完了率を分析したい
講師別の評価を比較したい
受講者の属性（年齢層、職業など）別の傾向を分析したい
月別・四半期別の受講トレンドを把握したい

【解答例】

— ファクトテーブル
fact_enrollment（受講ファクト）
—————————————————————–
enrollment_id | date_id | student_id | course_id | instructor_id |
progress_rate | completion_flag | rating_score | study_minutes

— ディメンションテーブル

dim_date（日付ディメンション）
—————————————————————–
date_id | full_date | year | quarter | month | day_of_week | is_weekend

dim_student（受講者ディメンション）
—————————————————————–
student_id | student_name | age_range | occupation | prefecture |
registration_date | membership_type | is_active | effective_date | is_current

dim_course（コースディメンション）
—————————————————————–
course_id | course_name | category | difficulty_level | duration_hours |
price | release_date | is_active

dim_instructor（講師ディメンション）
—————————————————————–
instructor_id | instructor_name | specialty | years_experience |
average_rating | total_students

設計のポイント：

粒度：1行 = 1つの受講記録（受講者×コースの組み合わせ）
メジャー：進捗率、完了フラグ、評価スコア、学習時間など数値データ
受講者ディメンション：SCD Type 2で会員ステータス変更を追跡
コースディメンション：難易度、価格帯での分析に対応

問題 5 応用

以下のスタースキーマを使って、「前年同月比での売上成長率をカテゴリ別に算出する」SQLを書いてください。

dim_date(date_id, full_date, year, month)
dim_product(product_id, product_name, category_level1, is_current)
fact_sales(sales_fact_id, date_id, product_id, sales_amount)

【解答】

— 2025年1月と2024年1月を比較
WITH current_year AS (
    SELECT 
        p.category_level1 AS category,
        SUM(f.sales_amount) AS sales_2025
    FROM fact_sales f
    JOIN dim_date d ON f.date_id = d.date_id
    JOIN dim_product p ON f.product_id = p.product_id
    WHERE d.year = 2025 AND d.month = 1
      AND p.is_current = true
    GROUP BY p.category_level1
),
previous_year AS (
    SELECT 
        p.category_level1 AS category,
        SUM(f.sales_amount) AS sales_2024
    FROM fact_sales f
    JOIN dim_date d ON f.date_id = d.date_id
    JOIN dim_product p ON f.product_id = p.product_id
    WHERE d.year = 2024 AND d.month = 1
      AND p.is_current = true
    GROUP BY p.category_level1
)
SELECT 
    c.category AS カテゴリ,
    c.sales_2025 AS 今年売上,
    p.sales_2024 AS 前年売上,
    c.sales_2025 – p.sales_2024 AS 増減額,
    ROUND((c.sales_2025 – p.sales_2024) / p.sales_2024 * 100, 2) AS 成長率
FROM current_year c
LEFT JOIN previous_year p ON c.category = p.category
ORDER BY 成長率 DESC;

実行結果例：

カテゴリ | 今年売上 | 前年売上 | 増減額 | 成長率電子機器 | 125680000 | 98500000 | 27180000 | 27.59 衣料品 | 85200000 | 82100000 | 3100000 | 3.78 食品 | 45600000 | 48900000 | -3300000 | -6.75

問題 6 応用

【総合問題】飲食チェーン店の売上分析DWHを設計してください。以下の要件を満たすスタースキーマを設計し、CREATE TABLE文を書いてください。

分析要件：

店舗別・時間帯別の売上分析
メニューカテゴリ別の売上傾向
天気・曜日・イベントの売上への影響分析
スタッフのシフトと売上の相関分析

【解答例】

— ファクトテーブル
CREATE TABLE fact_restaurant_sales (
    sales_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    date_id INT NOT NULL,
    time_id INT NOT NULL,          — 時間帯ディメンション
    store_id INT NOT NULL,
    menu_id INT NOT NULL,
    weather_id INT NOT NULL,       — 天気ディメンション
    
    — メジャー
    quantity INT NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    sales_amount DECIMAL(12, 2) NOT NULL,
    cost_amount DECIMAL(12, 2),
    table_number INT,
    party_size INT,                — 人数
    
    FOREIGN KEY (date_id) REFERENCES dim_date(date_id),
    FOREIGN KEY (time_id) REFERENCES dim_time(time_id),
    FOREIGN KEY (store_id) REFERENCES dim_store(store_id),
    FOREIGN KEY (menu_id) REFERENCES dim_menu(menu_id),
    FOREIGN KEY (weather_id) REFERENCES dim_weather(weather_id)
);

— 日付ディメンション
CREATE TABLE dim_date (
    date_id INT PRIMARY KEY,
    full_date DATE NOT NULL,
    year INT NOT NULL,
    month INT NOT NULL,
    day_of_week INT NOT NULL,
    day_name VARCHAR(20) NOT NULL,
    is_weekend BOOLEAN NOT NULL,
    is_holiday BOOLEAN NOT NULL,
    event_name VARCHAR(100)        — イベント名（クリスマス、バレンタインなど）
);

— 時間帯ディメンション
CREATE TABLE dim_time (
    time_id INT PRIMARY KEY,
    hour INT NOT NULL,             — 0-23
    time_band VARCHAR(20) NOT NULL,– ‘モーニング’, ‘ランチ’, ‘ディナー’, ‘レイトナイト’
    is_peak_hour BOOLEAN NOT NULL  — ピークタイムかどうか
);

— 店舗ディメンション
CREATE TABLE dim_store (
    store_id INT PRIMARY KEY,
    store_name VARCHAR(100) NOT NULL,
    prefecture VARCHAR(50) NOT NULL,
    city VARCHAR(100) NOT NULL,
    store_type VARCHAR(50),        — ‘路面店’, ‘モール内’, ‘駅構内’
    seats_count INT,
    opening_date DATE,
    manager_name VARCHAR(100)
);

— メニューディメンション
CREATE TABLE dim_menu (
    menu_id INT PRIMARY KEY,
    menu_name VARCHAR(200) NOT NULL,
    category VARCHAR(50) NOT NULL, — ‘メイン’, ‘サイド’, ‘ドリンク’, ‘デザート’
    sub_category VARCHAR(50),
    unit_price DECIMAL(10, 2) NOT NULL,
    cost_price DECIMAL(10, 2),
    is_seasonal BOOLEAN DEFAULT false,
    is_active BOOLEAN DEFAULT true
);

— 天気ディメンション
CREATE TABLE dim_weather (
    weather_id INT PRIMARY KEY,
    weather_condition VARCHAR(50) NOT NULL, — ‘晴れ’, ‘曇り’, ‘雨’, ‘雪’
    temperature_range VARCHAR(20),          — ‘氷点下’, ‘寒い’, ‘涼しい’, ‘暖かい’, ‘暑い’
    precipitation BOOLEAN NOT NULL          — 降水があったか
);

分析クエリ例：

— 天気別・時間帯別の売上分析
SELECT 
    w.weather_condition AS 天気,
    t.time_band AS 時間帯,
    COUNT(*) AS 販売件数,
    SUM(f.sales_amount) AS 売上合計,
    ROUND(AVG(f.party_size), 1) AS 平均人数
FROM fact_restaurant_sales f
JOIN dim_weather w ON f.weather_id = w.weather_id
JOIN dim_time t ON f.time_id = t.time_id
JOIN dim_date d ON f.date_id = d.date_id
WHERE d.year = 2025 AND d.month = 1
GROUP BY w.weather_condition, t.time_band
ORDER BY w.weather_condition, t.time_id;

❓ よくある質問

Q1: ファクトテーブルに商品名を含めてはいけませんか？

基本的には含めません。スタースキーマでは、ファクトテーブルには外部キーと数値のみを保持します。理由は、データの重複を避け、ストレージを節約し、商品名変更時の対応が簡単になるためです。商品名が必要な場合は、dim_productをJOINして取得します。ただし、パフォーマンス重視でJOIN回数を減らしたい場合は、例外的に含めることもあります。

Q2: SCD Type 2は必ず実装すべきですか？

ビジネス要件によります。商品価格の変更履歴を保持したい、顧客セグメントの変化を追跡したい、過去の分析結果を正確に再現したい場合はSCD Type 2が必要です。一方、履歴が不要で常に最新の情報で分析したい場合、システムをシンプルに保ちたい場合はSCD Type 1で十分です。多くの場合、商品ディメンションと顧客ディメンションにはSCD Type 2を適用し、他のディメンションはSCD Type 1で十分です。

Q3: ファクトテーブルに計算済みの利益額を保持するのはなぜですか？

クエリパフォーマンスのためです。利益額 = 売上金額 – 原価は、クエリ実行時に毎回計算することも可能です。しかし、数億行のファクトテーブルで毎回計算すると遅く、集計クエリが複雑になります。ストレージコストは安価なので、頻繁に使う計算結果は事前計算して保存するのが一般的です。

Q4: ETL処理が失敗した場合、どう対処すべきですか？

以下の対策を実装します。ETL処理全体をトランザクションで囲みエラー時はロールバック、処理開始・終了時刻やエラー内容をログ記録、失敗時にメールやSlackで通知、一時的なエラーの場合は自動リトライ、ロード前後で件数チェックや整合性チェックを行います。また、ETL処理状況を管理するテーブルを用意し、どこまで処理したかを記録しておくことも重要です。

Q5: サマリーテーブルとファクトテーブル、どちらを使うべきか判断基準は？

用途に応じて使い分けます。サマリーテーブルは頻繁に実行される集計（経営ダッシュボードなど）、粒度が粗くて良い場合（日次、月次など）、パフォーマンスが最優先の場合に使います。ファクトテーブルは詳細な分析が必要な場合、アドホックなクエリ（事前に予測できない）、柔軟な切り口での分析が必要な場合に使います。推奨は両方を用意し、よく使う集計はサマリーテーブルから、詳細分析はファクトテーブルから取得することです。

Q6: 日付ディメンションは本当に必要ですか？date型のカラムでは不十分ですか？

日付ディメンションを強く推奨します。理由として、「四半期別」「曜日別」などがWHEREだけで可能で集計が簡単になること、企業の会計年度に合わせた集計が容易になること、営業日ベースの分析で祝日判定が簡単になること、日付関数を使わないためパフォーマンスが向上することが挙げられます。例えば、date型だけだと「四半期別」集計は `QUARTER(date)`関数が必要ですが、日付ディメンションがあれば `WHERE quarter = 1` だけでOKです。

📝

学習メモ

データベース設計・データモデリング - Step 15

📋 過去のメモ一覧 ▼