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

siguiente registro en sql

Question

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í.

Answer 1

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;

Answer 2

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 :

• Seleccione la primera fila en cada grupo GROUP BY?

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.