Selecione a primeira linha em cada grupo GROUP BY?

Como o título sugere, gostaria de selecionar a primeira linha de cada conjunto de linhas agrupadas com GROUP BY .

Especificamente, se eu tenho uma tabela de purchases que se parece com isso:

 SELECT * FROM purchases; 

Minha saída:

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

Gostaria de consultar o id da maior compra ( total ) feita por cada customer . Algo assim:

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

Saída esperada:

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

No Oracle 9.2+ (não 8i + como originalmente indicado), 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 

Suportado por qualquer database:

Mas você precisa adicionar lógica para quebrar os laços:

  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 

No PostgreSQL isso é tipicamente mais simples e rápido (mais otimização de desempenho abaixo):

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

Ou mais curto (se não tão claro) com números ordinais de colunas de saída:

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

Se total pode ser NULL (não vai doer de qualquer maneira, mas você vai querer coincidir com os índices existentes):

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

Pontos principais

  • DISTINCT ON é uma extensão PostgreSQL do padrão (onde apenas DISTINCT em toda a lista SELECT é definida).

  • Listar qualquer número de expressões na cláusula DISTINCT ON , o valor da linha combinada define duplicatas. O manual:

    Obviamente, duas linhas são consideradas distintas se diferirem em pelo menos um valor de coluna. Valores nulos são considerados iguais nesta comparação.

    Ousada ênfase minha.

  • DISTINCT ON pode ser combinado com ORDER BY . As expressões iniciais devem corresponder às expressões DISTINCT ON na mesma ordem. Você pode adicionar expressões adicionais a ORDER BY para escolher uma linha específica de cada grupo de pares. Eu adicionei id como último item para quebrar os laços:

    “Escolha a linha com o menor id de cada grupo compartilhando o maior total .”

    Se total puder ser NULL, você provavelmente desejará a linha com o maior valor não nulo. Adicione NULLS LAST como demonstrado. Detalhes:

    • PostgreSQL classificar por datetime asc, null primeiro?
  • A lista SELECT não é restrita por expressões em DISTINCT ON ou ORDER BY de qualquer forma. (Não é necessário no caso simples acima):

    • Você não precisa include nenhuma das expressões em DISTINCT ON ou ORDER BY .

    • Você pode include qualquer outra expressão na lista SELECT . Isso é fundamental para replace consultas muito mais complexas com subqueries e funções de agregação / janela.

  • Eu testei com versões do Postgres 8.3 – 10. Mas o recurso foi lá pelo menos desde a versão 7.1, então basicamente sempre.

Índice

O índice perfeito para a consulta acima seria um índice de várias colunas abrangendo todas as três colunas na sequência correspondente e com a ordem de sorting correspondente:

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

Pode ser especializado demais para aplicativos do mundo real. Mas use-o se o desempenho de leitura for crucial. Se você tiver DESC NULLS LAST na consulta, use o mesmo no índice para que o Postgres saiba as correspondências de ordem de sorting.

Eficácia / otimização de desempenho

Você precisa ponderar o custo e o benefício antes de criar um índice personalizado para cada consulta. O potencial do índice acima depende em grande parte da distribuição de dados .

O índice é usado porque fornece dados pré-classificados e, no Postgres 9.2 ou posterior, a consulta também pode se beneficiar de uma varredura de índice apenas se o índice for menor que a tabela subjacente. O índice tem que ser escaneado na sua totalidade, no entanto.

  • Para algumas linhas por cliente , isso é muito eficiente (ainda mais se você precisar de uma saída ordenada). O benefício diminui com um número crescente de linhas por cliente.
    Idealmente, você tem work_mem suficiente para processar a etapa de sorting envolvida na RAM e não derramar no disco. Geralmente, configurar work_mem muito alto pode ter efeitos adversos. Considere SET LOCAL para consultas excepcionalmente grandes. Encontre o quanto você precisa com o EXPLAIN ANALYZE . A menção de ” Disco: ” na etapa de sorting indica a necessidade de mais:

    • Parâmetro de configuração work_mem no PostgreSQL no Linux
    • Otimize a consulta simples usando ORDER BY date e text
  • Para muitas linhas por cliente , uma varredura de índice flexível seria (muito) mais eficiente, mas isso não é implementado atualmente no Postgres (até a v10).
    Existem técnicas de consulta mais rápidas para replace isso. Em particular, se você tiver uma tabela separada com clientes exclusivos, que é o caso de uso típico. Mas também se você não:

    • Otimize a consulta GROUP BY para recuperar o registro mais recente por usuário
    • Otimizar consulta máxima de grupos
    • Consultar as últimas N linhas relacionadas por linha

