SQL Server库存表逐行计算月度库存：Lag函数是否适用？

Absolutely! You can easily compute running inventory levels row-by-row in SQL Server, and while LAG() is a valid tool for this kind of work, there's a more straightforward (and often more efficient) approach using a windowed SUM() function that fits your scenario perfectly.

Let's break this down with an example. First, let's assume your inventory table looks something like this:

CREATE TABLE Inventory (
    MonthKey INT, -- Format: YYYYMM (e.g., 202401 for Jan 2024)
    QuantityChange INT -- For Jan, this is your initial inventory; for other months, it's the net change (+/-)
);

-- Sample data matching your example
INSERT INTO Inventory VALUES
(202401, 10),  -- Jan initial inventory: 10
(202402, -1),  -- Feb change: -1 (inventory becomes 9)
(202403, 4);   -- Mar change: +4 (inventory becomes 13)

The Optimal Approach: Windowed SUM() #

Since your January value is the starting point and each subsequent row represents a net change, we can compute the running inventory by summing all values from the first row up to the current row using SUM() OVER (...). This is efficient because it scans the table once, with no need for recursive logic or repeated lookups.

Here's the query:

SELECT
    MonthKey,
    QuantityChange,
    SUM(QuantityChange) OVER (ORDER BY MonthKey) AS CurrentInventory
FROM Inventory
ORDER BY MonthKey;

Output: #

This directly gives you the inventory level for each month, exactly as you need it.

Using LAG(): When Might It Make Sense? #

LAG() is useful if you need to reference the previous row's calculated inventory explicitly (for example, if you have conditional logic based on prior levels). However, since LAG() can't reference a computed column in the same SELECT clause, you'll need to use a CTE or subquery to first compute the running inventory, or use a recursive CTE.

Here's how you could do it with a recursive CTE:

WITH RecursiveInventory AS (
    -- Anchor member: get the initial Jan inventory
    SELECT
        MonthKey,
        QuantityChange,
        QuantityChange AS CurrentInventory
    FROM Inventory
    WHERE MonthKey = (SELECT MIN(MonthKey) FROM Inventory)

    UNION ALL

    -- Recursive member: add current change to previous month's inventory
    SELECT
        i.MonthKey,
        i.QuantityChange,
        ri.CurrentInventory + i.QuantityChange AS CurrentInventory
    FROM Inventory i
    JOIN RecursiveInventory ri ON i.MonthKey = ri.MonthKey + 1 -- Assumes consecutive months
)
SELECT * FROM RecursiveInventory ORDER BY MonthKey;

Note that this recursive approach works if your months are consecutive and ordered numerically. If there are gaps, you'd need to adjust the join logic (e.g., using DATEADD if you're using date columns instead of integer keys).

Which Should You Use? #

• Stick with the windowed SUM() for your scenario: it's simpler, faster, and easier to maintain. It's designed exactly for these kinds of running total calculations.
• Use LAG() or recursive CTEs only if you need to add custom logic that depends on the previous month's inventory (like checking if inventory dropped below a threshold before applying the current change).

内容的提问来源于stack exchange，提问作者Jeff McClain