PostgreSQL: PostgreSQL Funcionalidades Avançadas

Última atualização: 2026-08-26

1. O que Você Aprenderá

Usar funções de janela para estatísticas de presença e ranking
Usar views e materialized views para encapsular consultas complexas
Projetar estratégias de indexação para otimizar o desempenho de consultas
Usar JSONB para armazenar tags de habilidades flexíveis dos funcionários
Integrar busca textual para localização de funcionários
Completar um projeto completo de banco de dados para um sistema de gerenciamento de funcionários

2. A História

Alice é engenheira de banco de dados em uma empresa SaaS. O departamento de RH precisa que ela construa o banco de dados para um sistema de gerenciamento de funcionários. Os requisitos são:

Estatísticas de presença: dias de presença mensais, contagem de atrasos e horas extras por funcionário, com ranking por funções de janela
Ranking de desempenho: uma pontuação composta a partir de avaliações multidimensionais, armazenada em cache em uma materialized view
Tags de habilidades: as tags de habilidades de cada funcionário não são fixas, então armazene-as de forma flexível em JSONB
Busca de funcionários: buscar funcionários rapidamente por nome, habilidade, departamento, etc., usando busca textual

Alice precisa combinar funções de janela, views, índices, JSONB e busca textual para entregar este projeto.

3. Conceito: Design do Banco de Dados do Projeto

(1) Diagrama ER

