电商数仓(dws 层)

一、dws 层介绍

统计各个主题对象的当天行为，服务于 DWT 层的主题宽表，以及一些业务明细数据，应对特殊需求(例如，购买行为，统计商品复购率)。

二、dws 层用户行为数据

2.1 每日设备行为 dws_uv_detail_daycount

1、介绍
每日设备行为，主要按照设备 id 统计。

2、数据来源
dwd_start_log

3、建表

drop table if exists dws_uv_detail_daycount; 
create external table dws_uv_detail_daycount ( `mid_id` string COMMENT '设备唯一标识', `user_id` string COMMENT '用户标识', `version_code` string COMMENT '程序版本号',`version_name` string COMMENT '程序版本名', `lang` string COMMENT '系统语言', `source` string COMMENT '渠道号', `os` string COMMENT '安卓系统版本', `area` string COMMENT '区域', `model` string COMMENT '手机型号', `brand` string COMMENT '手机品牌', `sdk_version` string COMMENT 'sdkVersion', `gmail` string COMMENT 'gmail', `height_width` string COMMENT '屏幕宽高', `app_time` string COMMENT '客户端日志产生时的时间', `network` string COMMENT '网络模式', `lng` string COMMENT '经度', `lat` string COMMENT '纬度', `login_count` bigint COMMENT '活跃次数'
) 
partitioned by(dt string) 
stored as parquet 
location '/warehouse/gmall/dws/dws_uv_detail_daycount'
tblproperties ("parquet.compression"="lzo");

4、加载数据

insert overwrite table dws_uv_detail_daycount partition(dt='2020-03-10') 
select mid_id, concat_ws('|', collect_set(user_id)) user_id, concat_ws('|', collect_set(version_code)) version_code, concat_ws('|', collect_set(version_name)) version_name, concat_ws('|', collect_set(lang))lang, concat_ws('|', collect_set(source)) source, concat_ws('|', collect_set(os)) os, concat_ws('|', collect_set(area)) area, concat_ws('|', collect_set(model)) model, concat_ws('|', collect_set(brand)) brand, concat_ws('|', collect_set(sdk_version)) sdk_version, concat_ws('|', collect_set(gmail)) gmail, concat_ws('|', collect_set(height_width)) height_width, concat_ws('|', collect_set(app_time)) app_time, concat_ws('|', collect_set(network)) network, concat_ws('|', collect_set(lng)) lng, concat_ws('|', collect_set(lat)) lat, count(*) login_count 
from dwd_start_log 
where dt='2020-03-10'
group by mid_id;

三、dws 层业务数据表

DWS 层的宽表字段，是站在不同维度的视角去看事实表，重点关注事实表的度量值。

3.1 每日会员行为 dws_user_action_daycount

1、数据来源
dwd_start_log、dwd_fact_cart_info、dwd_fact_order_info、dwd_fact_payment_info。

2、建表

drop table if exists dws_user_action_daycount; 
create external table dws_user_action_daycount ( user_id string comment '用户 id', login_count bigint comment '登录次数', cart_count bigint comment '加入购物车次数', cart_amount double comment '加入购物车金额', order_count bigint comment '下单次数', order_amount decimal(16,2) comment '下单金额', payment_count bigint comment '支付次数', payment_amount decimal(16,2) comment '支付金额' 
) COMMENT '每日用户行为' 
PARTITIONED BY (`dt` string) 
stored as parquet 
location '/warehouse/gmall/dws/dws_user_action_daycount/' 
tblproperties ("parquet.compression"="lzo");

3、加载数据

with 
tmp_login as 
( select user_id, count(*) login_count from dwd_start_log where dt='2020-03-10' and user_id is not null group by user_id 
), 
tmp_cart as 
( select user_id, count(*) cart_count,sum(cart_price*sku_num) cart_amount from dwd_fact_cart_info where dt='2020-03-10' and user_id is not null and date_format(create_time,'yyyy-MM-dd')='2020-03-10'group by user_id
), 
tmp_order as 
( select user_id, count(*) order_count, sum(final_total_amount) order_amount from dwd_fact_order_info where dt='2020-03-10'group by user_id 
) , 
tmp_payment as 
( select user_id, count(*