Seleccione la primera fila en cada grupo GROUP BY?

Como sugiere el título, me gustaría seleccionar la primera fila de cada set de filas agrupadas con un GROUP BY .

Específicamente, si tengo una tabla de purchases que se ve así:

 SELECT * FROM purchases; 
 id |  cliente |  total
 --- + ---------- + ------
  1 |  Joe |  5
  2 |  Sally |  3
  3 |  Joe |  2
  4 |  Sally |  1

Me gustaría consultar el id de la compra más grande ( total ) realizada por cada customer . Algo como esto:

 SELECT FIRST(id), customer, FIRST(total) FROM purchases GROUP BY customer ORDER BY total DESC; 
 PRIMERO (id) |  cliente |  PRIMERO (total)
 ---------- + ---------- + -------------
         1 |  Joe |  5
         2 |  Sally |  3

En Oracle 9.2+ (no 8i + como se dijo originalmente), SQL Server 2005+, PostgreSQL 8.4+, DB2, Firebird 3.0+, Teradata, Sybase, Vertica:

 WITH summary AS ( SELECT p.id, p.customer, p.total, ROW_NUMBER() OVER(PARTITION BY p.customer ORDER BY p.total DESC) AS rk FROM PURCHASES p) SELECT s.* FROM summary s WHERE s.rk = 1 

Compatible con cualquier database:

Pero necesitas agregar lógica para romper loops:

  SELECT MIN(x.id), -- change to MAX if you want the highest x.customer, x.total FROM PURCHASES x JOIN (SELECT p.customer, MAX(total) AS max_total FROM PURCHASES p GROUP BY p.customer) y ON y.customer = x.customer AND y.max_total = x.total GROUP BY x.customer, x.total 

En PostgreSQL esto suele ser más simple y más rápido (más optimization del performance a continuación):

 SELECT DISTINCT ON (customer) id, customer, total FROM purchases ORDER BY customer, total DESC, id; 

O más corto (si no tan claro) con numbers ordinales de columnas de salida:

 SELECT DISTINCT ON (2) id, customer, total FROM purchases ORDER BY 2, 3 DESC, 1; 

Si el total puede ser NULL (no le hará daño de ninguna manera, pero querrá hacer coincidir los índices existentes):

 ... ORDER BY customer, total DESC NULLS LAST , id; 

Puntos principales

  • DISTINCT ON es una extensión de PostgreSQL del estándar (donde solo se define DISTINCT en toda la list SELECT ).

  • Enumere cualquier número de expresiones en la cláusula DISTINCT ON , el valor de fila combinada define los duplicates. El manual:

    Obviamente, dos filas se consideran distintas si difieren en al less un valor de columna. Los valores nulos se consideran iguales en esta comparación.

    Negrita énfasis mío.

  • DISTINCT ON se puede combinar con ORDER BY . Las expresiones principales deben coincidir con las expresiones DISTINCT ON líderes en el mismo order. Puede agregar expresiones adicionales a ORDER BY para seleccionar una fila particular de cada grupo de pares. Agregué el id como último elemento para romper loops:

    "Elija la fila con la id más pequeña de cada grupo que comparte el total más alto".

    Si el total puede ser NULO, lo más probable es que desee la fila con el mayor valor no nulo. Agregue NULLS LAST como se demostró. Detalles:

    • PostgreSQL orderar por datetime asc, null primero?
  • La list SELECT no está restringida por expresiones en DISTINCT ON u ORDER BY de ninguna manera. (No es necesario en el caso simple anterior):

    • No tiene que include ninguna de las expresiones en DISTINCT ON ni ORDER BY .

    • Puede include cualquier otra expresión en la list SELECT . Esto es fundamental para replace consultas mucho más complejas con subconsultas y funciones agregadas / windows.

  • Probé con las versiones 8.3 – 10. Pero la característica ha estado allí al less desde la versión 7.1, así que básicamente siempre.

Índice

El índice perfecto para la consulta anterior sería un índice de varias columnas que abarca las tres columnas en la secuencia de coincidencia y con el order de sorting correspondiente:

 CREATE INDEX purchases_3c_idx ON purchases (customer, total DESC, id); 

Puede ser demasiado especializado para aplicaciones del mundo real. Pero úsela si el performance de lectura es crucial. Si tiene DESC NULLS LAST en la consulta, use el mismo en el índice para que Postgres conozca las coincidencias de order de sorting.

Eficacia / optimization del performance

Debe sopesar el costo y el beneficio antes de crear un índice personalizado para cada consulta. El potencial del índice anterior depende en gran medida de la distribución de datos .

