表设计范式
1.第一范式(1NF): 原子性,字段不可再分割
数据库表的每一列都是不可分割的原子数据项,而不能是集合,数组,记录等非原子项数据
例:
购买商品数量: 10部华为手机, 可以拆分为: 商品名+数量
2.第二范式(2NF): 唯一性,一个表只能说明一个事务,有主键,非主键字段依赖主键
要求数据库表中的每行(或称每个实例)必须可以被唯一的区分,为实现区分通常需要我们设计一个主键来实现
注: 第二范式需先满足第一范式
3…第三范式(3NF):非主键字段不能相互依赖,不能存在传递依赖
要求数据库表中不应包含已在其他表中已包含的非主键字段
注: 第三范式需先满足第二范式
例: (2NF 3NF)
订单表: 订单号, 商品名, 商品数量, 商户名, 商户地址, 商户电话, 订单用户;
可以发现"商户地址, 商户电话"是不依赖主键订单号的, 而是依赖"商品名"; 所以"商户地址, 商户电话"是冗余了, 可以去掉(范式就是为了降冗余,防止数据不一致)
反范式
三大范式是一般设计数据库理念, 有时为了提高运行效率(减少关联)会适当增加冗余字段, 这就是反范式
反范式化要适度, 并且需要在表原本已满足三范式的基础上, 再进行反范式的调整(满足1NF,2NF;适当破坏3NF)
ER实体关系模型
实体(Entity):实体具有自己的属性(对实体的描述/修饰),一类有意义的实体构成实体集, 实体在ER中一般用方框表示, 属性用椭圆表示
关系(Relationship):表示实体间的关联关系
数据仓库与维度建模
数据仓库(Data Warehouse, 简称DW 或 DWH)
1.企业中含有很多管理系统平台,不同系统的数据可能存储在不同类型的关系型数据库中(分析时存在跨系统,跨库场景; 那么就需要数仓进行整合)
2.关系型数据库存储的数据一般是最基本的,日常事务处理,面向业务操作的数据, 但会缺少一些用户操作日志数据(操作埋点数据量大, 不适合关系型数据库进行存储)
3.战略决策需从产品,区域,客群等不同方面观察数据(OLTP场景难以满足)
所以会需要对各类数据进行汇集,清洗,管理
数据仓库是面向主题的,集成的(非简单的数据堆积),相对稳定的,反应历史变化的数据集合;
数仓中的数据是有组织有结构的存储数据集合,用于对管理决策过程的支持
维度建模
维度建模主要源自数据集市,主要面向分析场景,为了提高查询性能可以增加数据冗余,反范式设计(ER建模是面向应用,遵循范式)
事实表(fact)
发生在现实世界中的操作型事件,其所产生的可度量数值,存储在事实表中(一般一张事实表包含维度列(区域/时间/产品等维度)和度量列(数量值:销量/销售额等))
维度列一般使用代号, 需要知道具体含义时, 需要关联维度表
维度表(dim)
维度表包含了维度的每个成员的特定名称,维度成员的名称称为"属性"(Attribute)
多维分析模型
星型模型(维表存在一定冗余)
当所有的维度都由连接键连接到事实表时, 结构如星星一样
雪花模型(维表基本不存在冗余, 更加范式化)
当有一个或多个维表没有直接连接到事实表上,而是通过其他维表连接到事实表上时
星座模型
事实星座模型需要多个事实表共享维度表,因而可以视为星型模型的集合,故亦被称为星座模型
如: 区域维表
星型: 区域ID, 省名, 市名, 区名
雪花: 区域ID, 省ID, 市ID, 区ID; 再关联省表(省ID,省名), 市表(市ID,市名), 区表(区ID,区名)
星型更利于分析, 雪花的冗余更少
数据仓库的分层
在大数据分析中,我们希望我们分析的数据在整个分析流程中有秩序的流转,数据的整个流程能够清晰明确的被我们掌握使用
反例:
分层设计
ODS(Operational Data Store)层 - 操作数据层
ODS层,操作数据层,也叫贴源层,本层直接存放从业务系统抽取过来的数据,这些数据从结构上和数据上与业务系统保持一致,降低了数据抽取的复杂性,本层数据大多是按照源头业务系统的分类方式而分类的。一般来讲,为了考虑后续可能需要追溯数据问题,因此对于这一层就不建议做过多的数据清洗工作,原封不动地接入原始数据即可。
DW(Data Warehouse)层 - 数据仓库层
数据仓库层是我们在做数据仓库时要核心设计的一层,本层将从 ODS 层中获得的数据按照主题建立各种数据模型,每一个主题对应一个宏观的分析领域,数据仓库层排除对决策无用的数据,提供特定主题的简明视图。DW层又细分为 DWD(Data Warehouse Detail)层、DWM(Data Warehouse Middle)层和DWS(Data Warehouse Service)层。
数据明细层:DWD(Data Warehouse Detail)
该层一般保持和ODS层一样的数据粒度,并且提供一定的数据质量保证,在ODS的基础上对数据进行加工处理,提供更干净的数据。同时,为了提高数据明细层的易用性,该层会采用一些维度退化手法,当一个维度没有数据仓库需要的任何数据时,就可以退化维度,将维度退化至事实表中,减少事实表和维表的关联。例如:订单id,这种量级很大的维度,没必要用一张维度表来进行存储,而我们一般在进行数据分析时订单id又非常重要,所以我们将订单id冗余在事实表中,这种维度就是退化维度。
数据中间层:DWM(Data Warehouse Middle)
该层会在DWD层的数据基础上,对数据做轻度的聚合操作,生成一系列的中间表,提升公共指标的复用性,减少重复加工处理数据。简单来说,就是对通用的维度进行聚合操作,算出相应的统计指标,方便复用。
数据服务层:DWS(Data Warehouse Service)
该层数据表会相对比较少,大多都是宽表(一张表会涵盖比较多的业务内容,表中的字段较多)。按照主题划分,如订单、用户等,生成字段比较多的宽表,用于提供后续的业务查询,OLAP分析,数据分发等。
在实际业务处理中,如果直接从DWD或者ODS计算出宽表的统计指标,会存在计算量太大并且维度太少的问题,因此一般的做法是,在DWM层先计算出多个小的中间表,然后再拼接成一张DWS的宽表。由于宽和窄的界限不易界定,也可以去掉DWM这一层,只留DWS层,将所有的数据在放在DWS也没有问题。
DM(Data Mart)层 - 数据集市层
数据集市层,也可以称为数据应用层,基于DW上的基础数据,整合汇总成分析某一个主题域的报表数据。主要是提供给数据产品和数据分析使用的数据,一般会存放在 ES、PostgreSql、Redis等系统中供线上系统使用,也可能会存在 Hive 或者 Druid 中供数据分析和数据挖掘使用。比如我们经常说的报表数据,一般就放在这里。
命名规范