sql - valores - ¿Seleccionar primera fila en cada grupo por grupo?

sql contar registros agrupados

Question

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

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

SELECT * FROM purchases;

Mi salida:

id | customer | total ---+----------+------ 1 | Joe | 5 2 | Sally | 3 3 | Joe | 2 4 | Sally | 1

Me gustaría consultar la 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;

Rendimiento esperado:

FIRST(id) | customer | FIRST(total) ----------+----------+------------- 1 | Joe | 5 2 | Sally | 3

Answer 1

Punto de referencia

Probar a los candidatos más interesantes con Postgres 9.4 y 9.5 con una tabla realista a mitad de camino de 200k filas en purchases y 10k de ID de customer_id distintos ( promedio de 20 filas por cliente ).

Para Postgres 9.5, realicé una segunda prueba con 86446 clientes distintos. Ver abajo ( promedio de 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 de customer_id entero, ya que es una configuración 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 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 prueba para 9.5 usé la misma configuración, pero con random() * 100000 para generar customer_id para obtener solo unas pocas filas por customer_id .

Tamaños de objetos para la purchases mesas.

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 optimización)

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 required
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. mesa 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 ), lo mejor de 5 ejecuciones .

Todas las consultas utilizaron un escaneo de índice solo 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

Referencia original (desactualizada) de 2011

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

1. Seleccionar toda la tabla, da como resultado 5958 filas en este caso.

   A: 567.218 ms
   B: 386.673 ms

2. Use 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

Misma prueba repetida 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

Answer 2

En Oracle 9.2+ (no 8i + como se indicó 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

Soportado por cualquier base de datos:

Pero necesitas agregar lógica para romper los lazos:

 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

Answer 3

En PostgreSQL, esto suele ser más simple y más rápido (más optimización de rendimiento a continuación):

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

O más corto (si no es tan claro) con números 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 se verá afectado 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 lista SELECT ).

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

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

  Énfasis en negrita el mio

• DISTINCT ON se puede combinar con ORDER BY . Las expresiones iniciales deben coincidir con las expresiones DISTINCT ON en el mismo orden. Puede agregar expresiones adicionales a ORDER BY para elegir una fila particular de cada grupo de pares. Agregué id como último artículo para romper lazos:

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

  Si el total puede ser NULL, lo más probable es que desee la fila con el mayor valor no nulo. Añadir NULLS LAST como demostrado. Detalles:

  • PostgreSQL ordenar por fecha y hora asc, nulo primero?

• La lista SELECT no está limitada por las expresiones en DISTINCT ON u ORDER BY de ninguna manera. (No es necesario en el caso simple arriba):

  • No tiene que incluir ninguna de las expresiones en DISTINCT ON o ORDER BY .

  • Puede incluir cualquier otra expresión en la lista SELECT . Esto es fundamental para reemplazar consultas mucho más complejas con subconsultas y funciones agregadas / ventana.

• Probé con las versiones 8.3 - 10 de Postgres. Pero la característica ha estado allí al menos 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 abarque las tres columnas en una secuencia coincidente y con un orden de clasificación coincidente:

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

Puede ser demasiado especializado para aplicaciones del mundo real. Pero úsalo si el rendimiento de lectura es crucial. Si tiene DESC NULLS LAST en la consulta, use el mismo en el índice para que Postgres sepa que las ordenaciones coinciden.

Eficacia / Optimización del rendimiento.

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 usa porque entrega datos preclasificados, y en Postgres 9.2 o posterior, la consulta también puede beneficiarse de una exploración de índice solo si el índice es más pequeño que la tabla subyacente. Sin embargo, el índice debe ser escaneado en su totalidad.

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

  • Parámetro de configuración work_mem en PostgreSQL en Linux
  • Optimice la consulta simple utilizando ORDEN POR fecha y texto

• Para muchas filas por cliente , un escaneo de índice suelto sería (mucho) más eficiente, pero eso no está implementado actualmente en Postgres (hasta v10).
  Hay técnicas de consulta más rápidas para sustituir esto. En particular, si tiene una tabla separada con clientes únicos, que es el caso de uso típico. Pero también si no lo haces:

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

Punto de referencia

Tenía un punto de referencia simple aquí que está obsoleto por ahora. Lo reemplacé con un punto de referencia detallado en esta respuesta por separado .

Answer 4

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 cuenta:

• array_agg es una función agregada, por lo que funciona con GROUP BY .
• array_agg permite especificar un orden de alcance solo para sí mismo, por lo que no restringe la estructura de toda la consulta. También hay una sintaxis de cómo ordenar NULL, si necesita hacer algo diferente de la predeterminada.
• Una vez que construimos la matriz, tomamos el primer elemento. (Las matrices de Postgres son 1-indexadas, no 0-indexadas).
• Podría usar array_agg de una manera 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 conservar su GROUP BY , en caso de que lo desee por otros motivos.

Answer 5

