Agregação Avançada

Autor

Douglas Braga

As funções de agregação básicas (SUM, AVG, COUNT) agrupam linhas e produzem um único valor por grupo. Este capítulo apresenta duas extensões poderosas: funções de janela e agregação multidimensional (ROLLUP, CUBE, GROUPING SETS).

Funções de Janela (Window Functions)

Uma função de janela calcula um valor para cada linha com base em um conjunto de linhas relacionadas — a janela — sem colapsar as linhas em grupos como GROUP BY faz. A cláusula OVER define a janela.

função() OVER (
    [PARTITION BY coluna, ...]
    [ORDER BY coluna [ASC|DESC], ...]
    [ROWS | RANGE BETWEEN ... AND ...]
)

A diferença fundamental:

`GROUP BY`	`OVER`
Retorna uma linha por grupo	Retorna uma linha por linha da tabela
Agrega e colapsa	Calcula sem colapsar
Não pode misturar colunas de grupo e não-grupo	Pode misturar livremente

Ranking: RANK, DENSE_RANK, ROW_NUMBER

-- Professores ranqueados por salário (geral)
SELECT nome,
       id_escola,
       salario,
       RANK()       OVER (ORDER BY salario DESC)          AS rank_geral,
       DENSE_RANK() OVER (ORDER BY salario DESC)          AS dense_rank_geral,
       ROW_NUMBER() OVER (ORDER BY salario DESC, id_prof) AS row_num
FROM   professor
ORDER BY rank_geral;

7 records
nome	id_escola	salario	rank_geral	dense_rank_geral	row_num
Helena Carvalho	31	6700	1	1	1
Carla Pinto	11	6600	2	2	2
Bruno Teixeira	11	6500	3	3	3
Felipe Araujo	21	6400	4	4	4
Gustavo Costa	31	3300	5	5	5
Eduarda Souza	21	3200	6	6	6
Igor Melo	31	3100	7	7	7

Função	Comportamento com empates
`RANK()`	Posições iguais para empates; salta posições após
`DENSE_RANK()`	Posições iguais para empates; não salta posições
`ROW_NUMBER()`	Numera linhas sequencialmente; sem empates

Ranking por Partição

PARTITION BY reinicia o ranking dentro de cada grupo:

-- Ranking de salário dentro de cada escola
SELECT p.nome,
       e.sigla                                          AS escola,
       p.salario,
       RANK() OVER (PARTITION BY p.id_escola
                    ORDER BY p.salario DESC)             AS rank_na_escola
FROM   professor p
JOIN   escola   e ON e.id_escola = p.id_escola
ORDER BY e.sigla, rank_na_escola;

7 records
nome	escola	salario	rank_na_escola
Felipe Araujo	EEMA	6400	1
Eduarda Souza	EEMA	3200	2
Helena Carvalho	ESETI	6700	1
Gustavo Costa	ESETI	3300	2
Igor Melo	ESETI	3100	3
Carla Pinto	ESG	6600	1
Bruno Teixeira	ESG	6500	2

NTILE — Dividindo em Quartis (ou N grupos)

NTILE(n) distribui as linhas em n grupos de tamanhos iguais (ou o mais próximo possível):

-- Distribuição dos alunos em quartis de nota (apenas notas lançadas)
SELECT a.nome,
       md.nota,
       NTILE(4) OVER (ORDER BY md.nota DESC NULLS LAST) AS quartil
FROM   matricula_disciplina md
JOIN   aluno a USING (id_aluno)
WHERE  md.nota IS NOT NULL
ORDER BY quartil, md.nota DESC;

Displaying records 1 - 10
nome	nota	quartil
Thiago Gomes	10.0	1
Camila Teixeira	9.9	1
Beatriz Carvalho	9.8	1
Carolina Lima	9.7	1
Carolina Lima	9.2	1
Matheus Silva	9.0	1
Larissa Araújo	9.0	1
Amanda Moreira	8.8	1
Beatriz Carvalho	8.8	1
Rafael Costa	8.7	1

