Window Functions: Row_number()

 Olá pessoal, tudo certo com vocês? Há algum tempo estava pensando em escrever sobre as Window Fuctions (Funções de Janela) do SQL-SERVER, pois vejo que muita gente desconhece sobre essa maravilhosa função do SQL e não existe muito material em português. Antes de começar a introdução no assunto, vou deixar uma dica de livro do qual eu li para construir essa série de artigos que estarei postando aqui, gostaria também de deixar um artigo da lenda do Tuning o Fabiano Amorim, que escreveu sobre esse tema em 2011 no simple-talk.

Livro: https://www.amazon.com.br/T-SQL-Window-Functions-Itzik-Ben-Gan/dp/0135861446

Artigo do Fabiano: https://www.red-gate.com/simple-talk/sql/learn-sql-server/window-functions-in-sql-server/

Bem, gostaria de começar explicando o que afinal são essas Window functions?  São funções que se aplicam a um conjunto de linhas e para poder entender melhor sobre isso, recomendo uma leitura sobre o set-based, um tipo de pensamento que não é fácil de ter, mas que quando é entendido é capaz de resolver problemas complexos de forma simples.  Todas as funções de agregações exceto as string_Agg e grouping podem virar funções de janela, basta adicionar a clausula OVER, veremos mais sobre essa clausula.

Agora que sabemos o que são Window Functions, vamos começar pela mais conhecida de todas e que você pode ter usado sem se dar conta de que estava utilizando uma wf, a row_number(), a função row_number é usada para criar um conjunto de números sequencias em uma coluna, muito parecido com o identity, só que com uma função a mais, que é poder particionar o resultado.

Vamos a um exemplo:

select 

 a.name

,ROW_NUMBER() over (order by name) as [row_number()]

from cliente a 

inner join vendas b

on a.cod_cli = b.cod_cli

resultado

reparem que nesse resultado foi criado um sequencial. 

Agora que vem a magia da coisa, vamos particionar esse número sequencial por nome.

select 

 a.name

,ROW_NUMBER() over (partition by name order by name) as [row_number()]

from cliente a 

inner join vendas b

on a.cod_cli = b.cod_cli

resultado:

Repararem que nesse exemplo eu fiz um  sequencial recomeçar a cada nome da coluna name, o que gostaria que fosse entendido nesse artigo é que, cada cor dessa que está no resultado é uma janela diferente, para fazer isso utilizei a clausula over(PARTITION BY NAME) ou seja, é como se  a tabela fosse divida entre varias tabelas pela coluna name, podemos ver que o azul escuro é a janela 1 (Benito), azul claro a janela 2 (Leandro) e assim por diante. Reparem que no primeiro exemplo não especifiquei a clausular over com partition by, pois a janela no exemplo 1 seria toda a tabela.

Outro exemplo de aplicação da função row_number() é pegar as 2 maiores vendas por cada cliente.

Exemplo:

;with cte

as

(

select 

 a.name

 ,b.cod_cli

 ,value

 ,ROW_NUMBER() over (partition by B.COD_CLI order by value desc) as [row_number()]

from cliente a 

inner join vendas b

on a.cod_cli = b.cod_cli

)

select * from cte where  [row_number()] in (1,2)

ORDER BY 1

Vou explicar passo a passo desse código, mas se você nunca viu uma CTE, keep calmo, uma CTE nada mais é do que uma sub-consulta, mas com o beneficio de pode fazer uma recursividade, mas não se preocupe com isso por enquanto.

 O primeiro passo foi fazer um join entre a tabela de cliente e a tabela de vendas, após fazer o join usei a row_number() OVER(PATITION BY id) para poder separar a lógica por cliente, criei um número sequencial para cada cliente ordenando pela maior venda, então sei que as vendas 1 e 2 são as maiores vendas daqueles clientes. Peguei o resultado dessa consulta e joguei na CTE, pois não consigo referenciar a coluna criada pela window functions, depois que joguei esse resultado na CTE filtrei somente os números 1 e 2. Muito massa, né?

Estarei postando mais artigos de window functions, é um mundo maravilhoso que podemos explorar juntos!