El índice se utiliza porque entrega datos preorderados, y en Postgres 9.2 o posterior, la consulta también puede beneficiarse de un análisis de índice solamente si el índice es más pequeño que la tabla subyacente. Sin embargo, el índice debe escanearse en su totalidad.

  • Para pocas filas por cliente , esto es muy eficiente (incluso más si necesita salida orderada de todos modos). El beneficio se networkinguce con un número creciente de filas por cliente.
    Idealmente, tiene suficiente work_mem para procesar el paso de orderación involucrado en la RAM y no dertwigrlo en el disco. Generalmente, establecer work_mem demasiado alto puede tener efectos adversos. Considere SET LOCAL para consultas singulares en grandes sets. Encuentra cuánto necesitas con EXPLAIN ANALYZE . La mención de " Disco: " en el paso de sorting indica la necesidad de más:

    • Parámetro de configuration work_mem en PostgreSQL en Linux
    • Optimice la consulta simple usando ORDER BY date y text
  • Para muchas filas por cliente , una exploración de índice flexible sería (mucho) más eficiente, pero eso no se implementa actualmente en Postgres (hasta v10).
    Hay técnicas de consulta más rápidas para sustituir esto. En particular, si tiene una table separada con clientes únicos, que es el caso de uso típico. Pero también si no:

    • Optimizar la consulta GROUP BY para recuperar el último logging por usuario
    • Optimizar la consulta máxima de Groupwise
    • Consultar las últimas N filas relacionadas por fila

Punto de reference

Aquí tenía un punto de reference simple para Postgres 9.1, que estaba desactualizado para 2016. Así que ejecuté uno nuevo con una configuration mejor y reproducible para Postgres 9.4 y 9.5 y agregué los resultados detallados en otra respuesta .

Punto de reference

Probar los candidatos más interesantes con Postgres 9.4 y 9.5 con una tabla medianamente realist de 200k filas en purchases y 10k ID de customer_id distinto ( promedio de 20 filas por cliente ).

Para Postgres 9.5 realicé una segunda testing con 86446 clientes distintos. Vea a continuación ( promedio 2.3 filas por cliente ).

Preparar

Mesa principal

 CREATE TABLE purchases ( id serial , customer_id int -- REFERENCES customer , total int -- could be amount of money in Cent , some_column text -- to make the row bigger, more realistic ); 

Utilizo una serial (restricción PK agregada a continuación) y un número integer customer_id ya que esa es una configuration más típica. También se agregó some_column para compensar típicamente más columnas.

Datos ficticios, PK, índice: una tabla típica también tiene algunas tuplas muertas:

 INSERT INTO purchases (customer_id, total, some_column) -- insert 200k rows SELECT (random() * 10000)::int AS customer_id -- 10k customers , (random() * random() * 100000)::int AS total , 'note: ' || repeat('x', (random()^2 * random() * random() * 500)::int) FROM generate_series(1,200000) g; ALTER TABLE purchases ADD CONSTRAINT purchases_id_pkey PRIMARY KEY (id); DELETE FROM purchases WHERE random() > 0.9; -- some dead rows INSERT INTO purchases (customer_id, total, some_column) SELECT (random() * 10000)::int AS customer_id -- 10k customers , (random() * random() * 100000)::int AS total , 'note: ' || repeat('x', (random()^2 * random() * random() * 500)::int) FROM generate_series(1,20000) g; -- add 20k to make it ~ 200k CREATE INDEX purchases_3c_idx ON purchases (customer_id, total DESC, id); VACUUM ANALYZE purchases; 

tabla de customer : para una consulta superior

 CREATE TABLE customer AS SELECT customer_id, 'customer_' || customer_id AS customer FROM purchases GROUP BY 1 ORDER BY 1; ALTER TABLE customer ADD CONSTRAINT customer_customer_id_pkey PRIMARY KEY (customer_id); VACUUM ANALYZE customer; 

En mi segunda testing para 9.5 usé la misma configuration, pero con random() * 100000 para generar customer_id para get solo algunas filas por customer_id .

Tamaños de objects para purchases table

Generado con esta consulta .

  what | bytes/ct | bytes_pretty | bytes_per_row -----------------------------------+----------+--------------+--------------- core_relation_size | 20496384 | 20 MB | 102 visibility_map | 0 | 0 bytes | 0 free_space_map | 24576 | 24 kB | 0 table_size_incl_toast | 20529152 | 20 MB | 102 indexes_size | 10977280 | 10 MB | 54 total_size_incl_toast_and_indexes | 31506432 | 30 MB | 157 live_rows_in_text_representation | 13729802 | 13 MB | 68 ------------------------------ | | | row_count | 200045 | | live_tuples | 200045 | | dead_tuples | 19955 | | 

