分享

大数据技术之高频面试题(九):业务数据数仓搭建

问题导读:
1、业务数据数仓如何搭建?
2、如何设计ods层、dwd层、dws层?
3、如何设计GMV成交总额、转化率、品牌复购率?
4、如何设计拉链表?


上一篇:大数据技术之高频面试题(八):业务交互数据分析

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层
2020-10-27_221914.jpg
从订单表 dwd_order_info 中获取 下单次数 和 下单总金额
从支付流水表 dwd_payment_info 中获取 支付次数 和 支付总金额
从事件日志评论表 dwd_comment_log 中获取评论次数
最终按照user_id聚合,获得明细,跟之前的mid_id聚合不同

6.9 需求一:GMV成交总额

2020-10-27_222007.jpg

从用户行为宽表中dws_user_action,根据统计日期分组,聚合,直接sum就可以了。

6.10 需求二:转化率
6.10.1 新增用户占日活跃用户比率表
2020-10-27_222041.jpg
从日活跃数表 ads_uv_count 和 日新增设备数表 ads_new_mid_count 中取即可。

6.10.2 用户行为转化率表
2020-10-27_222118.jpg

从用户行为宽表dws_user_action中取,下单人数(只要下单次数>0),支付人数(只要支付次数>0)
从日活跃数表 ads_uv_count 中取活跃人数,然后对应的相除就可以了。

6.11 需求三:品牌复购率
需求:以月为单位统计,购买2次以上商品的用户

6.11.1 用户购买商品明细表(宽表)

2020-10-27_222213.jpg
6.11.2 品牌复购率表
2020-10-27_222244.jpg
从用户购买商品明细宽表dws_sale_detail_daycount中,根据品牌id--sku_tm_id聚合,计算每个品牌购买的总次数,购买人数a=购买次数>=1,两次及以上购买人数b=购买次数>=2,三次及以上购买人数c=购买次数>=3,
单次复购率=b/a,多次复购率=c/a

6.12 项目中有多少张宽表
宽表要3-5张,用户行为宽表,用户购买商品明细行为宽表,商品宽表,购物车宽表,物流宽表、登录注册、售后等。
1)为什么要建宽表
需求目标,把每个用户单日的行为聚合起来组成一张多列宽表,以便之后关联用户维度信息后进行,不同角度的统计分析。


6.13 拉链表
2020-10-27_222346.jpg
订单表拉链表 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)把拉链临时表覆盖到旧的拉链表中



已有(1)人评论

跳转到指定楼层
若无梦何远方 发表于 2020-10-28 08:11:49
如何设计ods层、dwd层、dws层? 感觉可以详细说下 这些层所代表的含义
每天进步一点点 (以后遇到相同的sql操作 可以来这里看一看咯)
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

关闭

推荐上一条 /5 下一条