Postgres 16

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 tbl (year int, country text, product text, profit int);
INSERT INTO tbl VALUES
  (2000, 'Finland', 'Computer'  , 1500)
, (2000, 'Finland', 'Phone'     ,  100)
, (2001, 'Finland', 'Phone'     ,   10)
, (2000, 'India'  , 'Calculator',   75)
, (2000, 'India'  , 'Calculator',   75)
, (2000, 'India'  , 'Computer'  , 1200)
;

CREATE TABLE



INSERT 0 6


SELECT t.country, t.year, t.profit
     , sum(profit) OVER (PARTITION BY country ORDER BY year
                         RANGE BETWEEN 1 PRECEDING AND 1 PRECEDING) AS sum_profit_previous_year
FROM   tbl t
ORDER  BY t.country DESC, t.year;  -- optional?


country
year
profit
sum_profit_previous_year


India
2000
75
null

India
2000
75
null

India
2000
1200
null

Finland
2000
1500
null

Finland
2000
100
null

Finland
2001
10
1600



SELECT 6


SELECT t.country, t.year, t.profit
     , y.profit AS sum_profit_previous_year
FROM   tbl t
LEFT   JOIN (
   SELECT country, year, sum(profit) AS profit
   FROM   tbl t1
   GROUP  BY 1, 2
   ) y ON y.country = t.country
      AND y.year = t.year - 1
ORDER  BY t.country DESC, t.year;  -- optional?


country
year
profit
sum_profit_previous_year


India
2000
75
null

India
2000
75
null

India
2000
1200
null

Finland
2000
1500
null

Finland
2000
100
null

Finland
2001
10
1600



SELECT 6

country	year	profit	sum_profit_previous_year
India	2000	75	null
India	2000	75	null
India	2000	1200	null
Finland	2000	1500	null
Finland	2000	100	null
Finland	2001	10	1600