Funções de Janela com Agregação

Funções de janela podem usar qualquer agregado (SUM, AVG, COUNT) com OVER:

-- Nota de cada aluno vs. média da turma (por disciplina/ano/semestre)
SELECT d.nome_disciplina,
       a.nome                                      AS aluno,
       md.nota,
       ROUND(AVG(md.nota) OVER (
           PARTITION BY md.id_disciplina, md.ano, md.semestre
       ), 2)                                       AS media_turma,
       md.nota - ROUND(AVG(md.nota) OVER (
           PARTITION BY md.id_disciplina, md.ano, md.semestre
       ), 2)                                       AS desvio_da_media
FROM   matricula_disciplina md
JOIN   aluno      a USING (id_aluno)
JOIN   disciplina d USING (id_disciplina)
WHERE  md.nota IS NOT NULL
ORDER BY d.nome_disciplina, md.nota DESC;

Displaying records 1 - 10
nome_disciplina	aluno	nota	media_turma	desvio_da_media
Bases da Eng. de Software 2	Matheus Silva	9.0	7.55	1.45
Bases da Eng. de Software 2	Larissa Araújo	6.1	7.55	-1.45
Bases da Eng. de Software 2	Diego Correia	6.1	4.00	2.10
Bases da Eng. de Software 2	Isabela Freitas	4.3	4.30	0.00
Bases da Eng. de Software 2	Juliana Nunes	1.9	4.00	-2.10
Bases da Eng. de Software 4	Larissa Araújo	9.0	8.25	0.75
Bases da Eng. de Software 4	Isabela Freitas	7.7	7.70	0.00
Bases da Eng. de Software 4	Matheus Silva	7.5	8.25	-0.75
Bases da Eng. de Software 6	Matheus Silva	4.1	2.20	1.90
Bases da Eng. de Software 6	Larissa Araújo	0.3	2.20	-1.90

Windowing — Janelas Deslizantes

A especificação ROWS BETWEEN define uma janela móvel de linhas relativas à linha atual:

-- Média acumulada de notas por aluno (ordenadas por semestre da disciplina)
SELECT a.nome,
       d.semestre,
       d.nome_disciplina,
       md.nota,
       ROUND(AVG(md.nota) OVER (
           PARTITION BY md.id_aluno
           ORDER BY d.semestre
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ), 2) AS media_acumulada
FROM   matricula_disciplina md
JOIN   aluno      a USING (id_aluno)
JOIN   disciplina d USING (id_disciplina)
WHERE  md.nota IS NOT NULL
ORDER BY a.nome, d.semestre;

Displaying records 1 - 10
nome	semestre	nome_disciplina	nota	media_acumulada
Amanda Moreira	2	Desenvolvimento Humano	8.4	8.40
Amanda Moreira	2	Didática Geral	8.2	8.30
Amanda Moreira	4	Eletiva I	8.5	8.37
Amanda Moreira	4	Fundamentos e Orientações Metodológicas - Ed. Infantil - BNCC	8.8	8.48
Amanda Moreira	6	Educação do Campo, Indígena e Quilombola	6.2	7.70
Amanda Moreira	6	Eletiva II	6.1	8.00
Beatriz Carvalho	2	História Econômica Geral	9.8	9.80
Beatriz Carvalho	2	Culturas Digitais	8.8	9.30
Camila Teixeira	2	História Econômica Geral	6.1	8.00
Camila Teixeira	2	Culturas Digitais	9.9	9.90

Especificações de janela comuns:

Especificação	Significado
`ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`	Da primeira linha até a atual (acumulado)
`ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING`	Linha anterior, atual e próxima (média móvel de 3)
`ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING`	Da atual até a última

Agregação Multidimensional: ROLLUP e CUBE

