榮威銷售額面試題

partition by 與 group by 的聯(lián)合使用， partition在計算單度量值時可以對group取得的表格進行分區(qū)，這種情況一般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; -- 此做法錯誤，忽略了產(chǎn)品類別與金額累計之間的關(guān)系，累計應(yīng)該是以類目的金額累計的；