erDiagram
    EMPLOYEES ||--o{ ATTENDANCE : has
    EMPLOYEES ||--o{ PERFORMANCE : receives
    EMPLOYEES }o--|| DEPARTMENTS : belongs_to

    EMPLOYEES {
        int employee_id PK
        text name
        int department_id FK
        text position
        date hire_date
        jsonb skills
        tsvector search_doc
    }

    DEPARTMENTS {
        int department_id PK
        text dept_name
        text location
    }

    ATTENDANCE {
        int attendance_id PK
        int employee_id FK
        date attend_date
        text status
        time check_in_time
        time check_out_time
        numeric overtime_hours
    }

    PERFORMANCE {
        int perf_id PK
        int employee_id FK
        int year
        int quarter
        numeric technical_score
        numeric communication_score
        numeric leadership_score
        numeric overall_score
    }

▶ Exemplo: Criando a Estrutura Base das Tabelas

SQL

CREATE TABLE departments (
  department_id SERIAL PRIMARY KEY,
  dept_name TEXT NOT NULL,
  location TEXT NOT NULL
);

CREATE TABLE employees (
  employee_id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  department_id INT NOT NULL REFERENCES departments(department_id),
  position TEXT NOT NULL,
  hire_date DATE NOT NULL DEFAULT CURRENT_DATE,
  skills JSONB NOT NULL DEFAULT '[]',
  search_doc tsvector
);

CREATE TABLE attendance (
  attendance_id SERIAL PRIMARY KEY,
  employee_id INT NOT NULL REFERENCES employees(employee_id),
  attend_date DATE NOT NULL,
  status TEXT NOT NULL CHECK (status IN ('present','late','absent','leave')),
  check_in_time TIME,
  check_out_time TIME,
  overtime_hours NUMERIC(4,2) DEFAULT 0
);

CREATE TABLE performance (
  perf_id SERIAL PRIMARY KEY,
  employee_id INT NOT NULL REFERENCES employees(employee_id),
  year INT NOT NULL,
  quarter INT NOT NULL CHECK (quarter BETWEEN 1 AND 4),
  technical_score NUMERIC(5,2) CHECK (technical_score BETWEEN 0 AND 100),
  communication_score NUMERIC(5,2) CHECK (communication_score BETWEEN 0 AND 100),
  leadership_score NUMERIC(5,2) CHECK (leadership_score BETWEEN 0 AND 100),
  overall_score NUMERIC(5,2) CHECK (overall_score BETWEEN 0 AND 100),
  UNIQUE (employee_id, year, quarter)
);

TEXT 📖 Somente leitura

CREATE TABLE

(2) Inserir Dados de Teste

▶ Exemplo: Inserindo Departamentos e Funcionários

SQL

INSERT INTO departments (dept_name, location) VALUES
  ('Engineering', 'Floor 3'),
  ('Sales', 'Floor 2'),
  ('Marketing', 'Floor 1'),
  ('HR', 'Floor 1');

INSERT INTO employees (name, department_id, position, hire_date, skills) VALUES
  ('Alice Chen', 1, 'Senior Engineer', '2022-03-15',
   '["PostgreSQL","Python","Docker","Kubernetes"]'),
  ('Bob Wang', 1, 'Junior Engineer', '2023-06-01',
   '["Java","Spring","MySQL"]'),
  ('Charlie Zhang', 2, 'Sales Manager', '2021-01-10',
   '["Negotiation","CRM","English","French"]'),
  ('Diana Liu', 3, 'Marketing Specialist', '2023-09-20',
   '["SEO","Content Writing","Google Analytics"]'),
  ('Edward Wu', 1, 'DevOps Engineer', '2022-11-01',
   '["Docker","Kubernetes","AWS","Terraform"]'),
  ('Fiona Li', 4, 'HR Manager', '2020-05-15',
   '["Recruiting","Employee Relations","Payroll"]');

TEXT 📖 Somente leitura

INSERT 0 1

▶ Exemplo: Inserindo Dados de Presença

SQL

INSERT INTO attendance (employee_id, attend_date, status, check_in_time, check_out_time, overtime_hours)
SELECT
  e.employee_id,
  d.dt::date,
  CASE WHEN random() < 0.05 THEN 'absent'
       WHEN random() < 0.12 THEN 'late'
       ELSE 'present'
  END,
  CASE WHEN random() < 0.12 THEN '09:15' ELSE '08:55' END,
  CASE WHEN random() < 0.15 THEN '19:00' ELSE '18:00' END,
  CASE WHEN random() < 0.15 THEN 1.0 ELSE 0 END
FROM employees e
CROSS JOIN (
  SELECT generate_series('2025-01-01'::timestamp, '2025-03-31'::timestamp, '1 day') AS dt
) d
WHERE EXTRACT(dow FROM d.dt) NOT IN (0, 6);

TEXT 📖 Somente leitura

INSERT 0 1

▶ Exemplo: Inserindo Dados de Desempenho

SQL

INSERT INTO performance (employee_id, year, quarter, technical_score, communication_score, leadership_score, overall_score)
VALUES
  (1, 2025, 1, 92, 85, 88, 89.0),
  (2, 2025, 1, 78, 72, 65, 72.3),
  (3, 2025, 1, 70, 90, 85, 82.0),
  (4, 2025, 1, 75, 88, 72, 78.3),
  (5, 2025, 1, 88, 76, 80, 82.0),
  (6, 2025, 1, 65, 92, 90, 82.3);

TEXT 📖 Somente leitura

INSERT 0 1

4. Conceito: Funções de Janela — Estatísticas de Presença e Ranking

(1) Resumo Mensal de Presença

▶ Exemplo: Presença Mensal por Funcionário

SQL

SELECT
  e.employee_id,
  e.name,
  d.dept_name,
  TO_CHAR(a.attend_date, 'YYYY-MM') AS month,
  COUNT(*) FILTER (WHERE a.status = 'present') AS present_days,
  COUNT(*) FILTER (WHERE a.status = 'late') AS late_days,
  COUNT(*) FILTER (WHERE a.status = 'absent') AS absent_days,
  SUM(a.overtime_hours) AS total_overtime
FROM employees e
JOIN attendance a ON e.employee_id = a.employee_id
JOIN departments d ON e.department_id = d.department_id
GROUP BY e.employee_id, e.name, d.dept_name, TO_CHAR(a.attend_date, 'YYYY-MM')
ORDER BY e.employee_id, month;

TEXT 📖 Somente leitura

 count 
-------
     5
(1 row)

Função	Descrição
`COUNT(*) FILTER (WHERE ...)`	Contagem condicional (específica do PostgreSQL, mais clara que SUM(CASE))
`TO_CHAR(date, 'YYYY-MM')`	Agrupar por mês

(2) Ranking de Presença no Departamento

▶ Exemplo: Ranking dentro de um Departamento com Funções de Janela

SQL

SELECT
  e.name,
  d.dept_name,
  COUNT(*) FILTER (WHERE a.status = 'present') AS present_days,
  SUM(a.overtime_hours) AS total_overtime,
  RANK() OVER (PARTITION BY d.dept_name ORDER BY COUNT(*) FILTER (WHERE a.status = 'present') DESC) AS attendance_rank,
  RANK() OVER (PARTITION BY d.dept_name ORDER BY SUM(a.overtime_hours) DESC) AS overtime_rank
FROM employees e
JOIN attendance a ON e.employee_id = a.employee_id
JOIN departments d ON e.department_id = d.department_id
WHERE a.attend_date BETWEEN '2025-01-01' AND '2025-03-31'
GROUP BY e.employee_id, e.name, d.dept_name
ORDER BY d.dept_name, attendance_rank;

TEXT 📖 Somente leitura

 count 
-------
     5
(1 row)

Função de janela	Tratamento de empates	Caso de uso
`RANK()`	Empates pulam posições (1,1,3)	Ranking que permite empates
`DENSE_RANK()`	Empates não pulam posições (1,1,2)	Ranking contínuo
`ROW_NUMBER()`	Nunca empata (1,2,3)	Ranking único

▶ Exemplo: Dias de Presença Acumulados (Total Corrente)

SQL

SELECT
  e.name,
  a.attend_date,
  a.status,
  SUM(CASE WHEN a.status = 'present' THEN 1 ELSE 0 END)
    OVER (PARTITION BY e.employee_id ORDER BY a.attend_date) AS cumulative_present
FROM employees e
JOIN attendance a ON e.employee_id = a.employee_id
WHERE e.employee_id = 1
ORDER BY a.attend_date;

TEXT 📖 Somente leitura

  result  
----------
   42.50
(1 row)

▶ Exemplo: Este Mês vs. Mês Anterior (LAG)

SQL

WITH monthly_stats AS (
  SELECT
    e.employee_id,
    e.name,
    TO_CHAR(a.attend_date, 'YYYY-MM') AS month,
    COUNT(*) FILTER (WHERE a.status = 'present') AS present_days
  FROM employees e
  JOIN attendance a ON e.employee_id = a.employee_id
  GROUP BY e.employee_id, e.name, TO_CHAR(a.attend_date, 'YYYY-MM')
)
SELECT
  name,
  month,
  present_days,
  LAG(present_days) OVER (PARTITION BY employee_id ORDER BY month) AS prev_month,
  present_days - LAG(present_days) OVER (PARTITION BY employee_id ORDER BY month) AS diff
FROM monthly_stats
ORDER BY employee_id, month;

TEXT 📖 Somente leitura

 count 
-------
     5
(1 row)

5. Conceito: Views e Materialized Views — Encapsulando o Ranking de Desempenho

(1) View vs Materialized View

Dimensão	View	Materialized View
Armazena dados	Não; calculada em tempo real a cada consulta	Sim; lê o resultado armazenado diretamente
Atualidade dos dados	Tempo real	Requer REFRESH manual
Desempenho de consulta	Igual à consulta base	Rápida (pré-calculada)
Caso de uso	Dados atualizados com frequência	Relatórios/estatísticas, dados que raramente mudam

▶ Exemplo: Criando a View de Ranking de Desempenho

SQL

CREATE VIEW v_performance_ranking AS
SELECT
  e.employee_id,
  e.name,
  d.dept_name,
  e.position,
  p.year,
  p.quarter,
  p.technical_score,
  p.communication_score,
  p.leadership_score,
  p.overall_score,
  RANK() OVER (PARTITION BY d.dept_name ORDER BY p.overall_score DESC) AS dept_rank,
  RANK() OVER (ORDER BY p.overall_score DESC) AS company_rank
FROM employees e
JOIN performance p ON e.employee_id = p.employee_id
JOIN departments d ON e.department_id = d.department_id;

SELECT * FROM v_performance_ranking WHERE quarter = 1 ORDER BY company_rank;

TEXT 📖 Somente leitura

CREATE TABLE

▶ Exemplo: Criando a Materialized View de Ranking de Desempenho

SQL

CREATE MATERIALIZED VIEW mv_performance_ranking AS
SELECT
  e.employee_id,
  e.name,
  d.dept_name,
  p.year,
  p.quarter,
  p.overall_score,
  RANK() OVER (ORDER BY p.overall_score DESC) AS company_rank
FROM employees e
JOIN performance p ON e.employee_id = p.employee_id
JOIN departments d ON e.department_id = d.department_id
WITH DATA;

-- Atualizar quando os dados de desempenho mudarem
REFRESH MATERIALIZED VIEW mv_performance_ranking;

-- Atualização concorrente (não bloqueia leituras)
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_performance_ranking;

TEXT 📖 Somente leitura

CREATE TABLE

Método REFRESH	Lock	Descrição
`REFRESH MATERIALIZED VIEW`	ACCESS EXCLUSIVE	Bloqueia leituras e escritas, mas não precisa de índice único
`REFRESH ... CONCURRENTLY`	SHARE	Não bloqueia leituras, requer um índice único

▶ Exemplo: Materialized View de Resumo de Presença

SQL

CREATE MATERIALIZED VIEW mv_attendance_summary AS
SELECT
  e.employee_id,
  e.name,
  d.dept_name,
  TO_CHAR(a.attend_date, 'YYYY-MM') AS month,
  COUNT(*) FILTER (WHERE a.status = 'present') AS present_days,
  COUNT(*) FILTER (WHERE a.status = 'late') AS late_days,
  COUNT(*) FILTER (WHERE a.status = 'absent') AS absent_days,
  SUM(a.overtime_hours) AS total_overtime
FROM employees e
JOIN attendance a ON e.employee_id = a.employee_id
JOIN departments d ON e.department_id = d.department_id
GROUP BY e.employee_id, e.name, d.dept_name, TO_CHAR(a.attend_date, 'YYYY-MM')
WITH DATA;

TEXT 📖 Somente leitura

 count 
-------
     5
(1 row)

6. Conceito: Otimização de Estratégia de Indexação

(1) Princípios de Design de Índices

Princípio	Descrição
Indexar colunas usadas em condições WHERE de alta frequência	O caso de uso mais básico
Indexar colunas de JOIN	Colunas de chave estrangeira não têm índice por padrão; crie manualmente
Evitar excesso de indexação	Cada índice adiciona overhead de escrita
Observar a ordem das colunas em índices compostos	Colunas de condição de igualdade primeiro, colunas de condição de intervalo por último
Verificar com EXPLAIN ANALYZE	O plano de execução real é o árbitro final

▶ Exemplo: Criando Índices para Consultas Principais

SQL

-- Índice para consultas de presença por funcionário e intervalo de datas
CREATE INDEX idx_attendance_emp_date
ON attendance (employee_id, attend_date);

-- Índice para consultas de desempenho por ano/trimestre
CREATE INDEX idx_performance_emp_quarter
ON performance (employee_id, year, quarter);

-- Índice para funcionários por departamento
CREATE INDEX idx_employees_dept
ON employees (department_id);

-- Índice parcial: indexar apenas registros de presença não ausentes
CREATE INDEX idx_attendance_present
ON attendance (employee_id, attend_date)
WHERE status != 'absent';

TEXT 📖 Somente leitura

CREATE TABLE

Tipo de índice	Sintaxe	Caso de uso
B-tree (padrão)	`CREATE INDEX idx ON t(col)`	Igualdade, intervalo, ordenação
Índice composto	`CREATE INDEX idx ON t(col1, col2)`	Consultas combinadas de múltiplas colunas
Índice parcial	`CREATE INDEX idx ON t(col) WHERE ...`	Indexar apenas linhas que atendem a uma condição
Índice de expressão	`CREATE INDEX idx ON t(lower(col))`	Consultas sobre resultados de funções

▶ Exemplo: Verificando o Efeito do Índice com EXPLAIN ANALYZE

SQL

-- Antes do índice: Seq Scan
EXPLAIN ANALYZE
SELECT * FROM attendance WHERE employee_id = 1 AND attend_date >= '2025-01-01';

-- Após o índice: Index Scan
CREATE INDEX idx_attendance_emp_date ON attendance (employee_id, attend_date);
EXPLAIN ANALYZE
SELECT * FROM attendance WHERE employee_id = 1 AND attend_date >= '2025-01-01';

TEXT 📖 Somente leitura

CREATE TABLE

▶ Exemplo: Índice de Cobertura para Evitar Acesso à Tabela

SQL

-- Incluir colunas para evitar acesso à tabela
CREATE INDEX idx_attendance_covering
ON attendance (employee_id, attend_date)
INCLUDE (status, overtime_hours);

-- Esta consulta precisa apenas do índice, sem acesso à tabela
EXPLAIN ANALYZE
SELECT status, overtime_hours
FROM attendance
WHERE employee_id = 1 AND attend_date >= '2025-01-01';

TEXT 📖 Somente leitura

CREATE TABLE

7. Conceito: JSONB para Armazenar Tags de Habilidades

(1) Design JSONB para Tags de Habilidades

▶ Exemplo: Consultando Habilidades dos Funcionários

SQL

-- Consultar habilidade específica
SELECT name, skills
FROM employees
WHERE skills @> '["PostgreSQL"]';

-- Consultar múltiplas habilidades (tem QUALQUER uma destas)
SELECT name, skills
FROM employees
WHERE skills ?| array['PostgreSQL', 'Docker'];

-- Consultar múltiplas habilidades (tem TODAS estas)
SELECT name, skills
FROM employees
WHERE skills @> '["Docker", "Kubernetes"]';

TEXT 📖 Somente leitura

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ Exemplo: Expandindo Habilidades em Linhas

SQL

SELECT
  e.name,
  jsonb_array_elements_text(e.skills) AS skill
FROM employees e
ORDER BY e.name;

TEXT 📖 Somente leitura

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ Exemplo: Contando Funcionários por Habilidade

SQL

SELECT
  skill,
  COUNT(*) AS employee_count
FROM (
  SELECT jsonb_array_elements_text(skills) AS skill FROM employees
) sub
GROUP BY skill
ORDER BY employee_count DESC;

TEXT 📖 Somente leitura

 count 
-------
     5
(1 row)

(2) Índice JSONB para Acelerar Consultas de Habilidades

▶ Exemplo: Criando um Índice GIN

SQL

CREATE INDEX idx_employees_skills ON employees USING gin (skills);

-- O índice GIN suporta consultas @> e ?|
EXPLAIN ANALYZE
SELECT name FROM employees WHERE skills @> '["PostgreSQL"]';

TEXT 📖 Somente leitura

CREATE TABLE

▶ Exemplo: Atualizando Tags de Habilidades para JSONB Estruturado

SQL

-- Atualizar de array simples para habilidades estruturadas
ALTER TABLE employees ADD COLUMN skill_profile JSONB DEFAULT '{}';

UPDATE employees SET skill_profile = '{
  "primary": ["PostgreSQL", "Python"],
  "secondary": ["Docker"],
  "certifications": ["AWS Solutions Architect"],
  "years_of_experience": {"PostgreSQL": 5, "Python": 8}
}'::jsonb
WHERE employee_id = 1;

