记一次值得收藏的案例

计算淘系近一年购物会员人数,近一年购物会员数量,复购率,取日、周、月维度计算。

思路是:计算得到每一次购买的上次购买时间,
坑的地方在于 LAG这个使用并不是rank的减一操作,而是单纯得取上一列。所以用denserank去做rank的操作是不可行的。
后改为用 f_rank=f_rank+1 的方式。

先放代码,quiky之处空了再补充!

with 
date_info as 
(
select 
period_date,
from_unixtime(unix_timestamp(month_start_dt_wid,'yyyyMMdd'), 'yyyy-MM-dd') as month_start,
from_unixtime(unix_timestamp(month_end_dt_wid,'yyyyMMdd'),'yyyy-MM-dd') as month_end,
from_unixtime(unix_timestamp(monday_of_week,'yyyyMMdd'),'yyyy-MM-dd') as week_start,
to_date(date_add(from_unixtime(unix_timestamp(monday_of_week,'yyyyMMdd'),'yyyy-MM-dd'),6)) as week_end
from dim.dim_app_period_d 
where year>='2019'),
 date_info_2 as(
        select
        '日' as date_type,
        period_date,
        period_date as period_start,
        period_date as period_end
        from date_info
        UNION ALL
        select
        '周' as date_type,
        period_date,
        week_start as period_start,
        week_end as period_end
        from date_info
        UNION ALL
        select
        '月' as date_type,
        period_date,
        month_start as period_start,
        month_end as period_end
        from date_info
),
 temp_rebuy as 
(
select
    to_date(pay_time) as pay_date, -- 支付日期
    buyer_nick, -- 每人
    shop_id,
    real_shop_name,
    b.create_time_tm,
    date_type,
    period_date,
    period_start,
    period_end,
    dense_rank() over(partition by buyer_nick,date_type order by period_start) as f_rank -- 这里的排序和店铺品类都无关,只是淘系平台。
    from ecm.vipuser_t_order_main a
    inner join ecm.vipuser_dw_user_info b on a.buyer_nick = b.buyer_nick_tm 
    inner join date_info_2 c on to_date(a.pay_time) = c.period_date
    WHERE to_date(pay_time) >= to_date(create_time_tm)   -- 购物时已经是阿里通会员
          and a.platform_id in ('1','4') -- 淘系平台发生购买
),
 date_para as (
select
period_date,
period_date as date_end,
add_months(period_date,-12) as year_ago
from dim.dim_app_period_d
where year>='2019'
),
date_cum as (
select
a.period_date,
b.date_end,
b.year_ago
from date_para a
inner join date_para b on 1=1
WHERE  a.period_date >= b.year_ago and a.period_date < b.date_end
and year(a.period_date)>=2019   -- 这里限制了是2019年之后
),
uid_cum as(
select
b.date_end,
b.year_ago,
count(distinct buyer_nick) as year_cnt
from temp_rebuy a
inner join date_cum b on a.pay_date=b.period_date
group by date_end, year_ago
),
rebuy as 
(
select
        a.date_type,
        a.pay_date,
        a.period_start,
        a.period_end,
        a.buyer_nick,
        a.shop_id,
        a.real_shop_name,
        b.pay_date as buy_lt  -- 上一次购买的日期
    from temp_rebuy a
    inner join temp_rebuy b on a.buyer_nick =b.buyer_nick and a.date_type=b.date_type and a.f_rank =b.f_rank +1 
),
final_rebuy as 
(select
date_type,
period_start,
period_end,
shop_id,
real_shop_name,
count(distinct buyer_nick) as rebuy_cnt
from rebuy
where buy_lt between add_months(period_start,-12) and date_sub(period_start,1) -- 在一年之间
 and shop_id in ('11','12','13','18','47','121','151','3977','174','332','537','806','897','4133','3069','3215','3731','4111')
group by date_type, period_start, period_end, shop_id, real_shop_name
)
select
date_type,
period_start,
period_end,
shop_id,
real_shop_name,
rebuy_cnt,
b.date_end,
b.year_cnt,
a.rebuy_cnt/b.year_cnt as rebuy_rate
from final_rebuy a
inner join uid_cum b on a.period_start = b.date_end 
; 


©️2020 CSDN 皮肤主题: 创作都市 设计师:CSDN官方博客 返回首页