千亿数仓第四章(指标计算_用户订单指标业务开发)

用户订单指标业务开发
3.1 需求分析
电商平台往往需要根据用户的购买数据来分析用户的行为，此处。我们基于用户的订单情况进行一些统计分析，用于将来的用户行为分析。根据用户的消费行为习惯，对运营部门提供用户分析数据指标。表是订单表！！
以下为本需求需要统计的基于用户的订单指标：


3.2 创建dw层表
1、创建itcast_dw.dim_user表

drop table if exists itcast_dw.dim_user;
create table itcast_dw.dim_user(
userId bigint,
loginName string,
userSex bigint
)
partitioned by (dt string)
STORED AS PARQUET;

2、创建订单临时表tmp_order_wide
在dw层创建 订单临时表tmp_order_wide

– 订单临时订单表
drop table if exists itcast_dw.tmp_order_wide;
create table itcast_dw.tmp_order_wide(
orderId bigint,
orderStatus bigint,
payType bigint,
userId bigint,
userAddressId bigint,
payTime string,
payMoney double,
createtime string
)
partitioned by(dt string)
STORED AS PARQUET;

3、创建订单时间标志宽表tmp_order_datetag_wide

drop table itcast_dw.tmp_order_datetag_wide;
create table itcast_dw.tmp_order_datetag_wide(
orderId bigint,
orderStatus bigint,
payType bigint,
userId bigint,
userAddressId bigint,
payTime string,
payMoney double,
createtime string,
flag30 bigint,
flag60 bigint,
flag90 bigint,
flag180 bigint,
flagTimeBucket string
)
partitioned by(dt string)
STORED AS PARQUET;

4、创建订单时间标志、地址标志宽表 fact_order_wide

– 地址拉宽
drop table itcast_dw.fact_order_wide;
create table itcast_dw.fact_order_wide(
orderId bigint,
orderStatus bigint,
payType bigint,
userId bigint,
userAddressId bigint,
payTime string,
payMoney double,
createtime string,
flag30 bigint,
flag60 bigint,
flag90 bigint,
flag180 bigint,
flagTimeBucket string,
othername string
)
partitioned by(dt string)
STORED AS PARQUET;

3.3 订单宽表ETL处理
1、加载用户维度数据：

insert overwrite table itcast_dw.dim_user partition(dt=‘20190908’)
select
userId,
loginName,
userSex
from
itcast_ods.itcast_users ;

–验证
select * from itcast_dw.dim_user limit 10;

2、导入订单数据：

insert overwrite table itcast_dw.tmp_order_wide partition (dt=‘20190908’)
select
orderid,
orderstatus,
paytype,
userid,
useraddressid,
paytime,
totalmoney,
createtime
from itcast_ods.itcast_orders where dt=‘20190908’ ;
– 测试
select * from itcast_dw.tmp_order_wide limit 10;

3、时间近30天、90天、180天、订单上午、下午时间拉宽

insert overwrite table itcast_dw.tmp_order_datetag_wide partition(dt=‘20190908’)
select
orderId,
orderStatus,
payType,
userId,
userAddressId,
payTime,
paymoney,
createtime,
case when datediff(current_timestamp, createtime) <= 30
then 1
else 0
end as flag_30,
case when datediff(current_timestamp, createtime) <= 60
then 1
else 0
end as flag_60,
case when datediff(current_timestamp, createtime) <= 90
then 1
else 0
end as flag_90,
case when datediff(current_timestamp, createtime) <= 180
then 1
else 0
end as flag_180,
case when hour(createtime) >= 0 and hour(createtime) < 6
then ‘凌晨’
when hour(createtime) >= 6 and hour(createtime) < 12
then ‘上午’
when hour(createtime) >= 12 and hour(createtime) < 14
then ‘中午’
when hour(createtime) >= 14 and hour(createtime) < 18
then ‘下午’
else ‘晚上’
end as flag_time_bucket
from
itcast_dw.tmp_order_wide
where dt=‘20190908’;

– 测试语句

select * from itcast_dw.tmp_order_datetag_wide limit 5;

4、与地址表合并加入收货地址信息
–创建dw层dim_user_address表

drop table if exists itcast_dw.dim_user_address;
create table itcast_dw.dim_user_address(
addressId bigint,
userId bigint,
userName string,
otherName string,
userPhone string,
areaIdPath string,
areaId bigint,
userAddress string,
isDefault bigint,
dataFlag bigint,
createTime string
)
partitioned by (dt string)
STORED AS PARQUET;
​
–从ods层itcast_user_address导出数据到dim_user_address表
insert overwrite table itcast_dw.dim_user_address partition(dt=“20190908”)
select
addressId,
userId,
userName,
otherName,
userPhone,
areaIdPath,
areaId,
userAddress,
isDefault,
dataFlag,
createTime
from itcast_ods.itcast_user_address where dt=“20190908”;
​
​

–地址表合并加入收货地址信息

insert overwrite table itcast