sql - valores - ¿Seleccionar primera fila en cada grupo por grupo?
sql contar registros agrupados (11)
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
):
Seleccionar toda la tabla, da como resultado 5958 filas en este caso.
A: 567.218 ms B: 386.673 ms
Use la condición
WHERE customer BETWEEN x AND y
resultando en 1000 filas.A: 249.136 ms B: 55.111 ms
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
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
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
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 defineDISTINCT
en toda la listaSELECT
).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 conORDER BY
. Las expresiones iniciales deben coincidir con las expresionesDISTINCT ON
en el mismo orden. Puede agregar expresiones adicionales aORDER 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 eltotal
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ñadirNULLS LAST
como demostrado. Detalles:La lista
SELECT
no está limitada por las expresiones enDISTINCT ON
uORDER BY
de ninguna manera. (No es necesario en el caso simple arriba):No tiene que incluir ninguna de las expresiones en
DISTINCT ON
oORDER 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 suficientework_mem
para procesar el paso de clasificación involucrado en la RAM y no para derramar en el disco. Generalmente, el ajuste dework_mem
demasiado alto puede tener efectos adversos. ConsidereSET LOCAL
para consultas excepcionalmente grandes. Encuentra cuánto necesitas conEXPLAIN ANALYZE
. La mención de " Disco: " en el paso de clasificación indica la necesidad de más: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:
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 .
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 conGROUP 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, peromax(total)
es más simple. - A diferencia de
DISTINCT ON
, el uso dearray_agg
permite conservar suGROUP BY
, en caso de que lo desee por otros motivos.
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 .
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.
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:
t1, t2 son alias de subconsulta que podrían eliminarse dependiendo de la base de datos.
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.
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;
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);
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.
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 demax/min
. Por lo tanto no se puede usar la pista conDISTINCT 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.