Postgres 11

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 mytable
    ("dates" date, "sales" int)
;
    
INSERT INTO mytable
    ("dates", "sales")
VALUES
    ('01-01-2019', 1),
    ('02-01-2019', 3),
    ('03-01-2019', 5),
    ('04-01-2019', 2),
    ('05-01-2019', 7),
    ('06-01-2019', 8),
    ('08-01-2019', 10),
    ('09-01-2019', 1),
    ('10-01-2019', 8);
    
SELECT * FROM mytable

CREATE TABLE



INSERT 0 9




dates
sales


2019-01-01
1

2019-01-02
3

2019-01-03
5

2019-01-04
2

2019-01-05
7

2019-01-06
8

2019-01-08
10

2019-01-09
1

2019-01-10
8



SELECT 9


SELECT
    gs::date as dates,
    mt.sales
FROM
    mytable mt
RIGHT JOIN generate_series('2019-01-01', '2019-01-11', interval '1 day') gs
    ON gs = mt.dates


dates
sales


2019-01-01
1

2019-01-02
3

2019-01-03
5

2019-01-04
2

2019-01-05
7

2019-01-06
8

2019-01-07
null

2019-01-08
10

2019-01-09
1

2019-01-10
8

2019-01-11
null



SELECT 11


SELECT
    gs::date as dates,
    COALESCE(mt.sales, 0) AS sales,
    sum(sales) over(order by gs ROWS BETWEEN 6 preceding AND CURRENT ROW)
FROM
    mytable mt
RIGHT JOIN generate_series('2019-01-01', '2019-01-11', interval '1 day') gs
    ON gs = mt.dates


dates
sales
sum


2019-01-01
1
1

2019-01-02
3
4

2019-01-03
5
9

2019-01-04
2
11

2019-01-05
7
18

2019-01-06
8
26

2019-01-07
0
26

2019-01-08
10
35

2019-01-09
1
33

2019-01-10
8
36

2019-01-11
0
34



SELECT 11


WITH date_bounds AS (
    SELECT min(dates), max(dates) FROM mytable
)
SELECT
    gs::date as dates,
    COALESCE(mt.sales, 0) AS sales,
    sum(sales) over(order by gs ROWS BETWEEN 6 preceding AND CURRENT ROW)
FROM
    mytable mt
RIGHT JOIN 
    generate_series(
         (SELECT min FROM date_bounds), 
         (SELECT max FROM date_bounds), 
         interval '1 day'
    ) gs
    ON gs = mt.dates


dates
sales
sum


2019-01-01
1
1

2019-01-02
3
4

2019-01-03
5
9

2019-01-04
2
11

2019-01-05
7
18

2019-01-06
8
26

2019-01-07
0
26

2019-01-08
10
35

2019-01-09
1
33

2019-01-10
8
36



SELECT 10

dates	sales
2019-01-01	1
2019-01-02	3
2019-01-03	5
2019-01-04	2
2019-01-05	7
2019-01-06	8
2019-01-08	10
2019-01-09	1
2019-01-10	8