-- Consultar por habilidade principal
SELECT name, skill_profile -> 'primary' AS primary_skills
FROM employees
WHERE skill_profile -> 'primary' @> '["PostgreSQL"]';

-- Consultar por certificação
SELECT name
FROM employees
WHERE skill_profile -> 'certifications' @> '["AWS Solutions Architect"]';

-- Consultar por anos de experiência
SELECT name
FROM employees
WHERE (skill_profile -> 'years_of_experience' ->> 'PostgreSQL')::int >= 3;

TEXT 📖 Somente leitura

UPDATE 3

8. Conceito: Busca Textual para Localização de Funcionários

(1) Construindo o Documento de Busca tsvector

▶ Exemplo: Criando a Coluna de Documento de Busca e a Trigger

SQL

-- Adicionar coluna de documento de busca
ALTER TABLE employees ADD COLUMN search_doc tsvector;

-- Criar função para construir o documento de busca
CREATE FUNCTION employees_search_update() RETURNS trigger AS $$
BEGIN
  NEW.search_doc :=
    setweight(to_tsvector('english', COALESCE(NEW.name, '')), 'A') ||
    setweight(to_tsvector('english', COALESCE(NEW.position, '')), 'B') ||
    setweight(to_tsvector('simple', COALESCE(
      array_to_string(
        ARRAY(SELECT jsonb_array_elements_text(NEW.skills)), ' '
      ), '')), 'C');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Criar trigger
