离线数仓—ADS层商品主题需求的实现
前言
前面完成了ADS层用户主题相关需求的设计和开发,下面进行ADS层商品主题的设计和开发。
一、最近7/30日各品牌复购率
1.需求说明和分析
1)需求说明
统计周期 | 统计粒度 | 指标 | 说明 |
---|---|---|---|
最近7、30日 | 品牌 | 复购率 | 重复购买人数占购买人数比例 |
2)字段分析
根据需求,可以得知要包含以下字段:统计日期、最近天数、品牌ID、品牌名称、复购率
2.建表语句
DROP TABLE IF EXISTS ads_repeat_purchase_by_tm;
CREATE EXTERNAL TABLE ads_repeat_purchase_by_tm
(
`dt` STRING COMMENT '统计日期',
`recent_days` BIGINT COMMENT '最近天数,7:最近7天,30:最近30天',
`tm_id` STRING COMMENT '品牌ID',
`tm_name` STRING COMMENT '品牌名称',
`order_repeat_rate` DECIMAL(16, 2) COMMENT '复购率'
) COMMENT '各品牌复购率统计'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/warehouse/gmall/ads/ads_repeat_purchase_by_tm/';
3.数据装载
1)我的思路
先算7天的,直接从交易域用户商品粒度订单最近n日汇总表获取数据,按用户+品牌分组,对于每个用户的每个品牌,若最近7日下单次数为1,则代表了购买该品牌的人数,若最近7日下单次数大于1,则代表了重复购买该品牌的人数。要注意的是,这个表的粒度是用户——商品粒度,可能出现一个用户多次购买一个品牌的情况,所以要按用户+品牌进行分组语句如下:
select
tm_id,
tm_name,
sum(if(order_count>1),1,0)/sum(if(order_count>=1,1,0))
from
(
select
user_id,
tm_id,
tm_name,
sum(order_count_7d) order_count
from dws_trade_user_sku_order_nd
where dt='2020-06-14'
group by user_id,tm_id,tm_name
)t1
group by tm_id,tm_name
30天的复购率方法类似,语句如下:
select
tm_id,
tm_name,
sum(if(order_count>1),1,0)/sum(if(order_count>=1,1,0))
from
(
select
user_id,
tm_id,
tm_name,
sum(order_count_30d) order_count
from dws_trade_user_sku_order_nd
where dt='2020-06-14'
group by user_id,tm_id,tm_name
)t1
group by tm_id,tm_name
相同的数据,聚合逻辑不同,采用炸裂的方式炸裂两份。
select
recent_days,
tm_id,
tm_name,
sum(if(order_count>1),1,0)/sum(if(order_count>=1,1,0))
from
(
select
recent_days,
user_id,
tm_id,
tm_name,
if(recent_days=7,sum(order_count_7d),sum(order_count_30d)) order_count
from dws_trade_user_sku_order_nd LATERAL VIEW explode(array(7,30)) tmp AS recent_days
where dt='2020-06-14'
group by user_id,tm_id,tm_name,recent_days
)t1
group by tm_id,tm_name,recent_days
其中,子查询语句可以这样写:
sum(if(recent_days=7,order_count_7d,order_count_30d) order_count
2)给的思路
insert overwrite table ads_repeat_purchase_by_tm
select * from ads_repeat_purchase_by_tm
union
select
'2020-06-14' dt,
recent_days,
tm_id,
tm_name,
cast(sum(if(order_count>=2,1,0))/sum(if(order_count>=1,1,0)) as decimal(16,2))
from
(
select
'2020-06-14' dt,
recent_days,
user_id,
tm_id,
tm_name,
sum(order_count) order_count
from
(
select
recent_days,
user_id,
tm_id,
tm_name,
case recent_days
when 7 then order_count_7d
when 30 then order_count_30d
end order_count
from dws_trade_user_sku_order_nd lateral view explode(array(7,30)) tmp as recent_days
where dt='2020-06-14'
)t1
group by recent_days,user_id,tm_id,tm_name
)t2
group by recent_days,tm_id,tm_name;
二、各品牌商品交易统计
1.需求说明和分析
1)需求说明
统计周期 | 统计粒度 | 指标 | 说明 |
---|---|---|---|
最近1、7、30日 | 品牌 | 订单数 | 略 |
最近1、7、30日 | 品牌 | 订单人数 | 略 |
最近1、7、30日 | 品牌 | 退单数 | 略 |
最近1、7、30日 | 品牌 | 退单人数 | 略 |
2)字段分析
根据需求说明,可以得知,要包含以下字段:统计日期、最近天数、订单数、订单人数、退单数、退单人数、品牌ID、品牌名称
2.建表语句
DROP TABLE IF EXISTS ads_trade_stats_by_tm;
CREATE EXTERNAL TABLE ads_trade_stats_by_tm
(
`dt` STRING COMMENT '统计日期',
`recent_days` BIGINT COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天',
`tm_id` STRING COMMENT '品牌ID',
`tm_name` STRING COMMENT '品牌名称',
`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_by_tm/';
3.数据装载
1)我的思路
先算1天内订单数:
--最近1天内订单数:
--1个用户对一个品牌的下单次数(一个品牌可能对应多个用户)
--1个品牌的下单次数和下单人数
select
tm_id,
tm_name,
count(*)
sum(order_count)
from
(
select
user_id,
tm_id,
tm_name,
sum(order_count_1d) order_count
from dws_trade_user_sku_order_1d
where dt='2020-06-14'
group by user_id,tm_id,tm_name
)t1
group by tm_id,tm_name
算7天内订单数和订单人数: