Funções de Janela

Q: P: Funções de janela podem ser usadas na cláusula WHERE?

R: Não. WHERE é executado antes das funções de janela, então a função de janela ainda não foi calculada. Você precisa primeiro calcular a função de janela usando uma subconsulta ou CTE, depois filtrar com WHERE na consulta externa.

Q: P: Quando devo usar ROW_NUMBER vs RANK?

R: Use ROW_NUMBER quando precisar de "numeração única, sem empates" (ex: paginação, deduplicação); use RANK quando precisar de "classificação por valor, permitindo empates" (ex: classificação de notas, ranking de vendas).

Q: P: Qual a diferença entre PARTITION BY e GROUP BY?

R: GROUP BY mescla múltiplas linhas em uma (reduzindo a contagem de linhas); PARTITION BY apenas define o escopo da janela sem reduzir linhas (cada linha é preservada). Ambos podem ser usados juntos: primeiro agregue com GROUP BY, depois use funções de janela para anexar classificações.

🌍 Analogia do Mundo Real

Imagine um boletim escolar:

GROUP BY — Agrupa toda a turma por gênero, calcula a nota média para meninos e meninas, resultando em apenas duas linhas
Função de janela — No boletim de cada aluno, adicionalmente escreve "sua classificação entre os meninos" e "quanto acima da média dos meninos", mantendo as informações de cada pessoa

Funções de janela são como uma "lupa" — não reduzem o número de linhas, mas permitem ver estatísticas agregadas em cada linha.

🎯 Conceitos Fundamentais

O que é uma Função de Janela

Funções de janela realizam cálculos de agregação ou classificação sobre uma "janela" de dados sem reduzir a contagem de linhas. Cada linha pode ver seus próprios detalhes, bem como as informações resumidas dentro de seu grupo.

A Cláusula OVER

OVER() é o núcleo das funções de janela — ela define o escopo da "janela":

SQL

função() OVER (
    [PARTITION BY coluna]    -- Por qual campo particionar (agrupar)
    [ORDER BY coluna]        -- Como ordenar dentro da partição
    [ROWS/RANGE frame]       -- Opcional: restringir ainda mais o intervalo de linhas
)

ROW_NUMBER / RANK / DENSE_RANK — Funções de Classificação

SQL

-- ROW_NUMBER: sequência estritamente crescente, sem empates (1, 2, 3, 4)
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC)

-- RANK: permite empates, com lacunas (1, 2, 2, 4)
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC)

-- DENSE_RANK: permite empates, sem lacunas (1, 2, 2, 3)
DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC)

Função	Empates Permitidos	Lacunas	Resultado Exemplo
ROW_NUMBER	Não	—	1, 2, 3, 4
RANK	Sim	Sim	1, 2, 2, 4
DENSE_RANK	Sim	Não	1, 2, 2, 3

NTILE — Distribuição em Grupos

Divide os dados uniformemente em N grupos:

SQL

-- Divide os funcionários em 4 faixas por salário (quartis)
NTILE(4) OVER (ORDER BY salary DESC)

LAG / LEAD — Acessando Linhas Adjacentes

SQL

-- LAG: acessa a linha anterior
LAG(salary, 1) OVER (PARTITION BY department_id ORDER BY hire_date)

-- LEAD: acessa a próxima linha
LEAD(salary, 1) OVER (PARTITION BY department_id ORDER BY hire_date)

Função	Direção	Caso de Uso
LAG(col, n)	Olha n linhas para trás	Calcular variação período a período, comparar com registro anterior
LEAD(col, n)	Olha n linhas para frente	Prever o próximo registro

SUM / AVG / COUNT OVER — Funções de Janela de Agregação

SQL

-- Cada linha mostra o total de salário do departamento
SUM(salary) OVER (PARTITION BY department_id)

-- Cada linha mostra a média salarial do departamento
AVG(salary) OVER (PARTITION BY department_id)

