Neste conjunto de posts que irei iniciar, vou tratar de um grande recurso na linguagem T-SQL que é um grupo de funções chamada Window Function. Este grupo de funções permite ao usuário aplicar boas análises de dados de uma forma muito eficiente e prática.

A grande vantagem de utilizar este recurso de ‘janela’ de função com SQL é que se torna mais fácil, intuitivo e eficiente realizar análises, cálculos ou até mesmo, detectar alguma inconsistência nos dados.

Além disso vamos entender as cláusulas OVER E PARTITION, como esse grupo de função atua para retornar os valores das queries e revisar conceitos antigos e ver funções novas.

COMO FUNCIONA A WINDOW FUNCTION?

Suponhamos que uma consulta simples é realizada no banco AdventureWorks para retornar o ranking com as vendas do último ano corrente de acordo com cada região. Temos o seguinte resultado:

Repare que o resultado está sendo exibido e comparado entre os ID dos territórios e sempre que muda o território, a contagem reinicia. Isso ocorre devido a cláusula PARTITION BY que cria uma divisão de acordo com a coluna passada para que a função RANK atue (OVER) nesta coluna. 

O ORDER BY serve para ordenar o resultado da exibição. Porém, ele não garante que que o resultado será exibido devidamente ordenado. Para isso, declare-o novamente ao final do SELECT. Visto isso, podemos entender que a cláusula OVER  é quem inicia toda a estrutura para o resultado acima.

Quando utilizamos a w.f para cada linha no resultado da query é gerado uma ‘janela’ independente. Ausente de qualquer restrição, cada linha representa um conjunto com todas as linhas retornadas. Então, a linha um é um conjunto com todas as outras linhas; a linha dois é um outro conjunto com todas as outras linhas.

Como não há restrição na query a função over apenas ordena pela coluna que foi declarada no order by. (over trabalha em cima dessa coluna dizendo para o order by que ele deve criar uma ordenação por ela para cada linha retornada)

Assim, dentro do conjunto da linha um ele pega o maior e define como o valor exibido na query e o valor para função rank. O mesmo se aplica para a linha dois comparando o valor com a linha um e assim sucessivamente

ENTENDENDO A CLÁUSULA OVER

Como dito, o over é quem dita como o resultado será exibido, sempre que formos utilizar este recurso, não podemos deixar de declará-lo  nas consultas.

Dentro da cláusula OVER podemos usar funções de agregação como SUM, AVG, COUNT e etc tendo também três argumentos que constituem essa cláusula:

  • Partition By
  • Order by
  • ROW ou Range (não há diferença)

Como a W.F atua linha por linha daquele subconjunto que foi gerado pela query que possui a cláusula over, o resultado é mais detalhado.

Em alguns casos, quando utilizamos função de agregação ou analíticas, não é possível ordenar dentro do over.

Não é necessário declarar tanto o partition by ou order by nas queries com over. Se não declarar uma coluna com partition, será considerado todo o conjunto como sendo único. Veja o exemplo:

Reparem que na primeira coluna a contagem ocorreu de forma normal e distinta, como um único grupo, sem divisão. Já na segunda, enquanto temos valores únicos e distintos, nosso ranking fica sempre em 1, porém, quando chegamos no país US, a contagem muda. Isso ocorre devido a adição do partition by na consulta.

Também podemos utilizar a mesma query sem o ordenamento (order by), porém depende muito da função. Algumas exigem que esta cláusula esteja na query. Veja abaixo:

Como a função de COUNT não obriga a query a ter um ordenamento, podemos executar sem problema nenhum.

Abaixo, irei mostrar um exemplo de como este recurso de window function facilita a criação de resultados para análise de dados ou geração de relatório.

Veja por exemplo a query que retorna o total de vendas, a porcentagem que cada cliente representa e a porcentagem que todos representam sobre o valor anual.

FRAMING QUANDO UTILIZAMOS WINDOW FUNCTION

O framing é uma opção adicional quando utilizamos W.F que determina a forma como o resultado de uma query será executado e por final, exibido.

Estas cláusulas são muito utilizadas quando temos queries que atuam principalmente com formatos finais, do tipo: LEAD, LAST_VALUE e quando queremos fazer cálculos que englobam valores históricos.

É possível determinar a quantidade de linhas que queremos englobar na operação, podendo ser linhas anteriores ou seguintes a linha atual.

Vamos ao primeiro exemplo de uma query utilizando unbounded preceding e current row.

Nesta query, quando o resultado nos é mostrado, ele executa a seguinte operação: A linha atual irá somar com o resultado da linha anterior

Veja  por exemplo, a query abaixo: A linha 03 por considerar que o resultado da linha 02 é o valor sublinhado, irá somar este com o valor registrado em sua linha, dando o resultado de 1615,70 e este valor passa a ser o valor que a linha quatro irá somar para obter seu resultado.

Diferente do exemplo anterior, quando utilizamos current row and unbounded following a coluna val é exibida do maior para o menor valor, porém sua operação continua a mesma e o resultado final idem.

Veja o exemplo abaixo:

Como podemos perceber, o que muda é a ordem de exibição das colunas que são  passadas no parâmetro da cláusula order by, porém, o comportamento  é o mesmo e o resultado final também.

No próximo post irei continuar tratando deste assunto e mostrar formas e exemplos de utilizar esta cláusula.

Espero que gostem do post e fiquem bem!

Saúde!