乐趣区

关于面试:面试系列七-之-业务交互数据分析

jiezi

6.1 电商常识

SKU：一台银色、128G 内存的、反对联通网络的 iPhoneX

SPU：iPhoneX

Tm_id：品牌 Id 苹果，包含 IPHONE，耳机，mac 等

6.2 电商业务流程

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）

6.3.3 商品表

6.3.4 用户表

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 领取流水表

订单表跟订单详情表有什么区别？

  • 订单表的订单状态会变动，订单详情表不会，因为没有 订单状态。
  • 订单表 记录 user_id，订单 id 订单编号，订单的总金额 order_status，领取形式，订单状态等。
  • 订单详情表 记录 user_id，商品 sku_id , 具体的商品信息（商品名称 sku_name，价格 order_price，数量 sku_num）

6.4 MySql 中表的分类

实体表，维度表，事务型事实表，周期性事实表

其实最终能够把 事务型事实表 ， 周期性事实表 统称 实体表，实体表，维度表统称维度表

订单表（order_info）（周期型事实表）

订单详情表（order_detail）(事务型事实表)

商品表(实体表)

用户表(实体表)

商品一级分类表(维度表)

商品二级分类表(维度表)

商品三级分类表(维度表)

领取流水表(事务型实体表)

6.5 同步策略

实体表，维度表统称维度表，每日全量或者每月（更长时间）全量

事务型事实表：每日增量

周期性事实表：拉链表

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 层

从订单表 dwd_order_info 中获取 下单次数 和 下单总金额

从领取流水表 dwd_payment_info 中获取 领取次数 和 领取总金额

从事件日志评论表 dwd_comment_log 中获取评论次数

最终依照 user_id 聚合，取得明细，跟之前的 mid_id 聚合不同

6.9、需要

6.9.1 需要一：GMV 成交总额

从用户行为宽表中dws_user_action，依据统计日期分组，聚合，间接 sum 就能够了。

6.9.2、需要二：转化率

6.9.2.1 新增用户占日沉闷用户比率表

  从日沉闷数表 ads_uv_count 和 日新增设施数表 ads_new_mid_count 中取即可。

6.9.2.2 用户行为转化率表

从用户行为宽表 dws_user_action 中取，下单人数（只有下单次数 >0）, 领取人数（只有领取次数 >0）

从日沉闷数表 ads_uv_count 中取沉闷人数，而后对应的相除就能够了。

6.9.3、需要三：品牌复购率

需要：以月为单位统计，购买 2 次以上商品的用户

6.9.3.1 用户购买商品明细表（宽表）

6.9.3.2 品牌复购率表

从用户购买商品明细宽表 dws_sale_detail_daycount 中，依据品牌 id--sku_tm_id 聚合，计算每个品牌购买的总次数，购买人数 a = 购买次数 >=1, 两次及以上购买人数 b = 购买次数 >=2，三次及以上购买人数 c = 购买次数 >=3,

单次复购率 =b/a，屡次复购率 =c/a

6.10、我的项目中有多少张宽表

   宽表要 3 - 5 张，用户行为宽表，用户购买商品明细行为宽表，商品宽表，购物车宽表，物流宽表、登录注册、售后等。

1）为什么要建宽表

   需要指标，把每个用户单日的行为聚合起来组成一张多列宽表，以便之后关联用户维度信息后进行，不同角度的统计分析。

6.11、拉链表

订单表拉链表 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）把拉链长期表笼罩到旧的拉链表中

关注我的公众号【宝哥大数据】，更多干货

退出移动版