SQL 解鎖 - ABC 分析 in SQL

ABC 分析

ABC分析（ABC Analysis）是一種基於80/20的分類原則，常用於庫存管理、供應鏈分析和資源分配中。它將產品或項目按其相對重要性分為A、B 和 C三個類別。其目的是協助企業專注於最重要的項目，以提高效率和降低成本。

• A類 佔總價值的大部分（約70–80%），但數量比例較少（約20%）。這類項目對業務運營至關重要，需高度關注，例如高價值商品或核心供應商。
• -B類 佔總價值的次要部分（約15–25%），數量比例適中（約30%）。此類項目的重要性居中，不需要像 A 類那樣頻繁監控，但也不能忽視，定期檢查並優化庫存。
• C類 佔總價值的小部分（約5%），但數量比例最大（約50%）。此類項目價值較低，對整體運營的影響小。簡化管理流程，減少精力投入。

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]

2. 按需求量降序排列產品

Create View [Simulation].[View_002_Ranking] As 
SELECT [Product ID]
, Aggregate_Demand, ROW_NUMBER() OVER (ORDER BY Aggregate_Demand DESC) AS [Rank] 
FROM [Simulation].[View_001_Aggregate_Demand]

3. 計算占總需求的百分比

Create View [Simulation].[View_003_%_Of_Total] As 
select 
[Product ID], 
[Aggregate_Demand], 
[Rank], 
Sum([Aggregate_Demand]) OVER () Total_Demand, 
Cast(Cast([Aggregate_Demand] as numeric(9,2))/ cast(Sum([Aggregate_Demand]) OVER () as numeric(9,2)) as numeric(9,2)) As [%_Of_Total] 
from [Simulation].[View_002_Ranking]

4. 計算累計總量和累計總量百分比

Create View [Simulation].[View_004_Running_Total] As 
select 
[Product ID], [Aggregate_Demand], [Rank], Total_Demand, [%_Of_Total], Sum([Aggregate_Demand]) OVER (ORDER BY [Aggregate_Demand] DESC Rows BETWEEN unbounded preceding AND CURRENT row) [Running_Total], 
CAST(Sum([Aggregate_Demand]) OVER (ORDER BY [Aggregate_Demand] DESC Rows BETWEEN unbounded preceding AND CURRENT row) AS numeric(9,2))/ CAST(Total_Demand AS numeric(9,2)) [Running_Total_%] 
from [Simulation].[View_003_%_Of_Total]

謝謝您花時間將此篇文章讀完，若覺得對您有幫助可以幫忙按個讚、分享來或是珍藏喔!也歡迎Follow我的Threads/ FB，持續追蹤生產力工具、商業分析、商業英文的實用範例，提升自己的職場力喔!