数仓分层

数据分层原理

1.1 ODS层

存放原始数据，直接加载原始日志、数据，数据保持原貌不做处理。

（1）保持数据原貌，不做任何修改

（2）压缩采用LZO，压缩比是100g数据压缩完10g左右。

（3）创建分区表

 

1.2 DWD层

结构和粒度与ods层保持一致，对ods层数据进行清洗(去除空值，脏数据，超过极限范围的数据)，也有公司叫dwi。

1.2.1 数据清洗

（1）空值去除

（2）过滤核心字段无意义的数据，比如订单表中订单id为null，支付表中支付id为空

（3）将用户行为宽表和业务表进行数据一致性处理

select case when a is null then b else a end as JZR,

    …

from A

1.2.2 清洗的手段

       Sql、mr、rdd、kettle、Python（项目中采用sql进行清除）

1.2.3 清洗掉多少数据算合理

       1万条数据清洗掉1条。

1.2.4 脱敏

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

1.2.5 维度退化

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

1.2.6 压缩LZO

1.2.7 列式存储parquet

 

1.3 DWS层

以dwd为基础，进行轻度汇总。一般聚集到以用户当日，设备当日，商家当日，商品当日等等的粒度。
在这层通常会有以某一个维度为线索，组成跨主题的宽表，比如 一个用户的当日的签到数、收藏数、评论数、抽奖数、订阅数、点赞数、浏览商品数、添加购物车数、下单数、支付数、退款数、点击广告数组成的多列表。

1.3.1 DWS层有3-5张宽表（处理100-200个指标   70%以上的需求）

具体宽表名称：用户行为宽表，用户购买商品明细行为宽表，商品宽表，购物车宽表，物流宽表、登录注册、售后等。

1.3.2 哪个宽表最宽？大概有多少个字段？

最宽的是用户行为宽表。大概有60-100个字段

1.3.3 具体用户行为宽表字段名称

       评论、打赏、收藏、关注–商品、关注–人、点赞、分享、好价爆料、文章发布、活跃、签到、补签卡、幸运屋、礼品、金币、电商点击、gmv

CREATE TABLE `app_usr_interact`(

  `stat_dt` date COMMENT ‘互动日期’,

  `user_id` string COMMENT ‘用户id’,

  `nickname` string COMMENT ‘用户昵称’,

  `register_date` string COMMENT ‘注册日期’,

  `register_from` string COMMENT ‘注册来源’,

  `remark` string COMMENT ‘细分渠道’,

  `province` string COMMENT ‘注册省份’,

  `pl_cnt` bigint COMMENT ‘评论次数’,

  `ds_cnt` bigint COMMENT ‘打赏次数’,

  `sc_add` bigint COMMENT ‘添加收藏’,

  `sc_cancel` bigint COMMENT ‘取消收藏’,

  `gzg_add` bigint COMMENT ‘关注商品’,

  `gzg_cancel` bigint COMMENT ‘取消关注商品’,

  `gzp_add` bigint COMMENT ‘关注人’,

  `gzp_cancel` bigint COMMENT ‘取消关注人’,

  `buzhi_cnt` bigint COMMENT ‘点不值次数’,

  `zhi_cnt` bigint COMMENT ‘点值次数’,

  `zan_cnt` bigint COMMENT ‘点赞次数’,

  `share_cnts` bigint COMMENT ‘分享次数’,

  `bl_cnt` bigint COMMENT ‘爆料数’,

  `fb_cnt` bigint COMMENT ‘好价发布数’,

  `online_cnt` bigint COMMENT ‘活跃次数’,

  `checkin_cnt` bigint COMMENT ‘签到次数’,

  `fix_checkin` bigint COMMENT ‘补签次数’,

  `house_point` bigint COMMENT ‘幸运屋金币抽奖次数’,

  `house_gold` bigint COMMENT ‘幸运屋积分抽奖次数’,

  `pack_cnt` bigint COMMENT ‘礼品兑换次数’,

  `gold_add` bigint COMMENT ‘获取金币’,

  `gold_cancel` bigint COMMENT ‘支出金币’,

  `surplus_gold` bigint COMMENT ‘剩余金币’,

  `event` bigint COMMENT ‘电商点击次数’,

  `gmv_amount` bigint COMMENT ‘gmv’,

  `gmv_sales` bigint COMMENT ‘订单数’)

PARTITIONED BY (  `dt` string)

 

1.4 ADS层分析过哪些指标

1.4.1 分析过的指标（一分钟至少说出30个指标）

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

    产品经理最关心的：留转G复活

1.4.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%