-- Cada linha mostra o número de funcionários do departamento
COUNT(*) OVER (PARTITION BY department_id)

PARTITION BY e ORDER BY na Janela

SQL

-- PARTITION BY: define o escopo da janela (semelhante ao GROUP BY, mas não colapsa linhas)
-- ORDER BY: ordena dentro da janela, afeta classificações e cálculos acumulados

-- Exemplo: agrupa por departamento, classifica por salário decrescente dentro de cada departamento
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC)

-- Exemplo: soma acumulada (ordenada por data de contratação, acumulando salário linha por linha)
SUM(salary) OVER (ORDER BY hire_date)

Funções de Janela vs GROUP BY

Característica	GROUP BY	Função de Janela
Contagem de linhas	Reduzida (mesclada em uma linha)	Inalterada (todas as linhas preservadas)
Dados detalhados	Perdidos	Preservados
Caso de uso	Estatísticas resumidas	Classificação, comparação, cálculos acumulados
Ver detalhe e resumo simultaneamente	❌ Não	✅ Sim

💡 Regra geral: Use funções de janela quando precisar "manter detalhes após a agregação"; use GROUP BY quando precisar apenas de resultados resumidos.

📝 Sintaxe Básica

SQL

-- Sintaxe geral de funções de janela
função() OVER (
    [PARTITION BY col1, col2]
    [ORDER BY col3 [ASC|DESC]]
    [ROWS BETWEEN ... AND ...]
)

-- Classificação
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC)
RANK()       OVER (PARTITION BY department_id ORDER BY salary DESC)
DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC)

-- Linhas adjacentes
LAG(coluna, deslocamento, padrão)  OVER (ORDER BY coluna)
LEAD(coluna, deslocamento, padrão) OVER (ORDER BY coluna)

-- Agregação
SUM(coluna) OVER (PARTITION BY col)
AVG(coluna) OVER (PARTITION BY col)
COUNT(*)    OVER (PARTITION BY col)

-- Distribuição em grupos
NTILE(n) OVER (ORDER BY coluna)

💡 Dica:

PARTITION BY pode ser omitido, significando que a janela abrange todo o conjunto de resultados

ORDER BY é obrigatório para funções de classificação, opcional para funções de agregação

Funções de janela não podem ser usadas em WHERE ou HAVING; encapsule-as em uma subconsulta ou CTE

📌 Exemplos

Exemplo: Classificação de Salário de Funcionários por Departamento (Dificuldade ⭐)

SQL

SELECT 
    CONCAT(e.first_name, e.last_name) AS name,
    d.department_name AS department,
    e.salary,
    ROW_NUMBER() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS row_num,
    RANK()       OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS rank,
    DENSE_RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS dense_rank
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
ORDER BY d.department_name, salary DESC;

▶ Experimente

Resultado:

TEXT

name   | department | salary   | row_num | rank | dense_rank
-------+------------+----------+---------+------+-----------
Zhang Wei | Tech    | 12000.00 |       1 |    1 |         1
Li Na     | Tech    |  9500.00 |       2 |    2 |         2
Wang Qiang| Sales   |  8000.00 |       1 |    1 |         1
Zhao Min  | Sales   | 11000.00 |       2 |    2 |         2
Liu Yang  | Marketing| 7500.00 |       1 |    1 |         1
Chen Jing | NULL    |  9000.00 |       1 |    1 |         1

Explicação: A diferença entre as três funções de classificação é mais evidente quando há valores empatados. ROW_NUMBER atribui números sequenciais estritos, RANK permite lacunas após empates, e DENSE_RANK não.

Exemplo: Calcular Diferença em Relação à Média Salarial do Departamento (Dificuldade ⭐⭐)

SQL

