WITH RECURSIVE hierarquia AS (
  SELECT id, nome, id_pai, 1 AS nivel
  FROM unidades
  WHERE id_pai IS NULL
  UNION ALL
  SELECT u.id, u.nome, u.id_pai, h.nivel + 1
  FROM unidades u
  JOIN hierarquia h ON u.id_pai = h.id
),
produtividade AS (
  SELECT s.id, s.nome, s.id_unidade, s.produtividade,
         ROW_NUMBER() OVER (PARTITION BY s.id_unidade ORDER BY s.produtividade DESC) AS rn,
         RANK() OVER (PARTITION BY s.id_unidade ORDER BY s.produtividade DESC) AS rk,
         DENSE_RANK() OVER (PARTITION BY s.id_unidade ORDER BY s.produtividade DESC) AS drk,
         AVG(s.produtividade) OVER (PARTITION BY s.id_unidade) AS media_unidade
  FROM servidores s
)
SELECT h.nome AS unidade, p.nome AS servidor, p.produtividade, p.rn, p.rk, p.drk, p.media_unidade
FROM hierarquia h
JOIN produtividade p ON h.id = p.id_unidade
WHERE p.rn <= 3
ORDER BY h.nivel, h.nome, p.rn;

• AA CTE recursiva 'hierarquia' é materializada em uma tabela temporária antes de ser referenciada na consulta principal, o que impede o uso de índices na tabela 'unidades' durante a execução da parte recursiva, tornando o plano de execução menos eficiente do que uma subconsulta correlacionada equivalente.
• BAs funções de janela ROW_NUMBER(), RANK() e DENSE_RANK() produzem resultados idênticos quando não há empates na coluna 'produtividade' dentro de cada partição, mas divergem na presença de empates: ROW_NUMBER() atribui números sequenciais sem repetição, enquanto RANK() e DENSE_RANK() atribuem o mesmo número a valores empatados, sendo que RANK() deixa lacunas na sequência e DENSE_RANK() não.
• CA cláusula WHERE p.rn <= 3 é aplicada antes do cálculo das funções de janela, pois o PostgreSQL otimiza a consulta movendo predicados para dentro da CTE 'produtividade', o que reduz o número de linhas processadas pelas funções de janela e melhora o desempenho.
• DO uso de PARTITION BY s.id_unidade nas funções de janela faz com que o PostgreSQL crie um índice temporário na coluna 'id_unidade' para cada função, o que pode ser observado no plano de execução como um nó 'WindowAgg' com 'Sort Method: quicksort' e 'Memory: ...kB', indicando que a ordenação é feita em memória.
• EA CTE 'produtividade' é avaliada uma única vez e seu resultado é reutilizado tanto para o cálculo das funções de janela quanto para a junção com 'hierarquia', garantindo que a média por unidade (media_unidade) seja calculada sobre todas as linhas da partição, independentemente do filtro p.rn <= 3 aplicado posteriormente.

Atalhos: A–E marca · Shift+letra corta · Enter próxima

Selecione uma alternativa pra criar sua conta e salvar o progresso.