2022-12-30

スタースキーマとは

スタースキーマとは

スタースキーマは、データウェアハウスで使用されるデータモデリング技術の一種です。この技術は、複数の次元テーブルで囲まれたファクトテーブルと呼ばれる中央のテーブルにデータを組織化します。スタースキーマにおいて、ファクトテーブルには売上高や製品販売数量などの定量的データが含まれ、次元テーブルには、購入を行った顧客や売上が発生した時期などのそのデータのコンテキストが提供されます。

Star Schema: Still Relevant Almost 30 years Later?

スタースキーマを使用する主なメリットの1つは、そのシンプルさと使いやすさです。データがシンプルで直感的な構造に組織化されているため、分析者やビジネスユーザーがデータを理解し、アクセスすることが容易になります。これにより、複雑なクエリや分析を実行しやすくなり、より洞察力のあるアクションにつながります。

また、スタースキーマを使用することで、クエリのパフォーマンスを向上させることができます。データをスタースキーマに組織化することで、クエリのスピードと効率を最適化し、より迅速なレスポンス時間とよりタイムリーな意思決定を実現することができます。

なお、スタースキーマは、ディメンションモデリングの特定の種類です。ディメンションモデリングは、クエリと分析に最適化された構造を作成することを目的としたより広範なデータモデリングのアプローチです。スタースキーマは、データウェアハウジングでよく使用される次元モデリングの1つですが、スノーフレークスキーマやファクトコンステレーションスキーマなど、他の種類のディメンションモデリング技術も存在します。

スタースキーマのデザイン

スタースキーマのデザインには、データのファクトやディメンションの識別、ファクトテーブルやディメンションテーブルの作成、それらの間の関係の確立が含まれます。以下は、架空のEC企業のスタースキーマのデザイン例です。

ファクトやディメンションの識別

スタースキーマのデザインにあたっては、まずファクトやディメンションを識別することが必要です。EC企業の場合、ファクトには売上高、注文数、商品返品数などが含まれます。ディメンションには時間、商品、顧客、場所などが含まれます。

ファクトテーブルの作成

ファクトテーブルは、定量的なデータが格納されるスタースキーマの中心的なテーブルです。この例では、売上高と注文数が含まれるsales_fact_tableという名前のファクトテーブルを作成します。

sql

CREATE TABLE sales_fact_table (
   order_id INT,
   product_id INT,
   customer_id INT,
   time_id INT,
   location_id INT,
   revenue DECIMAL(10,2),
   orders INT
);

ディメンションテーブルの作成

ディメンションテーブルは、ファクトテーブル内のデータに対する文脈を提供します。この例では、商品、顧客、場所、時間の4つのディメンションテーブルを作成します。

sql

CREATE TABLE product_dim_table (
   product_id INT,
   product_name VARCHAR(50),
   category VARCHAR(50)
);

CREATE TABLE customer_dim_table (
   customer_id INT,
   customer_name VARCHAR(50),
   customer_email VARCHAR(50),
   customer_address VARCHAR(100)
);

CREATE TABLE location_dim_table (
   location_id INT,
   location_name VARCHAR(50),
   city VARCHAR(50),
   state VARCHAR(50)
);

CREATE TABLE time_dim_table (
   time_id INT,
   date DATE,
   month INT,
   year INT
);

関係の確立

ファクトテーブルとディメンションテーブルの間の関係を確立するために、この例では外部キーを使用しています。

sql

ALTER TABLE sales_fact_table ADD FOREIGN KEY (product_id) REFERENCES product_dim_table(product_id);
ALTER TABLE sales_fact_table ADD FOREIGN KEY (customer_id) REFERENCES customer_dim_table(customer_id);
ALTER TABLE sales_fact_table ADD FOREIGN KEY (location_id) REFERENCES location_dim_table(location_id);
ALTER TABLE sales_fact_table ADD FOREIGN KEY (time_id) REFERENCES time_dim_table(time_id);

以下は、サンプルデータを使用したスター・スキーマの例です。

売上ファクトテーブル：

Order ID	Product ID	Customer ID	Time ID	Location ID	Revenue	Orders
1	101	1001	1	1	500.00	2
2	102	1002	2	2	250.00	1
3	103	1003	3	3	1000.00	5

商品ディメンジョンテーブル：

Product ID	Product Name	Category
101	Product A	Electronics
102	Product B	Clothing
103	Product C	Home

顧客ディメンジョンテーブル：

Customer ID	Customer Name	Customer Email	Customer Address
1001	John Smith	john@smith.com	123 Main Street
1002	Jane Doe	jane@doe.com	456 Maple

スタースキーマのクエリ

スタースキーマのクエリは、ファクトテーブルとディメンションテーブルから有用な情報を抽出するためにSQLを使用することを含みます。以下は、架空のeコマース企業に対するスタースキーマのクエリの例です。

集計とグループ化

集計とグループ化は、スタースキーマのクエリに使用される一般的な技術です。これには、合計、平均、カウントなどの集計統計を計算し、1つ以上のディメンションによってデータをグループ化することが含まれます。

例：月ごとの総売上高を計算

この例では、各月の総売上高を計算します。

sql

SELECT time_dim_table.month, SUM(sales_fact_table.revenue) as total_sales
FROM sales_fact_table
JOIN time_dim_table ON sales_fact_table.time_id = time_dim_table.time_id
GROUP BY time_dim_table.month;

出力:

Month	Total Sales
1	5000.00
2	7500.00
3	10000.00

例：顧客セグメント別平均売上高

この例では、各顧客セグメントの平均売上高を計算します。

sql

SELECT customer_dim_table.customer_segment, AVG(sales_fact_table.revenue) as avg_sales
FROM sales_fact_table
JOIN customer_dim_table ON sales_fact_table.customer_id = customer_dim_table.customer_id
GROUP BY customer_dim_table.customer_segment;

出力:

Customer Segment	Average Sales
New Customers	750.00
Returning Customers	1000.00

時間ベースの分析の処理

時間ベースの分析は、スター・スキーマをクエリするための一般的なユースケースです。これには、年々の成長や移動平均など、時間のトレンドやパターンを分析することが含まれます。

例：年次売上成長率

この例では、各月の年次売上成長率を計算します。

sql

SELECT t1.month, SUM(t1.revenue) as current_year_sales, SUM(t2.revenue) as last_year_sales,
       (SUM(t1.revenue) - SUM(t2.revenue)) / SUM(t2.revenue) as yoy_growth
FROM sales_fact_table t1
JOIN sales_fact_table t2 ON t1.product_id = t2.product_id AND t1.time_id = t2.time_id
JOIN time_dim_table ON t1.time_id = time_dim_table.time_id
WHERE time_dim_table.year = 2022 AND t2.year = 2021
GROUP BY t1.month;

出力:

Month	Current Year Sales	Last Year Sales	YoY Growth
1	5000.00	4000.00	0.25
2	7500.00	6000.00	0.25
3	10000.00	8000.00	0.25

例：売上の移動平均

この例では、売上高の移動平均を3か月間計算します。

sql

SELECT time_dim_table.month, AVG(sales_fact_table.revenue) OVER (ORDER BY time_dim_table.month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as moving_average
FROM sales_fact_table
JOIN time_dim_table ON sales_fact_table.time_id = time_dim_table.time_id;