数仓重点
第一章 数据建模
1.1 建模工具
- PowerDesigner
- SQLYog
- EZDML
1.2 ODS层
Operation Data Store，原始数据层
ODS做了那些事？
（1）保持数据原貌不做任何修改，起到备份数据的作用。
（2）数据采用压缩（LZO），减少磁盘存储空间（例如：原始数据100G，可以压缩到10G左右）
（3）创建分区表，防止后续的全表扫描
1.3 DWD层
Data Warehouse Detail，明细数据层
DWD层需构建维度模型，一般采用星型模型，呈现的状态一般为星座模型。
维度建模一般按照以下四个步骤：
选择业务过程→声明粒度→确认维度→确认事实
（1）选择业务过程
在业务系统中，如果业务表过多，挑选我们感兴趣的业务线，比如下单业务，支付业务，退款业务，物流业务，一条业务线对应一张事实表。如果小公司业务表比较少，建议选择所有业务线。
（2）声明粒度
数据粒度指数据仓库的数据中保存数据的细化程度或综合程度的级别。
声明粒度意味着精确定义事实表中的一行数据表示什么，应该尽可能选择最小粒度，以此来应各种各样的需求。
典型的粒度声明如下：
订单当中的每个商品项作为下单事实表中的一行，粒度为每次
每周的订单次数作为一行，粒度为每周。
每月的订单次数作为一行，粒度为每月。
如果在DWD层粒度就是每周或者每月，那么后续就没有办法统计细粒度的指标了。所有建议采用最小粒度。
（3）确定维度
维度的主要作用是描述业务是事实，主要表示的是“谁，何处，何时”等信息。例如：时间维度、用户维度、地区维度等常见维度。
（4）确定事实
此处的“事实”一词，指的是业务中的度量值，例如订单金额、下单次数等。
在DWD层，以业务过程为建模驱动，基于每个具体业务过程的特点，构建最细粒度的明细层事实表。事实表可做适当的宽表化处理。
通过以上步骤，结合本数仓的业务事实，得出业务总线矩阵表如下表所示。业务总线矩阵的原则，主要是根据维度表和事实表之间的关系，如果两者有关联则使用√标记。
表 业务总线矩阵表
| 时间 | 用户 | 地区 | 商品 | 优惠券 | 活动 | 编码 | 度量值 | |
|---|---|---|---|---|---|---|---|---|
| 订单 | √ | √ | √ | √ | 件数/金额 | |||
| 订单详情 | √ | √ | √ | 件数/金额 | ||||
| 支付 | √ | √ | 次数/金额 | |||||
| 加购 | √ | √ | √ | 件数/金额 | ||||
| 收藏 | √ | √ | √ | 个数 | ||||
| 评价 | √ | √ | √ | 个数 | ||||
| 退款 | √ | √ | √ | 件数/金额 | ||||
| 优惠券领用 | √ | √ | √ | 个数 |
根据维度建模中的星型模型思想，将维度进行退化。
ODS层的业务数据有二十多张表，形成了比较复杂的关系模型，这种情况下想要获取一些细节维度的信息，通常需要进行多表join才能得到，为了使查询更加方便，也为了避免进行大量的表join计算，将关系模型进行适度的退化很有必要。
例如下图所示：地区表和省份表退化为地区维度表，商品表、品类表、spu表、商品三级分类、商品二级分类、商品一级分类表退化为商品维度表，活动信息表和活动规则表退化为活动维度表。
至此，数仓的维度建模已经完毕，DWS、DWT和ADS和维度建模已经没有关系了。
DWS和DWT都是建宽表，宽表都是按照主题去建。主题相当于观察问题的角度。对应着维度表。
DWD层做了哪些事？
- 数据清洗
- 空值去除
- 过滤核心字段无意义的数据，比如订单表中订单id为null，支付表中支付id为空
- 将用户行为宽表和业务表进行数据一致性处理
- 清洗的手段：HQL、MR、SparkSQL、Kettle、Python（项目中采用sql进行清除）
- 清洗掉多少数据算合理：1万条数据清洗掉1条。
- 脱敏：对手机号、身份证号等敏感数据脱敏
- 维度退化：对业务数据传过来的表进行维度退化和降维。（商品一级二级三级、省市县、年月日）
- 压缩：LZO
- 列式存储：parquet
1.4 DWS层
Data Warehouse Service，服务数据层
DWS层统计各个主题对象的当天行为，服务于DWT层的主题宽表。如图所示，DWS层的宽表字段，是站在不同维度的视角去看事实表，重点关注事实表的度量值，通过与之关联的事实表，获得不同的事实表的度量值。
DWS层做了哪些事？
DWS层有3-5张宽表（处理100-200个指标 70%以上的需求）
具体宽表名称：用户行为宽表，用户购买商品明细行为宽表，商品宽表，购物车宽表，物流宽表、登录注册、售后等。
哪个宽表最宽？大概有多少个字段？
最宽的是用户行为宽表。大概有60-100个字段
具体用户行为宽表字段名称
评论、打赏、收藏、关注--商品、关注--人、点赞、分享、好价爆料、文章发布、活跃、签到、补签卡、幸运屋、礼品、金币、电商点击、gmv等
1.5 DWT层
Data Warehouse Topic，主题数据层
以分析的主题对象为建模驱动，基于上层的应用和产品的指标需求，构建主题对象的全量宽表。
DWT层主题宽表都记录什么字段？
如图所示，每个维度关联的不同事实表度量值以及首次、末次时间、累积至今的度量值、累积某个时间段的度量值。
1.6 ADS层
分别对设备主题、会员主题、商品主题和营销主题进行指标分析，其中营销主题是用户主题和商品主题的跨主题分析案例。
面向实际的数据需求，以DWD层、DWS层和DWI层的数据为基础，组成各种统计报表。统计结果最终同步到关系型数据库，以供BI或应用系统查询使用。
ADS层分析过哪些指标？
日活、月活、周活、留存、留存率、新增（日、周、年）、转化率、流失、回流、七天内连续3天登录（点赞、收藏、评价、购买、加购、下单、活动）、连续3周（月）登录、GMV、复购率、复购率排行、点赞、评论、收藏、领优惠价人数、使用优惠价、沉默、值不值得买、退款人数、退款率 topn 热门商品
产品经理最关心的：留转G复活
（1）活跃
日活：100万 ；周活是日活的1.1-1.3倍；月活：是日活的2-3倍 300万
总注册的用户多少？1000万-3000万之间
（2）GMV
Gross Merchandise Volume，主要是指网站的成交金额，
GMV：每天 10万订单 （50 – 100元） 500万-1000万
10%-20% 100万-200万（人员：程序员、人事、行政、财务、房租、收电费）
（3）复购率
某日常商品复购；（手纸、面膜、牙膏）10%-20%
电脑、显示器、手表 1%
（4）转化率
商品详情 =》 加购物车 =》下单 =》 支付
5%-10% 60-70% 90%-95%
（5）留存率
1/2/3、周留存、月留存
搞活动： 10-20%
ADS层手写指标
如何分析用户活跃？
在启动日志中统计不同设备id出现次数。去重
如何分析用户新增？vivo
用活跃用户表 left join 用户新增表，用户新增表中mid为空的即为用户新增。
如何分析用户1天留存？
留存用户=前一天新增 join 今天活跃
用户留存率=留存用户/前一天新增
如何分析沉默用户？
沉默用户：指的是只在安装当天启动过，且启动时间是在一周前
按照设备id对日活表分组，登录次数为1，且是在一周前登录。
如何分析本周回流用户？
本周活跃left join本周新增 left join上周活跃，且本周新增id和上周活跃id都为null
如何分析流失用户？
(登录时间为7天前)
按照设备id对日活表分组，且七天内没有登录过。
如何分析最近连续3周活跃用户数？
按照设备id对周活进行分组，统计次数大于3次。
如何分析最近七天内连续三天活跃用户数？
1）查询出最近7天的活跃用户，并对用户活跃日期进行排名
2）计算用户活跃日期及排名之间的差值
3）对同用户及差值分组，统计差值个数
4）将差值相同个数大于等于3的数据取出，然后去重(去的是什么重？同一个mid_id)，即为连续3天及以上活跃的用户
7天连续收藏、点赞、购买、加购、付款、浏览、商品点击、退货
1个月连续7天
连续两周：
