SQL Academy · レッスン

ファクトテーブルとディメンションテーブル

Q: 「ファクトテーブルとディメンションテーブル」で何を学びますか？

データウェアハウスを構成する基本要素を学びます ブラウザで直接実行するハンズオンコードでSQL Academyを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

データウェアハウスを構成する基本要素を学びます

「ファクトテーブルとディメンションテーブル」はCoddyKit上の無料SQL Academyレッスンです。これはレッスン2/4です。下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。これはSQL Academy学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 SQL Academyコースには全4レッスンが含まれています。

データウェアハウスとは

データウェアハウスとは、レポート作成や分析クエリのために設計された中央リポジトリです。高速な書き込みに最適化されたトランザクションデータベースとは異なり、データウェアハウスは大量の履歴データに対する高速な読み取りに適しています。

データウェアハウスを構成する最も一般的な方法はスタースキーマです。スタースキーマでは、データをファクトテーブルとディメンションテーブルという2種類のテーブルに分けます。

ファクトテーブルの定義

ファクトテーブルには、測定可能な定量的イベント、つまり分析対象となる事象が格納されます。各行は、販売、ウェブページの表示、サポートチケットなど、1件のビジネスイベントの発生を表します。

ファクトテーブルは通常、行数が多く列数が少ない構造で、多くの列がディメンションテーブルへの外部キー、またはquantityやrevenueのような数値メジャーです。

CREATE TABLE fact_sales (
  sale_id      SERIAL PRIMARY KEY,
  date_key     INT NOT NULL,
  product_key  INT NOT NULL,
  customer_key INT NOT NULL,
  store_key    INT NOT NULL,
  quantity     INT NOT NULL,
  unit_price   NUMERIC(10, 2) NOT NULL,
  total_amount NUMERIC(12, 2) NOT NULL
);

ディメンションテーブルの定義

ディメンションテーブルには、各ファクトに文脈を与える説明属性が格納されます。たとえば、製品ディメンション（名前、カテゴリ、ブランド）や日付ディメンション（日、月、四半期、年）があります。

ディメンションテーブルは通常、行数は少ないものの、説明用の列が多い構造です。代理整数キーを使用してファクトテーブルと結合します。

CREATE TABLE dim_product (
  product_key  SERIAL PRIMARY KEY,
  product_name VARCHAR(200) NOT NULL,
  category     VARCHAR(100),
  brand        VARCHAR(100),
  unit_cost    NUMERIC(10, 2)
);

CREATE TABLE dim_customer (
  customer_key SERIAL PRIMARY KEY,
  full_name    VARCHAR(200) NOT NULL,
  email        VARCHAR(200),
  country      VARCHAR(100),
  segment      VARCHAR(50)
);

日付ディメンション

日付ディメンションは、あらゆるデータウェアハウスで最も一般的なディメンションです。ファクトテーブルに生のTIMESTAMPを格納する代わりに、あらかじめ作成したカレンダーテーブルを参照する整数キーを格納します。

これにより、クエリ実行時に日付計算を行わず、会計四半期、曜日、休日フラグなどのカレンダー属性でフィルタリングやグループ化ができます。

CREATE TABLE dim_date (
  date_key       INT PRIMARY KEY,  -- e.g. 20240315
  full_date      DATE NOT NULL,
  day_of_week    VARCHAR(10),
  day_of_month   INT,
  month_num      INT,
  month_name     VARCHAR(20),
  quarter        INT,
  year           INT,
  is_holiday     BOOLEAN DEFAULT FALSE,
  fiscal_quarter INT
);

-- Sample row
INSERT INTO dim_date VALUES
  (20240315, '2024-03-15', 'Friday', 15, 3, 'March', 1, 2024, FALSE, 2);

スタースキーマのパターン

1つのファクトテーブルを中央に配置し、周囲にディメンションテーブルを放射状に配置した図を描くと、星のように見えます。これがスタースキーマという名前の由来です。

ファクトテーブルの外部キーは、各ディメンションの主キーを指します。クエリでは通常、ファクトテーブルを1つ以上のディメンションと結合し、生の数値に説明的な文脈を加えます。

