数仓中的模型

special_etl_coder

已于 2023-12-07 15:26:10 修改

阅读量187

点赞数

CC 4.0 BY-SA版权

于 2023-12-07 15:05:51 首次发布

本文链接：https://blog.youkuaiyun.com/newitengineer/article/details/134855628

数据仓库专栏收录该内容

2 篇文章

订阅专栏

1. 范式建模

1.1 范式概念

为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关系型数据库，必须满足一定的范式
满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以此类推。一般说来，数据库只需满足第三范式(3NF）就行了

1.2 第一范式

概念:
如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库满足第一范式

示例:
地址信息表中, contry这一列,还可以继续拆分,不符合第一范式
在这里插入图片描述

1.3 第二范式

概念:
第二范式需要确保数据库表中每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中
示例1:
1.学员信息表中其实在描述两个事物 , 一个是学员的信息,一个是课程信息
2.如果放在一张表中,会导致数据的冗余,如果删除学员信息, 成绩的信息也被删除了
在这里插入图片描述

示例2：
在这里插入图片描述
这里产生一个问题：这个表中是以订单编号和商品编号作为联合主键，这样在该表中商品名称、单位、商品价格等信息不与该表的主键相关，而仅仅是与商品的编号相关，所以在这里违反了第二范式的设计原则。而如果把这个订单信息表进行拆分，把商品信息分离到另一个表中，把订单项目表也分离到另一个表中，就非常完美了，如下图。
在这里插入图片描述
这里这样设计，在很大程度上减小了数据库的冗余，如果要获取订单的商品信息，使用商品编号到商品信息表中查询即可。

1.4 第三范式

概念:
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

示例:
通过number 与 price字段就可以计算出总金额,不要在表中再做记录(空间最省)
在这里插入图片描述

1.5 数据库反三范式

（1）反范式化：
通过增加冗余或重复的数据，来提高数据库的读性能
浪费存储空间,节省查询时间 (以空间换时间)

（2）什么是冗余字段 ?
设计数据库时，某一个字段属于一张表，但它同时出现在另一个或多个表，且完全等同于它在其本来所属表的意义表示，那么这个字段就是一个冗余字段

（3）反三范式示例
两张表，用户表、订单表，用户表中有字段name，而订单表中也存在字段name。

在这里插入图片描述
（4）使用场景
当需要查询“订单表”所有数据, 并且只需要“用户表”的name字段时, 没有冗余字段, 就需要去join 连接用户表,假设表中数据量非常的大, 那么会这次连接查询就会非常大的消耗系统的性能。这时候冗余的字段就可以派上用场了, 有冗余字段我们查一张表就可以了.

1.6 总结

创建一个关系型数据库设计，我们有两种选择
1．尽量遵循范式理论的规约，尽可能少的冗余字段，让数据库设计看起来精致、优雅、让人心醉。
2．合理的加入冗余字段这个润滑剂，减少join，让数据库执行性能更高更快。
3．下文讲的维度建模就是一种反三范式的设计，在事实表中会有部分字段冗余的情况。

2. 维度建模

2.1 相关定义

事实表（Fact Table）
指存储有事实记录的表，如系统日志、销售记录等；事实表的记录在不断地动态增长，所以它的体积通常远大于其他表。
事实表作为数据仓库建模的核心，需要根据业务过程来设计，包含了引用的维度和业务过程有关的度量。

作为度量业务过程的事实，一般为整型或浮点型的十进制数值，有可加性，半可加性和不可加性三种类型
可加
最灵活最有用的事实是完全可加，可加性度量可以按照与事实表关联的任意维度汇总。比如订单总金额
半可加
半可加度量可以对某些维度汇总，但不能对所有维度汇总。差额是常见的半可加事实，除了时间维度外，他们可以跨所有维度进行操作。(比如每天的余额加起来毫无意义)

不可加
一些度量是完全不可加的，例如：比率。对非可加事实，一种好的方法是，分解为可加的组件来实现聚集

维度表
维度表（Dimension Table）或维表，有时也称查找表（Lookup Table），是与事实表相对应的一种表；它保存了维度的属性值，可以跟事实表做关联；相当于将事实表上经常重复出现的属性抽取、规范出来用一张表进行管理。
维度表是维度属性的集合。是分析问题的一个窗口。是人们观察数据的特定角度，是考虑问题时的一类属性，属性的集合构成一个维度
把逻辑业务比作一个立方体，产品维、时间维、地点维分别作为不同的坐标轴，而坐标轴的交点就是一个具体的事实。也就是说事实表是多个维度表的一个交点。而维度表是分析事实的一个窗口。
常见的维度表有：日期表（存储与日期对应的周、月、季度等的属性）、地点表（包含国家、省／州、城市等属性）等。维度是维度建模的基础和灵魂。

优点
缩小了事实表的大小。
便于维度的管理和维护，增加、删除和修改维度的属性，不必对事实表的大量记录进行改动。
维度表可以为多个事实表重用，以减少重复工作。

下钻
下钻是商业用户分析数据的最基本的方法。下钻仅需要在查询上增加一个行头指针，新行的头指针是一个维度属性，附加了sql语言的group by表达式，属性可以来自任何与查询使用的事实表关联的维度，下钻不需要预先存在层次的定义，或者是下钻路径。

退化维度
有时，维度除了主键外没有其他内容，例如，当某一发票包含多个数据项时，数据项事实行继承了发票的所有描述性维度外键，发票除了外键无其他项，但发票数量仍然是在此数据项级别的合法维度键。这种退化维度被放入事实表中，清楚的表明没有关联的维度表，退化维度常见于交易和累计快照事实表中。

2.2 示例

在这里插入图片描述

2.3 星型模型

概念
星形模型中有一张事实表，以及零个或多个维度表，事实表与维度表通过主键外键相关联，维度表之间没有关联，当所有维表都直接连接到事实表上时，整个图解就像星星一样，故将该模型称为星型模型。
星形模型是最简单，也是最常用的模型。由于星形模型只有一张大表，因此它相比于其他模型更适合于大数据处理。其他模型可以通过一定的转换，变为星形模型。
星型架构是一种非正规化的结构，多维数据集的每一个维度都直接与事实表相连接，不存在渐变维度，所以数据有一定的冗余，如在地域维度表中，存在国家 A 省 B 的城市 C 以及国家 A 省 B 的城市 D 两条记录，那么国家 A 和省 B 的信息分别存储了两次，即存在冗余。
示例

2.4 雪花模型

概念
当有一个或多个维表没有直接连接到事实表上，而是通过其他维表连接到事实表上时，其图解就像多个雪花连接在一起，故称雪花模型。
雪花模型是对星型模型的扩展。它对星型模型的维表进一步层次化，原有的各维表可能被扩展为小的维度表，形成一些局部的 " 层次 " 区域，这些被分解的表都连接到主维度表而不是事实表。如图，将地域维表又分解为国家，省份，城市等维表。它的优点是 : 通过最大限度地减少数据存储量以及联合较小的维表来改善查询性能。雪花型结构去除了数据冗余。
示例