Consultas

1. row_number() en CTE, ( ver otra respuesta )

 WITH cte AS ( SELECT id, customer_id, total , row_number() OVER(PARTITION BY customer_id ORDER BY total DESC) AS rn FROM purchases ) SELECT id, customer_id, total FROM cte WHERE rn = 1; 

2. row_number() en la subconsulta (mi optimization)

 SELECT id, customer_id, total FROM ( SELECT id, customer_id, total , row_number() OVER(PARTITION BY customer_id ORDER BY total DESC) AS rn FROM purchases ) sub WHERE rn = 1; 

3. DISTINCT ON ( ver otra respuesta )

 SELECT DISTINCT ON (customer_id) id, customer_id, total FROM purchases ORDER BY customer_id, total DESC, id; 

4. rCTE con subconsulta LATERAL ( ver aquí )

 WITH RECURSIVE cte AS ( ( -- parentheses requinetworking SELECT id, customer_id, total FROM purchases ORDER BY customer_id, total DESC LIMIT 1 ) UNION ALL SELECT u.* FROM cte c , LATERAL ( SELECT id, customer_id, total FROM purchases WHERE customer_id > c.customer_id -- lateral reference ORDER BY customer_id, total DESC LIMIT 1 ) u ) SELECT id, customer_id, total FROM cte ORDER BY customer_id; 

5. tabla de customer con LATERAL ( ver aquí )

 SELECT l.* FROM customer c , LATERAL ( SELECT id, customer_id, total FROM purchases WHERE customer_id = c.customer_id -- lateral reference ORDER BY total DESC LIMIT 1 ) l; 

6. array_agg() con ORDER BY ( ver otra respuesta )

 SELECT (array_agg(id ORDER BY total DESC))[1] AS id , customer_id , max(total) AS total FROM purchases GROUP BY customer_id; 

Resultados

Tiempo de ejecución para las consultas anteriores con EXPLAIN ANALYZE (y todas las opciones desactivadas ), la mejor de 5 ejecuciones .

Todas las consultas utilizaron un Escaneo Solo Índice en purchases2_3c_idx (entre otros pasos). Algunos de ellos solo por el tamaño más pequeño del índice, otros más efectivamente.

A. Postgres 9.4 con 200k filas y ~ 20 por customer_id

 1. 273.274 ms 2. 194.572 ms 3. 111.067 ms 4. 92.922 ms 5. 37.679 ms -- winner 6. 189.495 ms 

B. Lo mismo con Postgres 9.5

 1. 288.006 ms 2. 223.032 ms 3. 107.074 ms 4. 78.032 ms 5. 33.944 ms -- winner 6. 211.540 ms 

C. Igual que B., pero con ~ 2.3 filas por customer_id

 1. 381.573 ms 2. 311.976 ms 3. 124.074 ms -- winner 4. 710.631 ms 5. 311.976 ms 6. 421.679 ms 

Punto de reference original (desactualizado) de 2011

Ejecuté tres testings con PostgreSQL 9.1 en una tabla de vida real de 65579 filas y índices btree de una columna en cada una de las tres columnas involucradas y obtuve el mejor time de ejecución de 5 ejecuciones.
Comparando la primera consulta ( A ) de @OMGPonies con la solución DISTINCT ON ( B ) anterior:

  1. Seleccione toda la tabla, los resultados en 5958 filas en este caso.

     A: 567.218 ms B: 386.673 ms 
  2. Utilice la condición WHERE customer BETWEEN x AND y resultando en 1000 filas.

     A: 249.136 ms B: 55.111 ms 
  3. Seleccione un solo cliente con WHERE customer = x .

     A: 0.143 ms B: 0.072 ms 

La misma testing se repite con el índice descrito en la otra respuesta

 CREATE INDEX purchases_3c_idx ON purchases (customer, total DESC, id); 

 1A: 277.953 ms 1B: 193.547 ms 2A: 249.796 ms -- special index not used 2B: 28.679 ms 3A: 0.120 ms 3B: 0.048 ms 

Este es el problema común de mayor n-por-grupo , que ya ha sido probado y soluciones altamente optimizadas . Personalmente prefiero la solución de unión izquierda de Bill Karwin (la publicación original con muchas otras soluciones ).

Tenga en count que sorprendentemente se pueden encontrar muchas soluciones a este problema común en una de las fonts más oficiales, el manual de MySQL . Consulte Ejemplos de consultas comunes :: Las filas que contienen el máximo de una determinada columna para grupos .

En Postgres puedes usar array_agg así:

 SELECT customer, (array_agg(id ORDER BY total DESC))[1], max(total) FROM purchases GROUP BY customer 

