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

Question

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

我正在寻找一个可以从下表生成"result_good“的函数。

"result_good“是"trx”和自身前一行的累加，乘以"event“。

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

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

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<>fiddle https://dbfiddle.uk/?rdbms=postgres_14&fiddle=d4b83e3838bc3849f7fcc421eea1519d

呼叫：

SELECT * FROM f_special_running_sum();

我将结果编号格式化为文本，以与您想要的结果完全匹配。您可能需要一个numeric或double precision。相应地进行调整。

对于大型源表，性能将下降，因为下一行的成本将不断增加。有点像O(N平方)。

仔细表-将所有查询中的列名限定为用于多种用途的相同名称。

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

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