面试系列七 之 业务交互数据分析
## 6.1 电商常识
`SKU`:一台银色、128G内存的、支持联通网络的iPhoneX
`SPU`:iPhoneX
`Tm_id`:品牌Id苹果,包括IPHONE,耳机,mac等
## 6.2 电商业务流程
![在这里插入图片描述](https://www.icode9.com/i/ll/?i=2021062616304691.png?,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d1eGludGRyaA==,size_16,color_FFFFFF,t_70)
## 6.3 业务表关键字段
### 6.3.1 订单表(order_info)
标签 |含义
---|--
id |订单编号
total_amount |订单金额
order_status |订单状态
user_id |用户id
payment_way| 支付方式
out_trade_no |支付流水号
create_time |创建时间
operate_time |操作时间
### 6.3.2 订单详情表(order_detail)
![在这里插入图片描述](https://www.icode9.com/i/ll/?i=20210626163233947.png?,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d1eGludGRyaA==,size_16,color_FFFFFF,t_70)
### 6.3.3 商品表
![在这里插入图片描述](https://www.icode9.com/i/ll/?i=20210626163308927.png?,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d1eGludGRyaA==,size_16,color_FFFFFF,t_70)
### 6.3.4 用户表
![在这里插入图片描述](https://www.icode9.com/i/ll/?i=20210626163342477.png?,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d1eGludGRyaA==,size_16,color_FFFFFF,t_70)
### 6.3.5 商品一级分类表
标签| 含义
--|--
id| id
name |名称
### 6.3.6 商品二级分类表
标签 |含义
--|--
id |id
name |名称
category1_id |一级品类id
### 6.3.7 商品三级分类表
标签| 含义
--|--
id| id
name |名称
Category2_id | 二级品类id
### 6.3.8 支付流水表
![在这里插入图片描述](https://www.icode9.com/i/ll/?i=20210626163759536.png?,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d1eGludGRyaA==,size_16,color_FFFFFF,t_70)
**订单表跟订单详情表有什么区别?**
- 订单表的订单状态会变化,订单详情表不会,因为没有**订单状态**。
- **订单表**记录user_id,订单id订单编号,订单的总金额order_status,支付方式,订单状态等。
- **订单详情表**记录user_id,商品sku_id ,具体的商品信息(商品名称sku_name,价格order_price,数量sku_num)
## 6.4 MySql中表的分类
**实体表,维度表,事务型事实表,周期性事实表**
其实最终可以把**事务型事实表**,**周期性事实表**统称**实体表**,实体表,维度表统称维度表
订单表(order_info)(周期型事实表)
订单详情表(order_detail)(事务型事实表)
商品表(实体表)
用户表(实体表)
商品一级分类表(维度表)
商品二级分类表(维度表)
商品三级分类表(维度表)
支付流水表(事务型实体表)
## 6.5 同步策略
![在这里插入图片描述](https://www.icode9.com/i/ll/?i=20210626164305405.png?,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d1eGludGRyaA==,size_16,color_FFFFFF,t_70)
实体表,维度表统称维度表,每日全量或者每月(更长时间)全量
事务型事实表:每日增量
周期性事实表:拉链表
## 6.6 关系型数据库范式理论
`1NF`:**属性不可再分割**(例如不能存在5台电脑的属性,坏处:表都没法用)
`2NF`:**不能存在部分函数依赖**(例如主键(学号+课名)-->成绩,姓名,但学号 -->姓名,所以姓名部分依赖于主键(学号+课名),所以要去除,坏处:数据冗余)
`3NF`:**不能存在传递函数依赖**(学号 --> 宿舍种类 --> 价钱,坏处:数据冗余和增删异常)
**Mysql关系模型**:关系模型主要应用与**OLTP**系统中,为了保证数据的一致性以及避免冗余,所以大部分业务系统的表都是遵循第三范式的。
**Hive 维度模型**:维度模型主要应用于**OLAP**系统中,因为关系模型虽然冗余少,
但是在大规模数据,跨表分析统计查询过程中,会造成多表关联,这会大大降低执行效率。
所以HIVE把相关各种表整理成两种:**事实表和维度表**两种。所有维度表围绕着事实表进行解释。
## 6.7 数据模型
雪花模型、星型模型和星座模型
(在维度建模的基础上又分为三种模型:星型模型、雪花模型、星座模型。)
星型模型(一级维度表),雪花(多级维度),星座模型(星型模型+多个事实表)
## 6.8 业务数据数仓搭建
sqoop
导数据的原理是mapreduce,
import 把数据从关系型数据库 导到 数据仓库,自定义InputFormat,
export 把数据从数据仓库 导到 关系型数据库,自定义OutputFormat,
用sqoop从mysql中将八张表的数据导入数仓的ods原始数据层
全量无条件,增量按照创建时间,增量+变化按照创建时间或操作时间。
origin_data
sku_info商品表(每日导全量)
user_info用户表(每日导全量)
base_category1商品一级分类表(每日导全量)
base_category2商品二级分类表(每日导全量)
base_category3商品三级分类表(每日导全量)
order_detail订单详情表(每日导增量)
payment_info支付流水表(每日导增量)
order_info订单表(每日导增量+变化)
### 6.8.1 ods层
(八张表,表名,字段跟mysql完全相同)
从origin\_data把数据导入到ods层,表名在原表名前加`ods_`
### 6.8.2 dwd层
对ODS层数据进行判空过滤。对商品分类表进行**维度退化**(降维)。其他数据跟ods层一模一样
订单表 `dwd_order_info`
订单详情表 `dwd_order_detail`
用户表 `dwd_user_info`
支付流水表 `dwd_payment_info`
商品表 `dwd_sku_info`
其他表字段不变,唯独商品表,通过关联3张分类表,增加了
```
category2_id` string COMMENT '2id',
`category1_id` string COMMENT '3id',
`category3_name` string COMMENT '3',
`category2_name` string COMMENT '2',
`category1_name` string COMMENT '1',
```
小结:
1)**维度退化要付出什么代价**?或者说会造成什么样的需求处理不了?
- 如果被退化的维度,还有其他业务表使用,退化后处理起来就麻烦些。
- 还有如果要**删除数据,对应的维度可能也会被永久删除。**
2)想想在实际业务中还有**那些维度表可以退化**
- 城市的三级分类(省、市、县)等
### 6.8.3 dws层
![在这里插入图片描述](https://www.icode9.com/i/ll/?i=20210626165905760.png?,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d1eGludGRyaA==,size_16,color_FFFFFF,t_70)
从订单表 `dwd_order_info` 中获取 下单次数 和 下单总金额
从支付流水表 `dwd_payment_info` 中获取 支付次数 和 支付总金额
从事件日志评论表 `dwd_comment_log` 中获取评论次数
最终按照`user_id`聚合,获得明细,跟之前的`mid_id`聚合不同
## 6.9、需求
### 6.9.1 需求一:GMV成交总额
从用户行为宽表中`dws_user_action`,根据统计日期分组,聚合,直接sum就可以了。
![在这里插入图片描述](https://www.icode9.com/i/ll/?i=20210626171209574.png?,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d1eGludGRyaA==,size_16,color_FFFFFF,t_70)
### 6.9.2、 需求二:转化率
#### 6.9.2.1 新增用户占日活跃用户比率表
从日活跃数表 `ads_uv_count` 和 日新增设备数表 `ads_new_mid_count` 中取即可。
![在这里插入图片描述](https://www.icode9.com/i/ll/?i=20210626171652151.png?,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d1eGludGRyaA==,size_16,color_FFFFFF,t_70)
#### 6.9.2.2 用户行为转化率表
从用户行为宽表`dws_user_action`中取,**下单人数(只要下单次数>0),支付人数(只要支付次数>0)**
从日活跃数表 `ads_uv_count` 中取活跃人数,然后对应的相除就可以了。
![在这里插入图片描述](https://www.icode9.com/i/ll/?i=2021062617191173.png?,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d1eGludGRyaA==,size_16,color_FFFFFF,t_70)
### 6.9.3、 需求三:品牌复购率
需求:以月为单位统计,购买2次以上商品的用户
#### 6.9.3.1 用户购买商品明细表(宽表)
![在这里插入图片描述](https://www.icode9.com/i/ll/?i=20210626172216239.png?,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d1eGludGRyaA==,size_16,color_FFFFFF,t_70)
#### 6.9.3.2 品牌复购率表
从用户购买商品明细宽表`dws_sale_detail_daycount`中,根据品牌`id--sku_tm_id`聚合,计算每个品牌购买的总次数,购买人数a=购买次数>=1,两次及以上购买人数b=购买次数>=2,三次及以上购买人数c=购买次数>=3,
单次复购率=b/a,多次复购率=c/a
![在这里插入图片描述](https://www.icode9.com/i/ll/?i=20210626172304727.png?,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d1eGludGRyaA==,size_16,color_FFFFFF,t_70)
## 6.10、 项目中有多少张宽表
宽表要3-5张,用户行为宽表,用户购买商品明细行为宽表,商品宽表,购物车宽表,物流宽表、登录注册、售后等。
**1)为什么要建宽表**
需求目标,把每个用户单日的行为聚合起来组成一张多列宽表,以便之后关联用户维度信息后进行,不同角度的统计分析。
## 6.11、 拉链表
![在这里插入图片描述](https://www.icode9.com/i/ll/?i=20210626172911727.png?,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d1eGludGRyaA==,size_16,color_FFFFFF,t_70)
订单表拉链表 `dwd_order_info_his`
```
`id` string COMMENT '订单编号',
`total_amount` decimal(10,2) COMMENT '订单金额',
`order_status` string COMMENT '订单状态',
`user_id` string COMMENT '用户id' ,
`payment_way` string COMMENT '支付方式',
`out_trade_no` string COMMENT '支付流水号',
`create_time` string COMMENT '创建时间',
`operate_time` string COMMENT '操作时间' ,
`start_date` string COMMENT '有效开始日期',
`end_date` string COMMENT '有效结束日期'
```
1)创建订单表拉链表,字段跟拉链表一样,只增加了有效开始日期和有效结束日期
初始日期,从订单变化表`ods_order_info`导入数据,且让有效开始时间=当前日期,有效结束日期=`9999-99-99`
(从mysql导入数仓的时候就只导了新增的和变化的数据`ods_order_info`,`dwd_order_info`跟`ods_order_info`基本一样,只多了一个id的判空处理)
2)建一张拉链临时表`dwd_order_info_his_tmp`,字段跟拉链表完全一致
3)新的拉链表中应该有这几部分数据,
- (1)增加订单变化表`dwd_order_info`的全部数据
- (2)更新旧的拉链表左关联订单变化表`dwd_order_info`,关联字段:订单id, where 过滤出`end_date`只等于`9999-99-99`的数据,如果旧的拉链表中的`end_date`不等于`9999-99-99`,说明已经是终态了,不需要再更新
- 如果`dwd_order_info.id is null` , 没关联上,说明数据状态没变,让`end_date`还等于旧的`end_date`
- 如果`dwd_order_info.id is not null `, 关联上了,说明数据状态变了,让`end_date`等于当前日期`-1`
- 把查询结果插入到拉链临时表中
4)把拉链临时表覆盖到旧的拉链表中
# 关注我的公众号【宝哥大数据】, 更多干货
![在这里插入图片描述](https://www.icode9.com/i/ll/?i=20210626173520752.png)