大数据项目--电商业务数据仓库

一、电商业务与数据结构简介

1.1 电商业务流程图

1.2 数据表结构

1.2.1 电商常识 (SKU,SPU)

SKU=Stock Keeping Unit（库存量单位）。即库存进出计量的基本单元，可以是以件，盒，托盘等为单位。SKU这是对于大型连锁超市DC（配送中心）物流管理的一个必要的方法。现在已经被引申为产品统一编号的简称，每种产品均对应有唯一的SKU号。

SPU(Standard Product Unit)：标准化产品单元。是商品信息聚合的最小单位，是一组可复用、易检索的标准化信息的集合，该集合描述了一个产品的特性。

首先通过检索搜索出来的商品列表中，每个商品都是一个SKU。每个SKU都有自己独立的库存数。也就是说每一个商品详情展示都是一个SKU
示例：pandas 是基于NumPy 的一种工具，该工具是为了解决数据分析任务而创建的。

1.2.2 订单表(order_info)

字段	含义
id	订单编号
total_amount	订单金额
ordere_status	订单状态
user_id	用户id
payment_way	支付方式
out_trade_no	支付流水号
create_time	创建时间
operate_time	操作时间

订单详情表 (order_detail)

字段	含义
id	订单编号
order_id	订单号
user_id	用户id
sku_id	商品id
sku_name	商品名称
order_price	下单价格
sku_num	商品数量
create_time	创建时间

1.2.4 商品表（sku_info）

字段	含义
id	skuid
spu_id	spuid
price	价格
sku_name	商品名称
sku_desc	商品描述
weight	重量
tm_id	品牌id
category3_id	品类id
create_time	创建时间

1.2.5 用户表（user_inf)

字段	含义
id	用户id
name	姓名
birthda	生日
gender	性别
email	邮箱
user_level	用户等级
create_time	创建时间

1.2.6 商品一级分类表（base_category1）

字段	含义
id	id
name	名称

1.2.7 商品二级分类表（base_category2）

字段	含义
id	id
name	名称
category1_id	一级品类id

1.2.8 商品三级分类表(base_category3)

字段	含义
id	id
name	名称
category2_id	二级品类id

1.2.9 支付流水表（payment—_info）

字段	含义
id	编号
out_trade_no	对外业务编号
order_id	订单编号
user_id	用户编号
alipay_trade_no	支付宝交易流水编号
total_amount	支付金额
subject	交易内容
payment_type	支付类型
payment_time	支付时间

二、数仓理论

2.1 什么是数据仓库

官方解释：数据仓库(Data Warehouse)是一个面向主题的、集成的、稳定的且随时间变化的数据集合，用于支持管理人员的决策

几个特性

• 面向主题：
  主题就是类型的意思。
  传统数据库主要是为应用程序进行数据处理，未必会按照同一主题存储数据；
  数据仓库侧重于数据分析工作，是按照主题存储的。
  这一点，类似于传统农贸市场与超市的区别，市场里面，针对一个商贩，他卖的萝卜、白菜这些蔬菜以及水果会在一个摊位上；而超市里，蔬菜和水果是分开的，并且在蔬菜里面也会进行分类，不同类型的蔬菜放到不同的地方。也就是说，农贸市场里的菜(数据)是按照商贩(应用程序)去归类(存储)的，而超市里面则是按照蔬菜、水果的类型(同主题)归类的。
• 集成
  传统数据库通常与某些特定的应用相关，数据库之间相互独立。而数据仓库中的数据是在对原有分散的数据库数据抽取、清理的基础上经过系统加工、汇总和整理得到的，必须消除源数据中的不一致性，以保证数据仓库内的信息是关于整个企业的一致的全局信息。
• 稳定
  稳定说的是相对稳定
  传统数据库中的数据通常实时更新，数据根据需要及时发生变化。数据仓库的数据主要供企业决策分析使用，所涉及的数据操作主要是数据查询，一旦某个数据进入数据仓库以后，一般情况下将被长期保留，也就是数据仓库中一般有大量的查询操作，但修改和删除操作很少，通常只需要定期的加载、刷新。
• 变化
  这里的变化说的是反映历史变化
  传统数据库主要关心当前某一个时间段内的数据，而数据仓库中的数据通常包含历史信息，它里面记录了企业从过去某一时间点(如开始应用数据仓库的时间)到目前的各个阶段的信息，通过这些信息，可以对企业的发展历程和未来趋势做出分析和预测。

