荣威销售额面试题

partition by 与 group by 的联合使用， partition在计算单度量值时可以对group取得的表格进行分区，这种情况一般group by 多个字段；

老师的：

select *

from

(select

store_id,

item_id,

sum(sales) as 商品销售额,

sum(sum(sales)) over(partition by store_id order by sum(sales) desc) as 累积销售额,

sum(sum(sales)) over(partition by store_id) as 门店总销售,

sum(sum(sales)) over(partition by store_id order by sum(sales) desc)/sum(sum(sales)) over(partition by store_id) as 累积销售额占比

from store_sales_info

group by store_id,item_id) as t

where 累积销售额占比<=0.7;

我的是错的：

select store_id, item_id

from

(select store_id, item_id,

sum(sales) over(partition by store_id order by sales desc) 各门店累计销售额,

sum(sales) over(partition by store_id) 各门店总销售额,

sum(sales) over(partition by store_id order by sales desc)/ sum(sales) over(partition by store_id) 累计占比

from store_sales_info) t

where 累计占比<0.7; -- 此做法错误，忽略了产品类别与金额累计之间的关系，累计应该是以类目的金额累计的；