原題意已經變更,程式修改如下 :

CREATE TABLE TEST01 (
  dt date,
  goods varchar(10),
  price numeric(8, 2)
);
--
INSERT INTO TEST01
  VALUES ('2020-09-01','metal',10.0),
('2020-09-01','plastics',2.2),
('2020-09-01','paper',5.1),
('2020-09-02','metal',12.2),
('2020-09-02','plastics',3.1),
('2020-09-02','paper',5.2),
('2020-09-03','metal',16.3),
('2020-09-03','plastics',2.9),
('2020-09-03','paper',7.1);

SELECT *,
-- 計算累乘
ROUND(EXP(SUM(LOG(TempValue)) OVER (PARTITION BY goods ORDER BY dt)), 4) variance
FROM (
SELECT dt,goods,price,rank,
CASE WHEN rank = 1 THEN 1
     WHEN rank >= 2 THEN price * 0.5
     END AS TempValue
FROM (
SELECT dt,goods,price,
ROW_NUMBER() OVER (PARTITION BY goods ORDER BY dt) AS rank
FROM TEST01) AS Temp1
) AS Temp2
ORDER BY goods,dt,rank

Demo

這個variance值的計算是上一筆variance再乘上一個數值(假設是0.5)，最後再乘上price，

這個應該在資料庫的 stored procedure 中做吧，簡單又有效率。

按照新的多組 goods, 寫新的方式.

CREATE TABLE it200928d (
  dt date not null
, goods text not null
, price numeric(8,2) not null
);

INSERT INTO it200928d VALUES 
 ('2020-09-01'::date ,'metal' ,10.0)
,('2020-09-01'::date ,'plastics' ,2.2)
,('2020-09-01'::date ,'paper' ,5.1)
,('2020-09-02'::date ,'metal' ,12.2)
,('2020-09-02'::date ,'plastics' ,3.1)
,('2020-09-02'::date ,'paper' ,5.2)
,('2020-09-03'::date ,'metal' ,16.3)
,('2020-09-03'::date ,'plastics' ,2.9)
,('2020-09-03'::date ,'paper' ,7.1);

-- 分組後 遞迴
with recursive t1 as (
select dt, goods, price
     , row_number() over (partition by goods order by dt) grn
  from it200928d
), t2 (dt, goods, price, grn, variance) as (
select dt, goods, price, grn, 1::numeric
  from t1
 where grn = 1
union all
select b.dt, b.goods, b.price, a.grn + 1
     , a.variance * 0.5 * b.price
  from t2 a
     , t1 b
 where a.goods = b.goods
   and a.grn + 1 = b.grn
)
select *
  from t2
 order by goods, grn;
+------------+----------+-------+-----+-----------+
|     dt     |  goods   | price | grn | variance  |
+------------+----------+-------+-----+-----------+
| 2020-09-01 | metal    | 10.00 |   1 |         1 |
| 2020-09-02 | metal    | 12.20 |   2 |     6.100 |
| 2020-09-03 | metal    | 16.30 |   3 | 49.715000 |
| 2020-09-01 | paper    |  5.10 |   1 |         1 |
| 2020-09-02 | paper    |  5.20 |   2 |     2.600 |
| 2020-09-03 | paper    |  7.10 |   3 |  9.230000 |
| 2020-09-01 | plastics |  2.20 |   1 |         1 |
| 2020-09-02 | plastics |  3.10 |   2 |     1.550 |
| 2020-09-03 | plastics |  2.90 |   3 |  2.247500 |
+------------+----------+-------+-----+-----------+
(9 rows)

---- 另外 用 plpython 寫函數,再搭配 array_agg() 的方法

create or replace function ithelp200928(lst real[])
returns real
as $$
  if len(lst) == 1:
    return 1.0
  else :
    s = 1.0
    for elem in lst[1:]:
      s = s * 0.5 * elem
    return s
$$ language plpython3u;

with t1 as (
select *
     , array_agg(price::real) over(partition by goods order by dt) aggprc
  from it200928d
)
select *
     , ithelp200928(aggprc)
  from t1;
+------------+----------+-------+----------------+--------------+
|     dt     |  goods   | price |     aggprc     | ithelp200928 |
+------------+----------+-------+----------------+--------------+
| 2020-09-01 | metal    | 10.00 | {10}           |            1 |
| 2020-09-02 | metal    | 12.20 | {10,12.2}      |          6.1 |
| 2020-09-03 | metal    | 16.30 | {10,12.2,16.3} |    49.714996 |
| 2020-09-01 | paper    |  5.10 | {5.1}          |            1 |
| 2020-09-02 | paper    |  5.20 | {5.1,5.2}      |          2.6 |
| 2020-09-03 | paper    |  7.10 | {5.1,5.2,7.1}  |         9.23 |
| 2020-09-01 | plastics |  2.20 | {2.2}          |            1 |
| 2020-09-02 | plastics |  3.10 | {2.2,3.1}      |         1.55 |
| 2020-09-03 | plastics |  2.90 | {2.2,3.1,2.9}  |       2.2475 |
+------------+----------+-------+----------------+--------------+
(9 rows)

Time: 6.827 ms

你要確認這個table的哪一個欄位是你的seq key，不然怎麼確認計算順序?