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 pages (
date date,
pageviews int
);
insert into pages values
('2020-02-01'::date, 42),
('2020-02-02'::date, 3),
('2020-02-03'::date, 216),
('2020-02-04'::date, 186),
('2020-02-05'::date, 510),
('2020-02-06'::date, 419),
('2020-02-07'::date, 64),
('2020-02-09'::date, 230)
;
SELECT *,
(pageviews +
LAG(pageviews) OVER (order by date) +
LAG(pageviews, 2) OVER (order by DATE)) / 3 AS moving_average
FROM (
SELECT dates.date, coalesce(pageviews, 0) AS pageviews
FROM generate_series((select min(date) from pages),
(select max(date) from pages),
'1 day') as dates
LEFT JOIN pages on dates.date = pages.date
) AS pages_full
ORDER BY date
CREATE TABLE
INSERT 0 8
| date | pageviews | moving_average |
|---|---|---|
| 2020-02-01 | 42 | null |
| 2020-02-02 | 3 | null |
| 2020-02-03 | 216 | 87 |
| 2020-02-04 | 186 | 135 |
| 2020-02-05 | 510 | 304 |
| 2020-02-06 | 419 | 371 |
| 2020-02-07 | 64 | 331 |
| 2020-02-08 | 0 | 161 |
| 2020-02-09 | 230 | 98 |
SELECT 9