Postgres 17

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 rankings (
  userid int
, rank float
, ts timestamp
);

INSERT INTO rankings VALUES
  (1,   11, '2014-02-20 10:00')
, (1,   12, '2014-02-20 11:00')
, (1,   13, '2014-02-20 12:00')
, (2, 1000, '2014-02-01 10:00')
, (2,   12, '2014-02-20 11:00')
, (2,   11, '2014-02-20 12:00')
, (2,    1, '2014-02-23 11:00')
, (2,   11, '2014-02-24 01:00')
, (2,   12, '2014-02-24 02:00')
, (2,   13, '2014-02-24 03:00')
, (2,   14, '2014-02-24 04:00')
, (2,   15, '2014-02-24 05:00')
, (2,   16, '2014-02-24 06:00')
, (2,   17, '2014-02-24 07:00')
, (2,   18, '2014-02-24 08:00')
;

CREATE TABLE



INSERT 0 15


-- window functions
SELECT DISTINCT ON (userid)
       userid, last_value(rank) OVER w - first_value(rank) OVER w AS rank_delta
FROM   rankings
WINDOW w AS (PARTITION BY userid ORDER BY ts
             ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);


userid
rank_delta


1
2

2
-982



SELECT 2


-- aggregate functions
SELECT userid, r2.rank - r1.rank AS rank_delta
FROM  (
  SELECT userid, min(ts) AS first_ts, max(ts) AS last_ts
   FROM  rankings
   GROUP BY 1
   ) sub
JOIN   rankings r1 USING (userid)
JOIN   rankings r2 USING (userid)
WHERE  r1.ts = first_ts
AND    r2.ts = last_ts;


userid
rank_delta


1
2

2
-982



SELECT 2


-- Only the last 7 preceding rows:
SELECT DISTINCT ON (userid)
       userid
     , first_value(rank) OVER w  
     - last_value(rank)  OVER w AS rank_delta
FROM   rankings
WINDOW w AS (PARTITION BY userid ORDER BY ts DESC
             ROWS BETWEEN CURRENT ROW AND 7 FOLLOWING)
ORDER  BY userid, ts DESC;


userid
rank_delta


1
2

2
7



SELECT 2