用户订单指标业务开发
3.1 需求分析
电商平台往往需要根据用户的购买数据来分析用户的行为
,此处。我们基于用户的订单情况进行一些统计分析,用于将来的用户行为分析。根据用户的消费行为习惯,对运营部门提供用户分析数据指标
。表是订单表!!
以下为本需求需要统计的基于用户的订单指标:
3.2 创建dw层表
1、创建itcast_dw.dim_user表
drop table if exists
itcast_dw
.dim_user
;
create tableitcast_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 existsitcast_dw
.tmp_order_wide
;
create tableitcast_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 tableitcast_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 tableitcast_dw
.fact_order_wide
;
create tableitcast_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
fromitcast_ods
.itcast_orders
where dt=‘20190908’ ;
– 测试
select * fromitcast_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 tableitcast_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 tableitcast_dw
.dim_user_address
partition(dt=“20190908”)
select
addressId,
userId,
userName,
otherName,
userPhone,
areaIdPath,
areaId,
userAddress,
isDefault,
dataFlag,
createTime
fromitcast_ods
.itcast_user_address
where dt=“20190908”;
–地址表合并加入收货地址信息
insert overwrite table
itcast