這個相對于留存分析等相對簡單，附代碼

其中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;