开发者学堂课程【新电商大数据平台2020最新课程：电商项目之支付类指标统计表 SQL 实现】学习笔记，与课程紧密联系，让用户快速学习知识。
课程地址：https://developer.aliyun.com/learning/course/640/detail/10567
电商项目之支付类指标统计表 SQL 实现
支付类指标统计表
先创建 sql
with orders as(
select
oc.customer. gender,
oc .customer_age_range,
oc .customer_natives,
od.pay_ type,
od.pay_ nettype,
sum(od .payment_money) order_ amounts, - -支付金额
count(distinct od.customer_id) order_user_counts --支付用户
count(distinct od.product_code) pay_product_counts --支付商品
先找到交易流水表
from
dwd_ nshop . dwd_ nshop_orders_ details od
join
ods_ nshop.ods_ 02_customer oc
on
od.customer_ id=oc .customer_id
where
od. bdp_day=" 20200321 "
group by 取值范围
oc .customer_gender,
oc .customer_age_range,
oc .customer_natives,
od.pay_ type,
od.pay_ nettype
),
pay as(
select
oc .customer_gender,
oc .customer_age_range,
oc .customer_natives,
op.pay_ type，
op.pay_ nettype,
sum(od.payment_money) order_amounts，--支付金额
count(distinct odcustomer id) order_ user_counts， –支付用户
count(distinct od.customer code) pay_product_counts—支付商品数
from
dwd_nshop.dwd_nshop_orders_details od
join
ods_nshop.ods_e2_customer oc
on
od.customer_id=oc.customer_id
where
od .bdp_day= " 20200321"
group by
oc.customer_gender,
oc.customer_age_range,
oc.customer_natives,
od.pay_type,
od.pay_nettype
以上是第一步。
第二步求支付的详细信息，需要拿到原始数据表。
Pay sa(
Select
oc .customer_gender,
oc .customer_age_range,
oc .customer_natives,
op.pay_ type，
op.pay_ nettype
sum(case when op.pay_ status-1 then op.pay_ amount end) pay_ amounts,（根据状态进行判断）
sum(case when op.pay_ status-1 then 1 end) psy_success,
sum(case when op.pay_ status-0 then 1 end) psy_fail,
count(distinct op.order_id) pay_ order_counts ,（支付订单id）
count(distinct op.customer id) pay_ user_counts(支付的用户)
from
ods_ nshop.ods_ 02_orders_ pay_records op
join
ods_ nshop.ods_ 02_ customer oc
on
op.customer_ id=oc .customer_id
where
from_unixtine(cast(op.pay_ctime/1000 as int),’yyyyMMdd’ )= ' 20191102'
group by
oc .customer_gender,
oc .customer_age_range,
oc .customer_natives,
op.pay_ type，
op.pay_ nettype
select
from
join
orders
on
orders .customer _gender=pay. customer _gender
and
orders .customer _age_range=pay .customer _age_ range
and
orders.customer _natives=pay . customer _natives
and
orders.pay_ type=pay.pay _type
and
orders .pay_ nettype=pay .pay_nettype
查询一下时间，以防错误
Time taken : 0.164 seconds
hive> select from_unixtime(cast(op.pay_ctime/1000 as int) , yyywdd' ) from ods_nshop.ods_02_orders_pay_records op limit 10;
oK
Time taken : 0.164 seconds
hive> select from_unixtime(cast(op.pay_ctime/1000 as int) , yyywdd' ) from ods_nshop.ods_02_orders_pay_records op limit 10;
oK
20191102201911022019110220191102201911022019110220191102201911022019110220191102
Time taken : 0.094 seconds,Fetched: 10 row(s)hive>.
insert overwrite table ads. nshop.ads_ nshop_ pay_ stat_ gsets partition(bdp_day=' 20200321 )
from
join
orders
on
orders .customer _gender=pay. customer _gender
and
orders .customer _age_range=pay .customer _age_ range
and
orders.customer _natives=pay . customer _natives
and
orders.pay_ type=pay.pay _type
and
orders .pay_ nettype=pay .pay_nettype
select
pay.customer_gender
pay.customer_age_range,
pay.customer_natives,
pay.pay_type,
pay.pay_nettype,
pay.pay_ amounts,（支付金额）
pay.psy_ success,（支付成功数）
pay.psy_ fail,（支付失败数）
pay.pay_ order_counts,（支付订单数）
pay.pay_ user_counts,（支付用户数）
orders .pay_ product_counts,（支付商品数）
pay.pay_ amounts/orders.order_amounts order _pay_ anount_rate,（支付成功率应该用支付成功除以未支付成功的）
pay.pay_user_counts/orders.order_user_counts order pay_ user_rate
from
pay
join
orders
on
复制全部进 sercue 语句，注意要修改错别字，运行的时候要稍微等待一下，时间有点久。
运行结果：
其中有一些空的内容
将其补全;
insert overwrite table ads_nshop.ads_nshop_pay_stat_gsets partition(bdp_day- '20200321)
求支付类指标统计表的意义，提高用户体验度。
