Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

create table price_tbl as
select v.*
from (values ('2020-01-01'::date, 1 ),
              ('2020-01-02'::date, 2 ),
              ('2020-01-03'::date, 3 ),
              ('2020-01-04'::date, 4 )
     ) v(date, price)

SELECT 4


select p.*,
       (sum(power((1 / 0.5), seqnum) * price) over (order by seqnum) +
        first_value(price) over (order by seqnum)
       ) / power((1 / 0.5), seqnum + 1),
       (sum(power((1 / 0.5), seqnum) * price) over (order by seqnum)
       ) / sum(power((1 / 0.5), seqnum)) over (order by seqnum)
from (select p.*,
             row_number() over (order by date) - 1 as seqnum
      from price_tbl p
     ) p


date
price
seqnum
?column?
?column?


2020-01-01
1
0
1.00000000000000000000
1.00000000000000000000

2020-01-02
2
1
1.5000000000000000
1.6666666666666667

2020-01-03
3
2
2.2500000000000000
2.4285714285714286

2020-01-04
4
3
3.1250000000000000
3.2666666666666667



SELECT 4


with recursive p as (
      select p.*, row_number() over (order by date) as seqnum
      from price_tbl p
     ),
     cte as (
      select seqnum, date, price, price * 1.0 as exp_avg
      from p
      where seqnum = 1
      union all
      select p.seqnum, p.date, p.price, (cte.exp_avg * 0.5  + p.price * 0.5) 
      from cte join
           p
           on p.seqnum = cte.seqnum + 1
     )
select *
from cte


seqnum
date
price
exp_avg


1
2020-01-01
1
1.0

2
2020-01-02
2
1.50

3
2020-01-03
3
2.250

4
2020-01-04
4
3.1250



SELECT 4

date	price	seqnum	?column?	?column?
2020-01-01	1	0	1.00000000000000000000	1.00000000000000000000
2020-01-02	2	1	1.5000000000000000	1.6666666666666667
2020-01-03	3	2	2.2500000000000000	2.4285714285714286
2020-01-04	4	3	3.1250000000000000	3.2666666666666667

seqnum	date	price	exp_avg
1	2020-01-01	1	1.0
2	2020-01-02	2	1.50
3	2020-01-03	3	2.250
4	2020-01-04	4	3.1250