零食类商品中复购率top3高的商品

#一、先把90天内零食类商品的所有相关记录筛选出来
with t as (select b.product_id product_id,uid
from tb_order_overall a left join tb_order_detail b on a.order_id = b.order_id
left join tb_product_info c on c.product_id = b.product_id
where tag = '零食' and event_time BETWEEN date_sub((select max(event_time) from tb_order_overall),INTERVAL 89 day)
and (select max(event_time) from tb_order_overall) )
#二、计算复购率并输出结果
select a.product_id,round(复购人数/总人数,3) repurchase_rate
from 
(select product_id,(case when count(uid)>=2 then count(distinct uid) else 0 end) 复购人数
from t 
group by product_id,uid
) a #表a计算各零食类商品复购人数
                  right join (
select product_id,count(distinct uid) 总人数
from t 
group by product_id) b on a.product_id = b.product_id #表b计算各零食类商品总人数
order by repurchase_rate desc,product_id 
limit 3

代码有点长 但是应该比较好理解

with t as(
select
	t2.product_id,t1.uid,
	count(*) cnt
from
tb_order_overall t1
left join tb_order_detail t2
on t1.order_id=t2.order_id
left join tb_product_info t3
on t2.product_id=t3.product_id
where date(event_time)>(
select date(date_sub(max(event_time),interval 90 day)) from tb_order_overall)
and t3.tag='零食'
group by 1,2)
select product_id,round(sum(if(cnt>=2,1,0))/count(*),3) repurchase_rate from t group by product_id
order by 2 desc,1 limit 3

select product_id,round(sum(case when ranking>1 then 1 else 0 end)/count(DISTINCT uid),3) rate from 
(select product_id,uid,ROW_NUMBER() over(partition by product_id,uid order by event_time) ranking
from tb_order_detail left join tb_product_info using (product_id) left JOIN tb_order_overall using (order_id)
where tag='零食' and status=1 and datediff((select max(event_time) from tb_order_overall),event_time)<90)t
group by  product_id
order by rate desc,product_id asc
limit 3
思路是用窗口函数筛出购买了两次的人

select product_id,round(sum(cnt)/count(cnt),3) repurchase_rate
from
(select t1.product_id,uid,if(count(event_time)>=2,1,0) cnt
from tb_product_info t1
join tb_order_detail t3
on t1.product_id=t3.product_id
join tb_order_overall t2
on t2.order_id=t3.order_id
where status=1 and tag='零食' 
and datediff(date((select max(event_time) from tb_order_overall)),date(event_time))<90
group by product_id,uid) t
group by product_id
ORDER BY repurchase_rate DESC, product_id
LIMIT 3;

【类型】：时间函数经典题

【场景】：留存率、复购率、

【分类】：时间函数

分析思路

难点：

1.时间函数的应用
2.复购率

（1）统计每个货物用户的购买记录；利用窗口函数根据货号、用户分组按购买时间排序

• [使用] row_number（）；timestampdiff()

（2）统计零食类商品中复购率top3高的商品

求解代码

在时间的处理上，可能会遇到的问题，关于timestampdiff

错误代码1

#统计零食类商品中复购率top3高的商品
select
    product_id,
    round(count(distinct if(ranking >= 2,uid,null))/count(distinct uid),3) rate
from(
    #统计每个货物用户的购买记录；利用窗口函数根据货号、用户分组按购买时间排序
    select
        product_id,
        uid,
        row_number() over(partition by product_id,uid order by event_time) ranking
    from tb_order_detail
    left join tb_product_info using(product_id)
    left join tb_order_overall using (order_id)
    where tag='零食'
    and status=1
    and 90 > timestampdiff(day,event_time,(
        select
            max(date(event_time))
        from tb_order_overall))
) main
group by product_id
order by rate desc,product_id
limit 3

错误代码2

#统计零食类商品中复购率top3高的商品
select
    product_id,
    round(count(distinct if(ranking >= 2,uid,null))/count(distinct uid),3) rate
from(
    #统计每个货物用户的购买记录；利用窗口函数根据货号、用户分组按购买时间排序
    select
        product_id,
        uid,
        row_number() over(partition by product_id,uid order by event_time) ranking
    from tb_order_detail
    left join tb_product_info using(product_id)
    left JOIN tb_order_overall using (order_id)
    where tag='零食'
    and status=1
    and 90 > timestampdiff(date(
        (select
            max(event_time)
        from tb_order_overall)),date(event_time))
) main
group by product_id
order by rate desc,product_id
limit 3

