Get values from first and last row per group
There are various simpler and faster ways.
2x DISTINCT ON
SELECT *FROM ( SELECT DISTINCT ON (name) name, week AS first_week, value AS first_val FROM tbl ORDER BY name, week ) fJOIN ( SELECT DISTINCT ON (name) name, week AS last_week, value AS last_val FROM tbl ORDER BY name, week DESC ) l USING (name);
Or shorter:
SELECT *FROM (SELECT DISTINCT ON (1) name, week AS first_week, value AS first_val FROM tbl ORDER BY 1,2) fJOIN (SELECT DISTINCT ON (1) name, week AS last_week , value AS last_val FROM tbl ORDER BY 1,2 DESC) l USING (name);
Simple and easy to understand. Also fastest in my old tests. Detailed explanation for DISTINCT ON
:
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_valueFROM tbl tWINDOW w AS (PARTITION BY name ORDER BY week DESC)ORDER BY name, week;
The explicit WINDOW
clause only shortens the code, no effect on performance.
first_value()
of composite type
The aggregate functions min()
or max()
do not accept composite types as input. You would have to create custom aggregate functions (which is not that hard).
But the window functions first_value()
and last_value()
do. Building on that we can devise simple solutions:
Simple query
SELECT DISTINCT ON (name) name, week AS first_week, value AS first_value ,(first_value((week, value)) OVER (PARTITION BY name ORDER BY week DESC))::text AS lFROM tbl tORDER BY name, week;
The output has all data, but the values for the last week are stuffed into an anonymous record (optionally cast to text
). You may need decomposed values.
Decomposed result with opportunistic use of table type
For that we need a well-known composite type. An adapted table definition would allow for the opportunistic use of the table type itself directly:
CREATE TABLE tbl (week int, value int, name text); -- optimized column order
week
and value
come first, so now we can sort by the table type itself:
SELECT (l).name, first_week, first_val , (l).week AS last_week, (l).value AS last_valFROM ( 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;
Decomposed result from user-defined row type
That's probably not possible in most cases. Register a composite type with CREATE TYPE
(permanent) or with CREATE TEMP TABLE
(for the duration of the session):
CREATE TEMP TABLE nv(last_week int, last_val int); -- register composite type
SELECT name, first_week, first_val, (l).last_week, (l).last_valFROM ( 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;
Custom aggregate functions first()
& last()
Create functions and aggregates once per database:
CREATE OR REPLACE FUNCTION public.first_agg (anyelement, anyelement) RETURNS anyelement LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE AS'SELECT $1;'CREATE AGGREGATE public.first(anyelement) ( SFUNC = public.first_agg, STYPE = anyelement, PARALLEL = safe);CREATE OR REPLACE FUNCTION public.last_agg (anyelement, anyelement) RETURNS anyelement LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE AS'SELECT $2';CREATE AGGREGATE public.last(anyelement) ( SFUNC = public.last_agg, STYPE = anyelement, PARALLEL = safe);
Then:
SELECT name , first(week) AS first_week, first(value) AS first_val , last(week) AS last_week , last(value) AS last_valFROM (SELECT * FROM tbl ORDER BY name, week) tGROUP BY name;
Probably the most elegant solution. Faster with the additional module first_last_agg
providing a C implementation.
Compare instructions in the Postgres Wiki.
Related:
db<>fiddle here (showing all)
Old sqlfiddle
Each of these queries was substantially faster than the currently accepted answer in a quick test on a table with 50k rows with EXPLAIN ANALYZE
.
There are more ways. Depending on data distribution, different query styles may be (much) faster, yet. See:
This is a bit of a pain, because Postgres has the nice window functions first_value()
and last_value()
, but these are not aggregation functions. So, here is one way:
select t.name, min(t.week) as minWeek, max(firstvalue) as firstvalue, max(t.week) as maxWeek, max(lastvalue) as lastValuefrom (select t.*, first_value(value) over (partition by name order by week) as firstvalue, last_value(value) over (partition by name order by week) as lastvalue from table t ) tgroup by t.name;