Windows Functions – Parte 1

Posted on junho 22, 2012

1


 

Introdução

 

Windows Functions é um dos recursos mais profundos para o dialeto T-SQL. Com esse recurso extraordinário é possível facilitar grandemente o modo no qual escrevemos uma consulta dentro do banco de dados.

 

Elas foram implementadas para solucionar em diversas vezes, problemas já mapeados e díficeis de serem solucionados , começando  a partir do SQL Server 2005, hoje temos no SQL Server 2012 diversas Windows Functions implementadas.

 

Veremos de um modo bem detalhado como podemos tirar proveito desse novo recurso, alêm da facilidade e produtividade que podemos obter através delas que vêem com ainda mais força no SQL Server  2012.

 

Windows Functions

 

As windows functions são funções para serem trabalhadas com um conjunto de linhas que são definidas por uma cláusula OVER.

 

Essa cláusula faz com que seja possível se trabalhar com totais, agrupamentos, ordenações, cálculos complexos dentre outros.

 

A grande funcionalidade desse novo recurso é promover e solucionar problemas como:

 

– Paginação de uma consulta;

– Duplicação de dados;

– Computar ou totalizar por grupos;

– Retorno de Top Linhas por grupos;

– Identicar Gaps e Islands na consulta;

– Calcular porcentagem por grupos;

 

 

Elementos das Windows Functions

 

Alêm da presença da cláusula OVER, temos tambem 3 elementos que são: partitioning, ordering and framing, lembrando que todas as Windows Functions suportam todas as 3 especificadas.

 

Partitioning

 

Esse elemento é implementado na consulta como PARTITION BY. Quando utilizado, ele é restringido somente para os valores dos campos que forem especificados e a partir disso ele realiza o particionamento dos valores.

 

Pensando que temos IDEmpregado o mesmo será particionado somente para os valores de IDEmpregado.

 

Quando não especificado, não há restrição na hora da ordenação e agrupamento dos valores, ou seja o particionamento dos valores será do result set inteiro ou seja da pesquisa especificada que tratará todos os valores e campos como somente uma partição.

 

Sendo assim iremos entender melhor aqui:

 

USE tempdb

go

 

CREATE TABLE Partitioning

(

     IDEmpregado INT NOT NULL,

     IDPedido INT NOT NULL,

     ValorProduto DECIMAL(12,2) NOT NULL

)

 

INSERT INTO Partitioning(IDEmpregado, IDPedido, ValorProduto)

VALUES (1,10,100.00),

        (1,15,350.00),

        (1,27,439.00),

        (1,74,95.00),

        (2,7,550.00),

        (2,88,745.00),

        (2,32,900.00),

        (2,22,23.00),

        (3,1,1.00),

        (3,100,3.00),

        (3,35,45.00),

        (3,29,77.00)

 

SELECT IDEmpregado,

        IDPedido,

        ValorProduto,

        RANK() OVER(ORDER BY ValorProduto DESC) AS DenseRankNotPart,

        RANK() OVER(PARTITION BY IDEmpregado ORDER BY ValorProduto DESC) AS DenseRankPart

FROM dbo.Partitioning

 

Capture

(Figura 1 – Rank por IDEmpregado)

 

Sem trabalhar com o PARTITION BY, como estamos analisando todo o dataset então o campo DenseRankNotPart está sem partição, sendo assim temos do 1 ao 12, já o campo DenseRankPart está particionado pelo campo IDEmpregado, sendo assim temos somente 1 ao 4 para cada partição que no caso é o IDEmpregado, isso acontece porque cada IDEmpregado possui 4 valores, e n esse caso estamos selecionando os valores do maior para o menos particionado por IDEmpregado.

 

Ordering

 

Esse elemento define a ordenação dos valores de acordo com uma partição especificada ou de todos o result set.

 

image

(Figura 2 – Modo de ordenação dos valores)

 

Com essas funções, a ordenação das informações são bem intuitivas. Quando é especificado a ordenação DESCENDENTE a função de RANK() retorna o número maior da consulta +1 em sua respectiva partição.

 

Quando se utiliza a ordenação ASCENDENTE o mesmo acontece, só que o retorno do RANK() é o número de linhas menor do que o da ordenação.

 

Nesse caso acima vemos que mk_all possui o valor de 419 porém o algoritmo joga isso na hora da ordenação como 418, e assim sucessivamente para o mk_cust 4 que aparece como 3 e logo depois o mk_cust 3 que aparece como 2 e valor > 320,00 sendo assim o algoritmo sabe qual é o maior e menor valor especificado para a determinada partição.

 

Framing

 

Framing é um outro recurso de filtro que realizar a restrição de linhas dentro da partição especificada.

 

Esse recurso pode ser aplicado para funções de agragações assim como funções: FIRST_VALUE, LAST_VALUE e NTH_VALUE.

 

Esse elemento representa dois pontos em uma partição baseada em uma ordenação, fazendo com que essa área seja aplicada uma operação de cálculo.

 

Esse espaço entre dois pontos pode incluir as opções ROWS ou RANGE, que delimita o início e o término de uma linha dentro dessa área.

 

A opção ROWS – Permite que seja indicado pontos dentro de uma área para um deslocamento em linhas em relação a linha corrente.

 

A opção RANGE – Aplica um maior dinamismo aplicando a diferença do valor da área entre dois pontos e o valor atual de um registro.

 

Na próxima sessão irei explicar mais detalhadamente o framing e cada tipo de função que podemos utilizar.