# 查询每个商品的浏览量、成交量、转化率
select * from userbehavior_new;
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;

having防止分母是0，转化率为null,topn是指标分析常用