CREATE TRIGGER trg_employees_search
BEFORE INSERT OR UPDATE OF name, position, skills ON employees
FOR EACH ROW EXECUTE FUNCTION employees_search_update();

-- Atualizar linhas existentes
UPDATE employees SET name = name; -- A trigger dispara e popula search_doc

TEXT 📖 Somente leitura

INSERT 0 1

▶ Exemplo: Criando o Índice GIN

SQL

CREATE INDEX idx_employees_search ON employees USING gin (search_doc);

TEXT 📖 Somente leitura

CREATE TABLE

(2) A Função de Busca

▶ Exemplo: A Função de Busca de Funcionários

SQL

CREATE FUNCTION search_employees(p_query TEXT)
RETURNS TABLE (
  employee_id INT,
  name TEXT,
  position TEXT,
  dept_name TEXT,
  skills JSONB,
  rank REAL,
  headline TEXT
) AS $$
BEGIN
  RETURN QUERY
  SELECT
    e.employee_id,
    e.name,
    e.position,
    d.dept_name,
    e.skills,
    ts_rank(e.search_doc, websearch_to_tsquery('english', p_query)) AS rank,
    ts_headline(
      'english',
      COALESCE(e.name, '') || ' ' || COALESCE(e.position, ''),
      websearch_to_tsquery('english', p_query),
      'StartSel=<mark>,StopSel=</mark>'
    ) AS headline
  FROM employees e
  JOIN departments d ON e.department_id = d.department_id
  WHERE e.search_doc @@ websearch_to_tsquery('english', p_query)
  ORDER BY rank DESC;
