sumar siguiente seleccionar registros registro obtener numero filas fila fecha ejemplos anterior sql postgresql group-by aggregate greatest-n-per-group

sql - siguiente - Obtener valores de la primera y última fila por grupo



siguiente registro en sql (2)

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 registro 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 lista 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 función 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 ventana 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 pruebas. Explicación detallada de DISTINCT ON :

first_value() de tipo compuesto

Las funciones agregadas min() o max() no aceptan tipos compuestos como entrada. Tendría que crear funciones de agregado personalizadas (que no es tan difícil).
Pero la ventana 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 registro 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 tipos 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 vienen 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 prueba 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.