2.2 表的分类

2.2.1 实体表(事实表)

• 定义
  指保存了大量业务数据的表，或者说保存了一些真实的行为数据的表
  例如：销售商品所产生的订单数据
  

2.2.2 维度表

• 定义
  一般是指对应一些业务状态，代码的解释表。也可以称之为码表。例如：时间维度，地理区域维度，年龄维度，订单状态，支付方式，审批状态，商品分类等等。
  

2.2.3 事务型事实表

• 定义
  事务型事实表，一般指随着业务发生不断产生的数据。特点是一旦发生不会再变化。
  一般比如，交易流水，操作日志，出库入库记录等等。
  支付流水表：
  

2.2.4 周期型事实表

• 定义

周期型事实表，一般指随着业务发生不断产生的数据。
与事务型不同的是，数据会随着业务周期性的推进而变化。
比如订单，其中订单状态会周期性变化。再比如，请假、贷款申请，随着批复状态在周期性变化
订单表：

2.3 同步策略

数据同步策略的类型包括：全量表、增量表、新增及变化表、拉链表
全量表：存储全部的数据。
增量表：存储新增加的数据。
新增及变化表：存储新怎房价的数据和裱花的数据
拉链表：对新增及变化表做定期合并。

2.3.1 实体表同步策略

实体表：如用户、商品、商家、员工等
实体表数据量比较小：通常可以做每日全量。

2.3.2 维度表同步策略

维度表：如订单状态、审批状态、商品分类
维度表数据量比较小：通常做每日全量
注：
1）针对可能会有变化的状态数据可以存储每日全量
2）没变化的客观世界的维度（性别、地区、民族）可以存一份固定值。

2.3.3 事务型事实表同步策略

事务型事实表：交易流水、操作日志、出入库记录等
因为数据不会变化、且数据量大、可以做每日增量表、每天创建一个分区存储

2.3.4 周期性事务表同步策略

周期性事实表：订单、请假、贷款申请等。
这类表从数据量的角度，存每日全量的话、数据量太大、冗余也大。每日增量无法反应数据变化。
每日新增及变化量可以用，包括了当日的新增和修改。一般来说这个表，足够计算大部分当日数据的。但是这种依然无法解决能够得到某一个历史时间点（时间切片）的切片数据。
所以要用利用每日新增和变化表，制作一张拉链表，以方便的取到某个时间切片的快照数据。所以我们需要得到每日新增及变化量。

拉链表：

2.4 范式理论

关系型数据库设计时，遵照一定的规范要求，目的在于降低数据的冗余性，保证数据一致性，目前业界范式有：
第一范式(1NF)
第二范式(2NF)
第三范式(3NF)
巴斯-科德范式(BCNF)
第四范式(4NF)
第五范式(5NF)
不过后面那几种不太常见，数据库设计一般满足第三范式就足够了。

2.41 三范式区分

第一范式：数据库的每一列都是不可分割的原子数据项(属性不可分)
例：

学生id	姓名	性别	地址
001	渣渣辉	男	北京市朝阳区京望街10号

这里面存储的是学生信息
但是这里面的地址字段显然是不符合第一范式的，因为这里面的地址信息是可以拆分为省份+城市+街道信息的
所以针对这个字段进行拆分，让这个表满足第一范式

学生id	姓名	性别	省份	城市	街道
001	渣渣辉	男	北京市	朝阳区	京望街10号

第二范式(2NF)表示在1NF的基础上，数据库表中每一列都和主键相关，不能只和主键的某一部分相关(针对联合主键而言)
也就是说一个表中只能保存一种类型的数据，不可以把多种类型数据保存在同一张表中
如:

id	班级	班主任	课程	分数
001	计科1	渣渣辉	2001	98

这个表里面除了存储的有学生的班级信息，还有学生的考试成绩信息
根据我们刚才的分析，它是满足第一范式的，但是违背了第二范式，数据库表中的每一列并不是都和主键相关
所以我们为了让这个表满足第二范式，可以这样拆分：拆成两个表，一个表里面保存学生的班级信息，一个表里面保存学生的考试成绩信息

id	班级	班主任
001	计科1	渣渣辉

id	课程	分数
001	2001	98

