前言
前面完成了ADS层商品主题相关需求的设计和开发,下面进行ADS层交易主题的设计和开发。
一、交易综合统计
1.需求说明和分析
1)需求说明
统计周期 | 指标 | 说明 |
---|---|---|
最近1、7、30日 | 订单总额 | 订单最终金额 |
最近1、7、30日 | 订单数 | 略 |
最近1、7、30日 | 订单人数 | 略 |
最近1、7、30日 | 退单数 | 略 |
最近1、7、30日 | 退单人数 | 略 |
2)字段分析
根据需求可以,该表应包含以下字段:统计日期、最近天数、订单总额、订单数、订单人数、退单数、退单人数
2.建表语句
DROP TABLE IF EXISTS ads_trade_stats;
CREATE EXTERNAL TABLE ads_trade_stats
(
`dt` STRING COMMENT '统计日期',
`recent_days` BIGINT COMMENT '最近天数,1:最近1日,7:最近7天,30:最近30天',
`order_total_amount` DECIMAL(16, 2) COMMENT '订单总额,GMV',
`order_count` BIGINT COMMENT '订单数',
`order_user_count` BIGINT COMMENT '下单人数',
`order_refund_count` BIGINT COMMENT '退单数',
`order_refund_user_count` BIGINT COMMENT '退单人数'
) COMMENT '交易统计'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/warehouse/gmall/ads/ads_trade_stats/';
3.数据装载
1)我的思路
先计算订单数和下单人数,计算过程如下:
最近1日交易综合统计:
select
sum(order_total_amount_1d),
sum(order_count_1d),
count(*)
from dws_trade_user_order_1d
where dt='2020-06-14'
最近7日交易综合统计:
select
sum(order_total_amount_7d),
sum(order_count_7d),
count(if(order_count_7d>0,user_id,null))
from dws_trade_user_order_nd
where dt='2020-06-14'
最近30日交易综合统计:
select
sum(order_total_amount_30d),
sum(order_count_30d),
count(if(order_count_30d>0,user_id,null))
from dws_trade_user_order_nd
where dt='2020-06-14'
7日和30日整合:
select
recent_days,
sum(if(recent_days=7,order_total_amount_7d,order_total_amount_30d)),
sum(if(recent_days=7,order_count_7d,order_count_30d)),
count(if(if(recent_days=7,order_count_7d,order_count_30d),user_id,null)
from dws_trade_user_order_nd LATERAL VIEW explode(array(7,30)) tmp AS recent_days
where dt='2020-06-14'
group by recent_days
下面计算退单相关的数据,过程如下:
1日退单数据:
select
sum(order_refund_count_1d),
count(*)
from dws_trade_user_order_refund_1d
where dt='2020-06-14'
7日退单数据:
select
sum(order_refund_count_7d