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

为什么要建立宽表?

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

分析宽表

以用户为主体,今天下了多少次单,下单的总金额是多少,支付了多少次,支付的金额又是多少,以及评论的次数。
基于以上的分析,需要找到上面这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;