Postgresql multiply and sum row using windows function?

Question

i need to somehow use the LAG along with the SUM after each returning line

table

id	valor	data
1	1,0182	2022-01-01
2	1,0183	2022-02-01
3	1,0174	2022-03-01

Expected result

id	valor	data
1	1,0182	2022-01-01
2	1,0368	2022-02-01
3	1,0548	2022-03-01

in the column "valor" I need to take the previous value, multiply it with the current value, and add this value

linha 1 1,0182
linha 2 (1,0182 x 1,0183)
linha 3 (1,0182 x 1,0183) x 1,0548
linha 4 ((1,0182 x 1,0183) x 1,0548) x ##,####
...

nd yes onwards

SELECT i.id,

valor,
COALESCE(LAG (valor) OVER ( PARTITION BY indice_correcao_id ORDER BY DATA ), 1) as valor_anteior,

SUM ( valor ) OVER ( PARTITION BY indice_correcao_id ORDER BY DATA ) AS cum_amt


    
FROM
    indice_correcao_itens AS i 
WHERE
    i.indice_correcao_id = 1 
    AND i."data" BETWEEN '2022-01-01' 
AND '2022-03-28' 
ORDER BY i."data";

Answer 1

You can define your own aggregate function that returns the product of the input:

-- From https://stackoverflow.com/a/13156170/2650437
-- See this answer if your column is not a FLOAT but e.g. a NUMERIC
-- as you will need to adapt the aggregate a bit
CREATE AGGREGATE PRODUCT(DOUBLE PRECISION) (
    SFUNC = float8mul,
    STYPE = FLOAT8
);

You can use custom aggregates in window functions, so

CREATE TEMP TABLE t (
    "id"    INTEGER,
    "valor" FLOAT,
    "data"  TIMESTAMP
);

INSERT INTO t ("id", "valor", "data")
VALUES ('1', 1.0182, '2022-01-01')
     , ('2', 1.0183, '2022-02-01')
     , ('3', 1.0174, '2022-03-01');

SELECT id, SUM(valor) OVER (ORDER BY data, id), PRODUCT(valor) OVER (ORDER BY data, id)
FROM t;

returns

+--+------------------+--------------+
|id|sum               |product       |
+--+------------------+--------------+
|1 |1.0182            |1.0182        |
|2 |2.0365            |1.03683306    |
|3 |3.0539000000000005|1.054873955244|
+--+------------------+--------------+