Referência

Eu tinha um benchmark simples aqui que está desatualizado agora. Eu substituí-lo com uma referência detalhada nesta resposta separada .

Referência

Testando os candidatos mais interessantes com o Postgres 9.4 e 9.5, com uma tabela realista de meio de 200k linhas em purchases e 10k customer_id distinta ( média de 20 linhas por cliente ).

Para o Postgres 9.5, executei um segundo teste com efetivamente 86446 clientes distintos. Veja abaixo ( média de 2,3 linhas por cliente ).

Configuração

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

Eu uso um serial (restrição de PK adicionada abaixo) e um inteiro customer_id já que é uma configuração mais típica. Também foi adicionado some_column para compensar normalmente mais colunas.

Dados fictícios, PK, índice – uma tabela típica também tem algumas tuplas mortas:

 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; 

tabela de customer – para uma 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; 

No meu segundo teste para 9,5, usei a mesma configuração, mas com random() * 100000 para gerar customer_id para obter apenas algumas linhas por customer_id .

Tamanhos de object para purchases mesa

Gerado com 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() no CTE, ( veja outra resposta )

 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() na subconsulta (minha otimização)

 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 outra resposta )

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

4. rCTE com subquery LATERAL ( veja aqui )

 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. tabela do customer com LATERAL ( veja aqui )

 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() com ORDER BY ( veja outra resposta )

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

Resultados

Tempo de execução para as consultas acima com EXPLAIN ANALYZE (e todas as opções desativadas ), melhor de 5 execuções .

Todas as consultas usaram uma varredura somente de índice em purchases2_3c_idx (entre outras etapas). Alguns deles apenas para o tamanho menor do índice, outros mais efetivamente.

A. Postgres 9.4 com 200k linhas e ~ 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. O mesmo com o 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. O mesmo que B., mas com ~ 2,3 linhas 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 

Referência original (desatualizada) de 2011

Executei três testes com o PostgreSQL 9.1 em uma tabela real de 65579 linhas e índices btree de coluna única em cada uma das três colunas envolvidas e obtive o melhor tempo de execução de 5 execuções.
Comparando a primeira consulta do @OMGPonies ( A ) com a solução DISTINCT ON ( B ) acima:

  1. Selecione a tabela inteira, resulta em 5958 linhas neste caso.

     A: 567.218 ms B: 386.673 ms 
  2. Use a condição WHERE customer BETWEEN x AND y resultando em 1000 linhas.

     A: 249.136 ms B: 55.111 ms 
  3. Selecione um único cliente com WHERE customer = x .

     A: 0.143 ms B: 0.072 ms 

Mesmo teste repetido com o índice descrito na outra resposta

 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 

Esse é o problema comum de maior n-por-grupo , que já tem soluções bem testadas e altamente otimizadas . Pessoalmente eu prefiro a solução de junit à esquerda por Bill Karwin (o post original com muitas outras soluções ).

Note que muitas soluções para este problema comum podem ser surpreendentemente encontradas em uma das fonts mais oficiais, o manual do MySQL ! Veja Exemplos de Consultas Comuns :: As Linhas Segurando o Máximo de Grupo de uma Certa Coluna .

No Postgres você pode usar array_agg assim:

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

