单输入号逐行减法

Question

我想从先前的行减法结果中动态地减去，作为输入，我想给出一个数字。

我有a桌

CREATE TABLE a (id int, code text, qty numeric);

以及里面的数据

INSERT INTO a (id,code,qty)
(1,'test',5),
(2,'test',3),
(3,'test',10);

1.样本--当输入为16时，期望的结果是：

id | qty
--------------
1  | 0
2  | 0
3  | 2

计算应该是这样的

case when 16 > 5 then 16-5 else 5-16 end /* result 11, but displayed 0 */
case when 11 > 3 then 11-3 else 3-11 end /* result 8, but displayed 0 */
case when 8 > 10 then 8-10 else 10-8 end /* result 2 and displayed 2 */

2.样本--当输入为6时，期望的结果是：

id | qty
--------------
1  | 0
2  | 2
3  | 10 /* is optional to display */

计算应该是这样的

case when 6 > 5 then 6-5 else 5-6 end /* result 1, but displayed 0 */
case when 1 > 3 then 1-3 else 3-1 end /* result 2 and displayed 2 */
case when ... end /* result 10, since input is fulfilled */

Answer 1

从根本上说，这是一个正在运行的总体问题:对于每一行，您将得到运行的qty总量，并从中减去一个固定的值。更具体地说，这是关于如何使用运行的总计。在本例中，您希望根据前面的计算结果显示一个新的qty值。这里基本上有三种情况：

1. 减法的结果是阴性的。
2. 减法的结果超过了当前行的数量。
3. 结果是一个不超过qty的非负值.

第一种情况意味着所提供的固定值对于耗尽当前运行的总量来说是非常必要的-换句话说，它从开始到当前行(包括当前行)耗尽了所有的qty值。因此，我们用0代替qty。

在第二种情况下，我们只保留qty，因为运行的总数现在超过了固定的值，所以差异大于当前的qty，因此后者保持不变。

最后一种情况是，固定值要么精确地覆盖到目前为止的所有qty值，要么略小于总量，但并不少得多:直到当前qty的数量。在这种情况下，我们显示了运行总量和固定值之间的差异。

上面的SQL如下所示：

SELECT
  id,
  CASE
    WHEN balance < 0   THEN 0
    WHEN balance > qty THEN qty
    ELSE balance
  END AS qty
FROM
  (
    SELECT
      id,
      qty,
      SUM(qty) OVER (ORDER BY id ASC) - @fixedSum AS balance
    FROM
      a
  ) AS derived
;

其中@fixedSum是要从正在运行的总数中减去的固定值。

解决方案的中心部分是SUM(qty) OVER (ORDER BY id ASC) --用于获取qty运行总量的窗口聚合函数。

我在这里在嵌套的SELECT中计算运行总计，因为在一个CASE表达式中需要多次引用减法的结果，而且您不能在相同级别上引用一个计算过的列，需要为此使用嵌套。嵌套可以是派生表或公共表表达式(CTE)的形式。上面的解决方案使用一个派生表。

不过，有一种方法可以避免嵌套，并使用函数最小和最大的函数在单个选择中完成所有计算：

SELECT
  id,
  qty,
  LEAST(
    qty,
    GREATEST(
      SUM(qty) OVER (ORDER BY id ASC) - @fixedSum,
      0
    )
  ) AS balance
FROM
  a

不过，这个方法要比用例的方法简单得多。就性能而言，它可能也同样有效。

这个方法是这样工作的。首先，使用最大的函数在减法结果和0之间选择更大的值。因此，就上面列出的情况而言，如果我们有例1，则最大会产生0。一个非零的结果就意味着我们还有另外一个案子。

然后将所获得的值与当前的qty进行比较，并且这一次使用最小函数选择两个中较小的一个。如果我们以前得到了0，至少会保留它。如果我们有减法结果，现在它被发现大于qty，那么最小的函数将给出qty (这意味着这是例3)。如果差别恰好小于qty，那就是最小的结果(例2)。