Window functions

O GROUP BY devolve uma linha por grupo. A window function calcula sobre o grupo e mantém cada linha. A janela se define com OVER (PARTITION BY … ORDER BY …).

ROW_NUMBER, RANK e DENSE_RANK

Os três numeram dentro da partição; a diferença está no empate. Os pedidos 102 e 110 valem 900.

SELECT id, valor,
       ROW_NUMBER() OVER (ORDER BY valor DESC, id) AS row_number,
       RANK()       OVER (ORDER BY valor DESC) AS rank,
       DENSE_RANK() OVER (ORDER BY valor DESC) AS dense_rank
FROM pedidos
WHERE valor >= 180
ORDER BY valor DESC, id;

id  | valor | row_number | rank | dense_rank
--- | ----- | ---------- | ---- | ----------
105 | 1050  | 1          | 1    | 1
102 | 900   | 2          | 2    | 2
110 | 900   | 3          | 2    | 2
101 | 230   | 4          | 4    | 3
104 | 180   | 5          | 5    | 4

• ROW_NUMBER: sempre 1, 2, 3… (no empate, decide o desempate que você der, ou o acaso).
• RANK: empate divide a posição e pula a seguinte (1, 2, 2, 4).
• DENSE_RANK: empate divide a posição sem pular (1, 2, 2, 3).

O maior pedido de cada cliente

O padrão "top N por grupo": numere dentro da partição e filtre por fora (o WHERE roda antes da janela, então o filtro vai numa CTE ou subconsulta).

WITH ordenados AS (
  SELECT cliente_id, id, valor,
         ROW_NUMBER() OVER (PARTITION BY cliente_id ORDER BY valor DESC, id) AS n
  FROM pedidos
  WHERE valor IS NOT NULL
)
SELECT cliente_id, id, valor FROM ordenados WHERE n = 1 ORDER BY cliente_id;

cliente_id | id  | valor
---------- | --- | -----
1          | 110 | 900
2          | 102 | 900
3          | 104 | 180
4          | 105 | 1050
5          | 107 | 30
9          | 109 | 80

Total acumulado

Com ORDER BY dentro do OVER, a soma vira acumulada. O quadro padrão é RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: linhas empatadas na ordenação entram juntas. Para somar linha a linha, diga ROWS.

SELECT data, SUM(valor) AS no_dia,
       SUM(SUM(valor)) OVER (ORDER BY data) AS acumulado
FROM pedidos
WHERE status = 'entregue'
GROUP BY data
ORDER BY data;

data       | no_dia | acumulado
---------- | ------ | ---------
2024-06-01 | 1130   | 1130
2024-06-02 | 180    | 1310
2024-06-03 | 1050   | 2360
2024-06-04 | 180    | 2540
2024-06-05 | 980    | 3520

LAG e LEAD: a linha vizinha

SELECT id, data, valor,
       LAG(valor) OVER (PARTITION BY cliente_id ORDER BY data, id) AS anterior,
       valor - LAG(valor) OVER (PARTITION BY cliente_id ORDER BY data, id) AS diferenca
FROM pedidos
WHERE cliente_id = 1
ORDER BY data, id;

id  | data       | valor | anterior | diferenca
--- | ---------- | ----- | -------- | ---------
101 | 2024-06-01 | 230   | NULL     | NULL
103 | 2024-06-02 | 80    | 230      | -150
110 | 2024-06-05 | 900   | 80       | 820

A primeira linha não tem anterior: LAG devolve NULL (ou o terceiro argumento, se der um padrão).

• No PySpark: from pyspark.sql import Window e F.row_number().over(Window.partitionBy("cliente_id").orderBy(F.desc("valor"))).
• Janela sem PARTITION BY manda tudo para uma partição só: no Spark, isso é uma única tarefa processando a tabela inteira.