sql—基于同一列计算结果的累计和

tjvv9vkg 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(358)

我有下表，我正在尝试为其计算运行余额和剩余值，但剩余值是先前计算的行的函数，如下所示：

date         PR    amount  total    balance  remaining_value
----------------------------------------------------------
'2020-1-1'   1     1.0     100.0    1.0      100    -- 100 (inital total)
'2020-1-2'   1     2.0     220.0    3.0      320   -- 100 (previous row) + 220 
'2020-1-3'   1    -1.5    -172.5    1.5      160   -- 320 - 160 (see explanation 1)
'2020-1-4'   1     3.0     270.0    4.5      430   -- 160 + 270
'2020-1-5'   1     1.0      85.0    5.5      515   -- 430 + 85 
'2020-1-6'   1     2.0     202.0    7.5      717   -- 575 + 202 
'2020-1-7'   1    -4.0    -463.0    3.5      334.6 -- 717 - 382.4 (see explanation 2)
'2020-1-8'   1    -0.5     -55.0    3.0      ...
'2020-1-9'   1     2.0     214.0    5.0
'2020-1-1'   2     1.0     100      1.0      100   -- different PR: start new running total

逻辑如下：
对于正值行，剩余值只是列中前一行的值 remaining_value +列中的值 total 从那一排。
对于负数行，它会变得更棘手：
解释1：我们从 320 （上一行的余额）我们从中删除 1.5/3.0 （当前行金额的绝对值除以前一行余额），然后我们将其乘以前一行 remaining_value ，即 320 . 计算得出：

320 - (1.5/3 * 320) = 160

说明二：逻辑同上。
717 - (4/7.5 * 717) = 717 - 382.4 4/7.5 这里表示当前行的绝对金额除以前一行的余额。
我试过Windows的功能 sum() 但没有达到预期的效果。有没有一种方法可以在postgresql中实现这一点而不必求助于循环？
额外的复杂性：有多个产品由pr（产品id）、1、2等标识。每个产品都需要自己的运行总数和计算。

sql postgresql window-functions aggregate-functions cumulative-sum

来源：https://stackoverflow.com/questions/62746497/cumulative-sum-based-on-same-column-calculated-result

1条答案

按热度按时间

u59ebvdq1#

您可以创建自定义聚合函数：

CREATE OR REPLACE FUNCTION f_special_running_sum (_state numeric, _total numeric, _amount numeric, _prev_balance numeric)
  RETURNS numeric
  LANGUAGE sql IMMUTABLE AS
'SELECT CASE WHEN _amount > 0 THEN _state + _total
             ELSE _state * (1 + _amount / _prev_balance) END';

CREATE OR REPLACE AGGREGATE special_running_sum (_total numeric, _amount numeric, _prev_balance numeric) (
  sfunc    = f_special_running_sum 
, stype    = numeric
, initcond = '0'
);

这个 CASE 表达式进行拆分：如果amount为正数，只需添加total，否则应用（简化）公式： 320 * (1 + -1.5 / 3.0) 而不是 320 - (1.5/3 * 320) ，即：

_state * (1 + _amount / _prev_balance)

函数和聚合参数名称仅用于文档。
那么您的查询可以如下所示：

SELECT *
     , special_running_sum(total, amount, prev_balance) OVER (PARTITION BY pr ORDER BY date)
FROM  (
   SELECT pr, date, amount, total
        , lag(balance, 1, '1') OVER (PARTITION BY pr ORDER BY date) AS prev_balance
   FROM   tbl
   ) t;

db<>在这里摆弄
我们需要一个子查询来应用第一个窗口函数 lag() 并将上一个余额提取到当前行中( prev_balance ). 我默认为 1 如果没有要避免的前一行 NULL 价值观。
注意事项：
如果第一行的总和为负数，则结果未定义。我的聚合函数默认为 0 .
您没有声明数据类型，也没有声明有关精度的要求。我想 numeric 以最大的精确度为目标。计算 numeric 是精确的。但是你的公式会产生小数。如果不舍入，经过几次除法后会有大量的小数位数，计算性能会很快下降。你必须在精度和性能之间达成妥协。例如，对 double precision 性能稳定。
相关：
postgres中的动态基数累加

赞(0）回复(0）举报 2021-07-24

我来回答

sql—基于同一列计算结果的累计和

1条答案

相关问题

热门标签

最新问答