SELECT 
    CONCAT(e.first_name, e.last_name) AS name,
    d.department_name AS department,
    e.salary,
    ROUND(AVG(e.salary) OVER (PARTITION BY e.department_id), 2) AS dept_avg,
    ROUND(e.salary - AVG(e.salary) OVER (PARTITION BY e.department_id), 2) AS diff_from_avg
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
ORDER BY d.department_name, salary DESC;

▶ Experimente

Resultado:

TEXT

name   | department | salary   | dept_avg | diff_from_avg
-------+------------+----------+----------+--------------
Zhang Wei | Tech    | 12000.00 | 10750.00 |      1250.00
Li Na     | Tech    |  9500.00 | 10750.00 |     -1250.00
Zhao Min  | Sales   | 11000.00 |  9500.00 |      1500.00
Wang Qiang| Sales   |  8000.00 |  9500.00 |     -1500.00
Liu Yang  | Marketing|  7500.00|  7500.00 |         0.00
Chen Jing | NULL    |  9000.00 |  9000.00 |         0.00

Explicação: AVG() OVER() calcula a média do departamento em cada linha sem colapsar linhas, para que cada funcionário possa ver diretamente a diferença entre seu salário e a média.

Exemplo: LAG para Crescimento de Pedidos Período a Período (Dificuldade ⭐⭐)

SQL

SELECT 
    o.order_id,
    o.order_date,
    o.total_amount,
    LAG(o.total_amount, 1) OVER (ORDER BY o.order_date) AS prev_order_amount,
    ROUND(
        (o.total_amount - LAG(o.total_amount, 1) OVER (ORDER BY o.order_date)) 
        / LAG(o.total_amount, 1) OVER (ORDER BY o.order_date) * 100, 
    2) AS growth_pct
FROM orders o
WHERE o.status != 'cancelled'
ORDER BY o.order_date;

▶ Experimente

Resultado:

TEXT

order_id | order_date | total_amount | prev_order_amount | growth_pct
---------+------------+--------------+-------------------+-----------
    1001 | 2026-01-10 |      2500.00 |              NULL |       NULL
    1002 | 2026-02-15 |      1800.00 |           2500.00 |     -28.00
    1003 | 2026-02-20 |      3200.00 |           1800.00 |      77.78
    1004 | 2026-03-05 |       950.00 |           3200.00 |     -70.31
    1005 | 2026-03-10 |      4100.00 |            950.00 |     331.58
    ...

Explicação: LAG() acessa o valor da "linha anterior", sendo ideal para análises período a período, ano a ano e outras tendências. A primeira linha não possui linha anterior, portanto retorna NULL.

🎬 Prática de Cenários

Cenário 1: Encontrar o Melhor Vendedor de Cada Departamento (Dificuldade ⭐⭐⭐)

Encontre o funcionário com as maiores vendas em cada departamento.

SQL

WITH emp_sales AS (
    SELECT 
        CONCAT(e.first_name, e.last_name) AS name,
        d.department_name AS department,
        COALESCE(SUM(o.total_amount), 0) AS total_sales,
        RANK() OVER (PARTITION BY d.department_id ORDER BY COALESCE(SUM(o.total_amount), 0) DESC) AS dept_rank
    FROM employees e
    LEFT JOIN departments d ON e.department_id = d.department_id
    LEFT JOIN orders o ON e.employee_id = o.employee_id AND o.status != 'cancelled'
    GROUP BY e.employee_id, e.first_name, e.last_name, d.department_id, d.department_name
)
SELECT name, department, total_sales, dept_rank
FROM emp_sales
WHERE dept_rank = 1
ORDER BY total_sales DESC;

Ponto-chave: Primeiro use uma CTE + RANK() para classificar, depois use WHERE para filtrar o primeiro colocado. Funções de janela não podem ser usadas diretamente em WHERE; precisam ser encapsuladas em uma subconsulta ou CTE.

Cenário 2: Acompanhar Ordem de Contratação e Mudanças Salariais (Dificuldade ⭐⭐⭐)

SQL

