-- 3.2.3 每日瀏覽—加購—購買的轉(zhuǎn)化率

select

日期,

sum(if(behavior_type='pv',用戶數(shù),0)) as 瀏覽人數(shù),

sum(if(behavior_type='cart',用戶數(shù),0)) as 加購人數(shù),

sum(if(behavior_type='buy',用戶數(shù),0)) as 購買人數(shù),

sum(if(behavior_type='cart',用戶數(shù),0))/sum(if(behavior_type='pv',用戶數(shù),0)) as 瀏覽_加購轉(zhuǎn)化率,

sum(if(behavior_type='buy',用戶數(shù),0))/sum(if(behavior_type='cart',用戶數(shù),0)) as 加購_購買轉(zhuǎn)化率

from

(select 日期,behavior_type,count(distinct user_id) as 用戶數(shù)

from UserBehavior_new

where behavior_type in ('pv','cart','buy')

group by 日期,behavior_type) as t

group by 日期;