Obtener valores de la primera y última fila por grupo

Soy nuevo en Postgres, provengo de MySQL y espero que alguno de ustedes pueda ayudarme.

Tengo una tabla con tres columnas: name , week y value . Esta tabla tiene un logging de los nombres, la semana en la que registraron la altura y el valor de su altura. Algo como esto:

 Name | Week | Value ------+--------+------- John | 1 | 9 Cassie| 2 | 5 Luke | 6 | 3 John | 8 | 14 Cassie| 5 | 7 Luke | 9 | 5 John | 2 | 10 Cassie| 4 | 4 Luke | 7 | 4 

Lo que quiero es una list por usuario del valor en la semana mínima y la semana máxima. Algo como esto:

 Name |minWeek | Value |maxWeek | value ------+--------+-------+--------+------- John | 1 | 9 | 8 | 14 Cassie| 2 | 5 | 5 | 7 Luke | 6 | 3 | 9 | 5 

En Postgres, uso esta consulta:

 select name, week, value from table t inner join( select name, min(week) as minweek from table group by name) ss on t.name = ss.name and t.week = ss.minweek group by t.name ; 

Sin embargo, recibo un error:

la columna "w.week" debe aparecer en la cláusula GROUP BY o debe usarse en una function agregada
Posición: 20

Esto funcionó bien para mí en MySQL, así que me pregunto qué estoy haciendo mal aquí.

Esto es un poco first_value() , porque Postgres tiene las agradables funciones de window first_value() y last_value() , pero estas no son funciones de agregación. Entonces, aquí hay una forma:

 select t.name, min(t.week) as minWeek, max(firstvalue) as firstvalue, max(t.week) as maxWeek, max(lastvalue) as lastValue from (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 ) t group by t.name; 

Hay varias maneras más simples y más rápidas.

2x DISTINCT ON

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

O más corto:

 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); 

Simple y fácil de entender. También el más rápido en mis testings. Explicación detallada de DISTINCT ON :

  • Seleccione la primera fila en cada grupo GROUP BY?

first_value() de tipo compuesto

Las funciones agregadas min() o max() no aceptan types compuestos como input. Tendría que crear funciones de agregado personalizadas (que no es tan difícil).
Pero la window funciona first_value() y last_value() do . Sobre la base de eso podemos diseñar soluciones muy simples:

Consulta simple

 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 l FROM tbl t ORDER BY name, week; 

El resultado tiene todos los datos, pero los valores de la última semana se incluyen en un logging anónimo. Es posible que necesite valores descompuestos.

Resultado descompuesto con uso oportunista del tipo de tabla

Para eso necesitamos un tipo conocido que registre los types de elementos contenidos con el sistema. Una definición de tabla adaptada permitiría el uso oportunista del tipo de tabla en sí directamente:

 CREATE TABLE tbl (week int, value int, name text) -- note optimized column order 

week y el value son lo primero.

 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; 

Resultado descompuesto del tipo de fila definida por el usuario

Sin embargo, eso probablemente no sea posible en la mayoría de los casos. Simplemente use un tipo definido por el usuario desde CREATE TYPE (permanente) o desde CREATE TEMP TABLE (para uso ad-hoc):

 CREATE TEMP TABLE nv(last_week int, last_val int); -- register 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; 

En una testing local en Postgres 9.3 con una tabla similar de 50k filas, cada una de estas consultas fue sustancialmente más rápida que la respuesta actualmente aceptada. Prueba con EXPLAIN ANALYZE .

SQL Fiddle mostrando todo.