我有下表,我要为其计算运行余额和剩余值,但是剩余值是先前计算的行的函数,例如:
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] 删除。
我来说两句