电商离线数仓项目-用户每日行为宽表(重点!!)

忘是亡心i 2022-09-16 15:28 141阅读 0赞

# 为什么要建立宽表? #

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

# 分析宽表 #

以用户为主体,今天下了多少次单,下单的总金额是多少,支付了多少次,支付的金额又是多少,以及评论的次数。  
基于以上的分析,需要找到上面这6个字段所在的表:  
**订单表**:下单的次数和下单的金额  
**支付流水表**:支付的次数和支付的金额  
**评论表**:评论次数

注意:最主要的是写sql的流程,如何确保写sql的过程犯错降到最低,这个流程优化到如何能快速到每个字段,所以开启sublime ,将每个表的字段都放到sublime里面

其中,订单表如下:

create external table dwd_order_info ( 
        `id` string COMMENT '',
        `total_amount` decimal(10,2) COMMENT '', 
        `order_status` string COMMENT ' 1 2 3 4 5', 
        `user_id` string COMMENT 'id' ,
        `payment_way` string COMMENT '',  
        `out_trade_no` string COMMENT '',  
        `create_time` string COMMENT '',  
        `operate_time` string COMMENT '' 
    ) COMMENT ''
    PARTITIONED BY ( `dt` string)
    stored as  parquet
    location '/warehouse/gmall/dwd/dwd_order_info/'
    tblproperties ("parquet.compression"="snappy")
    ;

支付流水表:

drop table if exists `dwd_payment_info`;
    create external  table  `dwd_payment_info`(
        `id`   bigint COMMENT '',
        `out_trade_no`   string COMMENT '',
        `order_id`        string COMMENT '',
        `user_id`         string COMMENT '',
        `alipay_trade_no` string COMMENT '',
        `total_amount`    decimal(16,2) COMMENT '',
        `subject`         string COMMENT '',
        `payment_type` string COMMENT '',
        `payment_time`   string COMMENT ''
       )  COMMENT ''
    PARTITIONED BY ( `dt` string)
    stored as  parquet
    location '/warehouse/gmall/dwd/dwd_payment_info/'
    tblproperties ("parquet.compression"="snappy")
    ;

评论表是在用户行为数仓里面,不是在业务数仓里面,所以到用户行为数仓里找该表,表的情况如下:

CREATE EXTERNAL TABLE `dwd_comment_log`(
    `mid_id` string,
    `user_id` string, 
    `version_code` string, 
    `version_name` string, 
    `lang` string, 
    `source` string, 
    `os` string, 
    `area` string, 
    `model` string,
    `brand` string, 
    `sdk_version` string, 
    `gmail` string, 
    `height_width` string,  
    `app_time` string,
    `network` string, 
    `lng` string, 
    `lat` string, 
    comment_id int, 
    userid int, 
    p_comment_id int, 
    content string, 
    addtime string, 
    other_id int, 
    praise_count int,
    reply_count int,
    `server_time` string
    )
    PARTITIONED BY (dt string)
    location '/warehouse/gmall/dwd/dwd_comment_log/';

# 建立宽表 #

有了上面的分析,我们建立下宽表:

drop table if exists dws_user_action;
    create external table dws_user_action(
        user_id string comment '用户id',
        order_count bigint  comment '下单次数',
        order_amount decimal(16,2) comment '下单金额',
        payment_count bigint comment '支付次数',
        payment_amount decimal(16,2) comment '支付金额',
        comment_count bigint comment '评论次数'
    )comment '每日用户行为宽表'
    partitioned by (`dt` string)
    location '/warehouse/gmall/dws/dws_user_action';

向宽表中插入数据这个流程:  
1.从3个表中取出所需要的字段,存在临时表当中  
2.从这三张临时表里面根据宽表取出相应的数据进行union all操作,inser overwrite进宽表里面

**注意:在写这个表的时候,表里缺少的字段写法为 : 0 字段。**

with
    tmp_order as(
        select 
            user_id,
            sum(oc.total_amount) order_amount,
            count(*) order_count
        from 
            dwd_order_info oc
        where date_format(create_time,'yyyy-MM-dd')='2021-06-22'
        group by user_id
    ),
    tmp_payment as(
        select 
            user_id,
            count(*) payment_count,
            sum(pi.total_amount)   payment_amount
        from 
            dwd_payment_info pi
        where date_format(payment_time,'yyyy-MM-dd')='2021-06-22'
        group by user_id
    ),
    tmp_comment as(
        select 
            user_id,
            count(*) comment_count
        from 
            dwd_comment_log c
        where date_format(c.dt,'yyyy-MM-dd')='2021-06-22'
        group by user_id
    )
    insert overwrite table dws_user_action partition (dt='2021-06-22')
    select
        user_actions.user_id,
        sum(user_actions.order_count),
        sum(user_actions.order_amount),
        sum(user_actions.payment_count),
        sum(user_actions.payment_amount),
        sum(user_actions.comment_count)
    from 
    (
        select 
            user_id,
            order_amount,
            order_count,
            0 payment_count,
            0 payment_amount,
            0 comment_count
        from tmp_order
        union all 
        select 
            user_id,
            0 order_count,
            0 order_amount,
            payment_count,
            payment_amount,
            0 comment_count
        from tmp_payment
        union all 
        select 
            user_id,
            0 ,
            0 ,
            0 ,
            0 ,
            comment_count
        from tmp_comment
    )user_actions
    group by user_id;