por ·0 comentários

Considerando isoladamente a extração de dados OVER é uma o mais interessante recurso incluído ao Firebird.

Há uma possibilidade grande de combinações onde OVER se aplica – não vamos conseguir cobrir tudo somente neste POST, então vamos nos concentrar a um texto já tratando neste blog em relação a versão 2.5, que é apresentação de RANK, ou seja, mostrar a posição que se encontra o dado dentro da lista..

Ex: Qual a posição do vendedor XXX no RANK de vendas;

A implementação segue um padrão de estrutura tratado como “analytical functions”, ou seja, funções analíticas ou de análise de dados.

A ideia é usar um SELECT para separar uma janela de dados em paralelo do mesmo dado e montar um relacionamento dos dados do SELECT com os dados da JANELA para efetuar algum cálculo… no caso, calcular o RANK.
Tomando o exemplo da documentação emprestado

[code language=”sql”]select id, salary,
dense_rank() over (order by salary),
rank() over (order by salary),
row_number() over (order by salary),
sum(1) over (order by salary)
from employee
order by salary;

The result set:
id salary dense_rank rank row_number sum
— —— ———- —- ———- —
3 8.00 1 1 1 1
4 9.00 2 2 2 2
1 10.00 3 3 3 4
5 10.00 3 3 4 4
2 12.00 4 5 5 5

[/code]

Fazendo uma leitura do exemplo, em primeiro passo o engine monta o SELECT – quando encontra o comando OVER, ele separa uma janela em paralelo com os dados do mesmo SELECT (agora tem 1 cópia do mesmo dado do SELECT)… o que vem depois do OVER (….) é a forma em que o dado será organizado – no exemplo ORDER BY Salary; Sobre a janela de dados aplica a função indicada – o RANK();

Pegando carona no exemplo:

Há uma diferença entre RANK() e DENSE_RANK(),  em relação a forma de sequencia montada quando existe repetição do dado… enquanto RANK() continua a contagem quando encontra repetições, no DENSE_RANK() o número é mantido e segue a mesma sequencia do número anterior.

ROW_NUMBER() é uma contagem simples de linhas sem diferenciar dados repetidos como faz o RANK();

 

 

 

por ·0 comentários

Outro dia precisava montar um RANK de produtos, ou seja, precisava saber qual a classificação no RANK de cada produto com possibilidade de apresentar somente os primeiros 100 produtos.
Fazer RANK de dados não é um recurso disponível até o firebird 2.5 – o que irá ocorrer somente no firebird 3.0 – como ainda não é uma versão de produção, então o caminho ainda é resolver com o 2.5;Captura de tela 2016-03-02 21.13.57

Depois de ler muitas sugestões de como fazer, conclui que tinha que montar uma própria – eram todas confusas e levavam a resultados com muito tempo de respostas.

Estratégia:
Montar um select que ordene em ordem crescente os valores de venda em ordem decrescente de cada um dos produtos, depois criar um loop que marque com uma coluna na resposta com a posição do produto (o RANK);

Código Delphi:

      
       query1 := TFDQuery.create(nil);
       try
           with query1 do
           begin
              connection := xxx;
              sql.text := '....'
              open;  
           end;
           ....
       finally
          query1.free;
       end;

Select no Firebird:

execute block 
returns (codigo varchar(18),
         valor double precision,
         conta integer, 
         rank integer, 
         acumulado double precision)
as
declare variable n integer;
begin
   n = 0;
   acumulado = 0;

   for select codigo,sum(valor) valor, count(*) ct from sigcaut2
       where valor>0 and 
             data between '01/01/2015' and '12/31/2015'
       group by codigo
       order by valor desc
       rows 100
       into :codigo,:valor,:conta
   do
     begin
       n = n +1;
       rank = n;
       acumulado = acumulado + valor;
       suspend;
   end
end