这个相对于留存分析等相对简单，附代码

其中topn是常用的指标分析，而第一段的having是为了防止分母为0，转化率为null

# 查询每个商品的浏览量、成交量、转化率
select  item_id,
        sum(if(behavior_type='pv',1,0)) as 浏览量,
      sum(if(behavior_type='buy',1,0)) as 成交量,
     sum(if(behavior_type='buy',1,0))/sum(if(behavior_type='pv',1,0)) as 转化率
from userbehavior_new
group by item_id
having sum(if(behavior_type='pv',1,0)) and sum(if(behavior_type='buy',1,0)) >0;
# 爆款商品：流量top10
select item_id,
       sum(if(behavior_type = 'pv',1,0)) as 流量,
       row_number()over(order by sum(if(behavior_type = 'pv',1,0)) desc) as 排名
from userbehavior_new
group by item_id;
# 畅销商品：成交top10
select item_id,
       sum(if(behavior_type = 'buy',1,0)) as 流量,
       row_number()over(order by sum(if(behavior_type = 'buy',1,0)) desc) as 排名
from userbehavior_new
group by item_id;