技巧: max(event_time) over() 能省略单独写个子查询去查询最大日期; 将购买日期和最大日期比较, 能很容易的获取到购买日期小于90天的用户id select od.product_id, oo.uid,
date(oo.event_time),
# max(oo.event_time) over() 全局分组可求得最大日期, 记为当天
# 使用datediff将最大日期和购买日期比较, 得到购买日期小于90天的用户id
if(datediff(date(max(oo.event_time) over()), date(oo.event_time)) < 90, oo.uid, null) as days
from tb_order_detail od
left join tb_order_overall oo on od.order_id=oo.order_id
left join tb_product_info pi on od.product_id=pi.product_id
where pi.tag='零食'
技巧: 通过count(*) over(partition by t1.product_id, t1.days) 组内根据product_id, t1.days分组, 得到每款商品, 每个用户90天的购买次数 select t1.product_id, t1.uid,
# 根据上述得到购买日期小于90天的用户id, 再次使用窗口函数, 根据t1.product_id, t1.days分组,
# 进行count(*)统计, 得到number, 如果number大于2则表明, 该用户在90天内购买过至少2次
count(*) over(partition by t1.product_id, t1.days) number
from (
select od.product_id, oo.uid,
date(oo.event_time),
if(datediff(date(max(oo.event_time) over()), date(oo.event_time)) < 90, oo.uid, null) as days
from tb_order_detail od
left join tb_order_overall oo on od.order_id=oo.order_id
left join tb_product_info pi on od.product_id=pi.product_id
where pi.tag='零食'
) t1
来, 感受OLAP分析函数的强大吧!
select t2.product_id,
round(count(distinct if(number < 2, null, t2.uid)) / count(distinct t2.uid), 3) as repurchase_rate
from (
select t1.product_id,
t1.uid,
count(*) over(partition by t1.product_id, t1.days) number
from (
select od.product_id,
oo.uid,
date(oo.event_time),
if(datediff(date(max(oo.event_time) over()), date(oo.event_time)) < 90, oo.uid, null) as days
from tb_order_detail od
left join tb_order_overall oo on od.order_id=oo.order_id
left join tb_product_info pi on od.product_id=pi.product_id
where pi.tag='零食'
) t1
) t2
group by t2.product_id
order by repurchase_rate desc, product_id
limit 3