terça-feira, 20 de novembro de 2012

SQL Gaussiano

É bem conhecida a história de quando o professor de matemática de Gauss pediu que a turma somasse todos os números até 100. O mestre esperava ter um pouco de paz enquanto as crianças trabalhassem na soma. O pequeno Gauss, no entanto, resolveu a questão rapidamente.

A solução era simples. Ele somou 1 com 100, depois 2  com 99, então 3 com 98 e assim por diante. Vindo pelas pontas, a soma é sempre 101 e depois de 50 somas, os números encontram-se em 50 e 51. O total, portanto, é 50 vezes 101.

Esse pequeno truque pode ser usado no SQL para encontrar sequências de números ou de datas. Considere os seguintes números: 1; 2; 3; 5; 6; 8; 9; e 10. Se cada um estiver numa linha de uma tabela, eu quero uma consulta que gere o seguinte resultado:

   1   3
   5   6
   8 10

É necessária uma sequência decrescente para fazer o jogo com a nossa sequência crescente. Isso podemos resolver facilmente com funções analíticas. Na consulta abaixo, já somo cada número ao seu equivalente.


select n, n+row_number() over (order by n desc)
from numeros
order by n


Isso vai produzir as seguintes linhas:

19
29
39
510
610
811
911
1011

E todos os números consecutivos compartilham o mesmo valor na segunda coluna. O que nos resta é agrupá-los e encontrar o máximo e mínimo de cada grupo. Assim, chegamos ao nosso objetivo:


select min(n), max(n) from (
  select n, n+row_number() over (order by n desc) grupo
  from numeros
)
group by grupo
order by 1


O resultado é, conforme esperado, este:

   1   3
   5   6
   8 10

O mesmo pode ser feito com datas, basta tratar cada uma como o número de dias a partir da primeira data.

Um comentário:

  1. Esta técnica é muito útil. Por exemplo, ela ajudaria muito na elaboração de um relatório de controle de rupturas no varejo.

    ResponderExcluir