注意：满足第三范式（3NF）必须先满足第二范式（2NF）。
第三范式(3NF): 要求一个数据库表中不包含已在其它表中包含的非主键字段，就是说，表中的某些字段信息，如果能够被推导出来，就不应该单独的设计一个字段来存放(能尽量外键join就用外键join)。很多时候，我们为了满足第三范式往往会把一张表拆分成多张表
来看下面这个案例，针对刚才满足了第二范式的表，其实还可以进行拆分

id	班级	班主任
001	计科1	渣渣辉

id	课程	分数
001	2001	98

可以再拆分为这样：

id	班级
001	计科1

班级	班主任
计科1	渣渣辉

id	课程	分数
001	2001	98

这样就满足数据库的第三范式了。

2.5 数据仓库建模方式

数据仓库建模方式
数据仓库建模可以使用多种方式
1：ER实体模型，这种模型其实就是满足数据库第三范式的模型，这就是刚才我们为什么要分析数据库中的三范式了。

ER模型是数据库设计的理论基础，当前几乎所有的OLTP系统设计都采用ER模型建模的方式
Bill Inom提出的数仓理论，推荐采用ER关系模型进行建模，不过这种方式在实际工作中不推荐使用。
2：维度建模模型
Ralph Kimball提出的数仓理论中，提出了维度建模，将数据仓库中的表划分为事实表和维度表。基于事实表和维度表进行维度建模。
维度建模通常又分为星型模型和雪花模型
维度建模是我们在构建数据仓库中常用的方式。
3：Data Vault模型
Data Vault是在ER模型的基础上衍生而来，模型设计的初衷是有效的组织基础数据层，使之易扩展、灵活的应对业务的变化，同时强调历史性、可追溯性和原子性，不要求对数据进行过度的一致性处理；并非针对分析场景所设计。
3：Anchor模型
Anchor是对Data Vault模型做了更近一步的规范化处理，初衷是为了设计高度可扩展的模型，核心思想是所有的扩张只添加而不修改，于是设计出的模型基本变成了k-v结构的模型。

2.5.1 维度建模模型

星型模型和雪花模型
星型模型和雪花模型主要区别就是对维度表的拆分，对于雪花模型，维度表的设计更加规范，一般符合3NF；
而星型模型，一般采用降维的操作，利用冗余来避免模型过于复杂，提高易用性和分析效率
先来看一下星型模型

这里面的中间的订单表是事实表，外面的四个是维度表。
这几个维度表，其实严格意义上来说，只能满足第二范式，是不满足第三范式的。但是这样的好处是查询效率比较高，在查询的时候不需要关联很多张表。缺点就是数据有冗余。使用这个五角星代表星型模型还是比较形象的，因为针对事实表周边的这些维度表，外层就没有其它的表
了。
接下来看一下雪花模型

这个里面订单表是一个事实表，其余的都是维度表。针对商品维度表外层又拆分出来了一个商品类目的维度表，这样拆分之后其实就满足第三范式了，但是这样就变的复杂了，后期在获取商品维度数据的时候，还需要关联这个商品类目维度表。
这里使用这个雪花代表雪花模型也是比较形象的，事实表周边会有一层维度表，这些维度表外层还可能会有多层维度表
两种模型的优缺点总结
星型模型 VS 雪花模型
冗余：雪花模型符合业务逻辑设计，采用3NF设计，有效降低数据冗余；星型模型的维度表设计不符合3NF，反规范化，维度表之间不会直接相关，牺牲部分存储空间

性能：雪花模型由于存在维度间的关联，采用3NF降低冗余，通常在使用过程中，需要连接更多的维度表，导致性能偏低；星型模型违反三范式，采用降维的操作将维度整合，以存储空间为代价有效降低维度表连接数，性能比雪花模型高

在实际工作中我们多采用星型模型，因为数据仓库主要是侧重于做数据分析，对数据的查询性能要求比较
高，所以星型模型是比较好的选择，在实际工工作中我们会尽可能的多构建一些宽表，提前把多种有关联
的维度整合到一张表中，后期使用时就不需要多表关联了，比较方便，并且性能也高。

2.6 数据仓库分层

2.6.1 为什么要分层

