這個(gè)相對(duì)于留存分析等相對(duì)簡(jiǎn)單，附代碼

其中topn是常用的指標(biāo)分析，而第一段的having是為了防止分母為0，轉(zhuǎn)化率為null

# 查詢每個(gè)商品的瀏覽量、成交量、轉(zhuǎn)化率
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 轉(zhuǎn)化率
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;