END;
$$ LANGUAGE plpgsql;

-- Teste: buscar por engineer
SELECT * FROM search_employees('engineer');

-- Teste: buscar por habilidade Docker
SELECT * FROM search_employees('Docker');

TEXT 📖 Somente leitura

CREATE TABLE

9. Conceito: Consulta Abrangente em Ação

(1) Relatório Multidimensional de Funcionários

▶ Exemplo: Relatório de Informações Completas do Funcionário

SQL

CREATE VIEW v_employee_dashboard AS
SELECT
  e.employee_id,
  e.name,
  d.dept_name,
  e.position,
  e.hire_date,
  EXTRACT(YEAR FROM age(CURRENT_DATE, e.hire_date)) AS years_of_service,
  e.skills,
  p.overall_score AS latest_perf_score,
  p_r.company_rank,
  a_s.present_days AS q1_present,
  a_s.late_days AS q1_late,
  a_s.total_overtime AS q1_overtime
FROM employees e
JOIN departments d ON e.department_id = d.department_id
LEFT JOIN LATERAL (
  SELECT overall_score FROM performance
  WHERE employee_id = e.employee_id
  ORDER BY year DESC, quarter DESC LIMIT 1
) p ON true
LEFT JOIN LATERAL (
  SELECT company_rank FROM mv_performance_ranking
  WHERE employee_id = e.employee_id
  ORDER BY year DESC, quarter DESC LIMIT 1
) p_r ON true
LEFT JOIN LATERAL (
  SELECT
    COUNT(*) FILTER (WHERE status = 'present') AS present_days,
    COUNT(*) FILTER (WHERE status = 'late') AS late_days,
    SUM(overtime_hours) AS total_overtime
  FROM attendance
  WHERE employee_id = e.employee_id
    AND attend_date BETWEEN '2025-01-01' AND '2025-03-31'
) a_s ON true;

