Módulo 13 — Funções de Janela (OVER, PARTITION BY)

1) Conceitos & Sintaxe

Funções de janela calculam valores por linha considerando um conjunto de linhas ao redor (a “janela”). Diferem do GROUP BY porque não colapsam o resultado.

-- Estrutura geral
<funcao_de_janela>() OVER (
  [PARTITION BY <colunas>]   -- (opcional) divide em grupos
  [ORDER BY <colunas>]       -- (opcional) define ordem dentro do grupo
  [ROWS|RANGE BETWEEN ...]    -- (opcional) moldura (frame)
)

2) Funções de Ranking

-- Top vendedores por UF, com rankings por total de vendas
SELECT
  c.UF,
  c.Nome,
  SUM(v.Valor) AS Total,
  ROW_NUMBER() OVER (PARTITION BY c.UF ORDER BY SUM(v.Valor) DESC)  AS rn,
  RANK()       OVER (PARTITION BY c.UF ORDER BY SUM(v.Valor) DESC)  AS rk,
  DENSE_RANK() OVER (PARTITION BY c.UF ORDER BY SUM(v.Valor) DESC)  AS drk,
  NTILE(4)     OVER (PARTITION BY c.UF ORDER BY SUM(v.Valor) DESC)  AS quartil
FROM dbo.Vendas AS v
JOIN dbo.Clientes AS c ON c.IdCliente = v.IdCliente
GROUP BY c.UF, c.Nome;

3) Agregações em Janela

As funções de agregação podem operar como janela mantendo as linhas originais.

-- Totais por cliente exibidos em cada linha de venda do cliente
SELECT
  v.IdVenda, v.IdCliente, v.DataVenda, v.Valor,
  SUM(v.Valor) OVER (PARTITION BY v.IdCliente) AS TotalCliente,
  AVG(v.Valor) OVER (PARTITION BY v.IdCliente) AS MediaCliente,
  COUNT(*)    OVER (PARTITION BY v.IdCliente) AS QtdeVendasCliente
FROM dbo.Vendas AS v;

-- Total acumulado (running total) por cliente
SELECT
  v.IdCliente, v.DataVenda, v.Valor,
  SUM(v.Valor) OVER (
    PARTITION BY v.IdCliente
    ORDER BY v.DataVenda
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS AcumuladoCliente
FROM dbo.Vendas AS v;

4) Funções de Offset

-- Diferença para a venda anterior do mesmo cliente
SELECT
  v.IdCliente, v.DataVenda, v.Valor,
  LAG(v.Valor, 1, 0.00) OVER (PARTITION BY v.IdCliente ORDER BY v.DataVenda) AS ValorAnterior,
  v.Valor - LAG(v.Valor, 1, 0.00) OVER (PARTITION BY v.IdCliente ORDER BY v.DataVenda) AS Delta
FROM dbo.Vendas AS v;

-- Cuidado com LAST_VALUE: limite o frame até a linha atual
SELECT
  v.IdCliente, v.DataVenda, v.Valor,
  FIRST_VALUE(v.Valor) OVER (PARTITION BY v.IdCliente ORDER BY v.DataVenda
     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS PrimeiroValor,
  LAST_VALUE(v.Valor) OVER (PARTITION BY v.IdCliente ORDER BY v.DataVenda
     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS UltimoAteAqui
FROM dbo.Vendas AS v;

5) Percentis & Distribuição

SQL Server oferece funções para distribuição e percentis. As ordenadas-set (PERCENTILE_CONT / PERCENTILE_DISC) usam WITHIN GROUP (ORDER BY ...) e podem particionar com OVER.

-- Mediana (percentil 50) por UF
SELECT
  c.UF,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY v.Valor)
    OVER (PARTITION BY c.UF) AS MedianaUF
FROM dbo.Vendas AS v
JOIN dbo.Clientes AS c ON c.IdCliente = v.IdCliente;

-- Distribuição relativa
SELECT
  v.IdCliente, v.Valor,
  PERCENT_RANK() OVER (PARTITION BY v.IdCliente ORDER BY v.Valor) AS PercentRank,
  CUME_DIST()    OVER (PARTITION BY v.IdCliente ORDER BY v.Valor) AS CumeDist
FROM dbo.Vendas AS v;

6) Window Frames (ROWS x RANGE)

• ROWS: define por quantidade de linhas (mais previsível para totais acumulados).
• RANGE: define por valores da ordenação (suporte limitado no SQL Server: geralmente UNBOUNDED e CURRENT ROW).
• UNBOUNDED PRECEDING/FOLLOWING, CURRENT ROW delimitam início/fim.

-- Últimos 3 registros do cliente (janela deslizante)
SUM(v.Valor) OVER (
  PARTITION BY v.IdCliente
  ORDER BY v.DataVenda
  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS SomaUltimos3

7) GROUP BY x Janela

8) Laboratório Guiado

8.1) Setup (reaproveitando tabelas dos módulos anteriores)

-- Se necessário, crie as tabelas
IF OBJECT_ID('dbo.Clientes','U') IS NULL
BEGIN
  CREATE TABLE dbo.Clientes
  (
      IdCliente      int            IDENTITY PRIMARY KEY,
      Nome           nvarchar(100)  NOT NULL,
      CPF            varchar(11)    NULL,
      DataNascimento date           NULL,
      Cidade         nvarchar(100)  NULL,
      UF             char(2)        NULL,
      Ativo          bit            NOT NULL DEFAULT 1
  );
