encontrando com eficiência valores máximos e dados associados em grandes tabelas SQL

frequentemente, as consultas do banco de dados exigem encontrar o valor máximo em uma coluna e recuperar os dados correspondentes de outras colunas na mesma linha. Isso é especialmente desafiador com conjuntos de dados muito grandes. Considere uma tabela que precisa encontrar o número mais alto da versão para cada ID exclusivo, juntamente com sua tag associada:

Tabela de amostra:

ID  |  tag  |  version
----- ----- -----
1 |  A  |  10
2 |  A  |  20
3 |  B  |  99
4 |  C  |  30
5 |  F  |  40

resultado desejado:

ID  |  tag  |  version
----- ----- -----
2  |  A  |  20
3  |  B  |  99
4  |  C  |  30
5  |  F  |  40

Para tabelas com cerca de 28 milhões de linhas, métodos padrão como aninhados selecione instruções ou simples grupo por com max pode ser incrivelmente lento. Uma solução muito mais eficiente usa o row_number () função:

SELECT s.id, s.tag, s.version
FROM (
  SELECT t.*,
         ROW_NUMBER() OVER(PARTITION BY t.id ORDER BY t.version DESC) AS rnk
  FROM YourTable t
) s
WHERE s.rnk = 1;

Esta consulta funciona em duas etapas:

1. Query Inner: atribui uma classificação exclusiva ( rnk ) a cada linha dentro de cada id partição (grupo de linhas com o mesmo id). A classificação é baseada na coluna na ordem descendente, o que significa que a versão mais alta recebe a classificação 1.

2. Consulta externa: Filtra os resultados da consulta interna, selecionando apenas as linhas onde rnk = 1 . Isso efetivamente nos dá a linha com a versão máxima para cada id .

Esta abordagem evita consultas aninhadas e grupo por operações, tornando -a significativamente mais rápida para conjuntos de dados grandes. O uso de row_number () fornece uma maneira limpa e eficiente de obter o resultado desejado.