8、產(chǎn)品貢獻(xiàn)定量分析

找到產(chǎn)生購買行為的類別類目

按照類目分組

產(chǎn)品貢獻(xiàn)定量分析（帕累托分析）(累積銷售額百分比=累積銷售額/總銷售額)

select

item_category,

sum(amount) as 銷售額,

sum(sum(amount)) over(order by sum(amount) desc) as 累積銷售額,

sum(sum(amount)) over() as 總銷售額,

sum(sum(amount)) over(order by sum(amount) desc)/sum(sum(amount)) over() as 累積銷售額百分比

from userbehavior_new

where behavior_type='buy'

group by item_category

having sum(sum(amount)) over(order by sum(amount) desc)/sum(sum(amount)) over()<=0.8;#報(bào)錯(cuò)：having子句中不能使用開窗函數(shù)

根據(jù)順序先取出表，然后分組聚合運(yùn)算，having進(jìn)行篩選，但是篩選的組里面沒有開窗函數(shù)這個(gè)篩選條件，無法做判斷；只能再加where子句判斷；

累計(jì)一定用到開窗函數(shù)，order by累計(jì)值，不寫order by是計(jì)算所有值。

select *

from

(select

item_category,

sum(amount) as 銷售額,

sum(sum(amount)) over(order by sum(amount) desc) as 累積銷售額,

sum(sum(amount)) over() as 總銷售額,

sum(sum(amount)) over(order by sum(amount) desc)/sum(sum(amount)) over() as 累積銷售額百分比

from userbehavior_new

where behavior_type='buy'

group by item_category) as t

where 累積銷售額百分比<=0.8;