利用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 tableITPUB个人空间9G6K-B2a&M$\
USE tempdb
U
@!\9\.?$yJ&t:?0GO

CREATE TABLE Inventory_SnapshotITPUB个人空间J Bj(s:i"n ~kJ
(
"a.{7Uz4Ly0    ProductID    int PRIMARY KEY NOT NULL,ITPUB个人空间R/PjGe PS6p
    Quantity    int NOT NULL
'r!|Cv0J:uI*M0)ITPUB个人空间g"A ms\`
AHRg
GO

CREATE TABLE Inventory_Operation
6h?Z}?Gay L0(ITPUB个人空间2eE"u` v"V#a L
    OperationID        int PRIMARY KEY    IDENTITY(1,1),
5RN qGP0    OperationDate    datetime,ITPUB个人空间)VK1BGN-d{*XX
    OperationType    int,    --1:Move in; 2:Move out; 3:Adjustment
!K'QOy)A6s4n\0    ProductID        int,
0@Dm/gu0JZ4b}0    Quantity        intITPUB个人空间n A/|+ZW;s)d
)ITPUB个人空间3V4B1_|N$\
GO

--Step 2: Create stored procedure for new inventory operation
7^ |,~,{N-K(u5p;J0CREATE PROCEDURE usp_Inventory_OperationITPUB个人空间1PaQ9d];E ly
    @productID int,ITPUB个人空间co/PW7o%f(A q;k
    @operationDate datetime,
^h}9Xx"o0    @operationType int, --1:Move in; 2:Move out; 3:Adjustment
E5K]:O
_&B0    @quantity    int
"hM!t9g)a(`p0AS
7jc*^Df{1Y2}9?1D
^0INSERT INTO Inventory_Operation
'B/u*y)a'b0    (OperationDate, OperationType, ProductID, Quantity)ITPUB个人空间z,J
GD
e*K2G&n
VALUES
4S-r/S%Tz m e2B0    (@operationDate, @operationType, @productID, @quantity)
.Q:X!jX#^Jr~0GO

--Step 3: Create stored procedure for inventory snapshot calculation
(}Q.X$X2p0~$dSA0CREATE PROCEDURE usp_Inventory_Snapshot_Process
-`-M8ZC^0    @processDate datetime
%dQ%kWUZV@0ASITPUB个人空间0JGl6W/Z#X
MERGE Inventory_Snapshot AS invsITPUB个人空间,]:R0hjf.M!rM
USING (SELECT ProductID, Sum(ABSQuantity) AS SubTotal
OSg M*Kh0        FROM (SELECT ProductID, Quantity  *
-a'xv7]jv0                    CASE OperationType --1:Move in; 2:Move out; 3:AdjustmentITPUB个人空间 o8c]0Ly2T5T*T'iN$|
                        WHEN 1 THEN 1
,`e0S8D-a.Z'V0                        WHEN 2 THEN -1ITPUB个人空间3x%K[!W ~g8J
                        WHEN 3 THEN 1
VA
_*E6a.IH0qAAM0                        ELSE 0
;zp0Y^[0                    END AS ABSQuantity FROM Inventory_OperationITPUB个人空间'V#m{/u7S$c;m0pa
                WHERE perationDate = @processDate) AggInvo       ITPUB个人空间,S$X4t2SJ$wE1g$DQ p
        GROUP BY AggInvo.ProductID)ITPUB个人空间5W3Jro2C-l(X6FM
    AS invo(ProductID, SubTotal)
j2`,S uR-R%{r's0ON (invs.ProductID = invo.ProductID)
tG{9\L
mv5WJ%xX0WHEN MATCHED AND invs.Quantity <> invo.SubTotal AND invs.Quantity <> invo.SubTotal * -1ITPUB个人空间V7]ETkM8m
    THEN UPDATE SET invs.Quantity = invs.Quantity + invo.SubTotalITPUB个人空间8CF3~1rc.t5QEed
WHEN MATCHED AND invs.Quantity = invo.SubTotal * -1ITPUB个人空间 C5}_!Dv[-K/i!p nK8J
    THEN DELETE
%hz Ro Ez&Y0WHEN TARGET NOT MATCHED
8p4v)kch0    THEN INSERT VALUES (invo.ProductID, invo.SubTotal);ITPUB个人空间8l[y}`Tg$n~7z
GO

DELETE FROM dbo.Inventory_SnapshotITPUB个人空间`BdCG6[j9M
--Step 4: Test application logic
7x{^;_ y Ha[Kr0--2007-1-1
*wX{3NV)n0EXEC usp_Inventory_Operation 1000, '2007-1-1', 1, 500ITPUB个人空间t:s5T%Qj
EXEC usp_Inventory_Operation 1001, '2007-1-1', 1, 300
"\? ? cq~1E+CS0EXEC usp_Inventory_Operation 1002, '2007-1-1', 1, 250

EXEC usp_Inventory_Snapshot_Process '2007-1-1'

SELECT * FROM Inventory_SnapshotITPUB个人空间'O H?Aq A;H
GO

EXEC usp_Inventory_Operation 1001, '2007-1-2', 2, 200
,J-Y^R,\9x0EXEC usp_Inventory_Operation 1003, '2007-1-2', 1, 300ITPUB个人空间.] upX1gX5d
EXEC usp_Inventory_Operation 1000, '2007-1-2', 2, 200

EXEC usp_Inventory_Snapshot_Process '2007-1-2'

SELECT * FROM Inventory_SnapshotITPUB个人空间9{'W9N%v'PoTr
GO

EXEC usp_Inventory_Operation 1000, '2007-1-3', 2, 200ITPUB个人空间yd#Ovc?
G I SL
EXEC usp_Inventory_Operation 1002, '2007-1-3', 2, 250
%K'OBftB(u3K4S*lr
b0EXEC usp_Inventory_Operation 1004, '2007-1-3', 2, 300

EXEC usp_Inventory_Snapshot_Process '2007-1-3'

SELECT * FROM Inventory_Snapshot
c f'nK K0GO

最后需要说明的是，通常DBA或者开发员都会认为交易型记录一旦写入数据库后就不会修改，但实际上不是的。我就在一家大型物流公司中见过某些库存操作会找一条记录直接更新(实际上是锁仓操作，这种设计的初衷可能是考虑到锁仓操作非常频繁的缘故吧，不管怎么样，直接更新交易型记录的情况确实存在)。

就像上面的范例代码一样，如果我们对2007年1月2日的进出仓明细记录执行过了usp_Inventory_Snapshot_Process处理后2007年1月2日的交易记录又被修改了，这个时候怎么办呢？......这或许就是数据库架构设计迷人之处吧......