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

亚历克斯M88

我有下表,我要为其计算运行余额和剩余值,但是剩余值是先前计算的行的函数,例如:

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

说明2:与上述逻辑相同。717 - (4/7.5 * 717) = 717 - 382.4

4/7.5 这里表示当前行的绝对金额除以上一行的余额。

我尝试了window函数,sum()但没有获得期望的结果。有没有一种方法可以在PostgreSQL中完成此任务而不必求助于循环?

额外的复杂性:通过PR(产品ID),1、2等来标识多个产品。每个产品都需要自己的运行总计和计算。

欧文·布兰德斯特

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

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表达式进行拆分:如果数量为正,则仅添加总计,否则应用您(简化的)公式:
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具有恒定的性能。

有关:

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章

如何基于一列计算和应用 z 分数,并将其应用于 Pyhton 中的同一列?

在同一列上两次使用CUBE来输出计算结果

设置基于 SQL Server 插入的方法,其中一列从同一列计算为 Max

返回系列计算结果的方程

从另一列计算指数

一列计算多个输出

计算结果表中的其他列时如何显示一列?

如何从两个独立行的同一列计算商?

使用熊猫从excel中的给定日期列计算月和周,并追加到同一工作表中的另一列

SQL:两列计算第一列和第二列的第三列总和

使用上面data.frame中的行和另一列计算值

基于Power Bi中同一列中的上一行和另一列中的上一行进行计算

根据前一列计算得出的列

Jquery Datatable 根据另一列计算列

为每一列计算不同的多个列

pandas:聚合在一列并基于两列计数

如何基于工作表中的两个独立列计算一列中的唯一值

如何从mysql中的两列计算结果

如何基于另一列计算接下来的n行的平均值-SQL(Oracle)

计算结果集中的列

Oracle SQL 从一列计算增长值

根据另一列计算值的出现次数

如何根据另一列计算值的数量?

MongoDB对同一文档中的多个字段进行分组和计算结果

Pandas Group由两列组成,基于一列计算总计,但基于聚合器的总计计算百分比

根据日期列和另一列计算一个熊猫系列中的值

熊猫如何基于groupby计算结果

根据另一列重置累计和

根据另一列计算一列的总和