SQL Academy · 课时

星型模式与雪花模式

Q: 「星型模式与雪花模式」这节课中我会学到什么？

为快速分析建模数据。 你通过在浏览器中直接运行的动手代码来练习 SQL Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

为快速分析建模数据。

第 3 / 4 课13 个步骤

星型模式与雪花模式 是 CoddyKit 上的免费 SQL Academy 课时。这是第 3 节课，共 4 节。你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。这是 SQL Academy 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 SQL Academy 课程共包含 4 节课。

什么是数据仓库模式

在事务型（OLTP）数据库中，您会对数据进行规范化以避免冗余。在数据仓库中，您通常会有意进行反规范化，以存储空间换取查询速度。组织数据仓库表的两种经典模式是星型模式和雪花模式。

两者都围绕一个中央事实表展开，周围环绕着维度表。区别在于对这些维度进行规范化的程度。

事实表与维度表

事实表存储可度量的事件，例如销售、点击和发货。它通常包含很多行，并包含数值度量以及指向维度的外键。

维度表描述每个事件的上下文：谁、什么、何时、何地。维度表行数较少，但描述性列更丰富。

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,
  revenue      NUMERIC(12, 2) NOT NULL
);

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

星型模式

在星型模式中，每个维度表都与事实表直接连接。将这些关系画在纸上，它看起来就像一颗星——事实表是中心，维度表是各个尖角。

维度表完全反规范化：所有描述性属性都存放在同一张表中，即使某些属性会在多行之间重复。

-- Star schema: all product info in one flat dimension table
CREATE TABLE dim_product (
  product_key    SERIAL PRIMARY KEY,
  product_name   VARCHAR(200),
  category_name  VARCHAR(100),   -- denormalized
  subcategory    VARCHAR(100),   -- denormalized
  brand_name     VARCHAR(100),   -- denormalized
  brand_country  VARCHAR(100),   -- denormalized
  unit_price     NUMERIC(10, 2)
);

CREATE TABLE dim_date (
  date_key   INT PRIMARY KEY,   -- e.g. 20240315
  full_date  DATE,
  year       INT,
  quarter    INT,
  month      INT,
  month_name VARCHAR(20),
  week       INT,
  day_of_week VARCHAR(10)
);

星型模式查询

扁平化的维度表使查询更加简单。您只需将事实表与一个或多个维度连接并进行聚合，无需通过规范化表链执行额外连接。

这就是星型模式能够提供快速分析查询的原因——连接图较浅。

SELECT
  d.year,
  d.quarter,
  p.category_name,
  SUM(f.revenue)   AS total_revenue,
  SUM(f.quantity)  AS units_sold
FROM fact_sales f
JOIN dim_date    d ON d.date_key    = f.date_key
JOIN dim_product p ON p.product_key = f.product_key
WHERE d.year = 2024
GROUP BY d.year, d.quarter, p.category_name
ORDER BY d.quarter, total_revenue DESC;

雪花模式

雪花模式会将维度表拆分为子维度，从而进一步实现规范化。例如，与其将 category_name 和 brand_name 存储在 dim_product 中，不如创建单独的 dim_category 和 dim_brand 表。

生成的图表看起来像雪花——由相关表组成的分支状结构。

-- Snowflake schema: product dimension is normalized
CREATE TABLE dim_brand (
  brand_key     SERIAL PRIMARY KEY,
  brand_name    VARCHAR(100),
  brand_country VARCHAR(100)
);

CREATE TABLE dim_category (
  category_key   SERIAL PRIMARY KEY,
  category_name  VARCHAR(100),
  subcategory    VARCHAR(100)
);

CREATE TABLE dim_product (
  product_key  SERIAL PRIMARY KEY,
  product_name VARCHAR(200),
  category_key INT REFERENCES dim_category(category_key),
  brand_key    INT REFERENCES dim_brand(brand_key),
  unit_price   NUMERIC(10, 2)
);

