SQL 解鎖 - 模擬揀貨位置

假設公司有5個配送中心(DC)，如下圖，但不是所有商品都存放在所有的配送中心，舉例來說某辦公椅僅能存放在DC3和DC4，因此邏輯上美國東岸的需求會由DC3所配送(距離較近)，西岸的需求會由DC4所配送。請根據上述邏輯使用SQL來模擬這商品在各地的需求會如何被配送、從哪個配送中心配送 (提供大方向即可)?

我們在此篇會使用Tableau的 Superstores 裡的Orders，並會用Microsoft SQL Server 的SQL語法來講解概念。

我們假設所有的訂單都可以從這五個的配送中心直接配送。這五個的配送中心 ZIP code分別是12553, 29172, 60563, 80023, 92551，我們同時列出了經緯度如下表(存於[dbo].DC_ZIP)。

計算距離

要能模擬訂單需求如何配送，我們的第一步是算出這些配送中心每筆訂單和這些配送中心的距離，才能知道哪個配送中心是離終點位置最近且有備存貨。我們使用以下的程式碼先生成一個主表。

SELECT
distinct [Product ID]
, LEFT(A.[Product ID],6) PRODUCT_GROUP
, A.[Order ID] [ORDER_ID]
, A.[Postal Code] DEST_ZIP
, A.[Latitude] DEST_LAT
, A.[Longitude] DEST_LONG
, B.DC_NUMBER
, CAST(B.Zip5 AS INT) SOURCE_ZIP
, B.[Latitude] SOURCE_LAT
, B.[Longitude] SOURCE_LONG
INTO [dbo].[SOURCE_DEST_PRODUCT_COMBO]
FROM [dbo].[Orders] A ,[dbo].[DC_ZIP] B

// SQL小貼士
// 1​
SELECT *
INTO [新資料表]
FROM [現有資料表];

// 2 
// 在FROM裡，如果沒使用JOIN，直接在兩張資料表中加一個","，意味​會回傳兩表所有的可能排列
// ，也可以使用CROSS JOIN產生笛卡兒乘積 (Cartesian product)。

// 3 LEFT(欄位或字串, 左側擷取字符數)
​

增加一個距離欄位。

Alter table [dbo].[SOURCE_DEST_PRODUCT_COMBO]
Add Distance float;

使用經緯度求出直線距離。

Update [dbo].[SOURCE_DEST_PRODUCT_COMBO]
SET Distance =
2*ASIN(SQRT(SIN((3.14*SOURCE_LAT/180.0-3.14*DEST_LAT/180.0)/2)*SIN((3.14*SOURCE_LAT/180.0-3.14*DEST_LAT/180.0)/2)
+COS(3.14*SOURCE_LAT/180.0)*COS(3.14*DEST_LAT/180.0)*SIN((3.14*SOURCE_LONG/180.0-3.14*DEST_LONG/180.0)/2)*SIN((3.14*SOURCE_LONG/180.0-3.14*DEST_LONG/180.0)/2)))*3959

讓我們拿一些數字來驗證一下，

• DC1 ZIP 12553 到某訂單地址 ZIP 42420: 762 英里 vs Google顯示756 英里。

• DC1 ZIP 12553 到某訂單地址 ZIP 90063: 2446 英里 vs Google顯示2445.8英里。

找最近的DC

我們先來假設所有產品在每個配送中心都有存放，那邏輯上就是以最近的配送中心(RANKING = 1) 來揀貨並配送給客戶。

ALTER TABLE [dbo].[Orders]
ADD
SOURCE_DC_NUMBER INT;

UPDATE A
SET SOURCE_DC_NUMBER = B.DC_NUMBER
FROM [dbo].[Orders] A
INNER JOIN
(
SELECT *
FROM
(
SELECT DISTINCT DEST_ZIP
, DC_NUMBER
, DISTANCE
, Row_Number () OVER (PARTITION BY DEST_ZIP ORDER BY DISTANCE ASC) RANKING
FROM [dbo].SOURCE_DEST_PRODUCT_COMBO
) A
WHERE RANKING = 1
) B
ON
A.[Postal Code] = B.DEST_ZIP;

// SQL小貼士

// ROW_NUMBER()
// ROW_NUMBER()回傳不重複的累加序號，必須搭配​ OVER ()告訴SQL要如何產生序號。
// 若​需要分組排序，需在OVER()內再搭配PARTITION BY，這樣SQL會在每組組內產生序號。
// 以上述例子，讓SQL​以目的地的郵遞區號分組，並在每組目的地ZIP code以距離(與配送中心的距離)進行排序。
// => PARTITION BY 目的地的郵遞區號 ORDER BY 與配送中心的距離 ASC
// RANKING = 1 意味回傳最近的配送中心。

為避免過於複雜的狀況，我們假設每個Product Group (產品ID前六碼) 有獨特的存放分布，舉例來說。OFF-AP僅能存放在配送中心1-3。

以這樣的例子，我們要在上述的程式碼加一些條件，除了要找最近的配送中心，還要找裡面有存貨的配送中心。以上述的例子，原本離配送中心4和5最近的訂單會被配送中心3所配送，因此配送中心3對於OFF-AP類的產品備貨要考慮整個西岸的需求。

ALTER TABLE [dbo].[Orders]
ADD
PRODUCT_GROUP NVARCHAR(255)
, SOURCE_DC_NUMBER_2 INT;

UPDATE A
SET PRODUCT_GROUP = LEFT(A.[Product ID],6)
FROM [dbo].[Orders] A;

UPDATE A
SET SOURCE_DC_NUMBER_2 = B.DC_NUMBER
FROM [dbo].[Orders] A
INNER JOIN
(
SELECT *
FROM
(
SELECT DISTINCT PRODUCT_GROUP
, DEST_ZIP
, DC_NUMBER
, DISTANCE
, Row_Number () Over (PARTITION BY PRODUCT_GROUP, DEST_ZIP ORDER BY DISTANCE Asc) RANKING
FROM [dbo].SOURCE_DEST_PRODUCT_COMBO
WHERE STOCKED = 'STOCKED'
) A
WHERE RANKING = 1
) B
ON
A.PRODUCT_GROUP = B.PRODUCT_GROUP
AND
A.[Postal Code] = B.DEST_ZIP;

// SQL小貼士
// 由於加上了每組Product Group存放分布不同，在PARTITION BY後要以Product Group+目的地郵遞區號進行分組
// => PARTITION BY Product Group, 目的地的郵遞區號 ORDER BY 與配送中心的距離 ASC
// 而且沒有存放的組合被納入排序中​。=> WHERE STOCKED = 'STOCKED'
// RANKING = 1 意味回傳最近且有存放此Product Group的配送中心。

遵循著我們上述的思考脈絡，希望能對您思考這類問題有所助益，也謝謝您撥冗完食。這些內容是根據我過去在外商的經驗，非常歡迎業界先進留言與我交流，我也會不定期補充我的觀點，也歡迎Follow我的Threads，持續都會有這類問題討論和生產力提升的點子喔!