SQL 解鎖: ABC 分析 in SQL

目的：
為了決定產品的庫存策略，我們將首先檢視產品層級的需求量，並將產品分類為幾個組別。

數據：
按產品與地點層級的年度需求量。

SELECT 
A.[Product ID], 
A.Demand Demand_Location_1, 
B.Demand Demand_Location_2, 
C.Demand Demand_Location_3, 
D.Demand Demand_Location_4, 
E.Demand Demand_Location_5 
FROM (Select * from [Simulation].[001_Product_Loc_Demand] 
Where [Location ID] = 1 ) A 
Inner join (Select * from [Simulation].[001_Product_Loc_Demand] 
Where [Location ID] = 2 ) B 
On A.[Product ID] = B.[Product ID] 
Inner join (Select * from [Simulation].[001_Product_Loc_Demand] Where [Location ID] = 3 ) C 
On A.[Product ID] = C.[Product ID] 
Inner join (Select * from [Simulation].[001_Product_Loc_Demand] Where [Location ID] = 4 ) D 
On A.[Product ID] = D.[Product ID] 
Inner join (Select * from [Simulation].[001_Product_Loc_Demand] Where [Location ID] = 5 ) E 
On A.[Product ID] = E.[Product ID];

步驟

1. 匯總需求至產品層級:

Create View [Simulation].[View_001_Aggregate_Demandl] As Select [Product ID], Sum(Demand) Aggregate_Demand from [Simulation].[001_Product_Loc_Demand] Group by [Product ID]