需求说明如下

建表语句

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日汇总事实表

完整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;

思考步骤

1. 计算 一个人 买 一个商品 买了多少单（7 30 天内）

   
   
   

   结果：

2. 如果 某用户 下单次数 >1 则认定为 复购。
   复购 计为1 否则 0

`if`(order_count > 1, 1,0)

进行聚合

sum(`if`(order_count > 1, 1,0))

3. 统计 总下单人数
   下单次数 >0 则 计为 有效下单

`if`(order_count > 0, 1,0)

进行聚合

sum(`if`(order_count > 0, 1,0))

4. 计算复购率
   将 2 3 相除

 sum(`if`(order_count > 1, 1,0)) / sum(`if`(order_count > 0, 1,0))