Blog – Fabiano Neves Amorim

Windows Functions no SQL Server Denali

Vamos ver neste artigo como funcionam as Windows Functions que estarão disponíveis no SQL Server Denali.

As windows functions apresentadas abaixo estão disponíveis no CTP 3 do SQL Server Denali, você pode fazer o download para avaliação clicando no link abaixo:

https://www.microsoft.com/betaexperience/pd/SQLDCTP3CTA/enus/default.aspx

Base para testes

Para testar as windows functions iremos utilizar uma tabela de testes chamada Tab1. O código para criação da tabela é o seguinte:

USE TempDB
GO

IF OBJECT_ID('Tab1') IS NOT NULL
DROP TABLE Tab1
GO
CREATE TABLE Tab1 (Col1 Int)
GO
INSERT INTO Tab1 VALUES(5), (5), (3) , (1)
GO

LEAD()

A function LEAD é utilizada para ler o valor da próxima linha, ou linha de baixo. Quando a próxima linha não existir NULL é retornado.

Exemplo:

-- LEAD
SELECT Col1,
LEAD(Col1) OVER(ORDER BY Col1) AS "LEAD"
FROM Tab1

Como podemos observar a coluna LEAD retorna a próxima linha, no caso do último registro o valor NULL é retornado.

Por padrão a próxima linha é retornada, mas nós podemos alterar este comportamento para que função retorne o valor de N linhas seguintes, por exemplo:

-- LEAD
SELECT Col1,
LEAD(Col1, 2) OVER(ORDER BY Col1) AS "LEAD"
FROM Tab1

No resultado acima, solicitei que a função retornasse o valor de 2 linhas seguintes.

LAG()

A function LAG() é similar a function LEAD(), porém ao invés de retornar o próximo valor ela retorna o valor anterior. Por exemplo:

-- LAG
SELECT Col1,
LAG(Col1) OVER(ORDER BY Col1) AS "LAG()"
FROM Tab1

Podemos ver que o valor anterior foi retornado, quando a linha não contém valor anterior NULL é retornado.

Você pode estar se pensando que uma alternativa a function LAG() seria usar a function LEAD() com o valor do OffSet negativo certo? Ou seja, ao invés de pegar 1 valor pra frente pega -1.

Vejamos um exemplo:

SELECT Col1,
LEAD(Col1, -1) OVER(ORDER BY Col1) AS "LEAD() Como LAG()"
FROM Tab1

Msg 8730, Level 16, State 1, Line 1

Offset parameter for Lag and Lead functions cannot be a negative value.

Como podemos ver, não é possível usar valores negativos para as funções LEAD() e LAG().

FIRST_VALUE()

Como o nome praticamente diz FIRST_VALUE() retorna o primeiro valor de um frame. Por exemplo:

-- FIRST_VALUE
SELECT Col1,
FIRST_VALUE(Col1) OVER(ORDER BY Col1) AS "FIRST_VALUE()"
FROM Tab1

LAST_VALUE()

Como o nome praticamente diz LAST_VALUE() retorna o primeiro valor de um frame. Por exemplo:

-- LAST_VALUE
SELECT Col1,
LAST_VALUE(Col1) OVER(ORDER BY Col1 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS "LAST_VALUE()"
FROM Tab1

Para pegar o último valor da window acima precisei especificar qual era o frame, vou explicar melhor como isso funciona no próximo post.

PERCENT_RANK()

No primeiro artigo sobre windows functions mostrei o comportamento da function RANK(). A function PERCENT_RANK() é bem similar a function RANK() porém o resultado é o seguinte:

Exemplo:

-- PERCENT_RANK
SELECT Col1,
PERCENT_RANK() OVER(ORDER BY Col1) AS "PERCENT_RANK()"
FROM Tab1

O mesmo efeito era possível no SQL Server 2005, utilizando a seguinte fórmula:

• (RANK() – 1) / (NúmeroDeLinhas – 1)

Abaixo podemos visualizar um código para simular o uso da function PERCENT_RANK() no SQL Server 2005:

-- Fake PERCENT_RANK
SELECT Col1,
(RANK() OVER(ORDER BY Col1) - 1.) / ((SELECT COUNT(*) FROM Tab1) - 1.) AS "Fake PERCENT_RANK()"
FROM Tab1

CUME_DIST()

A function CUME_DIST() também é utilizada para calcular a posição de um valor em um rank. Vejamos um exemplo:

-- CUME_DIST()
SELECT Col1,
CUME_DIST() OVER(ORDER BY Col1) AS "CUME_DIST()"
FROM Tab1

O mesmo resultado pode ser visualizado utilizando a seguinte fórmula:

• COUNT(*) OVER (ORDER BY Col1) / COUNT(*) OVER ()

Conclusão

Ainda falta mostrar as functions de distribuition PERCENTILE_CONT e PERCENTILE_DISC, e explicar o que é e como usar o frame de uma window.

É isso ai pessoal, fique de olho para o próximo artigo sobre Windows Functions.