O GROUP BY padrão gera um único nível de agrupamento. ROLLUP e CUBE geram múltiplos níveis de agrupamento em uma única consulta — equivalentes a várias consultas com GROUP BY e UNION ALL.

ROLLUP — Subtotais Hierárquicos

ROLLUP(A, B, C) gera a união de: (A, B, C), (A, B), (A), ()

-- Total de alunos por escola, por curso, e total geral (ROLLUP)
SELECT e.sigla                            AS escola,
       c.sigla_curso                      AS curso,
       COUNT(a.id_aluno)                  AS total_alunos
FROM   escola e
JOIN   curso  c ON c.id_escola = e.id_escola
LEFT JOIN aluno a ON a.id_curso = c.id_curso
GROUP BY ROLLUP(e.sigla, c.sigla_curso)
ORDER BY escola NULLS LAST, curso NULLS LAST;

7 records
escola	curso	total_alunos
EEMA	PED	6
EEMA	NA	6
ESETI	ENS	6
ESETI	NA	6
ESG	ECO	6
ESG	NA	6
NA	NA	18

As linhas com NULL em curso são os subtotais por escola; a linha com NULL em ambas é o total geral.

CUBE — Todos os Agrupamentos Possíveis

CUBE(A, B) gera a união de todos os 2² = 4 subconjuntos: (A, B), (A), (B), ().

-- Salário médio cruzando escola × carga horária semanal (CUBE)
SELECT e.sigla                             AS escola,
       p.ch_semanal,
       ROUND(AVG(p.salario), 2)            AS media_salarial,
       COUNT(*)                            AS professores
FROM   professor p
JOIN   escola   e ON e.id_escola = p.id_escola
GROUP BY CUBE(e.sigla, p.ch_semanal)
ORDER BY escola NULLS LAST, ch_semanal NULLS LAST;

Displaying records 1 - 10
escola	ch_semanal	media_salarial	professores
EEMA	20	3200.00	1
EEMA	40	6400.00	1
EEMA	NA	4800.00	2
ESETI	20	3200.00	2
ESETI	40	6700.00	1
ESETI	NA	4366.67	3
ESG	40	6550.00	2
ESG	NA	6550.00	2
NA	20	3200.00	3
NA	40	6550.00	4

GROUPING() — Distinguindo NULL de Agregação vs. NULL de Dado

Quando um atributo tem NULL no resultado de ROLLUP/CUBE, é difícil saber se é um NULL genuíno do dado ou um NULL que representa “todos”. A função GROUPING() resolve isso:

-- GROUPING identifica linhas de subtotal (1 = agregado, 0 = valor real)
SELECT COALESCE(e.sigla, '*** TOTAL ***')  AS escola,
       COALESCE(c.sigla_curso, '---')      AS curso,
       COUNT(a.id_aluno)                   AS alunos,
       GROUPING(e.sigla)                   AS flag_escola,
       GROUPING(c.sigla_curso)             AS flag_curso
FROM   escola e
JOIN   curso  c ON c.id_escola = e.id_escola
LEFT JOIN aluno a ON a.id_curso = c.id_curso
GROUP BY ROLLUP(e.sigla, c.sigla_curso)
ORDER BY flag_escola, flag_curso, escola, curso;

7 records
escola	curso	alunos	flag_escola	flag_curso
EEMA	PED	6	0	0
ESETI	ENS	6	0	0
ESG	ECO	6	0	0
EEMA	—	6	0	1
ESETI	—	6	0	1
ESG	—	6	0	1
* TOTAL *	—	18	1	1

GROUPING SETS — Controle Granular

GROUPING SETS permite especificar exatamente quais agrupamentos gerar, sem precisar dos padrões de ROLLUP ou CUBE:

-- Apenas os agrupamentos por escola e por carga horária (sem combinação)
SELECT e.sigla        AS escola,
       p.ch_semanal,
       COUNT(*)       AS professores,
       ROUND(AVG(p.salario), 2) AS media_salarial
