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 IF NOT EXISTS item_ (
id SERIAL PRIMARY KEY,
transaction_date DATE NOT NULL,
cost NUMERIC NOT NULL,
type TEXT NOT NULL)
CREATE TABLE
INSERT INTO item_ VALUES
(1,'2019-01-01',94.12, 'A')
,(2,'2019-02-15',95.01, 'A')
,(3,'2020-05-16',45.22, 'B')
,(4,'2020-06-10',10.84, 'A')
,(5,'2020-03-01',36.22, 'A')
,(6,'2020-12-09',12.42, 'A')
,(7,'2021-05-16',64.88, 'A')
,(8,'2021-02-28',72.44, 'B')
,(9,'2021-04-16',91.08, 'A')
,(10,'2022-06-09',53.34, 'A')
,(11,'2022-10-05',36.93, 'A')
,(12,'2022-01-22',55.87, 'B')
,(13,'2022-04-06',99.66, 'A')
,(14,'2022-11-18',90.04, 'A')
,(15,'2023-01-23',82.30, 'A')
INSERT 0 15
select * from item_ order by transaction_date asc;
| id | transaction_date | cost | type |
|---|---|---|---|
| 1 | 2019-01-01 | 94.12 | A |
| 2 | 2019-02-15 | 95.01 | A |
| 5 | 2020-03-01 | 36.22 | A |
| 3 | 2020-05-16 | 45.22 | B |
| 4 | 2020-06-10 | 10.84 | A |
| 6 | 2020-12-09 | 12.42 | A |
| 8 | 2021-02-28 | 72.44 | B |
| 9 | 2021-04-16 | 91.08 | A |
| 7 | 2021-05-16 | 64.88 | A |
| 12 | 2022-01-22 | 55.87 | B |
| 13 | 2022-04-06 | 99.66 | A |
| 10 | 2022-06-09 | 53.34 | A |
| 11 | 2022-10-05 | 36.93 | A |
| 14 | 2022-11-18 | 90.04 | A |
| 15 | 2023-01-23 | 82.30 | A |
SELECT 15
select EXTRACT(YEAR FROM item_.transaction_date) AS year,
sum(case when EXTRACT(YEAR FROM CURRENT_DATE)>EXTRACT(YEAR FROM item_.transaction_date) AND type = 'A'
then item_.cost
else 0 end)
as box_A
from item_
GROUP BY year
order by year;
| year | box_a |
|---|---|
| 2019 | 189.13 |
| 2020 | 59.48 |
| 2021 | 155.96 |
| 2022 | 279.97 |
| 2023 | 0 |
SELECT 5
CREATE TABLE IF NOT EXISTS results (
year integer,
box_A NUMERIC)
CREATE TABLE
INSERT INTO results VALUES
(2019,189.13)
,(2020,59.48)
,(2021,155.96)
,(2022,279.97)
,(2023,0)
INSERT 0 5
select * from results order by year asc;
| year | box_a |
|---|---|
| 2019 | 189.13 |
| 2020 | 59.48 |
| 2021 | 155.96 |
| 2022 | 279.97 |
| 2023 | 0 |
SELECT 5
select EXTRACT(YEAR FROM item_.transaction_date) AS year,
CASE WHEN EXTRACT(YEAR FROM item_.transaction_date) < date_part('year', CURRENT_DATE)
THEN sum( item_.cost )
ELSE (SELECT sum( item_.cost) FROM item_ WHERE EXTRACT(YEAR FROM item_.transaction_date) = date_part('year', CURRENT_DATE - INTERVAL '1 year')
) * 1.5
END
as box_a
from item_
GROUP BY year
Order by year;
| year | box_a |
|---|---|
| 2019 | 189.13 |
| 2020 | 104.70 |
| 2021 | 228.40 |
| 2022 | 335.84 |
| 2023 | 503.760 |
SELECT 5
select
year,
case when box_a = 0 then lag(box_a) over(order by year) * 1.5
else box_a
end as box_a
from ( -- original query here
select
EXTRACT(YEAR FROM item_.transaction_date) AS year,
sum(case when EXTRACT(YEAR FROM CURRENT_DATE) >
EXTRACT(YEAR FROM item_.transaction_date) AND type = 'A'
then item_.cost
else 0 end
) as box_A
from item_
GROUP BY year
) x
order by year
| year | box_a |
|---|---|
| 2019 | 189.13 |
| 2020 | 59.48 |
| 2021 | 155.96 |
| 2022 | 279.97 |
| 2023 | 419.955 |
SELECT 5