数据仓库

数据仓库是一个面向主题的、集成的、相对稳定的、反映历史变化的数据集合，用于支持管理决策。数据仓库从多个源系统中提取、转换和加载数据（ETL流程），通过数据清洗、整合、转换等生成目标的过程。

按照数据的流向，可以将数据仓库分为3层（常见），也可以分为更多层（根据实际情况分层）：

贴源层（ODS）：数据来源于现有业务库，与源表的数据结构保持一致，一般不做改动，为其他逻辑层提供数据来源。
数据仓库层（DW）：数据来源于ODS层，对ODS的数据进行规范化（编码转换、清洗、统一格式、脱敏等），再进行各表数据的关联整合，输出主题宽表。
集市层（DM）：数据来源可以是ODS层，也可以是DW层，主要是面向业务需求进行开发。数据应用于前端报表展示或输出到项目库中。
总体来说，数仓像是一个逻辑性的概念，是为了帮助开发更好去管理数据，为业务提供数据支撑而延伸的概念过程。

14.1 数据建模
数据建模就是基于对业务的理解，将各种数据进行整合关联。在数据仓库DW和DM逻辑层需要分析数据，通常我们的中间层宽表就基于维度建模-星型模型来实现的。

数据建模方式有两类：范式建模、维度建模。

14.1.1 范式建模
范式建模的主要作用是减少数据冗余提高更新数据的效率，同时保证数据完整性，但是这样存在一个问题，划分的表会很多，表连接的查询越多就会影响性能。

范式建模主要有3种常用：第一范式、第二范式、第三范式。

1.第一范式

表中的每一列都是不可拆分的原子项，只能存在一个值。（属性不可再分）

2.第二范式

第二范式要同时满足下面两个条件：

（1）满足第一范式。

（2）没有部分依赖。（表中的非主键列都必须依赖于主键列）

3.第三范式

第三范式要同时满足下面两个条件：

（1）满足第二范式。

（2）没有传递依赖。（表中非主键列关系重复，能互相推导出来）

比如说：emp表再加一个部门名称的字段，那么：

部门编号依赖于员工编号，部门名称依赖于部门编号，部门名称间接依赖于员工编号，则产生了传递依赖。

在表的设计上采用ER模型（实体关系图）：

1.一对一关系
外键列设置在任意一张表中，都是可以的。

2.一对多关系
外键列要设置在多的一方。

3.多对多关系
假设是A表和B表，这种情况下，需要设计第三张表（桥表），桥表中设置俩个外键，分别引用A表的主键和B表的主键。

14.1.2 维度建模
维度建模是通过维度和指标来进行设计，它是面向分析的，目的是提高查询性能，快速完成需求分析且对于复杂查询及时响应。相应的缺点就是会造成数据冗余，可能会违反范式要求。

维度建模常用的有3种：星型模型、雪花模型、星座模型。

（1）星型模型

星形模型中有一张事实表和多个维度表，事实表与维度表通过主键外键相关联，维度表之间没有关联。当所有维表都直接连接到事实表上时，整个图就像星星一样，故将该模型称为星型模型。



（2）雪花模型

当有一个或多个维度表没有直接连接到事实表上，而是通过其他维表连接到事实表上时，其图解就像多个雪花连接在一起，故称雪花模型。



（3）星座模型

星座模型是由星型模型延伸而来，星型模型是基于一张事实表而星座模式是基于多张事实表，并且共享维度表信息，这种模型往往应用于数据关系比星型模型和雪花模型更复杂的场合。星座模型需要多个事实表共享维度表，因而可以视为星形模型的集合。



14.1.3 建模工具
常用的有Navicat、PowerDesigner

14.2 常见表类型
14.2.1 拉链表
一些表的数据不是静态的，而是会随着时间而缓慢地变化，这种随着时间发生变化的维度称之为缓慢变化维。例如用户修改了自己的住址、商品的描述信息更改等。

有时候的某些需求需要查看或统计某一个时间点或者时间段的历史快照信息。这个时候就需要拉链表。

拉链表定义：记录数据在某一时间内的状态以及数据在某一时点上的变化的数据存储方式。

算法：

新增数据 ==> 开链

删除数据 ==> 关链

修改数据 ==> 先关链，在开新的拉链

start_time

表示该条记录的生命周期开始时间——周期快照时的状态

end_time

该条记录的生命周期结束时间

end_time= ‘9999-12-31’ 表示该条记录目前处于有效状态

以emp表为原表举例：（阐述拉链表的过程，分为原表的更新和新增）

原表为更新时：

第一步：先以emp表为原表将结构及数据批量导入到新创建的拉链表empb中，同时在拉链表中新建两个字段，起始创建时间和结束时间。

create table empb as select emp.*,date'2023-03-21' starttime,date'9999-12-31' endtime from emp;

第二步：对原表某个值进行更新，这里选的是姓名为king的工资为5500。

update emp set sal=5500 where lower(ename)='king';

第三步：对拉链表进行更新，只更新结束时间，（结束时间与该条数据的下条新增的起始创建时间一致。因为原数据只有先结束标记为失效状态，再新增下条数据，标记为有效状态），然后再将原表emp的更新数据插入到拉链表empb中。

update empb set endtime=date'2023-03-22' where lower(ename)='king';

insert into empb select emp.*,date'2023-03-22',date'9999-12-31' from emp where lower(ename)='king';

原表为插入数据时：

第一步：与更新数据一致。

第二步：对原表插入数据。

第三步：不用对拉链表的日期更新，直接根据原表的数据插入即可（只需要第三步中的第二步）。

拉链表能反应出某个时间的所有信息的有效情况，相当于快照表。比如说：

已知拉链表中：用户A002有两条数据，分别是：

用户 起始时间 结束时间 状态

A002 2016-01-01 2017-01-03 失效

A002 2017-01-03 9999-12-31 有效

要求查询出在2016-06-01的用户情况：

select * from empb where start_time <= date‘2016-06-01’ and end_time >= date‘2016-06-01’;

此SQL语句会查询出这天所有用户的有效信息。

14.2.2 全量表
全量表就是记录所有的数据，一般使用它时都会清空目标表。用于数据量不大的表。

14.2.3 增量表
增量表只记录更新周期内的新增数据，就是基于原表，记录每次变化的数据。

特殊增量表：da表，每天的分区就是当天的数据，其数据特点就是数据产生后就不会发生变化，如日志表

14.2.4 流水表
对于表的每一个修改都会记录，可以用于反映实际记录的变更。

与拉链表的区别：流水表是只新增，不对原来的结束时间做出更新，且流水表只有创建时间，没有结束时间。流水表就是字面意思，直接罗列出来即可，无论原表是新增还是更新，流水表都是新增。
————————————————

版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

原文链接：https://blog.csdn.net/weixin_63021300/article/details/132267190