不通过案例输出

1    8002|0.500        
2    8004|0.500    1    8004|0.500
3    8003|0.333    2    8003|0.333  
                   3    8002|0.000

在时间的处理上，为什么使用timestampdiff()会导致提交不通过，但使用datediff()和date_sub就可以，因为用错了timestampdiff（）

正确代码

方法一

with子句

#统计零食类商品中复购率top3高的商品
with
    main as(
        #统计近90天内购买商品至少两次的人数
        select
            uid,
            product_id,
            count(distinct uid) as payusr_num
        from tb_product_info
        join tb_order_detail using(product_id)
        join tb_order_overall using(order_id)
        where tag = '零食'
        and status=1
        and date(event_time) > (
            select
                date(date_sub(max(event_time),interval 90 day))
            from tb_order_overall)
        group by product_id,uid having count(*) >= 2
    )
    ,attr as(
        #统计近90天内购买商品的总人数
        select
            product_id,
            if(count(distinct uid) = 0,0,count(distinct uid)) as usr_num
        from tb_product_info
        join tb_order_detail using(product_id)
        join tb_order_overall using(order_id)
        where tag = '零食'
        and status=1
        and date(event_time) > (
            select
                date(date_sub(max(event_time),interval 90 day))
            from tb_order_overall)
        group by product_id
    )
select
    product_id,
    round(if(payusr_num is null,0,payusr_num/usr_num),3) as repurchase_rate
from main
right join attr using(product_id)
order by repurchase_rate desc
limit 3

使用datediff()

#统计零食类商品中复购率top3高的商品
select
    product_id,
    round(count(distinct if(ranking >= 2,uid,null))/count(distinct uid),3) rate
from(
    #统计每个货物用户的购买记录；利用窗口函数根据货号、用户分组按购买时间排序
    #在时间的处理上，这道题不知道为什么不能使用timestampdiff()，会导致提交不通过
    #使用datediff()和date_sub就可以
    select
        product_id,
        uid,
        row_number() over(partition by product_id,uid order by event_time) ranking
    from tb_order_detail
    left join tb_product_info using(product_id)
    left JOIN tb_order_overall using (order_id)
    where tag='零食'
    and status=1
    and datediff(date(
        (select
            max(event_time)
        from tb_order_overall)),date(event_time)) < 90
) main
group by product_id
order by rate desc,product_id
limit 3

方法二

多表连接

#统计零食类商品中复购率top3高的商品
select
    product_id,
    round(count(distinct if(ranking >= 2,uid,null))/count(distinct uid),3) rate
from(
    #统计每个货物用户的购买记录；利用窗口函数根据货号、用户分组按购买时间排序
    #在时间的处理上，这道题不知道为什么不能使用timestampdiff()，会导致提交不通过
    #使用datediff()和date_sub就可以
    select
        product_id,
        uid,
        row_number() over(partition by product_id,uid order by event_time) ranking
    from tb_order_detail
    left join tb_product_info using(product_id)
    left JOIN tb_order_overall using (order_id)
    where tag='零食'
    and status=1
    and date(event_time) > date(
        (select
            date_sub(max(event_time),interval 90 day)
        from tb_order_overall)
        )
) main
group by product_id
order by rate desc,product_id
limit 3

输出结果

运行main表

1    8001|102|1    
2    8001|102|2    
3    8002|101|1    
4    8002|101|2        
5    8002|105|1        
6    8003|101|1        
7    8003|102|1        
8    8003|102|2        
9    8003|104|1

select  product_id,round(sum(case when num >=2 then 1 else 0 end)/count(*),3) as repurchase_rate from (
select product_id,uid,count(*) as num
from tb_order_overall join tb_order_detail using(order_id)
join tb_product_info using(product_id)
where datediff((select max(date(event_time)) from tb_order_overall),date(event_time)) <90
and tag = '零食'
group by product_id,uid)t
group by product_id
order by repurchase_rate desc,product_id 
limit 3

嵌套from子查询

先算出每个产品，每个人购买过多少次，然后再计算复购率

