时间戳

首页 > 解决方案 > SQL Server - 带有实际盘点的当前库存

sql - SQL Server - 带有实际盘点的当前库存

问题描述

我觉得好像我错过了一些非常基本的东西，应该是显而易见的。我基本上是在尝试获取以下数据并计算第五列：Inventory_Current。

示例数据：

日期戳 产品编号 Inventory_Change Inventory_Count
2021-07-01 100 -300 100000
2021-07-01 200 -700 50000
2021-07-02 100 200 无效的
2021-07-02 200 -100 无效的
2021-07-03 100 500 无效的
2021-07-03 200 300 无效的
2021-07-04 100 -1000 99500
2021-07-04 200 -100 无效的
2021-07-05 100 100 无效的
2021-07-05 200 300 50500

Inventory_Count 通常每月只执行一次，但销售规定 Inventory_Change 每天都发生。因此，我需要根据每个 ProductID 的最后一个 Inventory_Count 以来的 Inventory_Changes 总和来计算当前库存水平。示例数据旨在简洁地捕捉这个概念。

预期结果：

日期戳 产品编号 Inventory_Change Inventory_Count 库存_当前
2021-07-01 100 -300 100000 99700
2021-07-01 200 -700 50000 49300
2021-07-02 100 200 无效的 99900
2021-07-02 200 -100 无效的 49200
2021-07-03 100 500 无效的 100400
2021-07-03 200 300 无效的 49500
2021-07-04 100 -1000 99500 98500
2021-07-04 200 -100 无效的 49400
2021-07-05 100 100 无效的 98600
2021-07-05 200 300 50500 50800

要计算 Inventory_Current，我大致遵循以下逻辑：

select
  DateStamp,
  ProductID,
  Inventory_Change,
  Inventory_Count,
  iif(Inventory_Count is not null,
    Inventory_Count+Inventory_Change,
    /*Do magic here*/
  ) as Inventory_Current
from
  Inventory

我考虑过 Itzik Ben-Gan 的Last non Null概念（聪明，顺便说一句），利用 LAG、OVER PARTITION 或放弃（显然，SQL dba 不是我的主要角色）。似乎最大的问题是所有解决方案都依赖于主键，而我的 DateStamp 和 ProductID 组合似乎会导致问题。

即使是指向一些可靠阅读的链接也将不胜感激。或者在远离孩子的地方好好休息。:)

提前致谢。

标签： sqlsql-serversql-null

解决方案

没有预期的结果（在撰写本文时），这是一个猜测，但是，我猜测您想要累积SUM的值InventoryChange并将其添加到最后一个非NULL的值InventoryCount。如果是这样，那么也许这就是您所追求的：

CREATE TABLE dbo.YourTable (DateStamp date,
                            ProductID int,
                            InventoryChange int,
                            InventoryCount int);
GO
INSERT INTO dbo.YourTable
VALUES('2021-07-01',100,-300 , 100000),
      ('2021-07-01',200,-700 , 50000),
      ('2021-07-02',100,200  ,null),
      ('2021-07-02',200,-100 , null),
      ('2021-07-03',100,500  ,null),
      ('2021-07-03',200,300  ,null),
      ('2021-07-04',100,-1000,  99500),
      ('2021-07-04',200,-100 , null),
      ('2021-07-05',100,100  ,null),
      ('2021-07-05',200,300  ,50500);
GO
WITH Groups AS(
    SELECT DateStamp,
           ProductID,
           InventoryChange,
           InventoryCount,
           COUNT(InventoryCount) OVER (PARTITION BY ProductID ORDER BY DateStamp) AS Grp
    FROM dbo.YourTable)
SELECT DateStamp,
       ProductID,
       InventoryChange,
       CASE WHEN InventoryCount IS NOT NULL THEN InventoryCount + InventoryChange
            ELSE MAX(InventoryCount) OVER (PARTITION BY ProductID,Grp) +
                 SUM(InventoryChange) OVER (PARTITION BY ProductID,Grp ORDER BY DateStamp)
       END AS InventoryCount
FROM Groups
ORDER BY DateStamp;

GO

DROP TABLE dbo.YourTable;

db<>小提琴

如果您不希望将 的值InventoryChange计入具有非NULL值的行，则需要CASE在SUM.

推荐阅读