电商数仓(dwt 层)

一、dwt 层介绍

dws 层统计的是每日数据，dws 层数据服务 dwt 层数据，dwt 层统计的是不同主题的累积数据。

二、数据表

2.1 设备主题宽表 dwt_uv_topic

1、数据来源
dwt_uv_topic、dws_uv_detail_daycount。

2、建表

drop table if exists dwt_uv_topic; 
create external table dwt_uv_topic 
( 
	`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_date_first` string comment '首次活跃时间', 
	`login_date_last` string comment '末次活跃时间', 
	`login_day_count` bigint comment '当日活跃次数', 
	`login_count` bigint comment '累积活跃天数'
) 
stored as parquet 
location '/warehouse/gmall/dwt/dwt_uv_topic'
tblproperties ("parquet.compression"="lzo"); 

3、加载数据

insert overwrite table dwt_uv_topic 
select 
	nvl(new.mid_id,old.mid_id), 
	nvl(new.user_id,old.user_id), 
	nvl(new.version_code,old.version_code), 
	nvl(new.version_name,old.version_name), 
	nvl(new.lang,old.lang), 
	nvl(new.source,old.source), 
	nvl(new.os,old.os), 
	nvl(new.area,old.area), 
	nvl(new.model,old.model), 
	nvl(new.brand,old.brand), 
	nvl(new.sdk_version,old.sdk_version), 
	nvl(new.gmail,old.gmail), 
	nvl(new.height_width,old.height_width), 
	nvl(new.app_time,old.app_time), 
	nvl(new.network,old.network), 
	nvl(new.lng,old.lng), 
	nvl(new.lat,old.lat), 
	if(old.mid_id is null,'2020-03-10',old.login_date_first), 
	if(new.mid_id is not null,'2020-03-10',old.login_date_last), 
	if(new.mid_id is not null, new.login_count,0), 
	nvl(old.login_count,0)+if(new.login_count>0,1,0) 
from 
( 
	select 
		* 
	from 
		dwt_uv_topic 
)old full outer join 
( 
	select 
		* 
	from 
		dws_uv_detail_daycount 
	where 
		dt='2020-03-10' 
)new on old.mid_id=new.mid_id; 

2.2 会员主题宽表 dwt_user_topic

1、数据来源
dwt_user_topic、dws_user_action_daycount。

2、创建表

drop table if exists dwt_user_topic; 
create external table dwt_user_topic ( 
	user_id string comment '用户id', 
	login_date_first string comment '首次登录时间', 
	login_date_last string comment '末次登录时间', 
	login_count bigint comment '累积登录天数', 
	login_last_30d_count bigint comment '最近30日登录天数', 
	order_date_first string comment '首次下单时间', 
	order_date_last string comment '末次下单时间', 
	order_count bigint comment '累积下单次数', 
	order_amount decimal(16,2) comment '累积下单金额', 
	order_last_30d_count bigint comment '最近30日下单次数', 
	order_last_30d_amount bigint comment '最近30日下单金额', 
	payment_date_first string comment '首次支付时间', 
	payment_date_last string comment '末次支付时间', 
	payment_count decimal(16,2) comment '累积支付次数', 
	payment_amount decimal(16,2) comment '累积支付金额', 
	payment_last_30d_count decimal(16,2) comment '最近30日支付次数', 
	payment_last_30d_amount decimal(16,2) comment '最近30日支付金额' 
)COMMENT '用户主题宽表' 
stored as parquet 
location '/warehouse/g