Pregunta Seleccione la primera fila en cada grupo GROUP BY?


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 un purchases mesa que se ve así:

SELECT * FROM purchases;

Mi salida:

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

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

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

Rendimiento esperado:

PRIMERO (id) | cliente | PRIMERO (total)
---------- + ---------- + -------------
        1 | Joe | 5
        2 | Sally | 3

896
2017-09-27 01:23


origen


Respuestas:


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 base de datos:

Pero necesitas agregar lógica para romper 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

800
2017-09-27 01:27



En PostgreSQL esto es típicamente más simple y más rápido (más optimización del rendimiento a continuación):

SELECT DISTINTO EN (customer)
       id, customer, total
FROM   purchases
ORDER  BY customer, total DESC, id;

O más corto (si no 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 total puede ser NULL (no le hará ningún daño, pero querrá unir los índices existentes):

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

Puntos principales

  • DISTINCT ON es una extensión PostgreSQL del estándar (donde solo DISTINCT en conjunto SELECT la lista está definida).

  • Enumera cualquier cantidad de expresiones en DISTINCT ON cláusula, el valor de fila combinada define duplicados. El manual:

    Obviamente, dos filas se consideran distintas si difieren, al menos,   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 principales expresiones tienen que coincidir con DISTINCT ON expresiones en el mismo orden Puedes añadir adicional expresiones a ORDER BY para elegir una fila en particular de cada grupo de pares. yo añadí id como último elemento para romper lazos:

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

    Si total puede ser NULL, tu más probablemente quiere la fila con el mayor valor no nulo. Añadir NULLS LAST como demostrado. Detalles:

  • los SELECT lista no está limitado por expresiones en DISTINCT ON o ORDER BY de cualquier manera. (No es necesario en el caso simple anterior):

    • no tiene que incluir cualquiera de las expresiones en DISTINCT ON o ORDER BY.

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

  • Probé con las versiones 8.3-10 de Postgres. Pero la función ha estado allí al menos desde la versión 7.1, así que básicamente siempre.

Índice

los Perfecto índice para la consulta anterior sería una índice de varias columnas abarcando las tres columnas en la secuencia de coincidencia y con el orden de clasificación 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 rendimiento de lectura es crucial. Si usted tiene DESC NULLS LAST en la consulta, use el mismo en el índice para que Postgres conozca las coincidencias de orden de clasificación.

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 distribución de datos.

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

Punto de referencia

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


809
2017-10-03 02:21



Punto de referencia

Probando los candidatos más interesantes con Postgres 9.4 y 9.5 con una tabla a la mitad realista de 200k filas en purchases y 10k distinto customer_id (prom. 20 filas por cliente)

Para Postgres 9.5 realicé una segunda prueba con 86446 clientes distintos. Vea abajo (prom. 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
);

Uso un serial (Restricción de PK agregada a continuación) y un entero customer_id ya que esa 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;

customer tabla - 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 utilicé la misma configuración, pero con random() * 100000 para generar customer_id para obtener solo unas pocas filas por customer_id.

Tamaños de objeto para la tabla purchases

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 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 LATERAL subconsulta (mira 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. customer mesa con LATERAL (mira 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 apagado), lo mejor de 5 carreras.

Todas consultas usadas 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 referencia original (desactualizado) de 2011

Ejecuté tres pruebas con PostgreSQL 9.1 en una tabla de vida real de 65579 filas e índices btree de una columna en cada una de las tres columnas involucradas y tomó la mejor Tiempo de ejecución de 5 carreras.
Comparando @OMGPonies ' primera consulta (A) al encima DISTINCT ON solución (B)

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

    A: 567.218 ms
    B: 386.673 ms
    
  2. Condición de uso WHERE customer BETWEEN x AND y lo que resulta 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 prueba se repite con el índice descrito en la otra respuesta

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


82
2018-01-11 06:05



Esto es común  problema, que ya ha sido probado y altamente soluciones optimizadas. Personalmente prefiero el left join solution por Bill Karwin (el publicación original con muchas otras soluciones)

Tenga en cuenta que sorprendentemente se puede encontrar un montón de soluciones a este problema común en una de las fuentes más oficiales, Manual de MySQL! Ver Ejemplos de consultas comunes :: Las filas que contienen el máximo sabio de una determinada columna.


37
2018-06-27 08:38



En Postgres puedes usar array_agg Me gusta esto:

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

Esto te dará el 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 le permite especificar un orden con alcance solo para sí mismo, por lo que no restringe la estructura de toda la consulta. También hay una sintaxis para la forma de ordenar los valores NULL, si necesita hacer algo diferente al predeterminado.
  • Una vez que construimos la matriz, tomamos el primer elemento. (Las matrices de Postgres tienen 1 índice, no 0-indexado).
  • Podrías usar array_agg de manera similar para su tercera columna de salida, pero max(total) es mas simple
  • diferente a DISTINCT ON, utilizando array_agg te deja mantener tu GROUP BY, en caso de que quiera eso por otros motivos.

20
2017-08-27 18:14



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;

11
2018-06-17 18:02



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 tu ejemplo debería funcionar casi como es:

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 función; aparentemente más rápido que el camino anterior.


Editar 2 - Ordenar y filtrar

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 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 ordenar y filtrar como considere apropiado dentro del agregado; es una sintaxis muy poderosa.


6
2018-03-10 15:19