SQL 解鎖 - 程式碼解析 1

請以口語、白話形式解析以下SQL程式碼並描繪你認為的原資料表([Database].[Schema].[Table] )結構樣貌。

SELECT  
A.[Business_Unit],
A.[Order_Number] ,  
SUM([Sales_Units]) as [Sales_Units],

SUM([Delivery_Expense_Carrier_Mode_1]) as [Delivery_Expense_Carrier_Mode_1],
SUM([Delivery_Expense_Carrier_Mode_2]) as [Delivery_Expense_Carrier_Mode_2],
SUM([Delivery_Expense_Carrier_Mode_3]) as [Delivery_Expense_Carrier_Mode_3],

SUM(CASE WHEN [Fulfillment_Type] IN ('Fulfillment_Center')  THEN [Sales_Units] ELSE 0 END) AS [Sales_Units_Fullfillment_Center],
SUM(CASE WHEN [Fulfillment_Type] IN ('Fulfillment_Center')  THEN [Delivery_Expense_Total] ELSE 0 END) AS [Delivery_Expense_Fullfillment_Center],
		
		SUM(CASE WHEN [Fulfillment_Type] IN ('Vendor_Dropship')  THEN [Sales_Units] ELSE 0 END) AS [Sales_Units_Vendor_Dropship],
SUM(CASE WHEN [Fulfillment_Type] IN ('Vendor_Dropship')  THEN [Delivery_Expense_Total] ELSE 0 END) AS [Delivery_Expense_Vendor_Dropship],
		
		SUM(CASE WHEN  ([Delivery_Expense_Carrier_Mode_1] < 0)  THEN [Sales_Units] ELSE 0 END) AS [Sales_Units_Mode_1],
SUM(CASE WHEN  ([Delivery_Expense_Carrier_Mode_1] < 0)  THEN [Delivery_Expense_Total] ELSE 0 END) AS [Delivery_Expense_Mode_1],

SUM(CASE WHEN  ([Delivery_Expense_Carrier_Mode_2] < 0)  THEN [Sales_Units] ELSE 0 END) AS [Sales_Units_Mode_2],
SUM(CASE WHEN  ([Delivery_Expense_Carrier_Mode_2] < 0)  THEN [Delivery_Expense_Total] ELSE 0 END) AS [Delivery_Expense_Mode_2],

SUM(CASE WHEN  ([Delivery_Expense_Carrier_Mode_3] < 0)  THEN [Sales_Units] ELSE 0 END) AS [Sales_Units_Mode_3],
SUM(CASE WHEN  ([Delivery_Expense_Carrier_Mode_3] < 0)  THEN [Delivery_Expense_Total] ELSE 0 END) AS [Delivery_Expense_Mode_3],

FROM [Database].[Schema].[Table] AS A

WHERE [Fiscal_Year] = 2024
GROUP BY 
A.[Business_Unit],
A.[Order_Number] ;

閱讀題目分隔區

參考解析

這段 SQL 程式碼的目的是撈取、分析每個業務單位的每筆訂單銷售數量&物流運費狀況，並根據不同的配送類型、渠道，進行彙總統計。

以下是詳細解釋 -

▌篩選條件 & 資料粒度

從資料表中篩選出「2024財務年度」的訂單數據，並以業務單位 (Business Unit) 和 訂單編號(Order Number) 層級呈現。

▌統計欄位

- 總運費 (Delivery Expense)：將運費按三種不同的配送營運商 (Carrier Mode 1, 2, 3) 分別統計。

- 總銷售量 (Sales Units)：計算每筆訂單的銷售數量總和。

▌分類統計

除了總體數據外，還對配送類型、渠道與特殊情況進行了細分統計

1. 按配送渠道分類

- 由物流中心(Fulfillment Center)端出貨 的銷售量和總運費。

- 由廠商(Vendor Dropship) 端出貨的銷售量和總運費。

2. 按物流商(模式)分類 - 原表結構測: 原表可能為訂單

-產品(或更細)層級，每個產品可能由不同物流商以不同模式配送，且使用了某一 物流 商或模式就無法使用其他物流商或模式。

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