-- Join fact to two dimensions to enrich a sales report
SELECT
  dp.product_name,
  dp.category,
  SUM(fs.quantity)     AS total_units_sold,
  SUM(fs.total_amount) AS total_revenue
FROM fact_sales fs
JOIN dim_product  dp ON dp.product_key  = fs.product_key
JOIN dim_date     dd ON dd.date_key     = fs.date_key
WHERE dd.year = 2024
GROUP BY dp.product_name, dp.category
ORDER BY total_revenue DESC;

代理キーと自然キー

ディメンションテーブルでは、ビジネス上の意味から独立してデータベースが生成する合成整数である代理キーを使用します。自然キー（製品SKUや顧客のメールアドレスなど）は時間とともに変わる可能性がありますが、代理キーは変わりません。

代理キーを使用すると、ファクトテーブルを上流システムの変更から切り離せます。また、整数の比較は文字列の比較より低コストであるため、結合も高速になります。

-- Surrogate key approach: integer join is fast
SELECT fs.sale_id, dc.full_name, fs.total_amount
FROM fact_sales fs
JOIN dim_customer dc ON dc.customer_key = fs.customer_key
WHERE dc.country = 'Germany'
LIMIT 10;

-- Natural key approach (avoid in warehouses): slower string join
-- JOIN dim_customer dc ON dc.email = fs.customer_email

粒度：ファクトテーブルの詳細レベル

ファクトテーブルの粒度は、1行が正確に何を表すかを示します。データウェアハウスを構築する前に、粒度を宣言する必要があります。たとえば、販売注文に含まれる個々の商品明細ごとに1行と定義します。

粒度を明確に定義すると、曖昧な集計を防げます。異なる行が異なるイベントを表していると、SUMやCOUNTの結果は意味を持たなくなります。

-- Grain: one row per product per order line
-- Each row = one line item sold in one transaction
SELECT
  sale_id,
  date_key,
  product_key,
  quantity,
  unit_price,
  total_amount
FROM fact_sales
WHERE date_key = 20240315
ORDER BY sale_id;

加法的、半加法的、非加法的メジャー

ファクトは、集計方法に基づいて3種類に分けられます。

加法的 — すべてのディメンションにわたって合計できます（例：revenue、quantity）。
半加法的 — 一部のディメンションでは合計できますが、すべてのディメンションでは合計できません（例：口座のbalanceは顧客間では合計できますが、時間をまたいでは合計できません）。
非加法的 — 意味のある形で合計できません（例：unit_price、ratio）。代わりにAVGなどの集計関数を使用します。

SELECT
  dd.month_name,
  SUM(fs.total_amount)         AS total_revenue,   -- additive
  AVG(fs.unit_price)           AS avg_unit_price,   -- non-additive: use AVG
  SUM(fs.quantity)             AS total_units       -- additive
FROM fact_sales fs
JOIN dim_date dd ON dd.date_key = fs.date_key
WHERE dd.year = 2024
GROUP BY dd.month_name, dd.month_num
ORDER BY dd.month_num;

緩やかに変化するディメンション（SCDタイプ1および2）

ディメンション属性は時間とともに変化します。たとえば、顧客が居住国を変更したり、製品のカテゴリが変わったりします。緩やかに変化するディメンション（SCD）は、このような変更を処理します。

タイプ1 — 古い値を上書きします。単純ですが、履歴が失われます。
タイプ2 — 新しい代理キーと有効期間の日付を持つ新しい行を追加します。完全な履歴を保持できるため、過去のファクトはディメンションの正しいバージョンを参照し続けます。

-- SCD Type 2: add a new version of the row
ALTER TABLE dim_customer ADD COLUMN valid_from DATE;
ALTER TABLE dim_customer ADD COLUMN valid_to   DATE;
ALTER TABLE dim_customer ADD COLUMN is_current BOOLEAN DEFAULT TRUE;

-- Expire the old row
UPDATE dim_customer
SET is_current = FALSE,
    valid_to   = CURRENT_DATE - INTERVAL '1 day'
WHERE email = 'anna@example.com' AND is_current = TRUE;

-- Insert the updated version
INSERT INTO dim_customer (full_name, email, country, segment, valid_from, valid_to, is_current)
VALUES ('Anna Muller', 'anna@example.com', 'Austria', 'Premium', CURRENT_DATE, '9999-12-31', TRUE);