Esto le dará la id de la compra más grande de cada cliente.

Algunas cosas a tener en count:

  • array_agg es una function agregada, por lo que funciona con GROUP BY .
  • array_agg permite especificar un order con scope solo para sí mismo, por lo que no restringe la estructura de toda la consulta. También hay una syntax para la forma de orderar los valores NULL, si necesita hacer algo diferente al pnetworkingeterminado.
  • Una vez que construimos la matriz, tomamos el primer elemento. (Las matrices de Postgres tienen 1 índice, no 0-indexado).
  • Puede usar array_agg de forma similar para su tercera columna de salida, pero max(total) es más simple.
  • A diferencia de DISTINCT ON , el uso de array_agg permite mantener su GROUP BY , en caso de que lo desee por otros motivos.

La solución no es muy eficiente, como señaló Erwin, debido a la presencia de SubQs

 select * from purchases p1 where total in (select max(total) from purchases where p1.customer=customer) order by total desc; 

Lo uso de esta manera (postgresql solamente): https://wiki.postgresql.org/wiki/First/last_%28aggregate%29

 -- Create a function that always returns the first non-NULL item CREATE OR REPLACE FUNCTION public.first_agg ( anyelement, anyelement ) RETURNS anyelement LANGUAGE sql IMMUTABLE STRICT AS $$ SELECT $1; $$; -- And then wrap an aggregate around it CREATE AGGREGATE public.first ( sfunc = public.first_agg, basetype = anyelement, stype = anyelement ); -- Create a function that always returns the last non-NULL item CREATE OR REPLACE FUNCTION public.last_agg ( anyelement, anyelement ) RETURNS anyelement LANGUAGE sql IMMUTABLE STRICT AS $$ SELECT $2; $$; -- And then wrap an aggregate around it CREATE AGGREGATE public.last ( sfunc = public.last_agg, basetype = anyelement, stype = anyelement ); 

Entonces su ejemplo debería funcionar casi como está:

 SELECT FIRST(id), customer, FIRST(total) FROM purchases GROUP BY customer ORDER BY FIRST(total) DESC; 

CAVEAT: Ignora las filas NULL


Edit 1 – Use la extensión postgres en su lugar

Ahora uso de esta manera: http://pgxn.org/dist/first_last_agg/

Para instalar en ubuntu 14.04:

 apt-get install postgresql-server-dev-9.3 git build-essential -y git clone git://github.com/wulczer/first_last_agg.git cd first_last_app make && sudo make install psql -c 'create extension first_last_agg' 

Es una extensión de Postgres que le proporciona la primera y la última function; aparentemente más rápido que el path anterior.


Editar 2 – Ordenar y filtrar

Si usa funciones agregadas (como estas), puede orderar los resultados, sin la necesidad de tener los datos ya orderados:

 http://www.postgresql.org/docs/current/static/sql-expressions.html#SYNTAX-AGGREGATES 

Entonces, el ejemplo equivalente con orderar sería algo así como:

 SELECT first(id order by id), customer, first(total order by id) FROM purchases GROUP BY customer ORDER BY first(total); 

Por supuesto, puede orderar y filtrar como considere apropiado dentro del agregado; es una syntax muy poderosa.

Solución muy rápida

 SELECT a.* FROM purchases a JOIN ( SELECT customer, min( id ) as id FROM purchases GROUP BY customer ) b USING ( id ); 

y realmente muy rápido si la tabla está indexada por id:

 create index purchases_id on purchases (id); 

La solución "Apoyada por cualquier database" aceptada por los pony de OMG tiene buena velocidad de mi testing.

Aquí proporciono una solución de database similar, pero más completa y más limpia. Se consideran vínculos (suponga el deseo de get solo una fila para cada cliente, incluso loggings múltiples para un total máximo por cliente), y se seleccionarán otros campos de compras (por ejemplo, purchase_payment_id) para las filas reales que coinciden en la tabla de compras.

Compatible con cualquier database:

 select * from purchase join ( select min(id) as id from purchase join ( select customer, max(total) as total from purchase group by customer ) t1 using (customer, total) group by customer ) t2 using (id) order by customer 

Esta consulta es razonablemente rápida, especialmente cuando hay un índice compuesto (cliente, total) en la tabla de compras.

Observación:

  1. t1, t2 son alias de subconsulta que podrían eliminarse según la database.

  2. Advertencia : la cláusula using (...) no está actualmente admitida en MS-SQL y Oracle db a partir de esta edición de enero de 2017. Tiene que expandirla usted mismo, por ejemplo, on t2.id = purchase.id etc. La syntax de USING funciona en SQLite, MySQL y PostgreSQL.