扫码加入数据分析学习群

-- 练习（荣威）：查询每一家门店销售额从高到低累积占比前70%的商品

create table store_sales_info(

id int primary key,

store_id int,

order_id int,

item_id int,

sales decimal(10,2),

qty int

);

insert into store_sales_info values

(1,1001,6543,6557432,120,2),

(2,1001,6543,6557432,60,1),

(3,1001,6543,6556551,200,2),

(4,1001,6544,6556551,300,3),

(5,1001,6544,6556551,200,2),

(6,1001,6545,6556543,100,5),

(7,1001,6545,6556543,60,3),

(8,1002,6545,6556543,20,1),

(9,1002,6545,6558125,180,6),

(10,1002,6546,6558125,30,1),

(11,1002,6546,6554123,30,3),

(12,1002,6546,6554123,50,5),

(13,1003,6721,6554123,80,8),

(14,1003,6721,6553218,80,2),

(15,1003,6721,6553218,120,3),

(16,1003,6722,6553218,80,2),

(17,1003,6722,6556496,120,1);

select * from store_sales_info;

select *

from

(select

store_id,

item_id,

sum(sales) as 销售额,

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

#当over中指定了排序,但是没有指定滑动窗口范围时,默认计算当前分区内第一行到当前行(排序字段)取值范围内的记录

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

#当over中没有指定排序和滑动窗口范围时,默认计算当前分区内的所有记录

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;

#################################