用户订单指标业务开发

3.1 需求分析

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

• 以下为本需求需要统计的基于用户的订单指标：
  
• 以下为基于用户的退货指标：
  
  订单状态
• 1)等待支付
  如果您选择“网上支付”和“银行转账”这两种付款方式，在暂未收到您款项时，订单会显示“等待支付”，建议您在订单保留期限内及时付款。
• 2)等待预售商品到货
  若您订购的商品为预售商品，商品到货前订单会显示“等待预售商品到货”。
• 3)正在配货
  此状态说明您的订单正在库房配货。
• 4)等待移仓
  若您订单中的商品当地库房缺货，我们将从北京仓库调货至当地仓库，此时订单显示“等待移仓”。
• 5)正在移仓
  若您订单中的商品正在从北京仓库发往当地仓库，订单会显示“正在移仓”。
• 6)已配货
  此状态说明您的订单已完成配货，正在等待发货。
• 7)已发货
  若您的订单已从库房发出，正在配送途中，订单会显示“已发货”。
• 8)已送达
  1）若您已收到商品并在“我的订单”中进行了“收货反馈”，订单会显示“已送达”。
  2）若您未进行“收货反馈”操作，系统在发货后的20天默认变为“已送达”。
  3） 订单状态为“已发货”，但订单中的物流配送信息显示“配送成功”时，系统会默认将您的订单状态显示为“已送达”。
  4）国内平邮订单，如果您未进行“确认收货”操作，系统会在发货25天后默认您收到商品，订单状态显示为“已送达”。
  5）海外订单，如果您未进行“确认收货”操作，系统会在发货60天后默认您收到商品，订单状态显示为“已送达”。
• 9)交易成功
  若您的订单状态为“已送达”，且此状态后的15天内未发生退货，系统将默认变为“交易成功”。
• 10)交易未成功（拒收）——reject
  若订单未送达、送达后未签收或签收后办理了退货，订单状态都会默认显示“交易未成功”。
• 11)取消（退货）——return
  若您订单中的商品缺货，或您的订单超出了订单保留期限，或您将订单进行了取消操作，订单都将显示“取消”状态。

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='20190909')
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='20190909')
select
  orderid,
  orderstatus,
  paytype,
  userid,
  useraddressid,
  paytime,
  totalmoney,
  createtime
from `itcast_ods`.`itcast_orders` where dt='20190909' ;
   
       
-- 测试
select * from `itcast_dw`.`tmp_order_wide` limit 10;

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

insert overwrite table `itcast_dw`.`tmp_order_datetag_wide` partition(dt='20190909')
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='20190909';
       
-- 测试语句
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="20190909")
select 
addressId,
userId,
userName, 
otherName,
userPhone,
areaIdPath,
areaId,
userAddress,
isDefault,
dataFlag, 
createTime 
from `itcast_ods`.`itcast_user_address` where dt="20190909";
​
​
--地址表合并加入收货地址信息
insert overwrite table `itcast_dw`.`fact_order_wide` partition(dt='20190909')
select
  t1.orderId,
  t1.orderStatus,
  t1.payType,
  t1.userId,
  t1.userAddressId,
  t1.payTime,
  t1.paymoney,
  t1.createtime,
  t1.flag30,
  t1.flag60,
  t1.flag90,
  t1.flag180,
  t1.flagTimeBucket,
  t2.othername
from
  (select * from `itcast_dw`.`tmp_order_datetag_wide` where dt='20190909') t1
  left join
  (select * from `itcast_dw`.`dim_user_address` where dt='20190909') t2
   on t1.userAddressId = t2.addressId;
​
-- 测试
select * from `itcast_dw`.`fact_order_wide` limit 10;

3.4 指标开发

1 指标开发 一

参考代码：

select
  t1.userid,
  t1.loginname,
  MIN(t2.payTime) as first_paytime, --首次下单时间
  MAX(t2.payTime) as lastest_paytime, --尾单时间
  DATEDIFF(CURRENT_TIMESTAMP, MIN(t2.payTime)) as first_day_during_days,--首单距今
  DATEDIFF(CURRENT_TIMESTAMP, MAX(t2.payTime)) as lastest_day_durning_days, --尾单距今
  MIN(t2.paymoney) as min_paymoney,
  MAX(t2.paymoney) as max_paymoney
from
  (select * from `itcast_dw`.`fact_order_wide` where dt='20190909') as t2
  left join
    (select * from `itcast_dw`.