SELECT 
    CONCAT(e.first_name, e.last_name) AS name,
    d.department_name AS department,
    e.hire_date,
    e.salary,
    LAG(e.salary, 1) OVER (PARTITION BY e.department_id ORDER BY e.hire_date) AS prev_salary,
    ROUND(e.salary - LAG(e.salary, 1) OVER (PARTITION BY e.department_id ORDER BY e.hire_date), 2) AS salary_change,
    ROW_NUMBER() OVER (PARTITION BY e.department_id ORDER BY e.hire_date) AS hire_order
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
ORDER BY d.department_name, e.hire_date;

Ponto-chave: LAG() + PARTITION BY pode acompanhar mudanças sequenciais dentro de um grupo, sendo adequado para análise de tendências.

❓ Perguntas Frequentes

P: Funções de janela podem ser usadas na cláusula WHERE? R: Não. WHERE é executado antes das funções de janela, então a função de janela ainda não foi calculada. Você precisa primeiro calcular a função de janela usando uma subconsulta ou CTE, depois filtrar com WHERE na consulta externa.

P: Quando devo usar ROW_NUMBER vs RANK? R: Use ROW_NUMBER quando precisar de "numeração única, sem empates" (ex: paginação, deduplicação); use RANK quando precisar de "classificação por valor, permitindo empates" (ex: classificação de notas, ranking de vendas).

P: Qual a diferença entre PARTITION BY e GROUP BY? R: GROUP BY mescla múltiplas linhas em uma (reduzindo a contagem de linhas); PARTITION BY apenas define o escopo da janela sem reduzir linhas (cada linha é preservada). Ambos podem ser usados juntos: primeiro agregue com GROUP BY, depois use funções de janela para anexar classificações.

P: Qual a performance das funções de janela? R: Funções de janela geralmente têm melhor performance do que subconsultas com auto-junção. Para grandes conjuntos de dados, crie índices nas colunas usadas em PARTITION BY e ORDER BY.

📖 Resumo

Função	Caso de Uso	Exemplo
ROW_NUMBER	Número de sequência único	Paginação, deduplicação
RANK	Classificação com empates (com lacunas)	Classificação de notas
DENSE_RANK	Classificação com empates (sem lacunas)	Classificação por faixas
NTILE	Distribuição uniforme em grupos	Análise de percentis
LAG	Acessar linha anterior	Cálculo período a período
LEAD	Acessar próxima linha	Previsão de tendências
SUM/AVG/COUNT OVER	Agregação acumulada/de grupo	Análise comparativa

OVER() define o escopo da janela, PARTITION BY particiona, ORDER BY ordena
Funções de janela não reduzem a contagem de linhas — elas anexam informações agregadas preservando os detalhes
Funções de janela não podem ser colocadas em WHERE; encapsule-as em uma subconsulta ou CTE

📝 Exercícios

Use ROW_NUMBER() para numerar funcionários por data de contratação e encontre o funcionário mais recentemente contratado.
Use RANK() para classificar funcionários por salário dentro de cada departamento e encontre os dois funcionários mais bem pagos de cada departamento.
Use LAG() para calcular a mudança salarial dos funcionários dentro de cada departamento, ordenados por data de contratação.
Use SUM() OVER() para calcular o total acumulado de vendas para cada pedido (ordenado por data do pedido).

Próxima Lição

👉 20-cte-temp-table - CTEs e Tabelas Temporárias: Aprenda a usar cláusulas WITH (CTEs), incluindo CTEs recursivas, CTEs vs subconsultas, e a criação e casos de uso de tabelas temporárias.

Equipe Técnica Web-Tutorial

Uma plataforma de tutoriais mantida por diversos desenvolvedores. Cada tutorial é escrito e revisado por profissionais da área correspondente. Trabalhamos para manter nosso conteúdo preciso e confiável — se encontrar algum problema, avise-nos.

Sobre Nós