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?.
WITH your_table AS (
select 1 as id, 1 as x, 1 as y, 25 as value union all
select 1, 1, 2, 42 union all
select 1, 2, 3, 98 union all
select 1, 2, 4, 54 union all
select 1, 3, 5, 67 union all
select 2, 1, 1, 78 union all
select 2, 1, 2, 45 union all
select 2, 2, 3, 96
)
select
id,
x,
y,
coalesce(avg(value) over (partition by id order by y rows between 3 preceding AND 1 preceding), value) as rollingAvg
from your_table
order by id, y;
| id | x | y | rollingavg |
|---|---|---|---|
| 1 | 1 | 1 | 25 |
| 1 | 1 | 2 | 25.0000000000000000 |
| 1 | 2 | 3 | 33.5000000000000000 |
| 1 | 2 | 4 | 55.0000000000000000 |
| 1 | 3 | 5 | 64.6666666666666667 |
| 2 | 1 | 1 | 78 |
| 2 | 1 | 2 | 78.0000000000000000 |
| 2 | 2 | 3 | 61.5000000000000000 |
SELECT 8