利用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个人空间,z S'ju"d-NSU1b&\ y
USE tempdb
}Yh4N8o^`4s0\7590422GO

CREATE TABLE Inventory_Snapshot
4YW1[H#eV*Z7590422(ITPUB个人空间;XuE|0?L m.z'BB
    ProductID    int PRIMARY KEY NOT NULL,
ixe0JR)cd2v7590422    Quantity    int NOT NULLITPUB个人空间0]6hna
fp(|,w
)
@ J6tn2kk;c3K;c&a7590422GO

CREATE TABLE Inventory_Operation

a%En N1T9@ A7590422(
@yZ4lj9X7590422    OperationID        int PRIMARY KEY    IDENTITY(1,1),ITPUB个人空间Po zq5[ Y&@
    OperationDate    datetime,
H;BRI,I)Y,nT4_7590422    OperationType    int,    --1:Move in; 2:Move out; 3:AdjustmentITPUB个人空间8d6{0s7q&dVM1^`
    ProductID        int,
oXyO@"q7590422    Quantity        intITPUB个人空间oY.E9X4u
f
)
4}'}DO&nEEF7590422GO

--Step 2: Create stored procedure for new inventory operation
5YQ0{$EN w+\7590422CREATE PROCEDURE usp_Inventory_Operation
T+dj,Cyv&r7590422    @productID int,ITPUB个人空间t%ri3^)L`%L@
    @operationDate datetime,ITPUB个人空间
hi5Y8C _:w%U
    @operationType int, --1:Move in; 2:Move out; 3:AdjustmentITPUB个人空间2[*?
u;B.]6Y
t
    @quantity    int
#t0P+^.ccj~H7590422ASITPUB个人空间'W \4XwGE
INSERT INTO Inventory_Operation
:UG#Oqv0a7590422    (OperationDate, OperationType, ProductID, Quantity)
!FX+d;E]9x o(Il+~7590422VALUESITPUB个人空间9M$z y qO/C,sB
?
    (@operationDate, @operationType, @productID, @quantity)ITPUB个人空间u7\:pHU/T L
GO

--Step 3: Create stored procedure for inventory snapshot calculation
#R2w-B)R^[pig7590422CREATE PROCEDURE usp_Inventory_Snapshot_ProcessITPUB个人空间C-cN-pf}2U}$lT
    @processDate datetime
#Gc4Uv!]0Z;j)I7590422AS
_!N8y2ti W3]#}7590422MERGE Inventory_Snapshot AS invs
`|w"k2x7590422USING (SELECT ProductID, Sum(ABSQuantity) AS SubTotal
5YYK7|rd7590422        FROM (SELECT ProductID, Quantity  *
&f9fQ9]uHh~7590422                    CASE OperationType --1:Move in; 2:Move out; 3:Adjustment
N6xv8@J7u7590422                        WHEN 1 THEN 1ITPUB个人空间qipXJ,S{ b6O
                        WHEN 2 THEN -1
LYO O_7590422                        WHEN 3 THEN 1
}S9J M+F&H|0e7590422                        ELSE 0
t C!j7u1_.B7590422                    END AS ABSQuantity FROM Inventory_Operation
|*|y@'{{9LZ7590422                WHERE perationDate = @processDate) AggInvo       
'@/c/`7SB7590422        GROUP BY AggInvo.ProductID)
n*{y.uQ,u%``7590422    AS invo(ProductID, SubTotal)ITPUB个人空间;XuQ4l,P%|uSh
ON (invs.ProductID = invo.ProductID)
Bw@#w_ ^!o)^7590422WHEN MATCHED AND invs.Quantity <> invo.SubTotal AND invs.Quantity <> invo.SubTotal * -1ITPUB个人空间+{*jA
hU$wa Q@NL.~V*a
    THEN UPDATE SET invs.Quantity = invs.Quantity + invo.SubTotal
C'd7bwqbM7590422WHEN MATCHED AND invs.Quantity = invo.SubTotal * -1
&b^I&]9X_6k7590422    THEN DELETEITPUB个人空间)S.ugr+|/g
WHEN TARGET NOT MATCHED
[H@4wx`4a`.}7590422    THEN INSERT VALUES (invo.ProductID, invo.SubTotal);ITPUB个人空间 H~ \ R5`1g2zo
GO

DELETE FROM dbo.Inventory_SnapshotITPUB个人空间/a~/a9`#Bu
--Step 4: Test application logicITPUB个人空间#O;~I-m3vD"K8k
--2007-1-1ITPUB个人空间&eTO.{gNI6X)w-Ts
EXEC usp_Inventory_Operation 1000, '2007-1-1', 1, 500ITPUB个人空间w!U
c1a9Hn7M(PX
EXEC usp_Inventory_Operation 1001, '2007-1-1', 1, 300
ogdH5z3mZf
kb8m7590422EXEC usp_Inventory_Operation 1002, '2007-1-1', 1, 250

EXEC usp_Inventory_Snapshot_Process '2007-1-1'

SELECT * FROM Inventory_Snapshot
rGP9?/Sz7590422GO

EXEC usp_Inventory_Operation 1001, '2007-1-2', 2, 200
k+jF?7{0WA$c7590422EXEC usp_Inventory_Operation 1003, '2007-1-2', 1, 300
YS0o5i&nB4BU7590422EXEC usp_Inventory_Operation 1000, '2007-1-2', 2, 200

EXEC usp_Inventory_Snapshot_Process '2007-1-2'

SELECT * FROM Inventory_SnapshotITPUB个人空间6|^G3n)p
GO

EXEC usp_Inventory_Operation 1000, '2007-1-3', 2, 200
0wqA%_No)o
t7590422EXEC usp_Inventory_Operation 1002, '2007-1-3', 2, 250ITPUB个人空间K"KX#`2Sr9R \8M2@
EXEC usp_Inventory_Operation 1004, '2007-1-3', 2, 300

EXEC usp_Inventory_Snapshot_Process '2007-1-3'

SELECT * FROM Inventory_Snapshot
Iy/P rY'{(P7590422GO

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

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