縮退ディメンション

ディメンション属性に独自のテーブルが必要ない場合があります。縮退ディメンションとは、対応するディメンションテーブルを持たず、ファクトテーブルに直接格納されるディメンションキーです。

代表的な例として、注文番号、請求書番号、チケットIDがあります。これらはドリルダウンのための文脈を提供しますが、別のテーブルに格納する価値のある説明用の列はありません。

-- order_number is a degenerate dimension:
-- it lives in the fact table, no dim_order table needed
CREATE TABLE fact_order_lines (
  line_id      SERIAL PRIMARY KEY,
  order_number VARCHAR(20) NOT NULL,  -- degenerate dimension
  date_key     INT NOT NULL,
  product_key  INT NOT NULL,
  customer_key INT NOT NULL,
  quantity     INT NOT NULL,
  line_total   NUMERIC(12, 2) NOT NULL
);

SELECT order_number, SUM(line_total) AS order_total
FROM fact_order_lines
GROUP BY order_number
ORDER BY order_total DESC
LIMIT 5;

スタースキーマ全体へのクエリ

すべてを組み合わせると、一般的なデータウェアハウスクエリでは、ファクトテーブルを複数のディメンションと結合し、ディメンション属性にフィルターを適用して、ファクトテーブルのメジャーを集計します。

ファクトテーブルの外部キーにインデックスが付いており、ディメンションテーブルも比較的小さいため、オプティマイザーはこのような多表結合を効率的に処理できます。

SELECT
  dd.year,
  dd.quarter,
  dp.category,
  dc.country,
  SUM(fs.quantity)     AS units_sold,
  SUM(fs.total_amount) AS revenue
FROM fact_sales fs
JOIN dim_date     dd ON dd.date_key     = fs.date_key
JOIN dim_product  dp ON dp.product_key  = fs.product_key
JOIN dim_customer dc ON dc.customer_key = fs.customer_key
WHERE dd.year IN (2023, 2024)
  AND dp.category = 'Electronics'
GROUP BY dd.year, dd.quarter, dp.category, dc.country
ORDER BY dd.year, dd.quarter, revenue DESC;

クイックチェック：ファクトとディメンション

スタースキーマにおけるファクトテーブルとディメンションテーブルの違いについて、理解度を確認しましょう。

レッスンのまとめ

このレッスンでは、データウェアハウスのスタースキーマを構成する基本要素を学びました。

ファクトテーブルには、数値メジャーと外部キーを伴う測定可能なイベント（販売、クリック、トランザクション）が格納されます。
ディメンションテーブルは、代理キーを使用して説明的な文脈（誰が、何を、どこで、いつ）を提供します。
粒度は、1つのファクト行が正確に何を表すかを定義します。構築前に宣言してください。
メジャーには加法的、半加法的、非加法的なものがあり、それによって集計方法が決まります。
SCDタイプ2は、有効期間の日付を持つ新しい行を追加することで、過去のディメンション値を保持します。
縮退ディメンションは、説明する追加属性がない場合にファクトテーブル内に格納されます。

ファクトテーブルとディメンションテーブルを理解することは、高速でスケーラブルかつ分析能力に優れたデータウェアハウスを構築するための基礎です。

よくある質問

「ファクトテーブルとディメンションテーブル」レッスンは無料ですか？

はい。「ファクトテーブルとディメンションテーブル」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、SQL Academyコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 SQL Academyコースには全4レッスンが含まれています。

「ファクトテーブルとディメンションテーブル」で何を学びますか？

データウェアハウスを構成する基本要素を学びますブラウザで直接実行するハンズオンコードでSQL Academyを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

SQL Academyを始めるのに経験は必要ですか？

事前経験は必要ありません。CoddyKitのSQL Academyは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。これはレッスン2/4です。

「ファクトテーブルとディメンションテーブル」レッスンにはどのくらい時間がかかりますか？

ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。

このSQL Academyレッスンでコードを書いて実行できますか？

はい。すべてのSQL Academyレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。

このコースのすべてのレッスン

← SQL Academyに戻る