Funções de janela principais para análise

Antes de começar, imagina que você está trabalhando com uma tabela de milhares de linhas de vendas. Sua missão: descobrir quem é o vendedor número um em cada categoria, quem é o segundo, e por aí vai. Ou, por exemplo, você precisa numerar todas as linhas do resultado para acompanhar a ordem. Tudo isso fica fácil usando as funções de janela.

Funções de janela são funções SQL que trabalham com um subconjunto de linhas (vamos chamar de "janela") de um conjunto de dados. Diferente das funções agregadas, que juntam linhas em uma só (tipo SUM() ou AVG()), as funções de janela deixam as linhas intactas, só adicionando valores calculados a elas.

Diferença das funções agregadas

Funções agregadas "comprimem" os dados, agrupando as linhas:

SELECT department, COUNT(*)
FROM employees
GROUP BY department;

Resultado: só algumas linhas, uma pra cada departamento.

Agora compara com uma função de janela — aqui as linhas continuam lá, mas aparece um campo novo, tipo ROW_NUMBER():

SELECT employee_name, department,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_within_department
FROM employees;

Aqui você recebe todas as linhas, mas com uma coluna extra rank_within_department, onde cada funcionário ganha um número dentro do seu departamento.

Funções de janela principais

Sintaxe do OVER()

A parte mais importante de toda função de janela é a palavrinha mágica OVER(). É ela que define qual "janela" de dados a função vai usar. Dentro do OVER() você pode colocar divisão em grupos (PARTITION BY) e/ou ordem de classificação (ORDER BY).

Sintaxe geral:

<função_de_janela>() OVER (
   [PARTITION BY <grupo>] 
   [ORDER BY <ordem>]
)

Componentes:

• PARTITION BY: Divide as linhas em grupos. Tipo "separa os dados por departamento".
• ORDER BY: Define a ordem das linhas. Tipo "ordena os funcionários pelo salário, do maior pro menor".

Função ROW_NUMBER()

A função ROW_NUMBER() numera as linhas, começando do 1, dentro da "janela" que você definir. Às vezes é útil só pra criar um número de linha numa tabela temporária ou pra saber a posição de cada registro.

Exemplo. Tabela sales (vendas):

Consulta:

SELECT seller_name, product_category, revenue,
       ROW_NUMBER() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS row_number
FROM sales;

Resultado:

Como funciona:

1. Os dados são divididos em grupos por product_category.
2. Cada grupo é ordenado por revenue (do maior pro menor).
3. As linhas dentro de cada grupo recebem um número de ordem.

Função RANK()

A função RANK() serve pra ranquear as linhas. Diferente da ROW_NUMBER(), ela leva em conta valores iguais e pula números (ranks) se os valores forem iguais.

Exemplo:

SELECT seller_name, product_category, revenue,
       RANK() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS rank
FROM sales;

Resultado:

Função DENSE_RANK()

DENSE_RANK() é parecida com RANK(), mas tem um detalhe: ela não pula números de rank se tiver valores iguais.

Exemplo. Vamos adicionar uma venda com a mesma receita:

Consulta:

SELECT seller_name, product_category, revenue,
       DENSE_RANK() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS dense_rank
FROM sales;

Resultado:

Exemplos de uso: numeração de linhas

Missão: numerar todos os pedidos na tabela orders, ordenados pela data.

SELECT order_id, customer_name, order_date,
       ROW_NUMBER() OVER (ORDER BY order_date) AS order_number
FROM orders;

Resultado: você recebe a lista de pedidos numerados na ordem em que foram feitos.

Exemplos de uso: top-3 vendedores em cada categoria

Missão: descobrir os três melhores vendedores em cada categoria de produto.

WITH ranked_sales AS (
    SELECT seller_name, product_category, revenue,
           RANK() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS rank
    FROM sales
)
SELECT seller_name, product_category, revenue
FROM ranked_sales
WHERE rank <= 3;

Exemplos de uso: identificar receitas iguais

Missão: descobrir se tem vendedores com a mesma receita em cada categoria.

SELECT seller_name, product_category, revenue,
       DENSE_RANK() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS dense_rank
FROM sales;

Agora dá pra ver os ranks onde os valores "grudam" quando são iguais.