Este es el problema greatest-n-per-group común greatest-n-per-group , que ya cuenta con soluciones bien probadas y altamente optimizadas . Personalmente, prefiero la solución de unión izquierda de Bill Karwin (la publicación original con muchas otras soluciones ).

Tenga en cuenta que muchas de las soluciones oficiales para este problema común se pueden encontrar en una de las fuentes más oficiales, ¡el manual de MySQL ! Consulte Ejemplos de consultas comunes: las filas que contienen el máximo grupal de una determinada columna .

Answer 6

La consulta:

SELECT purchases.*
FROM purchases
LEFT JOIN purchases as p
ON
p.customer = purchases.customer
AND
purchases.total < p.total
WHERE p.total IS NULL

¡CÓMO FUNCIONA! (He estado allí)

Queremos asegurarnos de que solo tenemos el total más alto para cada compra.

Algunas cosas teóricas (omita esta parte si solo quiere entender la consulta)

Deje que Total sea una función T (cliente, id) donde devuelve un valor dado el nombre y el id. Para demostrar que el total dado (T (cliente, id)) es el más alto que tenemos para demostrar que queremos probar cualquiera

• ∀x T (customer, id)> T (customer, x) (este total es más alto que el resto del total para ese cliente)

O

• ¬∃x T (cliente, id) <T (cliente, x) (no existe un total más alto para ese cliente)

El primer enfoque nos requerirá que obtengamos todos los registros para ese nombre que realmente no me gustan.

El segundo necesitará una forma inteligente de decir que no puede haber un registro más alto que este.

Volver a SQL

Si dejamos unir la tabla en el nombre y el total es menor que la tabla unida:

 LEFT JOIN purchases as p
ON
p.customer = purchases.customer
AND
purchases.total < p.total

nos aseguramos de que todos los registros que tengan otro registro con el total más alto para el mismo usuario se unan:

purchases.id, purchases.customer, purchases.total, p.id, p.customer, p.total
1 , Tom , 200 , 2 , Tom , 300
2 , Tom , 300
3 , Bob , 400 , 4 , Bob , 500
4 , Bob , 500
5 , Alice , 600 , 6 , Alice , 700
6 , Alice , 700

Eso nos ayudará a filtrar el total más alto para cada compra sin necesidad de agrupación:

WHERE p.total IS NULL
purchases.id, purchases.name, purchases.total, p.id, p.name, p.total
2 , Tom , 300
4 , Bob , 500
6 , Alice , 700

Y esa es la respuesta que necesitamos.

Answer 7

La solución de "Compatibilidad con cualquier base de datos" de OMG Ponies aceptada tiene buena velocidad de mi prueba.

Aquí proporciono un mismo enfoque, pero una solución de base de datos más completa y limpia. Se consideran los vínculos (suponga que desea obtener solo una fila para cada cliente, incluso varios registros para el total máximo por cliente), y se seleccionarán otros campos de compra (por ejemplo, purchase_payment_id) para las filas reales coincidentes en la tabla de compras.

Soportado por cualquier base de datos:

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 como (cliente, total) en la tabla de compras.

Observación:

1. t1, t2 son alias de subconsulta que podrían eliminarse dependiendo de la base de datos.

2. Advertencia : la cláusula using (...) actualmente no es compatible con MS-SQL y Oracle db a partir de esta edición en enero de 2017. on t2.id = purchase.id ampliarla usted mismo, por ejemplo, on t2.id = purchase.id etc. La sintaxis de USING Trabaja en SQLite, MySQL y PostgreSQL.

Answer 8

La solución no es muy eficiente según lo señalado por 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;

Answer 9

Solucion muy rapida

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

Answer 10

Uso de esta manera (solo postgresql): 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 tu ejemplo debería funcionar casi como está:

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

CUEVA: Ignora las filas nulas.

Edición 1 - Use la extensión postgres en su lugar

Ahora lo 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 te da las primeras y últimas funciones; Aparentemente más rápido que el camino anterior.

Edit 2 - Pedidos y filtrado

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

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

Entonces el ejemplo equivalente, con ordenar sería algo 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 ordenar y filtrar según lo considere conveniente dentro del agregado; Es una sintaxis muy poderosa.

Answer 11

• Si desea seleccionar cualquier fila (por su condición específica) del conjunto de filas agregadas.

• Si desea utilizar otra función de agregación ( sum/avg ) además de max/min . Por lo tanto no se puede usar la pista con DISTINCT ON

Puedes usar la siguiente subconsulta:

SELECT
(
SELECT **id** FROM t2
WHERE id = ANY ( ARRAY_AGG( tf.id ) ) AND amount = MAX( tf.amount )
) id,
name,
MAX(amount) ma,
SUM( ratio )
FROM t2 tf
GROUP BY name

Puede reemplazar la amount = MAX( tf.amount ) con cualquier condición que desee con una restricción: esta subconsulta no debe devolver más de una fila

Pero si quieres hacer esas cosas, probablemente estés buscando funciones de ventana.