数仓-ADS-品牌复购率

这里利用了 炸裂后的多行数据recent_days，来筛选出对应天数的数据

lateral view explode(Array(1,7,30)) tmp as recent_days
        where dt>=date_add('2021-06-14',-29)
        and dt>=date_add('2021-06-14',-recent_days+1)

用户-商品-count 一条数据   ---品牌-商品，商品粒度更小，使用品牌进一步聚合

用户-品牌-sum(count)

insert overwrite table ads_repeat_purchase
select * from ads_repeat_purchase
union
select
    '2021-06-14' dt,
    recent_days,
    tm_id,
    tm_name,
    cast(sum(if(order_tm_count>=2,1,0))/sum(if(order_tm_count>=1,1,0))*100 as decimal(16,2)) order_repeat_rate
from
(
    select
        recent_days,
        user_id,
        tm_id,
        tm_name,
        sum(order_sku_count) order_tm_count
    from
    (
        select
            recent_days,
            user_id,
            sku_id,
            count(*) order_sku_count
        from dwd_order_detail
        lateral view explode(Array(1,7,30)) tmp as recent_days
        where dt>=date_add('2021-06-14',-29)
        and dt>=date_add('2021-06-14',-recent_days+1)
        group by recent_days,user_id,sku_id
    )t1
    left join
    (
        select
            id,
            tm_id,
            tm_name
        from dim_sku_info
        where dt='2021-06-14'
    )t2
    on t1.sku_id=t2.id
    group by recent_days,user_id,tm_id,tm_name
)t3
group by recent_days,tm_id,tm_name;