-- 練習(xí)（榮威）：查詢每一家門店銷售額從高到低累積占比前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 商品的累積銷售額,

#當(dāng)over中指定了排序,但是沒有指定滑動窗口范圍時,默認(rèn)計算當(dāng)前分區(qū)內(nèi)第一行到當(dāng)前行(排序字段)取值范圍內(nèi)的記錄

sum(sum(sales)) over(partition by store_id) as 門店總銷售額,

#當(dāng)over中沒有指定排序和滑動窗口范圍時,默認(rèn)計算當(dāng)前分區(qū)內(nèi)的所有記錄

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;

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