SQL Academy · 课时

事实表与维度表

Q: 「事实表与维度表」这节课中我会学到什么？

数据仓库的基础构件。 你通过在浏览器中直接运行的动手代码来练习 SQL Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

数据仓库的基础构件。

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

什么是数据仓库

数据仓库是为报告和分析查询而设计的中央存储库。事务数据库针对快速写入进行了优化，而数据仓库则针对在大量历史数据上进行快速读取进行了优化。

组织数据仓库最常见的方式是使用星型模式，将数据拆分为两种表：事实表和维度表。

事实表定义

事实表存储可度量的定量事件，即您希望分析的业务事件。每一行代表某个业务事件的一次发生，例如一次销售、一次网页浏览或一张支持工单。

事实表通常较长（行数很多）且较窄（列数较少），其中大多数列要么是指向维度表的外键，要么是数值度量，例如 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);

星型模式

如果将事实表放在中心、维度表向外辐射来绘制图表，它看起来就像一颗星，因此得名星型模式。

事实表中的外键指向各个维度的主键。查询通常会将事实表与一个或多个维度连接，为原始数值添加描述性上下文。

-- 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

粒度：事实表中的详细程度

事实表的粒度准确描述了一行代表什么。在构建数据仓库之前，您必须声明粒度——例如，销售订单中的每条产品明细占一行。

定义明确的粒度可以避免产生含义不明确的聚合。如果不同的行代表不同的事件，那么您的 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;

可加、半可加与不可加度量

根据聚合方式，事实度量分为三种类型：

可加——可以跨所有维度求和（例如 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);

退化维度

有时，维度属性不需要单独的表。退化维度是直接存放在事实表中的维度键，且没有对应的维度表。

典型示例包括订单号、发票号或工单编号。它们可以为下钻分析提供上下文，但没有其他值得存放在独立表中的描述性列。

-- 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;

快速检查：事实表与维度表

请测试您对星型模式中事实表与维度表差异的理解。

课程回顾

在本课中，您学习了数据仓库星型模式的核心构建块：

事实表存储可度量的事件（销售、点击、事务），并包含数值度量和外键。
维度表使用代理键提供描述性上下文（谁、什么、在哪里、何时）。
粒度准确规定一行事实代表什么，必须在构建之前声明。
度量可以是可加、半可加或不可加的，这决定了聚合它们的方式。
SCD 类型 2通过添加带有效日期的新行来保留历史维度值。
当退化维度没有额外属性需要描述时，它们可以直接存放在事实表中。

理解事实表和维度表，是构建快速、可扩展且分析能力强大的数据仓库的基础。

常见问题解答

「事实表与维度表」课时是免费的吗？

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

「事实表与维度表」这节课中我会学到什么？

数据仓库的基础构件。你通过在浏览器中直接运行的动手代码来练习 SQL Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 SQL Academy 需要有经验吗？

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

「事实表与维度表」课时需要多长时间？

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

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

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

此课程中的所有课时

← 返回 SQL Academy