Postgres 13

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 initial_table (weeknum int, daynum int, value int, PRIMARY KEY (weeknum, daynum));

INSERT INTO initial_table (weeknum, daynum, value) VALUES
(1, 1,  22),
(1, 2,  23),
(1, 3,   4),
(1, 4,  23),
(1, 5,  14),
(1, 6, 132),
(1, 7, 211),
(2, 1, 155),
(2, 2, 190),
(2, 3,  33),
(2, 4, 222),
(2, 5,  75),
(2, 6,  92),
(2, 7, 107);

SELECT * FROM initial_table;
14 rows affected



weeknum
daynum
value


1
1
22

1
2
23

1
3
4

1
4
23

1
5
14

1
6
132

1
7
211

2
1
155

2
2
190

2
3
33

2
4
222

2
5
75

2
6
92

2
7
107


WITH AvgList AS
(SELECT weeknum,
        daynum, 
        value, 
	SUM (value) OVER (ORDER BY weeknum, daynum) AS CumulativeSum,
        AVG (value) OVER (ORDER BY weeknum, daynum ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) :: decimal(8,2) AS Avg5,
        AVG (value) OVER (ORDER BY weeknum, daynum ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING) :: decimal(8,2) AS Avg1,
        AVG (value) OVER (ORDER BY weeknum, daynum ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) :: decimal(8,2) AS AvgOther
    FROM initial_table
)
SELECT weeknum,
       daynum, 
       value,
       CASE WHEN daynum = 5 THEN Avg5
            WHEN daynum = 1 THEN Avg1
            ELSE AvgOther END AS hello
FROM AvgList;


weeknum
daynum
value
hello


1
1
22
22.50

1
2
23
16.33

1
3
4
16.67

1
4
23
13.67

1
5
14
95.00

1
6
132
119.00

1
7
211
166.00

2
1
155
172.00

2
2
190
126.00

2
3
33
148.33

2
4
222
110.00

2
5
75
124.00

2
6
92
91.33

2
7
107
99.50


SELECT weeknum,
       daynum, 
       value,
       CASE WHEN daynum = 5 THEN AVG (value) OVER (ORDER BY weeknum, daynum ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) :: decimal(8,2)
            WHEN daynum = 1 THEN AVG (value) OVER (ORDER BY weeknum, daynum ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING) :: decimal(8,2)
            ELSE  AVG(value) OVER (ORDER BY weeknum, daynum ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) :: decimal(8,2) END AS hello
FROM initial_table;


weeknum
daynum
value
hello


1
1
22
22.50

1
2
23
16.33

1
3
4
16.67

1
4
23
13.67

1
5
14
95.00

1
6
132
119.00

1
7
211
166.00

2
1
155
172.00

2
2
190
126.00

2
3
33
148.33

2
4
222
110.00

2
5
75
124.00

2
6
92
91.33

2
7
107
99.50

weeknum	daynum	value
1	1	22
1	2	23
1	3	4
1	4	23
1	5	14
1	6	132
1	7	211
2	1	155
2	2	190
2	3	33
2	4	222
2	5	75
2	6	92
2	7	107

weeknum	daynum	value	hello
1	1	22	22.50
1	2	23	16.33
1	3	4	16.67
1	4	23	13.67
1	5	14	95.00
1	6	132	119.00
1	7	211	166.00
2	1	155	172.00
2	2	190	126.00
2	3	33	148.33
2	4	222	110.00
2	5	75	124.00
2	6	92	91.33
2	7	107	99.50

weeknum	daynum	value
1	1	22
1	2	23
1	3	4
1	4	23
1	5	14
1	6	132
1	7	211
2	1	155
2	2	190
2	3	33
2	4	222
2	5	75
2	6	92
2	7	107

weeknum	daynum	value
1	1	22
1	2	23
1	3	4
1	4	23
1	5	14
1	6	132
1	7	211
2	1	155
2	2	190
2	3	33
2	4	222
2	5	75
2	6	92
2	7	107