END
IF OBJECT_ID('dbo.Vendas','U') IS NULL
BEGIN
  CREATE TABLE dbo.Vendas
  (
      IdVenda     int            IDENTITY PRIMARY KEY,
      IdCliente   int            NOT NULL REFERENCES dbo.Clientes(IdCliente),
      DataVenda   date           NOT NULL,
      Valor       decimal(10,2)  NOT NULL
  );
END

-- Dados básicos se vazio
IF NOT EXISTS (SELECT 1 FROM dbo.Clientes)
BEGIN
  INSERT INTO dbo.Clientes (Nome, CPF, DataNascimento, Cidade, UF, Ativo) VALUES
  (N'João Silva', '11122233344', '1993-05-10', N'São Paulo', 'SP', 1),
  (N'Ana Souza',  '55566677788', '1997-09-21', N'Campinas',  'SP', 1),
  (N'Bruno Lima', NULL,          '1985-12-02', N'Curitiba',  'PR', 0);
END

IF NOT EXISTS (SELECT 1 FROM dbo.Vendas)
BEGIN
  INSERT INTO dbo.Vendas (IdCliente, DataVenda, Valor) VALUES
  (1, '2025-08-01', 1200.00),
  (1, '2025-08-15',  350.50),
  (1, '2025-08-21',  500.00),
  (2, '2025-08-10',  999.90),
  (2, '2025-08-22',  180.00);
END
GO

8.2) Ranking por UF

WITH Totais AS (
  SELECT c.UF, c.Nome, SUM(v.Valor) AS Total
  FROM dbo.Vendas AS v
  JOIN dbo.Clientes AS c ON c.IdCliente = v.IdCliente
  GROUP BY c.UF, c.Nome
)
SELECT *,
       ROW_NUMBER() OVER (PARTITION BY UF ORDER BY Total DESC) AS rn
FROM Totais;

8.3) Acumulados e comparação

-- Acumulado e diferença para venda anterior por cliente
SELECT
  v.IdCliente, v.DataVenda, v.Valor,
  SUM(v.Valor) OVER (PARTITION BY v.IdCliente ORDER BY v.DataVenda
       ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Acumulado,
  v.Valor - LAG(v.Valor,1,0) OVER (PARTITION BY v.IdCliente ORDER BY v.DataVenda) AS Delta
FROM dbo.Vendas AS v;

8.4) Percentis por UF

SELECT
  c.UF,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY v.Valor)
    OVER (PARTITION BY c.UF) AS MedianaUF
FROM dbo.Vendas AS v
JOIN dbo.Clientes AS c ON c.IdCliente = v.IdCliente;

9) Exercícios

1. TOP-N por grupo: Liste as 2 maiores vendas por UF usando ROW_NUMBER() e filtre rn <= 2.
2. Running total mensal: Calcule total acumulado por cliente ordenando por DataVenda.
3. Delta percentual: Mostre variação % de cada venda contra a anterior do mesmo cliente usando LAG.
4. Quartis: Classifique vendas em quartis por UF com NTILE(4).
5. Mediana por cliente: Use PERCENTILE_CONT(0.5) por IdCliente.

10) Gabarito (sugestões)

-- 1) Top 2 por UF
WITH Ranked AS (
  SELECT
    c.UF, v.IdVenda, v.IdCliente, v.DataVenda, v.Valor,
    ROW_NUMBER() OVER (PARTITION BY c.UF ORDER BY v.Valor DESC) AS rn
  FROM dbo.Vendas AS v
  JOIN dbo.Clientes AS c ON c.IdCliente = v.IdCliente
)
SELECT * FROM Ranked WHERE rn <= 2;

-- 2) Running total mensal por cliente
SELECT
  v.IdCliente, v.DataVenda, v.Valor,
  SUM(v.Valor) OVER (
    PARTITION BY v.IdCliente
    ORDER BY v.DataVenda
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS Acumulado
FROM dbo.Vendas AS v;

-- 3) Delta percentual vs anterior
SELECT
  v.IdCliente, v.DataVenda, v.Valor,
  LAG(v.Valor) OVER (PARTITION BY v.IdCliente ORDER BY v.DataVenda) AS PrevValor,
  CASE
    WHEN LAG(v.Valor) OVER (PARTITION BY v.IdCliente ORDER BY v.DataVenda) IS NULL OR
         LAG(v.Valor) OVER (PARTITION BY v.IdCliente ORDER BY v.DataVenda) = 0
      THEN NULL
    ELSE ROUND( (v.Valor - LAG(v.Valor) OVER (PARTITION BY v.IdCliente ORDER BY v.DataVenda))
                 / LAG(v.Valor) OVER (PARTITION BY v.IdCliente ORDER BY v.DataVenda) * 100.0, 2)
  END AS DeltaPercent
FROM dbo.Vendas AS v;

-- 4) Quartis por UF
SELECT
  c.UF, v.IdVenda, v.Valor,
  NTILE(4) OVER (PARTITION BY c.UF ORDER BY v.Valor DESC) AS Quartil
FROM dbo.Vendas AS v
JOIN dbo.Clientes AS c ON c.IdCliente = v.IdCliente;

-- 5) Mediana por cliente
SELECT
  v.IdCliente,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY v.Valor)
    OVER (PARTITION BY v.IdCliente) AS MedianaCliente
FROM dbo.Vendas AS v;

11) Erros Comuns & Boas Práticas

12) Próximos Passos

• Aplicar janelas em relatórios reais (ranking de produtos, churn, cohort).
• Estudar GROUPING SETS / ROLLUP / CUBE (resumos multi-dimensionais) no próximo módulo.
• Criar testes de desempenho comparando janelas vs subconsultas.