Configurando o frame da janela com ROWS e RANGE

Quando tu usa funções de janela, bate aquela dúvida: "Quantas linhas dentro da janela participam do cálculo do valor pra linha atual?" A resposta depende do frame da janela.

Frame da janela é o intervalo de linhas que é usado pra calcular o resultado da função de janela. Esse intervalo é construído com base na linha atual e também nas condições extras que tu define usando ROWS ou RANGE.

Um exemplo simples: calculando uma soma acumulada, tu pode dizer:

• Considera só a linha atual.
• Considera a linha atual e todas as linhas acima.
• Considera a linha atual e um número fixo de linhas acima/abaixo.

É justamente o ROWS e o RANGE que mandam quais linhas vão entrar no frame da janela.

Usando ROWS

ROWS define o frame da janela no nível do posicionamento físico das linhas. Isso quer dizer que ele conta as linhas de cima pra baixo na ordem delas, sem se importar com os valores dessas linhas.

Sintaxe

funcao_de_janela OVER (
    ORDER BY coluna
    ROWS BETWEEN inicio AND fim
)

Expressões chave:

• CURRENT ROW — linha atual.
• numero PRECEDING — um número definido de linhas acima da atual.
• numero FOLLOWING — um número definido de linhas abaixo da atual.
• UNBOUNDED PRECEDING — desde o começo da janela.
• UNBOUNDED FOLLOWING — até o fim da janela.

Exemplo: soma acumulada pra linha atual e 2 anteriores

SELECT
    employee_id,
    salary,
    SUM(salary) OVER (
        ORDER BY employee_id
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS rolling_sum
FROM employees;

Explicação:

• ROWS BETWEEN 2 PRECEDING AND CURRENT ROW quer dizer: pega a linha atual e as duas linhas acima dela.
• A soma acumulada vai ser calculada só pra essas três linhas.

Resultado:

Exemplo: análise de "janela deslizante" com número fixo de linhas

Tarefa: calcular a média salarial pra linha atual e as duas próximas.

SELECT 
    employee_id,
    salary,
    AVG(salary) OVER (
        ORDER BY employee_id
        ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING
    ) AS rolling_avg
FROM employees;

Resultado:

Usando RANGE

RANGE monta o frame da janela com base nos valores, não na posição das linhas. Isso quer dizer que as linhas entram no frame se os valores na coluna do ORDER BY caírem no intervalo definido.

Sintaxe

funcao_de_janela OVER (
    ORDER BY coluna
    RANGE BETWEEN inicio AND fim
)

Exemplo: soma acumulada por intervalo de valores

Tarefa: calcular a soma acumulada pras linhas onde o salário difere do atual em no máximo 2000.

SELECT 
    employee_id,
    salary,
    SUM(salary) OVER (
        ORDER BY salary
        RANGE BETWEEN 2000 PRECEDING AND 2000 FOLLOWING
    ) AS range_sum
FROM employees;

Explicação:

• RANGE BETWEEN 2000 PRECEDING AND 2000 FOLLOWING quer dizer: pega as linhas onde o salary tá no intervalo de ±2000 da linha atual.

Resultado:

Comparando ROWS e RANGE

• ROWS trabalha com as linhas reais e a quantidade delas. Ele não depende dos valores.
• RANGE trabalha com o intervalo lógico de valores definido pra coluna do ORDER BY.

Pra comparar, olha esse exemplo. Imagina que temos uma tabela sales com esses dados:

Vamos comparar as queries:

ROWS:

SELECT
    id,
    SUM(amount) OVER (
        ORDER BY amount
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS sum_rows
FROM sales;

Resultado:

Aqui cada linha vai sendo somada conforme ela realmente aparece.

RANGE:

SELECT 
    id,
    SUM(amount) OVER (
        ORDER BY amount
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS sum_range
FROM sales;

Resultado:

Aqui as linhas 1 e 2 se juntaram, porque o amount = 100 pra ambas. RANGE leva em conta valores repetidos na coluna amount.

Exemplos de tarefas reais

1. Calcular crescimento de receita

Tarefa: calcular a diferença de receita em relação à linha anterior.

SELECT 
    month,
    revenue,
    revenue - LAG(revenue) OVER (
        ORDER BY month
    ) AS revenue_change
FROM sales_data;

2. Comparar a linha atual com a média do grupo

Tarefa: pra cada departamento, calcular a diferença do salário do funcionário com a média salarial do departamento.

SELECT 
    department_id,
    employee_id,
    salary,
    salary - AVG(salary) OVER (
        PARTITION BY department_id
    ) AS salary_diff
FROM employees;

Erros ao usar ROWS e RANGE

Ordem da linha (ORDER BY) mal definida: Se tu não definir a ordem de ordenação, o PostgreSQL vai dar erro porque não consegue saber qual é a linha atual.

Misturar abordagens ROWS e RANGE na mesma tarefa: Escolhe a abordagem conforme teus dados. ROWS é melhor pra tarefas com número fixo de linhas, e RANGE — pra intervalos de valores.

Ignorar valores repetidos em RANGE: Lembra que RANGE considera todos os valores repetidos, o que pode mudar bastante o resultado.