sql - 项目计数的运行总和(给定项目人口变化的库存表)

Question

我想弄清楚是否有可能在给定包含起始值和人口差异的表的情况下轻松得出项目计数的运行总和，或者我是否应该以编程方式进行。下面是 SSCCE :

我们有一个表格用于跟踪库存类别的变化:

CREATE TABLE INVENTORY_MOVE (
n        INTEGER, -- time
product  VARCHAR, -- product code
countBef INTEGER, -- count of items for this product code before the inventory move
diff     INTEGER -- inventory difference
);


INSERT INTO INVENTORY_MOVE VALUES
(0, 'A'  , 0,  5), -- 5 As added in time '0',  total A: 5
(1, 'B'  , 0,  3), -- 3 Bs added in time '1',  total B: 3
(2, 'C'  , 0,  4),  --4 Cs added,  total C: 4
(3, 'C'  , 4,  2),  --2 Cs added,  total C: 6
(4, 'B'  , 3, -1),  --1 B removed, total B: 2
(5, 'A'  , 5, -3)   --3 A removed, total A: 2

现在的问题是，我被要求为特定时间窗口生成任意产品代码集合的运行总和。例如。代码 A 和 C 在时间窗口 [3, 5] 内的运行总和是多少？这应该产生以下内容:

time | count-bef   | diff   | count-aft
---------------------------------------
3        9             2       11  -- in time '3' there were 5 As and 4 Cs (total: 9) and 2 more Cs were added
4        9             0       9   -- the inventory change in time '4' did not affect A or C counts
5       11            -3       8   -- in time '5' there were 5 As and 6 Cs (total: 11) and 3 As were removed

顺便说一句，由于时间“4”的库存变化没有导致查询应该报告的数量发生任何变化，如果这导致更简单的查询，我将接受缺少该行的答案。

在 SQL 中执行上述操作是否有意义，还是我应该以编程方式执行？

Answer 1

有效地解决你的起始值问题:

SELECT im.n
     , row1.items + im.delta - im.diff AS count_bef
     , im.diff
     , row1.items + im.delta           AS count_aft
FROM  (
   SELECT COALESCE((
    SELECT sum(countBef + diff)::int
    FROM   inventory_move im 
    WHERE  product IN ('A', 'C')
    AND    n < 3
    AND    NOT EXISTS (
       SELECT 1 FROM inventory_move
       WHERE  product = im.product
       AND    n < 3
       AND    n > im.n
       )
    ), 0) AS items
   ) row1
CROSS JOIN (
   SELECT n, sum(diff) OVER (ORDER BY n)::int AS delta, diff
   FROM   inventory_move im 
   WHERE  product IN ('A', 'C')
   AND    n BETWEEN 3 AND 5   -- incl. both border
   ) im
ORDER  BY n;

解释

• 假设所有列都是NOT NULL，否则添加会产生NULL。
  正如稍后阐明的那样，n 是唯一的。
  正如还阐明的那样，我们只需要结果发生实际变化的时间(示例中没有 n = 4)。

• 在子查询 row1 中，计算给定产品在您的时间范围开始之前的最新值的总和。

• 在子查询 im 中计算时间范围自开始以来的变化运行总和。

• 在外层 SELECT 中添加相应的内容。

• COALESCE 仅当在您的时间范围之前没有行给定产品时才需要。详情:

  • Return zero if no record is found

或者，您可以像这样计算项目的起始数量:

SELECT sum(items) AS items
FROM  (
   SELECT DISTINCT ON (product)
          countBef + diff AS items
   FROM   inventory_move im 
   WHERE  product IN ('A', 'C')
   AND    n < 3
   ORDER  BY product, n DESC
   ) sub;

测试哪个对你来说更快。

SQL Fiddle.