Como o título sugere, I'gostaria de selecionar a primeira linha de cada conjunto de linhas agrupadas com um 'GRUPO POR'.
Especificamente, se I'tenho uma tabela de "compras" que se parece com isto:
SELECT * FROM purchases;
A minha saída:
id | cliente | total ---+----------+------ 1 | Joe | 5 2 | Sally | 3 3 | Joe | 2 4 | Sally | 1 I'gostaria de consultar oid
da maior compra (total
) feita por cadacliente
. Algo parecido com isto:SELECT FIRST(id), customer, FIRST(total) FROM purchases GROUP BY customer ORDER BY total DESC;
Produção prevista:
FIRST(id) | cliente | FIRST(total) ----------+----------+------------- 1 | Joe | 5 2 | Sally | 3
Em PostgreSQL isto é tipicamente **simpler e mais rápido*** (mais otimização de performance abaixo):
SELECT DISTINCT ON (cliente)
id, cliente, total
DE compras
ORDEM POR cliente, 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 (won't hurt either way, but you'will want to match existing indexes):
...
ORDEM POR cliente, total DESC NULLS LAST, id;
####Pontos maiores
- [**`DISTINCT ON`***][1] é uma extensão PostgreSQL do padrão (onde apenas `DISTINCT` em toda a lista `SELECT` está definida).
- Liste qualquer número de expressões na cláusula `DISTINCT ON`, o valor da linha combinada define duplicatas. [O manual:][2]
> Obviamente, duas filas são consideradas distintas se diferirem em pelo menos
> valor de uma coluna. **Os valores nulos são considerados iguais nesta comparação.**
Minhas palavras com ênfase em negrito.
- DISTINCT ON' pode ser combinado com **`ORDER BY`***. As expressões principais têm de corresponder às expressões principais `DISTINCT ON` na mesma ordem. Você pode adicionar *expressões adicionais* a `ORDER BY` para escolher uma linha em particular de cada grupo de pares. Eu adicionei `id` como último item para quebrar laços:
*"Escolha a linha com o menor `id` de cada grupo compartilhando o maior `total`."*
Para ordenar os resultados de uma forma que discorde da ordem de ordenação que determina o primeiro por grupo, você pode aninhar a consulta acima em uma consulta externa com outro `ORDER BY`. Como:
- https://stackoverflow.com/questions/9795660/postgresql-distinct-on-with-different-order-by/9796104#9796104
- Se `total` pode ser NULL, você *most provavelmente* quer a linha com o maior valor não-nulo. Adicione **`NULLS LAST`*** como demonstrado. Detalhes:
- https://stackoverflow.com/questions/9510509/postgresql-sort-by-datetime-asc-null-first/9511492#9511492
- **A lista `SELECT`** não é restringida por expressões em `DISTINCT ON` ou `ORDER BY` de forma alguma. (Não é necessário no caso simples acima):
- Você *don'não precisa* incluir qualquer uma das expressões em `DISTINCT ON` ou `ORDER BY`.
- Você *pode* incluir qualquer outra expressão na lista `SELECT`. Isto é fundamental para substituir consultas muito mais complexas por subconsultas e funções agregadas/janela.
- Eu testei com as versões 8.3 - 12 do Postgres. Mas o recurso está lá pelo menos desde a versão 7.1, então basicamente sempre.
##Index
O índice *perfeito* para a consulta acima seria um [índice multicoluna][3] abrangendo as três colunas em seqüência e com ordem de ordenação correspondentes:
CREATE INDEX purchases_3c_idx ON purchases (customer, total DESC, id);
Talvez seja demasiado especializado. Mas use-o se a performance de leitura para a consulta em particular for crucial. Se você tiver DESC NULLS LAST
na consulta, utilize o mesmo no índice para que a ordem de ordenação corresponda e o índice seja aplicável.
Pese o custo e o benefício antes de criar índices personalizados para cada consulta. O potencial do índice acima depende em grande parte de **dados de distribuição*. O índice é utilizado porque fornece dados pré-seleccionados. No Postgres 9.2 ou posterior, a consulta também pode beneficiar de um index only scan** se o índice for menor do que a tabela subjacente. No entanto, o índice tem de ser digitalizado na sua totalidade.
customer
), isto é muito eficiente. Ainda mais se você precisar de uma saída ordenada de qualquer maneira. O benefício diminui com um número crescente de filas por cliente.work_mem
*** para processar a etapa de ordenação envolvida na RAM e não derramar para o disco. Mas geralmente definir work_mem
too high pode ter efeitos adversos. Considere SET LOCAL
para consultas excepcionalmente grandes. Descubra o quanto você precisa com o EXPLAIN ANALYZE
. Menção de "Disk:" na etapa de ordenação indica a necessidade de mais:customer
), um [loose index scan]7 (a.k.a. "skip scan") seria (muito) mais eficiente, mas isso's não é implementado até Postgres 12. (Uma implementação para scans apenas de índice está em desenvolvimento para Postgres 13. Veja aqui e aqui.)Eu tinha aqui uma simples referência que já está ultrapassada. Eu o substituí por um detalhado benchmark nesta resposta separada.
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
Mas precisas de acrescentar lógica para quebrar 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
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;