TEXT 📖 Somente leitura

 count 
-------
     5
(1 row)

▶ Exemplo: Comparação de Desempenho por Departamento

SQL

SELECT
  d.dept_name,
  AVG(p.overall_score) AS avg_score,
  MAX(p.overall_score) AS max_score,
  MIN(p.overall_score) AS min_score,
  COUNT(*) AS employee_count,
  RANK() OVER (ORDER BY AVG(p.overall_score) DESC) AS dept_rank
FROM departments d
JOIN employees e ON d.department_id = e.department_id
JOIN performance p ON e.employee_id = p.employee_id
WHERE p.year = 2025 AND p.quarter = 1
GROUP BY d.dept_name
ORDER BY avg_score DESC;

TEXT 📖 Somente leitura

 count 
-------
     5
(1 row)

(2) Análise de Lacunas de Habilidades

▶ Exemplo: Distribuição de Habilidades por Departamento

SQL

SELECT
  d.dept_name,
  skill,
  COUNT(*) AS employees_with_skill,
  ROUND(COUNT(*)::numeric / SUM(COUNT(*)) OVER (PARTITION BY d.dept_name) * 100, 1) AS pct
FROM employees e
JOIN departments d ON e.department_id = d.department_id
CROSS JOIN LATERAL jsonb_array_elements_text(e.skills) AS skill
GROUP BY d.dept_name, skill
ORDER BY d.dept_name, employees_with_skill DESC;

TEXT 📖 Somente leitura

 count 
-------
     5
(1 row)

10. Na Prática: Um Banco de Dados Completo de Sistema de Gerenciamento de Funcionários