Isso fornecerá o id da maior compra de cada cliente.

Algumas coisas para anotar:

  • array_agg é uma function agregada, então funciona com GROUP BY .
  • array_agg permite que você especifique um escopo de ordenação apenas para si mesmo, de modo que não restrinja a estrutura de toda a consulta. Há também a syntax de como você classifica NULLs, se você precisar fazer algo diferente do padrão.
  • Uma vez que construímos o array, pegamos o primeiro elemento. (Os arrays Postgres são 1 indexados, não 0 indexados).
  • Você poderia usar array_agg maneira semelhante para sua terceira coluna de saída, mas max(total) é mais simples.
  • Ao contrário de DISTINCT ON , usar array_agg permite que você mantenha seu GROUP BY , caso deseje isso por outros motivos.

A solução não é muito eficiente, como apontado por Erwin, devido à presença de SubQs

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

Eu uso dessa maneira (somente 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 ); 

Então o seu exemplo deve funcionar quase como está:

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

CAVEAT: ignora as linhas NULL


Editar 1 – Use a extensão postgres

Agora eu uso dessa maneira: http://pgxn.org/dist/first_last_agg/

Para instalar no 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' 

É uma extensão postgres que lhe dá a primeira e última funções; aparentemente mais rápido do que o caminho acima.


Editar 2 – Ordenação e filtragem

Se você usar funções agregadas (como estas), poderá solicitar os resultados, sem a necessidade de ter os dados já solicitados:

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

Então, o exemplo equivalente, com o pedido seria algo como:

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

É claro que você pode pedir e filtrar conforme achar adequado dentro do agregado; é uma syntax muito poderosa.

Solução muito rápida

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

e realmente muito rápido se a tabela é indexada por id:

 create index purchases_id on purchases (id); 

A pergunta:

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

COMO ISSO FUNCIONA! (Eu estive lá)

Queremos ter certeza de que só temos o maior total para cada compra.


Algumas Coisas Teóricas (pule esta parte se você quiser apenas entender a consulta)

Seja Total uma function T (customer, id) onde retorna um valor dado o nome e id Para provar que o total dado (T (customer, id)) é o maior que temos para provar que queremos provar

  • ∀x T (cliente, id)> T (cliente, x) (esse total é maior do que o outro total para esse cliente)

OU

  • ¬∃x T (cliente, id)

A primeira abordagem precisará de nós para obter todos os registros para esse nome que eu realmente não gosto.

O segundo vai precisar de uma maneira inteligente de dizer que não pode haver um registro maior do que este.


De volta ao SQL

Se saímos juntamos a tabela no nome e o total é menor que a tabela unida:

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

Nos certificamos de que todos os registros que possuam outro registro com o total mais alto para o mesmo usuário sejam unidos:

 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 

Isso nos ajudará a filtrar pelo maior total de cada compra sem necessidade de agrupamento:

 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 

E essa é a resposta que precisamos.

A solução aceita “OMG Ponies” “Suportado por qualquer database” tem boa velocidade do meu teste.

Aqui eu forneço uma abordagem igual, mas mais completa e limpa qualquer solução de database. Os laços são considerados (suponha-se desejo de obter apenas uma linha para cada cliente, até vários registros para o total máximo por cliente) e outros campos de compra (por exemplo, purchase_payment_id) serão selecionados para as linhas reais correspondentes na tabela de compras.

Suportado por qualquer 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 

Essa consulta é razoavelmente rápida, especialmente quando há um índice composto como (cliente, total) na tabela de compras.

Observação:

  1. t1, t2 são alias de subconsulta que podem ser removidos dependendo do database.

  2. Ressalva : atualmente, a cláusula using (...) não é suportada no MS-SQL e no database Oracle a partir desta edição em janeiro de 2017. Você deve expandi-la por exemplo on t2.id = purchase.id etc. A syntax USING trabalha em SQLite, MySQL e PostgreSQL.