1.4.3 哪个商品卖的好？

面膜、手纸，每天销售5000个

1.5 ADS层手写指标

1.5.1 如何分析用户活跃？

在启动日志中统计不同设备id出现次数。

1.5.2 如何分析用户新增？vivo

用活跃用户表 left join 用户新增表，用户新增表中mid为空的即为用户新增。

1.5.3 如何分析用户1天留存？

留存用户=前一天新增 join 今天活跃

用户留存率=留存用户/前一天新增

1.5.4 如何分析沉默用户？

(登录时间为7天前,且只出现过一次)

按照设备id对日活表分组，登录次数为1，且是在一周前登录。

1.5.5 如何分析本周回流用户？

本周活跃left join本周新增 left join上周活跃，且本周新增id和上周活跃id都为null

1.5.6 如何分析流失用户？

(登录时间为7天前)

按照设备id对日活表分组，且七天内没有登录过。

1.5.7 如何分析最近连续3周活跃用户数？

按照设备id对周活进行分组，统计次数大于3次。

1.5.8 如何分析最近七天内连续三天活跃用户数？

1）查询出最近7天的活跃用户，并对用户活跃日期进行排名

2）计算用户活跃日期及排名之间的差值

3）对同用户及差值分组，统计差值个数

4）将差值相同个数大于等于3的数据取出，然后去重(去的是什么重???)，即为连续3天及以上活跃的用户

7天连续收藏、点赞、购买、加购、付款、浏览、商品点击、退货

1个月连续7天

连续两周：

1.6 分析过最难的指标

1.6.1 最近连续3周活跃用户

1.6.2 最近7天连续3天活跃用户数

1.6 数据仓库建模（绝对重点）

1.6.1 建模工具是什么？

PowerDesigner/sqlyog

1.6.2 ODS层

（1）保持数据原貌不做任何修改，起到备份数据的作用。

（2）数据采用压缩，减少磁盘存储空间（例如：原始数据100G，可以压缩到10G左右）

（3）创建分区表，防止后续的全表扫描

1.6.3 DWD层

DWD层需构建维度模型，一般采用星型模型，呈现的状态一般为星座模型。

维度建模一般按照以下四个步骤：

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

（1）选择业务过程

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

（2）声明粒度

数据粒度指数据仓库的数据中保存数据的细化程度或综合程度的级别。

声明粒度意味着精确定义事实表中的一行数据表示什么，应该尽可能选择最小粒度，以此来应各种各样的需求。

典型的粒度声明如下：

订单当中的每个商品项作为下单事实表中的一行，粒度为每次

每周的订单次数作为一行，粒度为每周。

每月的订单次数作为一行，粒度为每月。

如果在DWD层粒度就是每周或者每月，那么后续就没有办法统计细粒度的指标了。所有建议采用最小粒度。

（3）确定维度

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

（4）确定事实

此处的“事实”一词，指的是业务中的度量值，例如订单金额、下单次数等。

在DWD层，以业务过程为建模驱动，基于每个具体业务过程的特点，构建最细粒度的明细层事实表。事实表可做适当的宽表化处理。

通过以上步骤，结合本数仓的业务事实，得出业务总线矩阵表如下表所示。业务总线矩阵的原则，主要是根据维度表和事实表之间的关系，如果两者有关联则使用√标记。

表  业务总线矩阵表

	时间	用户	地区	商品	优惠券	活动	编码	度量值
订单	√	√	√			√		件数/金额
订单详情	√		√	√				件数/金额
支付	√		√					次数/金额
加购	√	√		√				件数/金额
收藏	√	√		√				个数
评价	√	√		√				个数
退款	√	√		√				件数/金额
优惠券领用	√	√			√			个数

根据维度建模中的星型模型思想，将维度进行退化。例如下图所示：地区表和省份表退化为地区维度表，商品表、品类表、spu表、商品三级分类、商品二级分类、商品一级分类表退化为商品维度表，活动信息表和活动规则表退化为活动维度表。

至此，数仓的维度建模已经完毕，DWS、DWT和ADS和维度建模已经没有关系了。

DWS和DWT都是建宽表，宽表都是按照主题去建。主题相当于观察问题的角度。对应着维度表。

1.6.4 DWS层

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

1.6.5 DWT层

以分析的主题对象为建模驱动，基于上层的应用和产品的指标需求，构建主题对象的全量宽表。

DWT层主题宽表都记录什么字段？

如图所示，每个维度关联的不同事实表度量值以及首次、末次时间、累积至今的度量值、累积某个时间段的度量值。

1.6.6 ADS层

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