SQL Interview Prep · 课时

星型模式与数据仓库设计

Q: 「星型模式与数据仓库设计」这节课中我会学到什么？

学习事实表、维度表、反规范化取舍和 OLAP 建模。 你通过在浏览器中直接运行的动手代码来练习 SQL Interview Prep，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习事实表、维度表、反规范化取舍和 OLAP 建模。

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

OLTP 与 OLAP

数据仓库相关问题通常从一个面试官期望您准确掌握的区分开始：OLTP 与 OLAP。

OLTP（事务型）：大量小型读取和写入，为保证完整性而高度规范化，为应用程序提供支持。
OLAP（分析型）：针对历史数据进行少量大型聚合读取，为提高速度而有意反规范化，为报表和仪表板提供支持。

星型模式是一种 OLAP 设计。其核心目的就是实现快速的分析查询，以可接受冗余为代价换取速度。

事实与维度

星型模式将数据分成两类表：

事实表：可度量的事件或事务（一次销售、一次点击）。其中保存数值度量值以及指向维度的外键。
维度表：用于切分分析的描述性上下文（日期、产品、客户、门店）。

事实表位于中心，维度表像星星的各个点一样围绕着它，因此得名。

事实表的构成

事实表主要由外键和数值度量值组成。它行数多、列数少，并且会持续增长。

度量值是可以进行聚合的可加数值：数量、收入、成本。必须清楚声明粒度（即一行代表一个？）；此处一行表示一笔销售中的一个商品明细。

CREATE TABLE fact_sales (
  sale_id      BIGINT PRIMARY KEY,
  date_key     INT  NOT NULL,   -- FK to dim_date
  product_key  INT  NOT NULL,   -- FK to dim_product
  customer_key INT  NOT NULL,   -- FK to dim_customer
  store_key    INT  NOT NULL,   -- FK to dim_store
  quantity     INT,             -- measure
  revenue      DECIMAL(12,2),   -- measure
  cost         DECIMAL(12,2)    -- measure
);

维度表的构成

维度表行数少、列数多：包含许多用于筛选和分组的描述性列。维度表会有意反规范化，因此查询时每个维度只需进行一次连接。

请注意，dim_product 将类别和品牌保存在同一行中，而不是拆分到不同表中。这种冗余正是设计目的：它可以避免查询时增加额外连接。

CREATE TABLE dim_product (
  product_key  INT PRIMARY KEY,   -- surrogate key
  product_id   INT,              -- natural/business key
  product_name VARCHAR(100),
  category     VARCHAR(50),      -- denormalized
  brand        VARCHAR(50),      -- denormalized
  unit_price   DECIMAL(10,2)
);

星型模式查询

这就是这种设计带来的好处。典型的分析查询会将事实表与少量维度表连接，进行筛选并聚合。每个维度只需一次连接，无需多层连接链。

面试官会要求您针对星型模式编写的正是这类查询。

SELECT d.category,
       t.year,
       SUM(f.revenue) AS total_revenue
FROM fact_sales f
JOIN dim_product d ON d.product_key = f.product_key
JOIN dim_date    t ON t.date_key    = f.date_key
WHERE t.year = 2025
GROUP BY d.category, t.year
ORDER BY total_revenue DESC;

代理键

维度表使用代理键：一种没有业务含义的整数主键（例如 product_key），由数据仓库生成，并且独立于源系统的自然键。

面试官关注这一点的原因：

它使数据仓库与不断变化的业务键解耦。
它让事实表保持精简（整数连接速度快）。
使用缓慢变化维度追踪历史记录时必须使用代理键（下一场内容）。

缓慢变化维度

这是数据仓库面试中常考的主题：当维度属性发生变化时（例如客户迁移到另一座城市），您会如何处理？这类维度称为缓慢变化维度（SCD）：

类型 1：覆盖旧值。不保留历史记录。
类型 2：添加包含生效日期和当前标志的新行。保留完整历史记录；这需要使用代理键。
类型 3：保留一个“先前值”列。只能保留有限的历史记录。

对于随时间追踪变化，类型 2 是最常见的预期答案。

-- SCD Type 2 dimension
CREATE TABLE dim_customer (
  customer_key INT PRIMARY KEY,   -- surrogate
  customer_id  INT,              -- natural key
  city         VARCHAR(50),
  valid_from   DATE,
  valid_to     DATE,
  is_current   BOOLEAN
);

星型模式与雪花模式

请准备好比较这两种模式的问题。雪花模式会将维度规范化为子表（商品 -> 类别 -> 部门），而星型模式会将维度保持扁平。

星型模式：连接更少、读取更快，但存在一定冗余。适合优先考虑查询性能的场景。
雪花模式：占用存储更少且维度维护更容易，但每次查询需要更多连接。

您可以这样回答：“默认使用星型模式以提升查询速度；只有在维度很大且会被复用时才使用雪花模式。”

日期维度

几乎每个星型模式都会使用专门的日期维度，而不是原始日期列。日期维度会预先计算年份、季度、月份、星期几、节假日标志和财务期间。

这样，分析人员就能通过一次简单的连接按“财务季度”或“是否周末”分组，而不必零散地调用日期函数。主动提及日期维度，是表明您构建过数据仓库的有力信号。

CREATE TABLE dim_date (
  date_key   INT PRIMARY KEY,   -- e.g. 20250131
  full_date  DATE,
  year       INT,
  quarter    INT,
  month      INT,
  day_of_week VARCHAR(10),
  is_weekend BOOLEAN,
  fiscal_qtr VARCHAR(6)
);

选择粒度

事实表最重要的决策是粒度：一行代表什么。请在做其他决策之前先明确它。

粒度过粗（每家门店每天一行）会丢失细节。
粒度过细（每个扫描商品一行）会使表迅速膨胀。

清晰的粒度声明，例如“每个订单明细中的每个商品一行”，会决定应该包含哪些维度和度量值。面试官会关注这种严谨性。

何时进行反规范化

请将它与规范化联系起来。OLTP 系统为保证完整性而规范化到 3NF；数据仓库则会有意反规范化维度表，以提高读取速度。

您必须说明其中的权衡：

维度数据存在冗余是可以接受的，因为数据仓库由受控的 ETL 过程加载，而不是由应用程序随意写入。
连接更少意味着在数十亿条事实记录上进行聚合时速度更快。

这里真正区分高级回答的，是判断能力，而不是死记规则。

快速检查

您正在设计销售数据仓库，需要在客户迁移城市时保留其城市的完整历史记录。

回顾：星型模式与数据仓库设计

现在，您已经能够应对数据仓库建模问题：

OLTP 为保证完整性而规范化；OLAP 为提高读取速度而反规范化。
星型模式以中央的事实表（外键和数值度量值）为核心，周围是扁平的维度表。
使用代理键和专用的日期维度。
使用 SCD 类型 2追踪变化；先声明事实表的粒度。
为了提高查询性能，优先选择星型模式而非雪花模式。

常见问题解答

「星型模式与数据仓库设计」课时是免费的吗？

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

「星型模式与数据仓库设计」这节课中我会学到什么？

学习事实表、维度表、反规范化取舍和 OLAP 建模。你通过在浏览器中直接运行的动手代码来练习 SQL Interview Prep，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 SQL Interview Prep 需要有经验吗？

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

「星型模式与数据仓库设计」课时需要多长时间？

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

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

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

此课程中的所有课时

← 返回 SQL Interview Prep