需求说明如下
| 统计周期 | 统计粒度 | 指标 | 说明 |
|---|---|---|---|
| 最近7、30日 | 品牌 | 复购率 | 重复购买人数占购买人数比例 |
建表语句
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/';
前提
今天是 2020-06-14
dws_trade_user_sku_order_nd：交易域用户商品粒度订单最近n日汇总事实表
| user_id | tm_id | tm_name | order_count_7d | order_count_30d |
|---|---|---|---|---|
| 用户id | 品牌id | 品牌名称 | 最近7日下单次数 | 最近30日下单次数 |
| 1001 | 22 | 七匹狼 | 5 | 15 |
| 1002 | 14 | 柒牌 | 12 | 30 |
| 1003 | 23 | 鸿星尔克 | 0 | 5 |
| 1005 | 24 | 匡威 | 15 | 55 |
完整sql
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,
-- 无法通过count(*) 统计购买过的人数
-- 因为可能存在 一个人在最近30天下单了，但是在最近7天没有下单
cast( sum(`if`(order_count > 1, 1,0)) / sum(`if`(order_count > 0, 1,0)) * 100 as decimal(16,2)) order_repeat_rate
from (
select recent_days,
tm_id,
tm_name,
user_id,
sum(`if`(recent_days = 7, order_count_7d, order_count_30d)) order_count
-- 统计的是用户截止到6-14日，对所有商品的最近7，30日的下单情况统计
-- 每天每个用户购买的每个商品是一行
from dws_trade_user_sku_order_nd
lateral view explode(`array`(7, 30)) tmp as recent_days
where dt = '2020-06-14'
-- 统计每个用户购买了每个品牌多少次(下过多少单)
group by recent_days, tm_id, tm_name, user_id
) t1
group by recent_days, tm_id, tm_name;
思考步骤
-
计算 一个人 买 一个商品 买了多少单（7 30 天内）
结果：
| user_id | tm_id | order_count | recent_days |
|---|---|---|---|
| 用户id | 品牌id | 下单次数 | 计算天数 |
| 1001 | 22 | 5 | 7 |
| 1001 | 22 | 22 | 30 |
| 1003 | 23 | 20 | 7 |
| 1003 | 23 | 60 | 30 |
- 如果 某用户 下单次数 >1 则认定为 复购。
复购 计为1 否则 0
`if`(order_count > 1, 1,0)
进行聚合
sum(`if`(order_count > 1, 1,0))
- 统计 总下单人数
下单次数 >0 则 计为 有效下单
`if`(order_count > 0, 1,0)
进行聚合
sum(`if`(order_count > 0, 1,0))
- 计算复购率
将 2 3 相除
sum(`if`(order_count > 1, 1,0)) / sum(`if`(order_count > 0, 1,0))