FROM   professor p
JOIN   escola   e ON e.id_escola = p.id_escola
GROUP BY GROUPING SETS (
    (e.sigla),          -- total por escola
    (p.ch_semanal),     -- total por carga horária
    ()                  -- total geral
)
ORDER BY escola NULLS LAST, ch_semanal NULLS LAST;

6 records
escola	ch_semanal	professores	media_salarial
EEMA	NA	2	4800.00
ESETI	NA	3	4366.67
ESG	NA	2	6550.00
NA	20	3	3200.00
NA	40	4	6550.00
NA	NA	7	5114.29

Para Praticar

-- Top-2 alunos por nota em cada disciplina (usando RANK com PARTITION)
WITH ranked AS (
    SELECT d.nome_disciplina,
           a.nome                            AS aluno,
           md.nota,
           RANK() OVER (
               PARTITION BY md.id_disciplina
               ORDER BY md.nota DESC NULLS LAST
           ) AS rank_na_turma
    FROM   matricula_disciplina md
    JOIN   aluno      a USING (id_aluno)
    JOIN   disciplina d USING (id_disciplina)
    WHERE  md.nota IS NOT NULL
)
SELECT nome_disciplina, aluno, nota, rank_na_turma
FROM   ranked
WHERE  rank_na_turma <= 2
ORDER BY nome_disciplina, rank_na_turma;

Displaying records 1 - 10
nome_disciplina	aluno	nota	rank_na_turma
Bases da Eng. de Software 2	Matheus Silva	9.0	1
Bases da Eng. de Software 2	Diego Correia	6.1	2
Bases da Eng. de Software 2	Larissa Araújo	6.1	2
Bases da Eng. de Software 4	Larissa Araújo	9.0	1
Bases da Eng. de Software 4	Isabela Freitas	7.7	2
Bases da Eng. de Software 6	Matheus Silva	4.1	1
Bases da Eng. de Software 6	Larissa Araújo	0.3	2
Culturas Digitais	Camila Teixeira	9.9	1
Culturas Digitais	Beatriz Carvalho	8.8	2
Desenvolvimento Humano	Letícia Souza	8.4	1

-- Resumo executivo: total de alunos e média salarial por centro (ROLLUP)
SELECT COALESCE(ce.nome_centro, 'TOTAL GERAL') AS centro,
       COUNT(DISTINCT a.id_aluno)              AS total_alunos,
       ROUND(AVG(p.salario), 2)                AS media_salario_prof
FROM   centro ce
JOIN   escola  es ON es.id_centro = ce.id_centro
LEFT JOIN curso c ON c.id_escola = es.id_escola
LEFT JOIN aluno a ON a.id_curso  = c.id_curso
LEFT JOIN professor p ON p.id_escola = es.id_escola
GROUP BY ROLLUP(ce.nome_centro)
ORDER BY centro NULLS LAST;

4 records
centro	total_alunos	media_salario_prof
Centro de Educação, Magistério e Artes	6	4800.00
Centro de Engenharias, Tecnologia e Inovação	6	4366.67
Ciências Humanas, Cidadania e Meio Ambiente	6	6550.00
TOTAL GERAL	18	5114.29

-- Percentil de cada professor no ranking salarial geral
SELECT nome,
       salario,
       ROUND((PERCENT_RANK() OVER (ORDER BY salario) * 100)::NUMERIC, 1) AS percentil,
       ROUND((CUME_DIST()    OVER (ORDER BY salario) * 100)::NUMERIC, 1) AS dist_acumulada
FROM   professor
ORDER BY salario;

7 records
nome	salario	percentil	dist_acumulada
Igor Melo	3100	0.0	14.3
Eduarda Souza	3200	16.7	28.6
Gustavo Costa	3300	33.3	42.9
Felipe Araujo	6400	50.0	57.1
Bruno Teixeira	6500	66.7	71.4
Carla Pinto	6600	83.3	85.7
Helena Carvalho	6700	100.0	100.0