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 features (uid int, geom text, date date, count int);
    
INSERT INTO features VALUES
    ( 1, 'FeatureA', '2016-02-01',  1),
    ( 2, 'FeatureA', '2016-02-02',  2),
    ( 3, 'FeatureA', '2016-02-03',  3),
    ( 4, 'FeatureA', '2016-02-04',  4),
    ( 5, 'FeatureA', '2016-02-05',  5),
    ( 6, 'FeatureA', '2016-02-06',  9),
    ( 7, 'FeatureA', '2016-02-07', 11),
    ( 8, 'FeatureA', '2016-02-08', 15),
    ( 9, 'FeatureA', '2016-02-09', 17),
    (10, 'FeatureA', '2016-02-10', 20),
    (11, 'FeatureB', '2016-02-01',  2),
    (12, 'FeatureB', '2016-02-02',  2),
    (13, 'FeatureB', '2016-02-03',  8),
    (14, 'FeatureB', '2016-02-04',  4),
    (15, 'FeatureB', '2016-02-05',  5),
    (16, 'FeatureB', '2016-02-06', 15),
    (17, 'FeatureB', '2016-02-07', 11),
    (18, 'FeatureB', '2016-02-08', 15),
    (19, 'FeatureB', '2016-02-09', 19),
    (20, 'FeatureB', '2016-02-10', 25);
20 rows affected

SELECT geom, date, count
     , CASE WHEN rn < 7 THEN NULL
            ELSE round(rolling_avg_count, 2) END AS rolling_avg_count
FROM  (
   SELECT geom, date, count
        , AVG(count) OVER (PARTITION BY geom
                           ORDER BY date
                           ROWS BETWEEN CURRENT ROW AND 6 FOLLOWING) AS rolling_avg_count
        , row_number() OVER (PARTITION BY geom ORDER BY date DESC) AS rn
   FROM   features
   ) sub;


geom
date
count
rolling_avg_count


FeatureA
2016-02-01
1
5.00

FeatureA
2016-02-02
2
7.00

FeatureA
2016-02-03
3
9.14

FeatureA
2016-02-04
4
11.57

FeatureA
2016-02-05
5
null

FeatureA
2016-02-06
9
null

FeatureA
2016-02-07
11
null

FeatureA
2016-02-08
15
null

FeatureA
2016-02-09
17
null

FeatureA
2016-02-10
20
null

FeatureB
2016-02-01
2
6.71

FeatureB
2016-02-02
2
8.57

FeatureB
2016-02-03
8
11.00

FeatureB
2016-02-04
4
13.43

FeatureB
2016-02-05
5
null

FeatureB
2016-02-06
15
null

FeatureB
2016-02-07
11
null

FeatureB
2016-02-08
15
null

FeatureB
2016-02-09
19
null

FeatureB
2016-02-10
25
null


-- same, but avoid additional sort
SELECT geom, date, count
     , CASE WHEN rn < 6 THEN NULL
            ELSE round(rolling_avg_count, 2) END AS rolling_avg_count
FROM  (
   SELECT geom, date, count
        , avg(count)   OVER (PARTITION BY geom
                             ORDER BY date
                             ROWS BETWEEN CURRENT ROW AND 6 FOLLOWING) AS rolling_avg_count
        , count(*)     OVER (PARTITION BY geom)
        - row_number() OVER (PARTITION BY geom ORDER BY date) AS rn
   FROM   features
   ) sub;


geom
date
count
rolling_avg_count


FeatureA
2016-02-01
1
5.00

FeatureA
2016-02-02
2
7.00

FeatureA
2016-02-03
3
9.14

FeatureA
2016-02-04
4
11.57

FeatureA
2016-02-05
5
null

FeatureA
2016-02-06
9
null

FeatureA
2016-02-07
11
null

FeatureA
2016-02-08
15
null

FeatureA
2016-02-09
17
null

FeatureA
2016-02-10
20
null

FeatureB
2016-02-01
2
6.71

FeatureB
2016-02-02
2
8.57

FeatureB
2016-02-03
8
11.00

FeatureB
2016-02-04
4
13.43

FeatureB
2016-02-05
5
null

FeatureB
2016-02-06
15
null

FeatureB
2016-02-07
11
null

FeatureB
2016-02-08
15
null

FeatureB
2016-02-09
19
null

FeatureB
2016-02-10
25
null

geom	date	count	rolling_avg_count
FeatureA	2016-02-01	1	5.00
FeatureA	2016-02-02	2	7.00
FeatureA	2016-02-03	3	9.14
FeatureA	2016-02-04	4	11.57
FeatureA	2016-02-05	5	null
FeatureA	2016-02-06	9	null
FeatureA	2016-02-07	11	null
FeatureA	2016-02-08	15	null
FeatureA	2016-02-09	17	null
FeatureA	2016-02-10	20	null
FeatureB	2016-02-01	2	6.71
FeatureB	2016-02-02	2	8.57
FeatureB	2016-02-03	8	11.00
FeatureB	2016-02-04	4	13.43
FeatureB	2016-02-05	5	null
FeatureB	2016-02-06	15	null
FeatureB	2016-02-07	11	null
FeatureB	2016-02-08	15	null
FeatureB	2016-02-09	19	null
FeatureB	2016-02-10	25	null