select product_id,
round(avg(case when cnt>=2 then 1 when cnt=1 then 0 end),3) repurchase_rate
from (
    select product_id,uid,count(*) cnt
    from (
        select c.product_id,b.uid,date(b.event_time) event_time,
        max(date(b.event_time)) over () max_time
        from tb_product_info a
        right join tb_order_detail c
        on a.product_id = c.product_id
        left join tb_order_overall b
        on c.order_id = b.order_id
        where a.tag = '零食' and b.status=1
        ) a
    where datediff(max_time,event_time) < 90
    group by product_id,uid
    ) t
group by product_id
order by repurchase_rate desc,product_id
limit 3;

select product_id,round(sum(if(t_cnt=2,1,0))/count(distinct uid),3) repurchase_rate
from(
    select product_id,uid,row_number()over(partition by product_id,uid order by event_time) t_cnt
    from tb_order_detail
    left join tb_order_overall using(order_id)
    left join tb_product_info using(product_id)
    where tag='零食'
    and datediff((select max(event_time) 
                  from tb_order_overall)
                 ,event_time)<90
    and status<>2
) t
where t_cnt<3
group by product_id
order by repurchase_rate desc,product_id
limit 3;

select b.product_id,
round(count(distinct c.uid)/(count(distinct b.uid)),3) repurchase_rate
from
(select distinct od1.product_id,o1.uid from tb_order_overall o1
inner join tb_order_detail od1 on od1.order_id = o1.order_id
inner join tb_product_info p1 on p1.product_id=od1.product_id
where p1.tag='零食'
and o1.status=1) as b
left join
(select product_id,uid from 
(select od.product_id,o.uid,count(o.uid) cnt from tb_order_overall o
inner join tb_order_detail od on od.order_id = o.order_id
inner join tb_product_info p on p.product_id=od.product_id
where p.tag='零食'
and timestampdiff(day,date(o.event_time),(select max(date(event_time)) from tb_order_overall))<=89
and o.status=1
group by od.product_id,o.uid) as a 
where a.cnt >= 2) as c on c.product_id=b.product_id
group by b.product_id
order by repurchase_rate desc,b.product_id limit 3

with c as 
(select
t3.product_id as pid,
t2.uid as uid,
date_format(event_time,'%Y-%m-%d') as dt,
# 得到每个商品每个用户最近下一次购买的日期
lead(date_format(event_time,'%Y-%m-%d'),1) over (partition by t3.product_id,t2.uid order by date_format(event_time,'%Y-%m-%d')) as dt2,
((select max(date_format(event_time,'%Y-%m-%d')) from tb_order_overall)) as today
from tb_order_detail t3 
left join tb_order_overall t2 on t2.order_id = t3.order_id
left join tb_product_info t1 on t1.product_id = t3.product_id
where t1.tag = '零食' 
)

select
pid,
round(count(distinct if ((datediff(today,dt)<=89) and dt2 is not null,uid,null))/count(distinct uid),3) as rate
from c
group by pid
order by rate desc,pid
limit 3

select distinct 
    od.product_id,
    round(avg(if(count(uid) > 1,1,0)) over(partition by od.product_id),3) repurchase_rate
from tb_product_info pi
join tb_order_detail od
on pi.product_id = od.product_id
join tb_order_overall oo
on od.order_id = oo.order_id
where tag = '零食'
and date(event_time) > date_sub((select date(max(event_time)) from tb_order_overall),interval 90 day)
group by od.product_id,oo.uid
order by repurchase_rate desc,od.product_id
limit 3;

select product_id,
round(count(distinct if(purchase_cnt_90>=2,uid,null))/count(distinct uid),3)
as repurchase_rate #第二步：计算复购率
from
(
select product_id,uid,
count(if(event_time>=date_sub((select max(event_time) from tb_order_overall),interval 89 day),od.order_id,null)) as purchase_cnt_90
from tb_order_detail as od
inner join
(select order_id,uid,date(event_time) as event_time from tb_order_overall) as oo 
on od.order_id=oo.order_id
where 
od.product_id in(select product_id from tb_product_info where tag='零食')
group by product_id,uid 
) as od1 #第一步：计算零食类产品、用户及对应90天内购买产品的数量
group by product_id
order by repurchase_rate desc,product_id asc
limit 3;#第三步：选取top3

