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 tbl (week int, value int, name text); -- note optimized column order!
   
INSERT INTO tbl VALUES
  (1,  9, 'John'  )
, (2,  5, 'Cassie')
, (6,  3, 'Luke'  )
, (8, 14, 'John'  )
, (5,  7, 'Cassie')
, (9,  5, 'Luke'  )
, (2, 10, 'John'  )
, (4,  4, 'Cassie')
, (7,  4, 'Luke'  );


-- Aux. composite type
CREATE TYPE nv AS (last_week int, last_val int);


-- Custom aggregate functions for last query variant below:
CREATE OR REPLACE FUNCTION first_agg (anyelement, anyelement)
  RETURNS anyelement
  LANGUAGE sql IMMUTABLE STRICT AS
'SELECT $1;';

CREATE AGGREGATE first(anyelement) (
  SFUNC = first_agg
, STYPE = anyelement
);


CREATE OR REPLACE FUNCTION last_agg (anyelement, anyelement)
  RETURNS anyelement
  LANGUAGE SQL IMMUTABLE STRICT AS
'SELECT $2;';

CREATE AGGREGATE last(anyelement) (
  SFUNC = last_agg
, STYPE = anyelement
);

CREATE TABLE



INSERT 0 9



CREATE TYPE



CREATE FUNCTION



CREATE AGGREGATE



CREATE FUNCTION



CREATE AGGREGATE


-- 2x DISTINCT ON
SELECT *
FROM  (
   SELECT DISTINCT ON (1)
          name, week AS first_week, value AS first_val
   FROM   tbl
   ORDER  BY 1, 2
   ) f
JOIN  (
   SELECT DISTINCT ON (1)
          name, week AS last_week, value AS last_val
   FROM   tbl
   ORDER  BY 1, 2 DESC
   ) l USING (name);


name
first_week
first_val
last_week
last_val


Cassie
2
5
5
7

John
1
9
8
14

Luke
6
3
9
5



SELECT 3


-- 2x window function, 1x DISTINCT ON
SELECT DISTINCT ON (name)
       name, week AS first_week, value AS first_val
     , first_value(week)  OVER w AS last_week
     , first_value(value) OVER w AS last_value
FROM   tbl t
WINDOW w AS (PARTITION BY name ORDER BY week DESC)
ORDER  BY name, week;


name
first_week
first_val
last_week
last_value


Cassie
2
5
5
7

John
1
9
8
14

Luke
6
3
9
5



SELECT 3


-- Simple query with composite type
SELECT DISTINCT ON (name)
       name, week AS first_week, value AS first_val
     ,(first_value((week, value)) OVER (PARTITION BY name ORDER BY week DESC))::text AS l
FROM   tbl t
ORDER  BY name, week;


name
first_week
first_val
l


Cassie
2
5
(5,7)

John
1
9
(8,14)

Luke
6
3
(9,5)



SELECT 3


-- Same with opportunistic use of table type
SELECT (l).name, first_week, first_val
     , (l).week AS last_week, (l).value AS last_val
FROM (
   SELECT DISTINCT ON (name)
          week AS first_week, value AS first_val
        , first_value(t) OVER (PARTITION BY name ORDER BY week DESC) AS l
   FROM   tbl t
   ORDER  BY name, week
   ) sub;


name
first_week
first_val
last_week
last_val


Cassie
2
5
5
7

John
1
9
8
14

Luke
6
3
9
5



SELECT 3


-- Same with dedicated composite type
SELECT name, first_week, first_val, (l).last_week, (l).last_val
FROM (
   SELECT DISTINCT ON (name)
          name, week AS first_week, value AS first_val
        , first_value((week, value)::nv) OVER (PARTITION BY name ORDER BY week DESC) AS l
   FROM   tbl t
   ORDER  BY name, week
   ) sub;


name
first_week
first_val
last_week
last_val


Cassie
2
5
5
7

John
1
9
8
14

Luke
6
3
9
5



SELECT 3


-- Custom aggregate functions first() & last()
SELECT name
     , first(week) AS first_week, first(value) AS first_val
     , last(week)  AS last_week , last(value)  AS last_val
FROM  (SELECT * FROM tbl ORDER BY name, week) t
GROUP  BY name;


name
first_week
first_val
last_week
last_val


Cassie
2
5
5
7

John
1
9
8
14

Luke
6
3
9
5



SELECT 3