2015-06-17 23:06 GMT-03:00 Luciano Bierhals <[email protected]>:
> Select r1.*
> from registros r1
> join (select max(data) as data, tipo
> from registro
> group by tipo) r2 on r1.tipo = r2.tipo and r1.data = r2.data
>
Isso é geralmente conhecido como "groupwise max" e exitem várias formas de
resolver, a sua é uma delas. Não tenho certeza da performance, mas
considerando muitos dados, possivelmente a agregação seria feita com um
HashAggregate, e o join um NestedLoop (talvez?) usando um índice em (tipo,
data). Sendo N = número de "tipos" distintos e M = número de tuplas em
"registros", você teria então: O(M) [para o HashAggregate, que retornaria N
tuplas] + O(NlogM) = O(M + NlogM). -- alguém me corrija se eu estiver
errado, acabei de acordar, o Tico ainda dorme.
A forma que geralmente oferece melhor performance, mas só funciona a partir
da versão 9.3, é usando o LATERAL. Mas, para tanto, você deve ter uma
tabela que referencia "registros" e armazena os "tipos" de forma única,
assumindo que essa tabela exista e se chame "tipos", a consulta seria:
SELECT t.tipo, r.*
FROM tipos t, LATERAL(
SELECT max(r1.data) AS data
FROM registros r1
WHERE r1.tipo = t.tipo
) r;
Ou, caso queira outras informações do registro (não somente a data), você
pode usar o ORDER BY + LIMIT (ambas devem produzir o mesmo plano de
execução):
SELECT t.tipo, r.*
FROM tipos t, LATERAL(
SELECT r1.*
FROM registros r1
WHERE r1.tipo = t.tipo
ORDER BY r1.data DESC
LIMIT 1 -- Se quiser padrão SQL (esse é horrível): FETCH FIRST 1
ROW ONLY
) r;
De forma sucinta, o LATERAL permite executar uma subconsulta correlacionada
na cláusula FROM, sendo que a execução lógica seria, para cada "tipos"
encontrado execute a subconsulta no LATERAL trazendo a maior data daquele
tipo na tabela "registros". E, isso terá uma boa performance, mas você
precisará *obrigatoriamente* do seguinte índice:
CREATE INDEX ON registros(tipo, data);
Assim, a consulta será O(NlogM), considerando mesmos N, M usados
anteriormente. Assim, assumindo que tenha muito mais "reigstros" que
"tipos" (o que inferi pelo contexto), essa última abordagem é bem
performática.
Outras abordagens, seriam:
- Usando o DISTINCT ON (específico do PostgreSQL):
SELECT DISTINCT ON(tipo) *
FROM registros
ORDER BY tipo DESC, data DESC;
- Usando uma window function (padrão SQL):
SELECT *
FROM (
SELECT row_number() OVER(PARTITION BY tipo ORDER BY data DESC) AS
rn, *
FROM registros
) t
WHERE t.rn = 1;
O que nos leva a 4 abordagens diferentes, apenas a DISTINCT ON é específica
do PostgreSQL, as demais todas estão no padrão SQL.
De longe (assumindo a distribuição N << M) a versão com LATERAL é a mais
performática. Existe uma abordagem que "imita" o LATERAL e funciona em
versões anteriores, mas é extremamente complexa e nem um pouco intuitiva,
por isso, qualquer que necessite isto, eu recomendo fortemente usar a
versão 9.3+.
Atenciosamente,
--
Matheus de Oliveira
_______________________________________________
pgbr-geral mailing list
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