雪花模式查询

查询雪花模式需要执行更多连接操作，以重新组装分散在多个表中的维度数据。查询优化器必须遍历额外的层级，因此与星型模式相比，可能会增加延迟。

不过，规范化后的维度更小且更加一致——在 dim_brand 的一行中更新品牌名称后，该更改会自动在所有位置生效。

SELECT
  d.year,
  c.category_name,
  b.brand_name,
  SUM(f.revenue) AS total_revenue
FROM fact_sales    f
JOIN dim_date      d ON d.date_key    = f.date_key
JOIN dim_product   p ON p.product_key = f.product_key
JOIN dim_category  c ON c.category_key = p.category_key
JOIN dim_brand     b ON b.brand_key    = p.brand_key
WHERE d.year = 2024
GROUP BY d.year, c.category_name, b.brand_name
ORDER BY total_revenue DESC;

代理键与自然键

维度表通常使用仓库生成的代理键（例如 SERIAL，一个整数），而不是源系统中的自然键。

即使源系统发生变化，代理键仍能保持稳定；对于大型事实表而言，它们占用空间小；此外还支持需要跟踪历史记录的缓慢变化维度。

-- Surrogate key (product_key) vs natural key (sku)
INSERT INTO dim_product (product_name, category_key, brand_key, unit_price)
VALUES ('Wireless Headphones', 3, 7, 89.99);
-- product_key is assigned by SERIAL -- the natural key (SKU) lives elsewhere

-- Natural key would be:
-- INSERT INTO dim_product (sku, product_name, ...)
-- VALUES ('WH-1000XM5', 'Wireless Headphones', ...);
-- Risky: SKU can be reused or reassigned by the source system

日期维度

日期维度很特殊——它几乎总是存在，通常会预先填充多年的日期。将派生属性（年份、季度、月份名称、财务期间、节假日标志）存储在维度表中，可以避免在查询时重新计算这些属性。

-- Populate dim_date for one year using generate_series
INSERT INTO dim_date (date_key, full_date, year, quarter, month, month_name, week, day_of_week)
SELECT
  TO_CHAR(d, 'YYYYMMDD')::INT  AS date_key,
  d                             AS full_date,
  EXTRACT(YEAR    FROM d)::INT  AS year,
  EXTRACT(QUARTER FROM d)::INT  AS quarter,
  EXTRACT(MONTH   FROM d)::INT  AS month,
  TO_CHAR(d, 'Month')           AS month_name,
  EXTRACT(WEEK    FROM d)::INT  AS week,
  TO_CHAR(d, 'Day')             AS day_of_week
FROM generate_series('2024-01-01'::DATE, '2024-12-31'::DATE, '1 day') AS d;

缓慢变化维度（SCD 类型 2）

当客户迁移城市或产品更换类别时怎么办？您需要跟踪历史记录。SCD 类型 2会在每次变更时插入一条新的维度行，同时为之前的行设置结束日期，从而将其关闭。事实表中的行仍然指向旧的维度键，保留历史准确性。

-- SCD Type 2 customer dimension
CREATE TABLE dim_customer (
  customer_key  SERIAL PRIMARY KEY,
  customer_id   INT NOT NULL,
  customer_name VARCHAR(200),
  city          VARCHAR(100),
  country       VARCHAR(100),
  valid_from    DATE NOT NULL,
  valid_to      DATE,
  is_current    BOOLEAN DEFAULT TRUE
);

-- When a customer moves, close old row and insert new one:
UPDATE dim_customer
   SET valid_to = CURRENT_DATE - 1, is_current = FALSE
 WHERE customer_id = 42 AND is_current = TRUE;

INSERT INTO dim_customer (customer_id, customer_name, city, country, valid_from, is_current)
VALUES (42, 'Alice Muller', 'Berlin', 'Germany', CURRENT_DATE, TRUE);

星型模式与雪花模式——权衡

