数仓建模理论
范数理论
函数依赖
完全函数依赖
设 \(X,Y\) 是关系 \(R\) 的两个属性集合,\(X'\) 是 \(X\) 的真子集,存在 \(X\rightarrow Y\),但是对每一个 \(X'\) 都有 \(X'!\rightarrow Y\),则称 \(Y\) 完全函数依赖于 \(X\)。
例如:学号和课程可以推断出分数,但是单独靠学号是不能推断出分数,所以分数完全函数依赖于学号和课程。
部分函数依赖
假如 \(Y\) 函数依赖于 \(X\),但是 \(Y\) 并不完全函数依赖于 \(X\),则称 \(Y\) 部分函数依赖于 \(X\)。
例如:学号和课程可以推断出姓名,但是单独靠学号也可以推断出姓名,所以姓名部分函数依赖于学号和课程。
传递函数依赖
设 \(X,Y,Z\) 是关系 \(R\) 中互不相同的属性集合,存在 \(X\rightarrow Y(Y!\rightarrow X),Y\rightarrow Z\),则称 \(Z\) 传递函数依赖于 \(X\)。
例如:学号可以推断出院系,院系可以推断出系主任,而院系推断不出学号,则称系主任传递函数依赖于学号。
第一范式
核心原则:属性不可分割
ID | 商品 | 商家 |
---|---|---|
01 | 5台电脑 | XXX旗舰店 |
上述表格设计不符合第一范式,因为“商品”这一列的数据不是原子数据项,是可以分割的。
ID | 商品 | 数量 | 商家 |
---|---|---|---|
01 | 电脑 | 5 | XXX旗舰店 |
第一范式是所有关系型数据库最基本的要求。
第二范式
核心原则:不能存在部分函数依赖
学号 | 姓名 | 课程名 | 分数 |
---|---|---|---|
202301 | 小明 | 数据结构 | 90 |
202301 | 小明 | 高数 | 88 |
上述表的主键为(学号,课程名),显然分数是完全函数依赖于(学号,课程名)的,但是姓名是部分函数依赖于(学号,课程名)。因此要去掉部分函数依赖,得到以下两张表。
学号 | 课程名 | 分数 |
---|---|---|
202301 | 数据结构 | 90 |
202301 | 高数 | 88 |
学号 | 姓名 |
---|---|
202301 | 小明 |
第三范式
核心原则:不能存在传递函数依赖
学号 | 姓名 | 系名 | 系主任 |
---|---|---|---|
202301 | 小明 | 计软系 | 张三 |
202302 | 小红 | 经济系 | 李四 |
上述表存在函数依赖,学号可以推出系名,系名可以推出系主任,但是系主任推不出学号。因此要去掉传递函数依赖,得到以下两张表。
学号 | 姓名 | 系名 |
---|---|---|
202301 | 小明 | 计软系 |
202302 | 小红 | 经济系 |
系名 | 系主任 |
---|---|
计软系 | 张三 |
经济系 | 李四 |
维度模型
维度模型将复杂的业务通过事实和维度两个概念进行呈现。事实通常对应业务过程,而维度通常对应业务过程发生时所处的环境。维度建模以数据分析作为出发点,为数据分析服务。
事实表
包含与该业务过程有关的维度引用(维度表外键)以及该业务过程的度量(通常是可累加的数字类型字段)。
事实表通常列较少,但行较多,且行的增速快。
事实表有三种类型:事务型事实表、周期快照事实表和累积快照事实表。
事务型事实表
记录各业务过程,保存各业务过程的原子操作事件(最细粒度的操作事件)。
- 选择业务过程:业务过程可以概括为一个个不可拆分的行为事件,如下单、付款和退单等。
- 声明粒度:精确定义每张事务型事实表的每行数据表示什么,应该尽可能选择最细粒度。
- 确定维度:确定与每张事务型事实表相关的维度有哪些。
- 确定事实:事实是指每个业务过程的度量值(通常是可累加的数字类型的值,例如次数、个数和金额等)。
对于某些特定类型的需求,事务型事实表的逻辑可能会比较复杂,或者效率会比较低下。
- 存量型指标:有一需求要统计截至当日的各用户虚拟货币余额。由于获取货币和使用货币均会影响到余额,故需要对两张事务型事实表进行聚合,且区分两者对余额的影响。
- 多事务关联统计:有一需求要统计最近30天,用户下单到支付的时间间隔的平均值。需要找到下单事务事实表和支付事务事实表,过滤出最近 30 天的记录,然后按照订单 id 对两张事实表进行关联。
周期快照事实表
以具有规律性的时间间隔来记录事实,主要用于分析一些存量型(例如商品库存,账户余额)或者状态型(空气温度,行驶速度)指标。
状态型指标往往是连续的,无法捕获其变动的原子事务操作,所以只能定期对其进行采样,构建周期型快照事实表。
- 确定粒度:粒度可由采样周期(常选择每日)和维度(统计每个仓库中每种商品的库存)描述。则该表粒度为每日-仓库-商品。
- 确认事实:可根据统计指标决定,例如指标为统计每个仓库中每种商品的库存,则事实为商品库存。
累积快照事实表
基于一个业务流程中的多个关键业务过程联合处理而构建的事实表,如交易流程中的下单、支付、发货、确认收货业务过程。主要用于分析业务过程(里程碑)之间的时间间隔等需求。
订单 id | 用户 id | 下单日期 | 支付日期 | 发货日期 | 收货日期 | 订单金额 | 支付金额 |
---|---|---|---|---|---|---|---|
1001 | 0001 | 2023-2-12 | 2023-2-13 | 2023-2-13 | 2023-2-13 | 1000 | 1000 |
- 选择业务过程:选择一个业务流程中需要关联分析的多个关键业务过程,多个业务过程对应一张累积型快照事实表。
- 声明粒度
- 确认维度:每各业务过程均需要一个日期维度。
- 确认事实
维度表
维度表围绕业务过程所处的环境进行设计。主要包含一个主键和各种维度字段(维度属性)。
设计步骤
- 确定维度:理论上每个相关维度均需对应一张维度表,但是可能存在多个事实表与同一个维度都相关的情况,这时只创建一张维度表。当维度属性很少时,则不创建维度表,把属性添加到事实表中,这个操作叫维度退化。
- 确定主维表和相关维表:相关维表的粒度通常与主维表相同。
- 确定维度属性:尽可能生成丰富的维度属性,尽量不使用编码,尽量沉淀出通用的维度属性。
维度设计要点
- 规范化:使用一系列范式设计数据库的过程,减少数据冗余,增强数据的一致性。规范化后得到的维度模型称为雪花模型。
- 反规范化:将多张表的数据冗余到一张表,减少 \(join\) 操作,提高查询性能。反规范化后得到的模型称为星型模型。
维度属性通常不是静态的,而是会随时间变化的,保存维度数据的历史状态有如下方法:
- 全量快照表:优点是简单而有效,缺点是浪费存储空间。
- 拉链表:记录每条信息的生命周期,一旦生命周期结束,就重新开始一条新的记录,并把当前日期放入生效开始日期。该方法适用于每天变化的比例不高的维度。