Recursive cumulative function - reuse resulting rows as input-CodePudding

I have tried arrays, lateral views, recursive views, custom functions, variables... I'm losing hope.

I'm looking for a function that can produce "result_good" from the table below.

"result_good" is the accumulation of "trx" and previous rows from itself, multiplied by "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

PostgreSQL 13.6 on x86_64-pc-linux-gnu 64-bit

CodePudding user response：

A window function does not operate recursively, and I don't see a way with a recursive query, either.

Each result contributes to the next row recursively. This function writes to a temp table and reuses results:

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 here

Call:

SELECT * FROM f_special_running_sum();

I formatted the resulting number as text to match your desired result exactly. You may want a numeric or double precision instead. Adapt accordingly.

Performance will deteriorate for big source tables, as the cost for each next row keeps growing. Something like O(N²).

Carefully table-qualify column names in all queries as the same names are used for multiple purposes.