数仓分层（ODS、DWD、DWS、DWT、ADS）和数仓建模

大家好，又见面了，我是你们的朋友全栈君。

一、数仓分层

数仓概念

1. 什么是数仓：数据仓库是为企业所有决策制定过程，提供所有系统数据支持的战略集合。通过对数据仓库中数据的分析，可以帮助企业改进业务流程、控制成本、提高产品质量等。数据仓库并不是数据的最终目的地，而是为数据最终的目的地做好准备。这些准备包括对数据的清洗、转义、分类、重组、合并、拆分、统计等等
2. 数仓的输入系统：埋点产生的用户行为数据、JavaEE 后台产生的业务数据、爬虫数据。
3. 输出系统：报表系统、用户画像系统、风控系统、推荐系统、机器学习等

ODS（原始数据层）做了哪些事

1）保持数据原貌，不做任何修改
2）压缩采用 LZO，压缩比是 100g 数据压缩完 10g 左右。
3）创建分区表

DWD（明细数据层）做了哪些事

1. 数据清洗
   （1）空值去除
   （2）过滤核心字段无意义的数据，比如订单表中订单 id 为 null，支付表中支付 id 为空
   （3）将用户行为宽表和业务表进行数据一致性处理

2. 清洗的手段
   Sql、mr、rdd、kettle、Python等等

3. 清洗掉多少数据算合理
   1 万条数据清洗掉 1 条。

4. 脱敏
   对手机号、身份证号等敏感数据脱敏

5. 维度退化
   对业务数据传过来的表进行维度退化和降维。（商品一级二级三级、省市县、年月日）

6. LZO压缩

7. 列式存储 parquet

DWS（服务数据层）做了哪些事

1. DWS 层有 3-5 张宽表（处理 100-200 个指标 70%以上的需
   求）
   具体宽表名称：用户行为宽表，用户购买商品明细行为宽表，商品宽表，购物车宽表，物流宽表、登录注册、售后等。

2. 哪个宽表最宽？大概有多少个字段？
   最宽的是用户行为宽表。大概有 60-100 个字段

3. 具体用户行为宽表字段名称
   评论、打赏、收藏、关注–商品、关注–人、点赞、分享、好价爆料、文章发布、活跃、签到、补签卡、幸运屋、礼品、金币、电商点击、gmv

DWT（主题数据层）做了哪些事

1. 分析过的指标
   日活、月活、周活、留存、留存率、新增（日、周、年）、转化率、流失、回流、七天内连续 3 天登录（点赞、收藏、评价、购买、加购、下单、活动）、连续 3 周（月）登录、GMV、复购率、复购率排行、点赞、评论、收藏、领优惠价人数、使用优惠价、沉默、值不值得买、退款人数、退款率 topn 热门商品

2. 留转 G 复活指标
   （1）活跃
   日活：100 万 ；月活：是日活的 2-3 倍 300 万
   总注册的用户多少？1000 万-3000 万之间
   （2）GMV
   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（应用数据层）做了哪些事

1. 如何分析用户活跃？
   在启动日志中统计不同设备 id 出现次数。

2. 如何分析用户新增?
   用活跃用户表 left join 用户新增表，用户新增表中 mid 为空的即为用户新增。

3. 如何分析用户 1 天留存？
   留存用户=前一天新增 join 今天活跃
   用户留存率=留存用户/前一天新增

4. 如何分析沉默用户？
   (登录时间为 7 天前,且只出现过一次)
   按照设备 id 对日活表分组，登录次数为 1，且是在一周前登录。

5. 如何分析本周回流用户？
   本周活跃 left join 本周新增 left join 上周活跃，且本周新增 id 和上周活跃 id 都为 null。

6. 如何分析流失用户？
   (登录时间为 7 天前)
   按照设备 id 对日活表分组，且七天内没有登录过。

7. 如何分析最近连续 3 周活跃用户数？
   按照设备 id 对周活进行分组，统计次数大于 3 次。

8. 如何分析最近七天内连续三天活跃用户数？
   1）查询出最近 7 天的活跃用户，并对用户活跃日期进行排名
   2）计算用户活跃日期及排名之间的差值
   3）对同用户及差值分组，统计差值个数
   4）将差值相同个数大于等于 3 的数据取出，然后去重，即为连续 3 天及以上活跃的用户
   7 天连续收藏、点赞、购买、加购、付款、浏览、商品点击、退货
   1 个月连续 7 天
   连续两周

二、数仓建模

常用的建模工具

PowerDesigner/SQLYog/EZDML

ODS 层

（1）保持数据原貌不做任何修改，起到备份数据的作用。
（2）数据采用压缩，减少磁盘存储空间（例如：原始数据 100G，可以压缩到 10G 左右）
（3）创建分区表，防止后续的全表扫描

DWD 层

DWD 层需构建维度模型，一般采用星型模型，呈现的状态一般为星座模型。维度建模一般按照以下四个步骤：

选择业务过程→声明粒度→确认维度→确认事实

（1）选择业务过程

在业务系统中，如果业务表过多，挑选我们感兴趣的业务线，比如下单业务，支付业务，退款业务，物流业务，一条业务线对应一张事实表。如果小公司业务表比较少，建议选择所有业务线。

（2）声明粒度

数据粒度指数据仓库的数据中保存数据的细化程度或综合程度的级别。
声明粒度意味着精确定义事实表中的一行数据表示什么，应该尽可能选择最小粒度，以此来应各种各样的需求。
典型的粒度声明如下：

1. 订单当中的每个商品项作为下单事实表中的一行，粒度为每次
2. 每周的订单次数作为一行，粒度为每周。
3. 每月的订单次数作为一行，粒度为每月。
   注意：如果在 DWD 层粒度就是每周或者每月，那么后续就没有办法统计细粒度的指标了。所有建议采用最小粒度。

（3）确定维度

维度的主要作用是描述业务是事实，主要表示的是“谁，何处，何时”等信息。例如：
时间维度、用户维度、地区维度等常见维度。

（4）确定事实

此处的“事实”一词，指的是业务中的度量值，例如订单金额、下单次数等。
在 DWD 层，以业务过程为建模驱动，基于每个具体业务过程的特点，构建最细粒度的明细层事实表。事实表可做适当的宽表化处理。

根据维度建模中的星型模型思想，将维度进行退化。例如：地区表和省份表退化为地区维度表，商品表、品类表、spu 表、商品三级分类、商品二级分类、商品一级分类表退化为商品维度表，活动信息表和活动规则表退化为活动维度表。至此，数仓的维度建模已经完毕，DWS、DWT 和 ADS 和维度建模已经没有关系了。DWS 和 DWT 都是建宽表，宽表都是按照主题去建。主题相当于观察问题的角度，对应着维度表。

DWS 层

DWS 层统计各个主题对象的当天行为，服务于 DWT 层的主题宽表。DWS层的宽表字段，是站在不同维度的视角去看事实表，重点关注事实表的度量值，通过与之关联的事实表，获得不同的事实表的度量值。

DWT 层

以分析的主题对象为建模驱动，基于上层的应用和产品的指标需求，构建主题对象的全量宽表。
DWT 层主题宽表都记录什么字段？
每个维度关联的不同事实表度量值以及首次、末次时间、累积至今的度量值、累积某个时间段的度量值。

ADS 层

分别对设备主题、会员主题、商品主题和营销主题进行指标分析，其中营销主题是用户主题和商品主题的跨主题分析案例