Alice integra todos os módulos em um projeto completo.

SQL

-- ============================================
-- Sistema de Gerenciamento de Funcionários - Schema Completo
-- ============================================

-- Passo 1: Tabelas (já criadas acima)
-- Garantir que todas as tabelas, índices e triggers existam

-- Passo 2: Índices principais
CREATE INDEX idx_attendance_emp_date
ON attendance (employee_id, attend_date);
CREATE INDEX idx_performance_emp_quarter
ON performance (employee_id, year, quarter);
CREATE INDEX idx_employees_dept
ON employees (department_id);
CREATE INDEX idx_employees_skills
ON employees USING gin (skills);
CREATE INDEX idx_employees_search
ON employees USING gin (search_doc);

-- Passo 3: Materialized views
CREATE MATERIALIZED VIEW mv_dept_performance AS
SELECT
  d.dept_name,
  p.year,
  p.quarter,
  AVG(p.overall_score) AS avg_score,
  AVG(p.technical_score) AS avg_tech,
  AVG(p.communication_score) AS avg_comm,
  AVG(p.leadership_score) AS avg_lead,
  COUNT(*) AS headcount
FROM departments d
JOIN employees e ON d.department_id = e.department_id
JOIN performance p ON e.employee_id = p.employee_id
GROUP BY d.dept_name, p.year, p.quarter
WITH DATA;

CREATE UNIQUE INDEX idx_mv_dept_perf
ON mv_dept_performance (dept_name, year, quarter);

-- Passo 4: Função de relatório de presença
CREATE FUNCTION get_attendance_report(
  p_year INT, p_month INT
) RETURNS TABLE (
  employee_id INT, name TEXT, dept_name TEXT,
  present_days BIGINT, late_days BIGINT,
  absent_days BIGINT, overtime_hours NUMERIC,
  attendance_rate NUMERIC, dept_rank BIGINT
) AS $$
BEGIN
  RETURN QUERY
  SELECT
    e.employee_id,
    e.name,
    d.dept_name,
    COUNT(*) FILTER (WHERE a.status = 'present') AS present_days,
    COUNT(*) FILTER (WHERE a.status = 'late') AS late_days,
    COUNT(*) FILTER (WHERE a.status = 'absent') AS absent_days,
    COALESCE(SUM(a.overtime_hours), 0) AS overtime_hours,
    ROUND(
      COUNT(*) FILTER (WHERE a.status IN ('present','late'))::numeric
      / NULLIF(COUNT(*), 0) * 100, 1
    ) AS attendance_rate,
    RANK() OVER (PARTITION BY d.dept_name
      ORDER BY COUNT(*) FILTER (WHERE a.status = 'present') DESC)
  FROM employees e
  JOIN attendance a ON e.employee_id = a.employee_id
  JOIN departments d ON e.department_id = d.department_id
  WHERE EXTRACT(YEAR FROM a.attend_date) = p_year
    AND EXTRACT(MONTH FROM a.attend_date) = p_month
  GROUP BY e.employee_id, e.name, d.dept_name
  ORDER BY d.dept_name, dept_rank;
END;
$$ LANGUAGE plpgsql;

-- Passo 5: Busca de funcionários (já criada acima)

-- Passo 6: Testar o sistema completo
-- Relatório de presença para janeiro de 2025
SELECT * FROM get_attendance_report(2025, 1);

-- Comparação de desempenho por departamento
SELECT * FROM mv_dept_performance
WHERE year = 2025 AND quarter = 1
ORDER BY avg_score DESC;

-- Encontrar funcionários com habilidades Docker E Kubernetes
SELECT name, position, skills
FROM employees
WHERE skills @> '["Docker","Kubernetes"]';

-- Busca textual por "engineer"
SELECT name, position, rank
FROM search_employees('engineer');

-- Atualizar materialized views quando os dados mudarem
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_dept_performance;
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_performance_ranking;

❓ Perguntas Frequentes

P Quando devo usar REFRESH CONCURRENTLY em uma materialized view?

R Use CONCURRENTLY quando a materialized view é consultada com frequência e o bloqueio de leituras é inaceitável. Ela requer pelo menos um índice UNIQUE na materialized view. Se uma breve indisponibilidade é aceitável, um REFRESH simples é mais rápido.

P Qual tem melhor desempenho, a sintaxe FILTER ou CASE WHEN?