with t1 as(
select order_id,uid,float_num,
    case
        when date(event_time) >= min_date then 1
        else 0
    end flag_date
from
(select order_id,uid,event_time,date_sub(date(max(event_time) over()),interval 89 day) min_date,
        case
            when status = 0 then 0
            when status = 1 then 1
            else -1
        end float_num
from tb_order_overall) oo
)
select product_id, round(sum(if(order_num>=2,1/order_num,0))/max(dr),3) repurchase_rate
from 
(
select od.product_id,uid,float_num,flag_date,sum(if(flag_date=1,float_num,0)) over(partition by product_id,uid) order_num,dense_rank() over(partition by product_id order by uid) dr
from t1
join tb_order_detail od
on t1.order_id = od.order_id
join (select product_id from tb_product_info where tag = '零食') pi
on od.product_id = pi.product_id
) t2
group by product_id
order by repurchase_rate desc
limit 3

#  我麻了，线上测试通过，一提交就错，理了一下午，还是过不去，小伙伴们帮忙看看呗
# 第一步
WITH t0 AS
(
SELECT
    t1.product_id,t2.uid
    ,COUNT(*) AS uid_pay_cnt -- 用户付款次数
    ,IF(COUNT(*)>=2,1,0) AS fguid-- 是否复购用户
FROM tb_order_detail t1-- 订单明细表
    LEFT JOIN tb_order_overall t2 ON t1.order_id = t2.order_id-- 订单总表
    LEFT JOIN tb_product_info t3 ON t1.product_id= t3.product_id-- 商品信息表
WHERE
    -- 近90天
    t2.event_time >= DATE_SUB((SELECT MAX(event_time) FROM tb_order_detail),INTERVAL 89 DAY)
    -- 筛选'零食'&剔除退款记录
    AND t3.tag='零食' AND t2.status != 2
GROUP BY t1.product_id,t2.uid -- “计算字段”发生的层次
)
# 第二步

SELECT
    product_id
    ,ROUND(SUM(fguid) / COUNT(uid),3) AS repurchase_rate
FROM t0
GROUP BY product_id
ORDER BY repurchase_rate DESC,product_id
LIMIT 3

with base as
(select uid,product_id from tb_product_info
join tb_order_detail using(product_id)
join tb_order_overall using(order_id)
where status=1
and tag='零食'
and timestampdiff(day,event_time,(select max(event_time) from tb_order_overall)) between 0 and 89)

select t1.product_id,ifnull(round(cnt_u/cnt_p,3),0) repurchase_rate from 
(select product_id,count(distinct uid) cnt_p from base group by product_id) t1
left join
(select product_id,uid,count(distinct uid) cnt_u from base group by product_id,uid
having count(uid)>=2)t2
on t1.product_id=t2.product_id
order by repurchase_rate desc,product_id
limit 3

with new_tab as 
(select * from 
    (select a.uid,a.order_id,b.product_id,a.event_time
    from tb_order_overall a,tb_order_detail b,tb_product_info c 
    where a.order_id=b.order_id and b.product_id=c.product_id
    and a.status=1 and c.tag='零食') t 
left join 
     (select max(event_time) as max_time from tb_order_overall) t2 on 1=1
     where datediff(max_time,event_time) between 0 and 89
)

select t2.product_id,round(if(re_cnt is null,0,re_cnt)/pro_cnt,3) as repurchase_rate
from
    (select product_id,count(distinct uid) as pro_cnt from new_tab group by product_id) t2
left join 
    (select product_id,count(distinct uid) as re_cnt
    from (select uid,product_id,count(product_id)over(partition by uid,product_id) as pur_cnt 
    from new_tab) t where pur_cnt>=2 group by product_id
    ) t1
on t1.product_id=t2.product_id
order by repurchase_rate desc,product_id limit 3

with basic_info as (
    select  
    a.order_id, # 订单号
    # a.cnt, # 订单数量
    c.uid,
    a.product_id,
    row_number()over(partition by c.uid,a.product_id order by c.event_time) as rk
    from tb_order_detail a
    join tb_product_info b
    on a.product_id = b.product_id
    join tb_order_overall c
    on a.order_id =c.order_id
    where b.tag = '零食'
    and status = 1
    and datediff((select date(max(event_time)) from tb_order_overall),date(c.event_time))<90
)
select 
product_id,
round(sum(case when rk=2 then 1 else 0 end)/count(distinct uid),3) as repurchase_rate
from basic_info
group by product_id
order by repurchase_rate desc,product_id asc
limit 3