事实表是分析型数据库结构中度量数据的主要存储地。维度表给出描述性信息,即“谁、何事、何地、何时”,而事实表则保存业务活动产生的量化数据。它处于星型模式的中央,通过外键将不同的维度关联起来。设计得当的事实表行数多而列数少。它可能包含数十亿行,每行表示一次独立的交易或事件,但其列数通常少于关联的维度表。这些列通常分为两类:引用维度表的外键,以及作为分析对象的数值型度量。事实表行结构事实表中的每一行都对应由粒度确定的特定事件。如果粒度定义为“客户收据上每行项目对应一行”,那么事实表必须记录与该单行项目相关的特定指标,如销售数量和延长价格。事实表的结构完整性依赖于其外键与维度表主键间的关系。这些外键的组合构成了事实表自身的复合主键。这保证了在给定粒度下每条记录的唯一性。digraph G { rankdir=TB; node [shape=plaintext fontname="Arial" fontsize=12]; edge [color="#adb5bd" arrowsize=0.7]; fact_table [label=< <table border="0" cellborder="1" cellspacing="0" cellpadding="8" bgcolor="#ffffff"> <tr><td bgcolor="#1c7ed6"><font color="white"><b>事实_销售</b></font></td></tr> <tr><td align="left" port="d1">日期键 (外键)</td></tr> <tr><td align="left" port="d2">产品键 (外键)</td></tr> <tr><td align="left" port="d3">门店键 (外键)</td></tr> <tr><td align="left" port="d4">客户键 (外键)</td></tr> <tr><td align="left" bgcolor="#f1f3f5"><i>销售数量</i></td></tr> <tr><td align="left" bgcolor="#f1f3f5"><i>扩展金额</i></td></tr> <tr><td align="left" bgcolor="#f1f3f5"><i>折扣值</i></td></tr> </table> >]; dim_date [label=< <table border="0" cellborder="1" cellspacing="0" cellpadding="6" bgcolor="#e7f5ff"> <tr><td bgcolor="#4dabf7"><font color="white"><b>维度_日期</b></font></td></tr> <tr><td align="left">日期键 (主键)</td></tr> </table> >]; dim_product [label=< <table border="0" cellborder="1" cellspacing="0" cellpadding="6" bgcolor="#e7f5ff"> <tr><td bgcolor="#4dabf7"><font color="white"><b>维度_产品</b></font></td></tr> <tr><td align="left">产品键 (主键)</td></tr> </table> >]; dim_store [label=< <table border="0" cellborder="1" cellspacing="0" cellpadding="6" bgcolor="#e7f5ff"> <tr><td bgcolor="#4dabf7"><font color="white"><b>维度_门店</b></font></td></tr> <tr><td align="left">门店键 (主键)</td></tr> </table> >]; dim_customer [label=< <table border="0" cellborder="1" cellspacing="0" cellpadding="6" bgcolor="#e7f5ff"> <tr><td bgcolor="#4dabf7"><font color="white"><b>维度_客户</b></font></td></tr> <tr><td align="left">客户键 (主键)</td></tr> </table> >]; dim_date -> fact_table:d1; dim_product -> fact_table:d2; dim_store -> fact_table:d3; dim_customer -> fact_table:d4; }事实表结构是维度的会合点。它包含指向维度的外键(FK)和从交易获取的数值型度量(底部三行)。度量的可加性事实表设计的一个主要考量是理解度量在聚合时的表现。分析师经常按区域、月份或产品类别对销售数据进行汇总,即跨不同维度求和。度量在这一汇总过程中的表现决定了其可加性。完全可加度量这些度量可以跨事实表关联的所有维度进行求和。这是最灵活、最受欢迎的度量类型。例如,零售交易表中的 销售数量 就是完全可加的。你可以跨门店、跨天或跨产品对销售数量求和,结果在逻辑上依然成立。$$总数量 = \sum_{i=1}^{n} q_i$$半可加度量半可加度量可以跨某些维度求和,但不能跨其他维度求和。最普遍的例子是库存快照或账户余额。设想一个仓库库存事实表。你可以对所有仓库中 产品 A 的数量求和,以获得某一特定日的总库存。但是,你不能跨时间对 产品 A 的库存求和。将 1 月 1 日的库存量加上 1 月 2 日的库存量,会得出无意义的数字,这相当于实际库存的两倍。为了在 SQL 中正确处理半可加事实,你通常对空间维度(仓库、区域)使用 SUM() 等聚合函数,但对时间维度(时间)则坚持使用 LAST_VALUE() 或带过滤条件的查询。{ "data": [ { "x": ["仓库 A", "仓库 B", "总计 (有效)"], "y": [100, 150, 250], "name": "1月1日快照", "type": "bar", "marker": {"color": ["#339af0", "#339af0", "#51cf66"]} }, { "x": ["1月1日", "1月2日", "总和 (无效)"], "y": [100, 110, 210], "name": "仓库 A 历史", "type": "bar", "xaxis": "x2", "yaxis": "y2", "marker": {"color": ["#339af0", "#339af0", "#ff6b6b"]} } ], "layout": { "grid": {"rows": 1, "columns": 2, "pattern": "independent"}, "title": "半可加聚合逻辑", "annotations": [ { "text": "空间聚合 (正常)", "x": 0.2, "y": 260, "showarrow": false, "xref": "paper", "yref": "y" }, { "text": "时间聚合 (错误)", "x": 0.8, "y": 220, "showarrow": false, "xref": "paper", "yref": "y2" } ], "showlegend": false, "height": 350, "margin": {"t": 50, "b": 40, "l": 40, "r": 40} } }对不同位置(左侧)的库存余额进行聚合会得出有效总数。而对不同时间(右侧)的余额进行聚合则会产生错误数据,由红色条表示。不可加度量不可加度量无法跨任何维度求和。这些通常是比率、百分比或单价。例如,毛利率 不能通过对每笔交易的百分比求和来聚合。要在更高的粒度上分析不可加度量,你必须存储构成该比率的基础组成部分(例如,总收入 和 总成本),并在聚合后计算该比率。$$正确利润 = \frac{\sum (收入 - 成本)}{\sum 收入}$$$$错误利润 \neq \sum \left( \frac{收入 - 成本}{收入} \right)$$处理空外键在生产环境中,源数据很少是完美的。一笔交易可能缺少客户 ID 或门店代码未定义。在规范化的操作型数据库中,这可能表示为一个 NULL 值。然而,在维度模型中,事实表应严禁外键列出现 NULL 值。如果外键中存在 NULL,许多报表工具和标准 SQL INNER JOIN 操作会从结果集中删除该行,从而导致总度量(如收入)报告不足。解决此问题的方法是,在对应的维度表中分配一个默认的“未知”或“不适用”记录。该记录通常被赋予 -1 或 0 的代理键。在 ETL(抽取、转换、加载)过程中,任何缺失或无效的源键都会映射到这个代理键。这种做法保证了参照完整性,并确保每一美元收入都被计入,即使其背景信息暂时不详。事实表类型概述尽管上述结构涵盖了最普遍的用例,但不同的业务流程需要专门的事实表结构。这些模式规定了表如何根据时间和事件记录数据。交易事实表: 最细致的粒度。一行对应一个事件(例如,一次点击,收银台的一次扫描)。这类表数据密集,通常是完全可加的。周期快照事实表: 数据在标准时间段内进行聚合,例如按天或按月。这常用于性能监测或账户余额(半可加)。累积快照事实表: 用于有明确开始和结束的工作流程,例如订单处理。一行表示订单的整个生命周期,随着流程从“已下单”到“已发货”再到“已交付”的变化,多个日期外键会更新。我们将在第 4 章考察周期快照和累积快照的具体实现细节及应用场景。在初步设计阶段,明确基础交易粒度并确保度量可加性正确,能为你构建更全面的分析模式打下良好基底。