没有哪种模式在所有情况下都更好。请根据您的优先事项进行选择：

星型模式 — 连接更少、查询更快、ETL 更简单、存储成本更高。最适合读取密集型分析工具（Tableau、Power BI）。
雪花模式 — 维度经过规范化、冗余更少、维度更新更容易，但需要更多连接。当维度很大或由多个事实表共享时，这种模式更合适。

-- Checking how much storage the denormalized category column costs
-- in a large dim_product (star schema) vs a separate dim_category (snowflake)
SELECT
  COUNT(*)                               AS total_products,
  COUNT(DISTINCT category_name)          AS unique_categories,
  pg_size_pretty(
    SUM(pg_column_size(category_name))
  )                                      AS category_storage
FROM dim_product;

星系模式（事实星座）

当数据仓库中有多个事实表共享维度表时，结果称为星系模式（或事实星座）。例如，零售数据仓库可能有单独的销售事实表和退货事实表，两者都引用相同的 dim_product 和 dim_date 表。

共享维度可以确保筛选条件一致，并使跨事实表比较变得直接明了。

CREATE TABLE fact_returns (
  return_id     SERIAL PRIMARY KEY,
  date_key      INT NOT NULL REFERENCES dim_date(date_key),
  product_key   INT NOT NULL REFERENCES dim_product(product_key),
  customer_key  INT NOT NULL,
  quantity      INT NOT NULL,
  refund_amount NUMERIC(12, 2) NOT NULL
);

-- Cross-fact query: net revenue = sales - refunds
SELECT
  d.year,
  d.month,
  SUM(s.revenue)       AS gross_revenue,
  SUM(r.refund_amount) AS total_refunds,
  SUM(s.revenue) - COALESCE(SUM(r.refund_amount), 0) AS net_revenue
FROM dim_date d
LEFT JOIN fact_sales   s ON s.date_key = d.date_key
LEFT JOIN fact_returns r ON r.date_key = d.date_key
WHERE d.year = 2024
GROUP BY d.year, d.month
ORDER BY d.month;

星型模式与雪花模式

请检验您对星型模式和雪花模式的理解。

在交互式课程中回答

课程回顾

本课介绍了两种基础的数据仓库设计模式：

星型模式 — 中央事实表周围环绕着平坦、非规范化的维度表。连接更少、查询更快，但存储量略高。
雪花模式 — 维度表进一步规范化为子维度。冗余更少、更新更容易，但需要更多连接。
事实表存储可度量的事件；维度表提供上下文（谁、什么、何时、何地）。
代理键可以保证历史准确性，并使数据仓库与源系统的变更解耦。
SCD 类型 2通过添加带有效期的行来跟踪维度历史，而不是覆盖旧行。
当多个事实表共享维度时，该设计会变成星系（事实星座）模式。

请选择星型模式以获得简单性和速度；当维度很大、更新频繁或被许多事实表共享时，请选择雪花模式。

免费开始

用 AI 导师学习 SQL — 免费

在浏览器中编写并运行真实代码，获得全天候 AI 导师的即时帮助，并在网页或应用中继续学习。

课程: 46
课程: 183

免费开始学习查看完整课程

常见问题解答

「星型模式与雪花模式」课时是免费的吗？

是的 — 「星型模式与雪花模式」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 SQL Academy 课程的其余内容，请升级到 CoddyKit PRO。 SQL Academy 课程共包含 4 节课。

「星型模式与雪花模式」这节课中我会学到什么？

为快速分析建模数据。你通过在浏览器中直接运行的动手代码来练习 SQL Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 SQL Academy 需要有经验吗？

无需任何先前经验。CoddyKit 上的 SQL Academy 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。这是第 3 节课，共 4 节。

「星型模式与雪花模式」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

我能在这节 SQL Academy 课中编写并运行代码吗？

能。每节 SQL Academy 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

此课程中的所有课时

← 返回 SQL Academy