R O desempenho é essencialmente o mesmo — o PostgreSQL otimiza internamente FILTER em um plano de execução equivalente ao CASE WHEN. A sintaxe FILTER é mais limpa e legível, por isso é recomendada.

P Qual é a diferença entre um LATERAL JOIN e uma subconsulta?

R LATERAL permite que uma subconsulta referencie colunas da consulta externa, como uma subconsulta correlacionada, mas pode retornar múltiplas linhas. Uma subconsulta normal não pode referenciar colunas externas. LATERAL se encaixa no cenário "calcular um resultado por linha externa".

P Array de habilidades em JSONB vs tabela de ligação — qual é melhor?

R Se as habilidades precisam ser gerenciadas independentemente (adicionar/remover/modificar, estatísticas, consultas relacionais), uma tabela de ligação (employee_skills) é melhor. Se as habilidades são apenas atributos do tipo tag com padrões de consulta simples, JSONB é mais flexível. Neste projeto, as habilidades são do tipo tag, então JSONB se encaixa melhor.

P Por que usar a configuração english em vez de simple ao buscar pela habilidade Docker?

R A configuração english faz stemming das palavras da consulta, mas "Docker" é um substantivo próprio, então o stemming não tem efeito. A configuração simple também funcionaria, mas se o termo de busca contiver palavras comuns em inglês (ex: "running"), o stemming da configuração english funciona melhor. Este projeto usa a configuração english uniformemente.

P Como atualizar uma materialized view automaticamente em um cronograma?

R O PostgreSQL não tem atualização automática nativa. Você pode usar a extensão pg_cron para executar REFRESH em um cronograma, ou usar o cron do OS/Agendador de Tarefas para invocar um comando psql. Por exemplo, atualização diária: SELECT cron.schedule('0 2 * * *', $$REFRESH MATERIALIZED VIEW CONCURRENTLY mv_dept_performance$$).

P Qual é a regra para a ordem das colunas em um índice composto?

R Colunas de consulta por igualdade vêm primeiro, colunas de consulta por intervalo vêm por último. Por exemplo, (employee_id, attend_date): primeiro filtra por employee_id com igualdade, depois faz range scan por attend_date. Ordem incorreta pode impedir o uso do índice.

📖 Resumo

Funções de janela impulsionam estatísticas de presença, ranking, comparação ano a ano (LAG/LEAD) e totais correntes
Views encapsulam consultas complexas; materialized views armazenam resultados em cache; CONCURRENTLY refresh não bloqueia leituras
Estratégia de indexação: indexar colunas WHERE/JOIN de alta frequência, observar a ordem das colunas em índices compostos, índices parciais reduzem overhead de manutenção
JSONB armazena tags de habilidades flexíveis; um índice GIN acelera consultas de contenção; @> é a consulta mais comum
Busca textual usa setweight para ponderar campos e triggers para sincronizar automaticamente o tsvector
O projeto abrangente funde múltiplas funcionalidades: funções de janela + materialized views + JSONB + busca textual + otimização de índices

📝 Exercícios

⭐ Use funções de janela para consultar os dias de presença no Q1 de cada funcionário, seu ranking de presença na empresa (DENSE_RANK) e quantos dias a menos eles têm em relação ao funcionário imediatamente acima no ranking (usando a função LAG).
⭐⭐ Crie uma materialized view mv_skill_gap que liste as habilidades que cada departamento não possui (habilidades presentes em outros departamentos, mas ausentes naquele departamento). Crie um índice UNIQUE, atualize com CONCURRENTLY e use EXPLAIN ANALYZE para comparar o desempenho da consulta antes e depois da atualização.
⭐⭐⭐ Desenvolva uma solução completa para o sistema de gerenciamento de funcionários: crie uma tabela training_records (employee_id, course_name, completion_date, score, tags JSONB) e escreva uma função recommend_training(p_employee_id INT) que recomende cursos de treinamento ausentes com base nas habilidades atuais do funcionário (employees.skills) e no treinamento existente (training_records.tags). Use busca textual para corresponder descrições de cursos, retornando as recomendações ordenadas por relevância.

Equipe Técnica Web-Tutorial

Uma plataforma de tutoriais mantida por diversos desenvolvedores. Cada tutorial é escrito e revisado por profissionais da área correspondente. Trabalhamos para manter nosso conteúdo preciso e confiável — se encontrar algum problema, avise-nos.

Sobre Nós