递归累积函数-重用结果行作为输入

Question

我尝试过数组、横向视图、递归视图、自定义函数、变量。。。我正在失去希望。

我正在寻找一个函数；result_good"；来自下表。

"result_good"；是"；trx"；以及从其自身起的前几行乘以"0"；事件"；。

with t(id, trx, event, result_good) as
(values 
(1, 20, 0.1, 2.000000),
(2,-10, 0.1, 1.200000),
(3, 20,-0.1,-3.320000),
(4,-10, 0.1, 1.988000),
(5, 20, 0.1, 4.186800),
(6,-10,-0.1,-3.605480),
(7, 20, 0.1, 5.244932)
) 
-- non-recursive approximation of intended result
select *,
prev + event*sum(prev) over(
order by id range between unbounded preceding and 1 preceding
) as not_quite_my_tempo
from
(select t.*, event*sum(trx) over(order by id) as prev
from t
) t
order by id

PostgreSQL 13.6在x86_64-pc-linux-gnu 64位上

Answer 1

窗口函数不递归操作，递归CTE不允许在递归项中进行聚合。

每个结果递归地贡献给下一行。此函数写入临时表并重复使用结果：

CREATE OR REPLACE FUNCTION f_special_running_sum()
RETURNS TABLE(id int, result text)
LANGUAGE plpgsql AS
$func$
DECLARE
t record;
BEGIN
DROP TABLE IF EXISTS pg_temp.result;
CREATE TEMP TABLE result (id int, trx int, result float8) ON COMMIT DROP;
FOR t IN 
TABLE tbl ORDER BY id  -- your actual table name here!
LOOP
INSERT INTO result(id, trx, result)
SELECT t.id, t.trx
, (COALESCE(sum(r.trx + r.result), 0) + t.trx) * t.event
FROM   result r;
END LOOP;
-- format output   
RETURN QUERY
SELECT r.id, to_char(r.result, 'FM9999990.000000')
FROM   result r;
END
$func$;

db<gt；小提琴这里

呼叫：

SELECT * FROM f_special_running_sum();

我将得到的数字格式化为文本，以便与您想要的结果完全匹配。您可能需要numeric或double precision。相应地调整。

由于下一行的成本不断增长，大型源表的性能将恶化。类似O(N²(的东西。

在所有查询中小心地使用表限定列名，因为相同的名称有多种用途。

---

或者，递归函数也可以工作。示例：

• 从递归树结构创建JSON对象