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.