数仓：事实表设计方法，原则和三种类型选择

关注公众号，回复关键字 【资料】，获取【10万字大数据框架面试知识点】与【大数据开发的命令手册】

事实表设计方法

事实表作为数据仓库维度建模的核心，紧紧围绕着业务过程来设计。其包含与该业务过程有关的维度引用（外键）以及该业务过程的度量。
一般设计会遵循以下四个步骤：

1. 选择业务过程及确定事实表类型

通常情况下，一个业务过程对应一张事实表。

2. 声明粒度

精确定义每张事实表的每行数据表示什么,按照业务尽可能选择最细粒度，以此来应各种细节程度的需求。

3. 确定维度

确定与每张事实表相关的维度有哪些? 维度的丰富程度就决定了模型能够支持的指标丰富程度。

4. 确定事实

每个业务过程的度量值（通常是可累加的数字类型的值，例如：次数、个数、件数、金额等）。细分如下：

（1）可加事实

可加事实是指可以按照与事实表相关的所有维度进行累加，例如事务型事实表中的事实。

（2）半可加事实

半可加事实是指只能按照与事实表相关的一部分维度进行累加，例如周期型快照事实表中的事实。比如库存，库存事实可以按照仓库或者商品维度进行累加，但是不能按照时间维度进行累加，因为将每天的库存累加是不科学的。

（3）不可加事实

不可加事实是指完全不具备可加性，例如比率型事实。不可加事实通常需要转化为可加事实，例如比率可转化为分子和分母。

开发指标过程中，指标定义总结以下公式(仅供参考)

指标 = 维度 + 度量 + (条件)

其中度量：基于事实表中可以进行统计的数据属性，如： 金额， 次数等。

映射到SQL就是

select 度量 from table where 条件 group by 维度

事实表设计原则

1. 尽可能包含所有与业务过程相关的事实
2. 只选择与业务过程相关的事实
3. 分解不可加性事实为可加的组件
4. 在选择维度和事实之前必须先声明粒度
5. 在同一个事实表中不能有多种不同粒度的事实
6. 事实的单位要保持一致
7. 对事实的 null 值要处理
8. 使用退化维度提高事实表的易用性

什么是维度退化？

维度退化的维度表可以被剔除，从而简化维度数据仓库的模式。因为简单的模式比复杂的更容易理解，也有更好的查询性能。
当一个维度没有数据仓库需要的任何数据时就可以退化此维度。需要把维度退化的相关数据迁移到事实表中，然后删除退化的维度。
维度属性也可以存储到事实表中，这种存储到事实表中的维度列被称为“维度退化”。与其他存储在维表中的维度一样 ， 维度退化也可以用来进行事实表的过滤查询、实现聚合操作等。

比如说订单id，这种量级很大的维度，没必要用一张维度表来进行存储，而我们进行数据查询或者数据过滤的时候又非常需要，所以这种就冗余在事实表里面，这种就叫退化维度，citycode这种我们也会冗余在事实表里面，但是它有对应的维度表，所以它不是退化维度。

三种类型

事务型事实表

是什么

事务事实表用来记录各业务过程，跟踪时间上某点的度量事件，它保存的是各业务过程的原子操作事件，即最细粒度。

场景

事务型事实表可用于分析与各业务过程相关的各项统计指标，由于其保存了最细粒度的记录，可以提供最大限度的灵活性，可以支持无法预期的各种细节层次的统计需求。

不适合干什么

（1）存量型指标不适合

比如说商品库存，账户余额… 对于这种存在进进出出（加或减操作）不适合。
一张存储商品的进货的原子操作事件，一张存储商品出库的原子操作事件。如果要统计当日的库存又多少，这就需要对进货表和出库表进行操作，需要一正一反地区分对库存的影响。所以在写代码或者sql过程还是执行效率都会打折扣。

（2）多事务关联统计不适合

比如在订单中，建立了下单事务事实表和支付事务事实表，现在指标统计某个时间内用户下单到支付的时间间隔的平均值，这就需要这两个表的Join操作，然而，订单在大型公司属于达标，大表 Join 大表 你可想而知。

周期型快照事实表

周期快照事实表以具有规律性的、可预见的时间间隔来记录事实，一般周期可以是每天、每周、每月、每年等。主要用于分析一些存量型或者状态型指标

场景

存量型：例如对于商品库存、账户余额这些存量型指标，业务系统中通常就会计算并保存最新结果，所以定期同步一份全量数据到数据仓库，构建周期型快照事实表，就能轻松应对此类统计需求。

状态型：例如对于空气温度、行驶速度这些状态型指标，由于它们的值往往是连续的，我们无法捕获其变动的原子事务操作，所以无法使用事务型事实表统计此类需求。而只能定期对其进行采样，构建周期型快照事实表。

累积型快照事实表

用来描述过程开始和结束之间的关键步骤事件，覆盖过程的整个生命周期，通常具有多个时间字段来记录关键时间点，当过程随着生命周期不断变化时，记录也会随着过程的变化而被修改。

场景

如交易流程中的下单、支付、发货、确认收货业务过程。会具体记录下单时间，支付时间，发货时间，收获时间。如果统计某个时间内用户下单到支付的时间间隔的平均值。这样就轻松搞定。

对比

-	事务型事实表	周期型事实表	累积型快照事实表
时间	离散事务时间	每天、每周、每月、每年等	多个时间字段来记录关键时间点
粒度	每行代表实体的一个事务	每行代表某时间周期的一个实体	每行代表一个实体的生命周期
数据加载方式	insert	insert	insert/update