利用MERGE重新整理庫存快照

---

SQL Server 2008中有一個新的語法叫做MERGE，這種語法可以融合UPDATE、DELETE和INSERT。特別適合於將交易型的記錄集合併到快照性的結果集中去。非常具有代表性的應用場景就是庫存管理，庫存管理應用(俗稱進銷存)中經常需要獲得某個時間點上的庫存，也稱為庫存結餘。

討論一下一些基礎的東西，在資料庫建模中有兩種基本模型：

1. 純交易事務型：這種方法主要是在一張交易表中記錄下所有商品的進出倉記錄，然後根據所有的進出倉詳細記錄來計算得出庫存結餘。這種方法的優勢就在於它儲存了所有的交易明細記錄，所以理論上面可以計算出過往任何一個時間點上的結餘庫存(只能說是理論上的，因為通常情況結餘庫存的計算不一定僅僅按照SKU，還可能根據品類、部門等其他因素，而這些資訊一般都存放在基礎資訊表中。因此如果要追溯以往的庫存結餘，有可能需要這些基礎資訊表也能夠追溯歷史，其實這也就是我們經常在資料倉儲中提到的維度變化問題。)。這種方法的缺點在於如果要獲得庫存結餘則需要進行大量的聚合工作，所以會在庫存結餘查詢的效能方面面臨挑戰，特別是需要查詢實時的庫存結餘時(儘管這種需求在庫存管理應用中並不多見)。
2. 純時點快照型：直接建立一張庫存結餘表，每次有進出倉操作的時候，直接更新庫存結餘表，因此這張庫存結餘表裡面的資料實際上就是當前的庫存結餘。這種方法的優勢非常明顯，就是查詢當前庫存的速度會非常快。當然這種方法的缺點也非常明顯——由於這種方法丟棄了交易明細記錄，因此要想追溯歷史庫存的話比較麻煩。(當然也是有辦法的，資料倉儲中有一種方法就是定期快照，也就是每隔一段時間存檔這個時間點上的庫存，當然這種方法需要平衡兩個因素，一是對儲存空間的消耗，二是快照的連續性，存檔越頻繁快照就越連續，儲存空間就會消耗的越厲害。通常這種頻率是由企業對資料分析的需求決定的，根據庫存商品的流動特性一般會在一小時到一週不等。)這種方法最致命的缺定還不在這兒，最為困擾DBA的問題應該是這種模型對應用併發能力的影響。由於所有進出倉操作都需要更新庫存結餘表，所以庫存結餘表會成為資料應用的邏輯瓶頸。

因此一般我們都會用混合模型，為了保證歷史的可追溯，進出倉的事務明細是一定要保留的(至少在一段時間內)，而為了滿足對庫存結餘查詢的及時性和效能則需要維護一張快照表，並且保證定期更新這張快照表。為了保證效能並滿足應用的邏輯彈性，這些混和模型的設計是相當重要的，也就是如何更新快照表，不過在這裡我們就不多說了。

在評估SQL Server 2008的過程中，我把SQL Server 2008聯機叢書裡面的程式碼給改了一下，一是更加貼和應用的實際情況，二是聯機叢書的MERGE範例只合並了UPDATE和DELETE，我多加了一個INSERT的情況。:)

在這裡和大家共享一下修改後的程式碼：

--Step 1: Create test table
USE tempdb
GO

CREATE TABLE Inventory_Snapshot
(
    ProductID    int PRIMARY KEY NOT NULL,
    Quantity    int NOT NULL
)
GO

CREATE TABLE Inventory_Operation
(
    OperationID        int PRIMARY KEY    IDENTITY(1,1),
    OperationDate    datetime,
    OperationType    int,    --1:Move in; 2:Move out; 3:Adjustment
    ProductID        int,
    Quantity        int
)
GO

--Step 2: Create stored procedure for new inventory operation
CREATE PROCEDURE usp_Inventory_Operation
    @productID int,
    @operationDate datetime,
    @operationType int, --1:Move in; 2:Move out; 3:Adjustment
    @quantity    int
AS
INSERT INTO Inventory_Operation
    (OperationDate, OperationType, ProductID, Quantity)
VALUES
    (@operationDate, @operationType, @productID, @quantity)
GO

--Step 3: Create stored procedure for inventory snapshot calculation
CREATE PROCEDURE usp_Inventory_Snapshot_Process
    @processDate datetime
AS
MERGE Inventory_Snapshot AS invs
USING (SELECT ProductID, Sum(ABSQuantity) AS SubTotal
        FROM (SELECT ProductID, Quantity  *
                    CASE OperationType --1:Move in; 2:Move out; 3:Adjustment
                        WHEN 1 THEN 1
                        WHEN 2 THEN -1
                        WHEN 3 THEN 1
                        ELSE 0
                    END AS ABSQuantity FROM Inventory_Operation
                WHERE perationDate = @processDate) AggInvo       
        GROUP BY AggInvo.ProductID)
    AS invo(ProductID, SubTotal)
ON (invs.ProductID = invo.ProductID)
WHEN MATCHED AND invs.Quantity <> invo.SubTotal AND invs.Quantity <> invo.SubTotal * -1
    THEN UPDATE SET invs.Quantity = invs.Quantity + invo.SubTotal
WHEN MATCHED AND invs.Quantity = invo.SubTotal * -1
    THEN DELETE
WHEN TARGET NOT MATCHED
    THEN INSERT VALUES (invo.ProductID, invo.SubTotal);
GO

DELETE FROM dbo.Inventory_Snapshot
--Step 4: Test application logic
--2007-1-1
EXEC usp_Inventory_Operation 1000, '2007-1-1', 1, 500
EXEC usp_Inventory_Operation 1001, '2007-1-1', 1, 300
EXEC usp_Inventory_Operation 1002, '2007-1-1', 1, 250

EXEC usp_Inventory_Snapshot_Process '2007-1-1'

SELECT * FROM Inventory_Snapshot
GO

EXEC usp_Inventory_Operation 1001, '2007-1-2', 2, 200
EXEC usp_Inventory_Operation 1003, '2007-1-2', 1, 300
EXEC usp_Inventory_Operation 1000, '2007-1-2', 2, 200

EXEC usp_Inventory_Snapshot_Process '2007-1-2'

SELECT * FROM Inventory_Snapshot
GO

EXEC usp_Inventory_Operation 1000, '2007-1-3', 2, 200
EXEC usp_Inventory_Operation 1002, '2007-1-3', 2, 250
EXEC usp_Inventory_Operation 1004, '2007-1-3', 2, 300

EXEC usp_Inventory_Snapshot_Process '2007-1-3'

SELECT * FROM Inventory_Snapshot
GO

最後需要說明的是，通常DBA或者開發員都會認為交易型記錄一旦寫入資料庫後就不會修改，但實際上不是的。我就在一家大型物流公司中見過某些庫存操作會找一條記錄直接更新(實際上是鎖倉操作，這種設計的初衷可能是考慮到鎖倉操作非常頻繁的緣故吧，不管怎麼樣，直接更新交易型記錄的情況確實存在)。

就像上面的範例程式碼一樣，如果我們對2007年1月2日的進出倉明細記錄執行過了usp_Inventory_Snapshot_Process處理後2007年1月2日的交易記錄又被修改了，這個時候怎麼辦呢？......這或許就是資料庫架構設計迷人之處吧......

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9079672/viewspace-159336/，如需轉載，請註明出處，否則將追究法律責任。