8、产品贡献定量分析

找到产生购买行为的类别类目

按照类目分组

产品贡献定量分析（帕累托分析）(累积销售额百分比=累积销售额/总销售额)

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;#报错：having子句中不能使用开窗函数

根据顺序先取出表，然后分组聚合运算，having进行筛选，但是筛选的组里面没有开窗函数这个筛选条件，无法做判断；只能再加where子句判断；

累计一定用到开窗函数，order by累计值，不写order by是计算所有值。

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;