数据仓库在构建过程中通常都需要进行分层处理，业务不同，分层的技术处理手段也不同，对数据进行分层的一个主要原因就是希望在管理数据的时候。能对数据有一个更加清晰的掌控
主要有下边几个原因：
1）清晰的数据结构：每一个分层的数据都有它的作用域，这样我们在使用表的时候能更方便地定位和理解。
2）数据血缘追踪：简单来讲可以这样理解，我们最终给业务方呈现的是一个能直接使用的业务表，但是它的来源有很多，如果有一张来源表出问题了，我们希望能够快速准确地定位到问题，并清楚它的危害范围，分层之后就很好定位问题，以及可以清晰的知道它的危害范围
3） 减少重复开发：规范数据分层，开发一些通用的中间层数据，能够减少重复计算。
4）把复杂问题简单化：将一个复杂的任务分解成多个步骤来完成，每一层只处理单一的步骤，比较简单和容易理解。而且便于维护数据的准确性， 当数据出现问题之后，可以不用修复所有的数据，只需要从有问题的步骤开始修复。

2.6.2 数据仓库分层设计

数据仓库一般会分为4层
1）ODS层：原始数据层，数据源中的数据，采集过来之后，原样保存
2）DWD层：明细数据层：这一层对ODS层的数据进行清洗，解决一些数据质量问题和数据的完整度问题。
3）DWS层：这一层是对DWD层的数据进行轻度聚合汇总，生成一系列的中间件，提升公共指标的复用性，减少重复加工，并且构建出来一些宽表，用于提供后续的业务查询
4） APP层(ads)：根据业务需要，由前面三层的数据统计而出的结果，可以直接提供查询展现，一般会把APP层的数据导出到MySQL中供线上系统使用，提供报表展示、数据监控及其它功能。也有公司把这层称为DM层。虽然名字不一样，但是性质是一样的。

注意：针对DWD层在对数据进行清洗的时候，一般需要遵循以下原则

1. 数据唯一性校验(通过数据采集工具采集的数据会存在重复的可能性)
2. 数据完整性校验(采集的数据中可能会出现缺失字段的情况，针对缺失字段的数据建议直接丢掉，如果可以确定是哪一列缺失也可以进行补全，可以用同一列上的前一个数据来填补或者同一列上的后一个数据来填补)
3. 数据合法性校验-1(针对数字列中出现了null、或者-之类的异常值，全部替换为一个特殊值，例如0或者-1，这个需要根据具体的业务场景而定)
4. 数据合法性校验-2(针对部分字段需要校验数据的合法性，例如：用户的年龄，不能是负数)
   代码如下（示例）：

三 数仓搭建

3.1 业务数据生成

创建数据库

create database gmall;

3.1.1 建表语句

 
 CREATE TABLE `order_info` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `consignee` VARCHAR(100) DEFAULT NULL COMMENT '收货人',
  `consignee_tel` VARCHAR(20) DEFAULT NULL COMMENT '收件人电话',
  `total_amount` DECIMAL(10,2) DEFAULT NULL COMMENT '总金额',
  `order_status` VARCHAR(20) DEFAULT NULL COMMENT '订单状态',
  `user_id` BIGINT(20) DEFAULT NULL COMMENT '用户id',
  `payment_way` VARCHAR(20) DEFAULT NULL COMMENT '付款方式',
  `delivery_address` VARCHAR(1000) DEFAULT NULL COMMENT '送货地址',
  `order_comment` VARCHAR(200) DEFAULT NULL COMMENT '订单备注',
  `out_trade_no` VARCHAR(50) DEFAULT NULL COMMENT '订单交易编号（第三方支付用)',
  `trade_body` VARCHAR(200) DEFAULT NULL COMMENT '订单描述(第三方支付用)',
  `create_time` DATETIME DEFAULT NULL COMMENT '创建时间',
  `operate_time` DATETIME DEFAULT NULL COMMENT '操作时间',
  `expire_time` DATETIME DEFAULT NULL COMMENT '失效时间',
  `tracking_no` VARCHAR(100) DEFAULT NULL COMMENT '物流单编号',
  `parent_order_id` BIGINT(20) DEFAULT NULL COMMENT '父订单编号',
  `img_url` VARCHAR(200) DEFAULT NULL COMMENT '图片路径',
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='订单表 订单表';
 
 
 
 DROP TABLE IF EXISTS sku_info;
 
