利用MERGE刷新库存快照

最新推荐文章于 2024-04-23 19:27:53 发布

cqpj28160

最新推荐文章于 2024-04-23 19:27:53 发布

阅读量516

点赞数

文章标签：数据库

SQL Server 2008中有一个新的语法叫做MERGE，这种语法可以融合UPDATE、DELETE和INSERT。特别适合于将交易型的记录集合并到快照性的结果集中去。非常具有代表性的应用场景就是库存管理，库存管理应用(俗称进销存)中经常需要获得某个时间点上的库存，也称为库存结余。

讨论一下一些基础的东西，在数据库建模中有两种基本模型：

纯交易事务型：这种方法主要是在一张交易表中记录下所有商品的进出仓记录，然后根据所有的进出仓详细记录来计算得出库存结余。这种方法的优势就在于它保存了所有的交易明细记录，所以理论上面可以计算出过往任何一个时间点上的结余库存(只能说是理论上的，因为通常情况结余库存的计算不一定仅仅按照SKU，还可能根据品类、部门等其他因素，而这些信息一般都存放在基础信息表中。因此如果要追溯以往的库存结余，有可能需要这些基础信息表也能够追溯历史，其实这也就是我们经常在数据仓库中提到的维度变化问题。)。这种方法的缺点在于如果要获得库存结余则需要进行大量的聚合工作，所以会在库存结余查询的性能方面面临挑战，特别是需要查询实时的库存结余时(尽管这种需求在库存管理应用中并不多见)。
纯时点快照型：直接创建一张库存结余表，每次有进出仓操作的时候，直接更新库存结余表，因此这张库存结余表里面的数据实际上就是当前的库存结余。这种方法的优势非常明显，就是查询当前库存的速度会非常快。当然这种方法的缺点也非常明显——由于这种方法丢弃了交易明细记录，因此要想追溯历史库存的话比较麻烦。(当然也是有办法的，数据仓库中有一种方法就是定期快照，也就是每隔一段时间存档这个时间点上的库存，当然这种方法需要平衡两个因素，一是对存储空间的消耗，二是快照的连续性，存档越频繁快照就越连续，存储空间就会消耗的越厉害。通常这种频率是由企业对数据分析的需求决定的，根据库存商品的流动特性一般会在一小时到一周不等。)这种方法最致命的缺定还不在这儿，最为困扰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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9079672/viewspace-159336/