MSSQL循环计算现有库存对应最早采购价的剩余库存数量

需求背景 #

• 查询单个商品采购记录的SQL：

Select Document_Date, Quantity, Price FROM ArticlesTrafic
Where ArticleID = '605467' 
and DocumentTypeID = 'PRE'
and DocumentDate > '01-01-2020'

返回不同日期的采购数量与价格。

• 查询当前库存的SQL：

Select stock from 
current_stock(1,GETDATE())
where ArticleID = '605467'

返回该商品当前库存为770。

核心需求：找出770件库存中对应最早采购价的剩余数量，预期结果为220件对应83,753的采购价。

现有实现（结果正确但性能差） #

修改自Charlieface的解决方案，SQL代码如下：

SELECT Q.ArticleID, Q.Stock, Q.Remaining, Q.ProvisionUnit FROM
(SELECT
  at.*, cs.Stock,
  Remaining = cs.Stock - (at.RunningSum - at.TraficQuantityCredit),
  ROW_NUMBER() OVER (PARTITION BY at.ArticleID ORDER BY at.DocumentDate ASC) AS ID
FROM (
    SELECT
      at.ArticleID,
      at.DocumentDate,
      at.TraficQuantityCredit,
      at.ProvisionUnit,
      RunningSum = SUM(at.TraficQuantityCredit) OVER
          (PARTITION BY at.ArticleID ORDER BY at.DocumentDate DESC
           ROWS UNBOUNDED PRECEDING)
      FROM ArticlesTrafic at
      WHERE at.ArticleID IN ('605466', '605467')
      AND at.DocumentTypeID = 'PRE'
      AND at.DocumentDate > '01-01-2020'
) at
JOIN dbo.fnArticleStockPerDayTable(1, GETDATE()) cs ON cs.ArticleID = at.ArticleID
WHERE cs.Stock > at.RunningSum - at.TraficQuantityCredit
  AND cs.Stock <= at.RunningSum) AS Q
WHERE Q.ID = 1

该代码处理大量商品时性能较慢，需优化。

优化建议 #

1. 索引优化 #

• 为ArticlesTrafic表创建覆盖复合索引，直接覆盖过滤、排序和查询所需列，避免表扫描或键查找：

CREATE NONCLUSTERED INDEX IX_ArticlesTrafic_Filter_Sort_Include 
ON ArticlesTrafic (ArticleID, DocumentTypeID, DocumentDate) 
INCLUDE (TraficQuantityCredit, ProvisionUnit);

• 若dbo.fnArticleStockPerDayTable是多语句表值函数，改为内联表值函数，内联函数可被SQL Server优化器与主查询合并执行计划；同时确保函数依赖的库存表（如current_stock）有ArticleID字段的索引。

2. 调整窗口函数逻辑 #

将窗口函数的排序方向改为ASC，贴合先进先出（FIFO）逻辑，减少不必要的反向排序开销，同时调整过滤条件匹配累计逻辑：

RunningSum = SUM(at.TraficQuantityCredit) OVER
    (PARTITION BY at.ArticleID ORDER BY at.DocumentDate ASC
     ROWS UNBOUNDED PRECEDING)

对应的WHERE条件调整为：

WHERE cs.Stock > (at.RunningSum - at.TraficQuantityCredit) 
  AND cs.Stock <= at.RunningSum

3. 减少数据扫描范围 #

使用APPLY关联，针对每个商品只查询满足库存覆盖条件的最早采购记录，避免全表扫描所有采购记录：

SELECT
    cs.ArticleID,
    cs.Stock,
    Remaining = cs.Stock - ISNULL(prevSum.Total, 0),
    curr.Price
FROM dbo.fnArticleStockPerDayTable(1, GETDATE()) cs
OUTER APPLY (
    SELECT TOP 1
        at.DocumentDate,
        at.TraficQuantityCredit,
        at.Price,
        RunningSum = SUM(at.TraficQuantityCredit) OVER (PARTITION BY at.ArticleID ORDER BY at.DocumentDate ASC)
    FROM ArticlesTrafic at
    WHERE at.ArticleID = cs.ArticleID
      AND at.DocumentTypeID = 'PRE'
      AND at.DocumentDate > '01-01-2020'
    ORDER BY at.DocumentDate ASC
    OFFSET 0 ROWS
    FETCH NEXT 1 ROWS ONLY
) curr
OUTER APPLY (
    SELECT SUM(TraficQuantityCredit) AS Total
    FROM ArticlesTrafic atPrev
    WHERE atPrev.ArticleID = cs.ArticleID
      AND atPrev.DocumentTypeID = 'PRE'
      AND atPrev.DocumentDate > '01-01-2020'
      AND atPrev.DocumentDate < curr.DocumentDate
) prevSum
WHERE cs.Stock > 0

4. 简化排序逻辑 #

原查询中通过ROW_NUMBER()标记后过滤ID=1的操作，可直接在APPLY子查询中用TOP 1获取目标记录，减少一次全局排序开销。

内容的提问来源于stack exchange，提问作者Zvonimir Klenovic