CREATE TABLE `sku_info` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '库存id(itemID)',
  `spu_id` BIGINT(20) DEFAULT NULL COMMENT '商品id',
  `price` DECIMAL(10,0) DEFAULT NULL COMMENT '价格',
  `sku_name` VARCHAR(200) DEFAULT NULL COMMENT 'sku名称',
  `sku_desc` VARCHAR(2000) DEFAULT NULL COMMENT '商品规格描述',
  `weight` DECIMAL(10,2) DEFAULT NULL COMMENT '重量',
  `tm_id` BIGINT(20) DEFAULT NULL COMMENT '品牌(冗余)', 
  `category3_id` BIGINT(20) DEFAULT NULL COMMENT '三级分类id（冗余)',
  `sku_default_img` VARCHAR(200) DEFAULT NULL COMMENT '默认显示图片(冗余)',
  `create_time` DATETIME DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8 COMMENT='库存单元表';
 
 
CREATE TABLE `user_info` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `login_name` VARCHAR(200) DEFAULT NULL COMMENT '用户名称',
  `nick_name` VARCHAR(200) DEFAULT NULL COMMENT '用户昵称',
  `passwd` VARCHAR(200) DEFAULT NULL COMMENT '用户密码',
  `name` VARCHAR(200) DEFAULT NULL COMMENT '用户姓名',
  `phone_num` VARCHAR(200) DEFAULT NULL COMMENT '手机号',
  `email` VARCHAR(200) DEFAULT NULL COMMENT '邮箱',
  `head_img` VARCHAR(200) DEFAULT NULL COMMENT '头像',
  `user_level` VARCHAR(200) DEFAULT NULL COMMENT '用户级别',
  `birthday` DATE DEFAULT NULL COMMENT '用户生日',
  `gender` VARCHAR(1) DEFAULT NULL COMMENT '性别 M男,F女',
  `create_time` DATETIME DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=9501 DEFAULT CHARSET=utf8 COMMENT='用户表';
 
 
 
 
 
CREATE TABLE `order_detail` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `order_id` BIGINT(20) DEFAULT NULL COMMENT '订单编号',
  `sku_id` BIGINT(20) DEFAULT NULL COMMENT 'sku_id',
  `sku_name` VARCHAR(200) DEFAULT NULL COMMENT 'sku名称（冗余)',
  `img_url` VARCHAR(200) DEFAULT NULL COMMENT '图片名称（冗余)',
  `order_price` DECIMAL(10,2) DEFAULT NULL COMMENT '购买价格(下单时sku价格）',
  `sku_num` VARCHAR(200) DEFAULT NULL COMMENT '购买个数',
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=55750 DEFAULT CHARSET=utf8 COMMENT='订单明细表';
 
 
 
 
DROP TABLE  IF EXISTS `payment_info`;
CREATE   TABLE  `payment_info`
(
       `id`            BIGINT NOT NULL AUTO_INCREMENT COMMENT '编号',
       `out_trade_no`   VARCHAR(20) COMMENT '对外业务编号',
       `order_id`         VARCHAR(20)  COMMENT '订单编号',
       `user_id`          VARCHAR(20) COMMENT '用户编号',
       `alipay_trade_no`  VARCHAR(20)  COMMENT '支付宝交易流水编号',
       `total_amount`    DECIMAL(16,2) COMMENT '支付金额',
       `subject`          VARCHAR(20)  COMMENT '交易内容',
       `payment_type`          VARCHAR(20)  COMMENT '支付方式',
       `payment_time`    VARCHAR(20) COMMENT '支付时间',
        PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=55750 DEFAULT CHARSET=utf8 COMMENT='支付流水表';
 
 
 
 
CREATE TABLE  `base_category1`
(
       `id`              BIGINT AUTO_INCREMENT PRIMARY KEY NOT NULL COMMENT '编号',
       `name`            VARCHAR(10) NOT NULL COMMENT '分类名称'
);
ALTER TABLE `base_category1` COMMENT= '一级分类表';
 
 
 
 
CREATE TABLE  `base_category2`
(
       `id`              BIGINT AUTO_INCREMENT PRIMARY KEY NOT NULL COMMENT '编号',
       `name`            VARCHAR(200) NOT NULL COMMENT '二级分类名称',
       `category1_id`     BIGINT COMMENT '一级分类编号'
);
ALTER TABLE `base_category2` COMMENT= '二级分类表';
 
 
 
 CREATE TABLE  `base_category3`
(
       `id`              BIGINT AUTO_INCREMENT PRIMARY KEY NOT NULL COMMENT '编号',
       `name`            VARCHAR(200) NOT NULL COMMENT '三级分类名称',
       `category2_id`     BIGINT COMMENT