SQL e manipulação de dados em bases do DETRAN

Trilha prática de consultas: SELECT avançado

Joins (INNER, LEFT, anti-join) em cenários do DETRAN

Joins combinam linhas de tabelas relacionadas para responder perguntas operacionais (atendimentos, veículos, infrações, pagamentos). O ponto crítico é escolher o tipo de join correto para não “perder” registros (INNER) ou para evidenciar ausências (LEFT + filtro).

Exemplo de contexto (nomes ilustrativos): cidadao, atendimento, veiculo, infracao, pagamento, auditoria_log.

-- 1) Atendimentos com dados do cidadão (INNER JOIN: só quem tem atendimento entra no resultado)
SELECT a.id_atendimento, a.dt_abertura, a.status, c.cpf, c.nome
FROM atendimento a
JOIN cidadao c ON c.id_cidadao = a.id_cidadao
WHERE a.dt_abertura >= DATE '2025-01-01';

-- 2) Veículos e seus proprietários, incluindo veículos sem proprietário vinculado (LEFT JOIN)
SELECT v.placa, v.renavam, c.cpf, c.nome
FROM veiculo v
LEFT JOIN cidadao c ON c.id_cidadao = v.id_proprietario;

-- 3) Anti-join: veículos sem proprietário (útil para detectar inconsistência cadastral)
SELECT v.placa, v.renavam
FROM veiculo v
LEFT JOIN cidadao c ON c.id_cidadao = v.id_proprietario
WHERE c.id_cidadao IS NULL;

Boas práticas: prefira condições de junção em chaves (PK/FK), evite funções na coluna de junção (ex.: UPPER(cpf)) e valide cardinalidade (1:N, N:N) para não multiplicar linhas sem querer.

Subqueries (correlacionadas e não correlacionadas)

Subqueries ajudam quando você precisa filtrar por um conjunto derivado (ex.: “último pagamento”, “maior data”, “existência de infração”). Subquery correlacionada executa “por linha” e pode ser mais custosa; quando possível, reescreva com JOIN/CTE.

-- 1) Cidadãos que tiveram atendimento em status 'PENDENTE'
SELECT c.id_cidadao, c.cpf, c.nome
FROM cidadao c
WHERE EXISTS (
  SELECT 1
  FROM atendimento a
  WHERE a.id_cidadao = c.id_cidadao
    AND a.status = 'PENDENTE'
);

-- 2) Veículos com infrações no último ano (subquery não correlacionada)
SELECT v.placa, v.renavam
FROM veiculo v
WHERE v.id_veiculo IN (
  SELECT i.id_veiculo
  FROM infracao i
  WHERE i.dt_infracao >= CURRENT_DATE - INTERVAL '1 year'
);

CTE (WITH) para legibilidade e reuso

CTEs organizam consultas complexas em etapas, facilitando manutenção e auditoria. Em alguns bancos, CTE pode materializar (impacto de desempenho); avalie o plano de execução.

Continue em nosso aplicativo

Você poderá ouvir o audiobook com a tela desligada, ganhar gratuitamente o certificado deste curso e ainda ter acesso a outros 5.000 cursos online gratuitos.

ou continue lendo abaixo...

Baixar o aplicativo

WITH atend_30d AS (
  SELECT a.id_atendimento, a.id_cidadao, a.dt_abertura, a.status
  FROM atendimento a
  WHERE a.dt_abertura >= CURRENT_DATE - INTERVAL '30 days'
),
pendentes AS (
  SELECT *
  FROM atend_30d
  WHERE status = 'PENDENTE'
)
SELECT p.id_atendimento, p.dt_abertura, c.cpf, c.nome
FROM pendentes p
JOIN cidadao c ON c.id_cidadao = p.id_cidadao
ORDER BY p.dt_abertura DESC;

Agregações e relatórios operacionais

GROUP BY, HAVING e métricas típicas

Agregações resumem dados para relatórios (volume de atendimentos, infrações por tipo, arrecadação por período). Use HAVING para filtrar após agregar.

-- 1) Atendimentos por status no mês atual
SELECT a.status, COUNT(*) AS qtde
FROM atendimento a
WHERE a.dt_abertura >= DATE_TRUNC('month', CURRENT_DATE)
  AND a.dt_abertura <  DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month'
GROUP BY a.status
ORDER BY qtde DESC;

-- 2) Tipos de infração com mais de 100 ocorrências no trimestre
SELECT i.cod_infracao, COUNT(*) AS qtde
FROM infracao i
WHERE i.dt_infracao >= DATE_TRUNC('quarter', CURRENT_DATE)
GROUP BY i.cod_infracao
HAVING COUNT(*) > 100
ORDER BY qtde DESC;

Dica de desempenho: em filtros por período, evite WHERE DATE(i.dt_infracao) = ... (função na coluna). Prefira intervalos com >= e < para permitir uso de índice.

Agregações condicionais

Útil para consolidar vários indicadores em uma única consulta (ex.: total, pendentes, concluídos).

SELECT
  COUNT(*) AS total,
  SUM(CASE WHEN status = 'PENDENTE' THEN 1 ELSE 0 END) AS pendentes,
  SUM(CASE WHEN status = 'CONCLUIDO' THEN 1 ELSE 0 END) AS concluidos
FROM atendimento
WHERE dt_abertura >= CURRENT_DATE - INTERVAL '7 days';

Window functions (funções analíticas) para auditoria e ranking

Conceito e quando usar

Window functions calculam métricas “sobre uma janela” sem colapsar linhas como o GROUP BY. São ideais para: identificar o último evento por entidade, calcular tempos entre eventos, ranking de unidades/serviços, e detectar duplicidades.

Último atendimento por cidadão (ROW_NUMBER)

WITH x AS (
  SELECT
    a.*, 
    ROW_NUMBER() OVER (PARTITION BY a.id_cidadao ORDER BY a.dt_abertura DESC) AS rn
  FROM atendimento a
)
SELECT id_atendimento, id_cidadao, dt_abertura, status
FROM x
WHERE rn = 1;

Tempo entre mudanças de status (LAG)

Supondo uma tabela de histórico atendimento_status_hist com registros de mudança de status.

SELECT
  h.id_atendimento,
  h.dt_evento,
  h.status,
  LAG(h.dt_evento) OVER (PARTITION BY h.id_atendimento ORDER BY h.dt_evento) AS dt_anterior,
  h.dt_evento - LAG(h.dt_evento) OVER (PARTITION BY h.id_atendimento ORDER BY h.dt_evento) AS delta
FROM atendimento_status_hist h
WHERE h.dt_evento >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY h.id_atendimento, h.dt_evento;

Ranking de unidades por volume de atendimentos (DENSE_RANK)

WITH agg AS (
  SELECT id_unidade, COUNT(*) AS qtde
  FROM atendimento
  WHERE dt_abertura >= CURRENT_DATE - INTERVAL '30 days'
  GROUP BY id_unidade
)
SELECT
  id_unidade,
  qtde,
  DENSE_RANK() OVER (ORDER BY qtde DESC) AS posicao
FROM agg
ORDER BY posicao, id_unidade;

Filtros por datas e status (padrões seguros)

Intervalos fechados/abertos para evitar erros

Para relatórios diários/mensais, use padrão [início, fim) (inclui início, exclui fim). Isso evita problemas com horários e milissegundos.

-- Registros do dia 2025-01-16 (independente do horário)
SELECT *
FROM atendimento
WHERE dt_abertura >= TIMESTAMP '2025-01-16 00:00:00'
  AND dt_abertura <  TIMESTAMP '2025-01-17 00:00:00';

-- Registros do mês de janeiro/2025
SELECT *
FROM infracao
WHERE dt_infracao >= TIMESTAMP '2025-01-01 00:00:00'
  AND dt_infracao <  TIMESTAMP '2025-02-01 00:00:00';

Status e domínios

Em bases operacionais, status costuma ser um domínio controlado (ex.: PENDENTE, EM_ANALISE, CONCLUIDO, CANCELADO). Filtrar por status é comum em filas de trabalho e auditorias.

SELECT id_atendimento, dt_abertura, prioridade
FROM atendimento
WHERE status IN ('PENDENTE', 'EM_ANALISE')
ORDER BY prioridade DESC, dt_abertura ASC;

Criação de views para padronizar consultas

Conceito

Views encapsulam consultas frequentes, padronizam regras de negócio de leitura (ex.: “atendimentos ativos”), e reduzem duplicação de SQL em relatórios e integrações. Não substituem índices nem resolvem, por si só, problemas de desempenho.

Exemplo: view de atendimentos ativos

CREATE VIEW vw_atendimentos_ativos AS
SELECT
  a.id_atendimento,
  a.id_cidadao,
  a.id_unidade,
  a.dt_abertura,
  a.status
FROM atendimento a
WHERE a.status IN ('PENDENTE', 'EM_ANALISE');

Passo a passo prático:

• Liste quais relatórios/consultas se repetem e quais filtros são “padrão”.
• Crie a view com colunas explícitas (evite SELECT *).
• Valide permissões: conceda acesso à view quando não quiser expor tabelas base.
• Teste impacto no plano de execução das consultas que usam a view.

DML com transações, isolamento e concorrência

INSERT com consistência e auditoria

Inserções em sistemas do DETRAN geralmente exigem consistência entre entidades e rastreabilidade. Use transação para garantir atomicidade e registre auditoria (via trigger ou via aplicação, conforme padrão do órgão).

BEGIN;

INSERT INTO atendimento (id_atendimento, id_cidadao, id_unidade, dt_abertura, status)
VALUES (NEXTVAL('seq_atendimento'), 12345, 10, CURRENT_TIMESTAMP, 'PENDENTE');

INSERT INTO auditoria_log (id_log, entidade, id_entidade, acao, dt_evento, usuario)
VALUES (NEXTVAL('seq_auditoria'), 'ATENDIMENTO', CURRVAL('seq_atendimento'), 'INSERT', CURRENT_TIMESTAMP, 'operador01');

COMMIT;

UPDATE com controle de concorrência (otimista e pessimista)

Concorrência ocorre quando dois usuários/processos tentam alterar o mesmo registro (ex.: atendimento em fila). Há duas estratégias comuns:

• Otimista: usa coluna de versão (ou timestamp) e falha se alguém alterou antes.
• Pessimista: bloqueia a linha durante a transação (SELECT ... FOR UPDATE).

-- 1) Concorrência otimista (exemplo com coluna versao)
BEGIN;

UPDATE atendimento
SET status = 'EM_ANALISE', versao = versao + 1
WHERE id_atendimento = 9001
  AND versao = 7; -- versão lida anteriormente

-- Se 0 linhas forem afetadas, alguém alterou antes: tratar na aplicação
COMMIT;

-- 2) Concorrência pessimista (bloqueio da linha)
BEGIN;

SELECT id_atendimento, status
FROM atendimento
WHERE id_atendimento = 9001
FOR UPDATE;

UPDATE atendimento
SET status = 'EM_ANALISE'
WHERE id_atendimento = 9001;

COMMIT;

DELETE seguro (soft delete e integridade)

Em bases com necessidade de rastreabilidade, é comum evitar DELETE físico e usar soft delete (ex.: ativo = 'N', dt_inativacao). Quando o DELETE é permitido, valide dependências (FK) e registre auditoria.

-- Soft delete
BEGIN;

UPDATE veiculo
SET ativo = 'N', dt_inativacao = CURRENT_TIMESTAMP
WHERE id_veiculo = 555;

INSERT INTO auditoria_log (id_log, entidade, id_entidade, acao, dt_evento, usuario)
VALUES (NEXTVAL('seq_auditoria'), 'VEICULO', 555, 'SOFT_DELETE', CURRENT_TIMESTAMP, 'admin01');

COMMIT;

Isolamento de transações (noções práticas)

O nível de isolamento define o que uma transação “enxerga” enquanto outras transações estão em andamento. Na prática:

• READ COMMITTED: evita ler dados não confirmados; pode haver leituras não repetíveis.
• REPEATABLE READ: garante estabilidade das linhas lidas; pode aumentar contenção.
• SERIALIZABLE: maior isolamento; pode gerar abortos por conflito em alta concorrência.

-- Exemplo genérico (varia por SGBD)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
-- operações
COMMIT;

Noções de stored procedures e funções

Quando usar

Procedures e funções são úteis para padronizar rotinas no banco: validações, cálculos, geração de protocolos, atualização de status com regras, e rotinas de auditoria. Evite concentrar regra de negócio volátil no banco se isso dificultar versionamento e testes; priorize rotinas estáveis e transacionais.

Exemplo de função para normalização simples

-- Exemplo ilustrativo: normalizar placa (remover espaços e padronizar maiúsculas)
CREATE FUNCTION fn_normaliza_placa(p_placa VARCHAR)
RETURNS VARCHAR
AS $$
  SELECT UPPER(REPLACE(TRIM(p_placa), ' ', ''));
$$ LANGUAGE SQL;

Exemplo de procedure para avançar status com auditoria

-- Exemplo ilustrativo (sintaxe varia por SGBD)
CREATE PROCEDURE sp_avanca_status_atendimento(p_id BIGINT, p_novo_status VARCHAR, p_usuario VARCHAR)
AS $$
BEGIN
  UPDATE atendimento
  SET status = p_novo_status
  WHERE id_atendimento = p_id;

  INSERT INTO auditoria_log (id_log, entidade, id_entidade, acao, dt_evento, usuario)
  VALUES (NEXTVAL('seq_auditoria'), 'ATENDIMENTO', p_id, 'UPDATE_STATUS', CURRENT_TIMESTAMP, p_usuario);
END;
$$;

Consultas típicas (listas prontas para prática)

1) Auditoria de alterações

• Quem alterou o status de um atendimento e quando.
• Quais campos foram alterados em um cadastro em um intervalo de datas.
• Volume de alterações por usuário/unidade.

-- Alterações por entidade e período
SELECT entidade, id_entidade, acao, usuario, dt_evento
FROM auditoria_log
WHERE dt_evento >= CURRENT_DATE - INTERVAL '7 days'
  AND entidade IN ('ATENDIMENTO', 'VEICULO', 'CIDADAO')
ORDER BY dt_evento DESC;

2) Relatórios de atendimento

• Fila atual por unidade (pendentes e em análise).
• Tempo médio entre abertura e conclusão (se houver dt_conclusao).
• Top serviços mais demandados (se houver tipo_servico).

-- Tempo médio de conclusão por unidade (exige dt_conclusao)
SELECT id_unidade,
       AVG(dt_conclusao - dt_abertura) AS tempo_medio
FROM atendimento
WHERE status = 'CONCLUIDO'
  AND dt_abertura >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY id_unidade
ORDER BY tempo_medio;

3) Detecção de inconsistências cadastrais

• Veículos sem proprietário.
• CPF duplicado (quando deveria ser único) ou cadastros com campos obrigatórios nulos.
• Infrações sem vínculo com veículo válido.

-- Possível duplicidade de CPF (se não houver constraint de unicidade)
SELECT cpf, COUNT(*) AS qtde
FROM cidadao
GROUP BY cpf
HAVING COUNT(*) > 1
ORDER BY qtde DESC;

-- Infrações sem veículo correspondente
SELECT i.id_infracao, i.id_veiculo, i.dt_infracao
FROM infracao i
LEFT JOIN veiculo v ON v.id_veiculo = i.id_veiculo
WHERE v.id_veiculo IS NULL;

Interpretação de planos de execução e boas práticas de desempenho

O que observar no plano

Ao analisar um plano de execução (EXPLAIN/EXPLAIN ANALYZE), foque em:

• Tipo de acesso: varredura completa (seq scan/full scan) vs. uso de índice (index scan/seek).
• Cardinalidade estimada vs. real: divergências sugerem estatísticas desatualizadas ou filtros pouco seletivos.
• Operadores caros: sort, hash aggregate, nested loop em grandes volumes, hash join com spill em disco.
• Ordem de joins e filtros: filtros seletivos cedo tendem a reduzir custo.

-- Exemplo genérico
EXPLAIN ANALYZE
SELECT a.id_atendimento, c.cpf
FROM atendimento a
JOIN cidadao c ON c.id_cidadao = a.id_cidadao
WHERE a.status = 'PENDENTE'
  AND a.dt_abertura >= CURRENT_DATE - INTERVAL '7 days';

Checklist de boas práticas

• Selecione apenas colunas necessárias (evite SELECT * em relatórios pesados).
• Use filtros por intervalo de datas (evite funções na coluna filtrada).
• Garanta índices coerentes com filtros e joins frequentes (ex.: (status, dt_abertura) quando consultas usam ambos).
• Evite subqueries correlacionadas em grandes tabelas quando um JOIN/CTE resolve.
• Paginação: prefira paginação por chave (keyset) em vez de OFFSET alto.
• Revise estatísticas (ANALYZE) e fragmentação conforme o SGBD.

Exercícios práticos (com foco em plano de execução)

Exercício 1: Join e filtro por período

Objetivo: listar atendimentos pendentes dos últimos 15 dias com CPF e unidade, ordenando por data.

• Escreva a consulta com JOIN.
• Rode EXPLAIN ANALYZE e identifique se há varredura completa em atendimento.
• Proponha um índice e reavalie o plano.

-- Esqueleto
EXPLAIN ANALYZE
SELECT a.id_atendimento, a.dt_abertura, a.id_unidade, c.cpf
FROM atendimento a
JOIN cidadao c ON c.id_cidadao = a.id_cidadao
WHERE a.status = 'PENDENTE'
  AND a.dt_abertura >= CURRENT_DATE - INTERVAL '15 days'
ORDER BY a.dt_abertura DESC;

Exercício 2: Último evento por entidade (window function)

Objetivo: obter a última mudança de status de cada atendimento no mês atual.

• Use ROW_NUMBER() particionando por atendimento.
• Compare com alternativa usando subquery com MAX(dt_evento).
• Analise qual gera menos custo no plano e por quê.

WITH x AS (
  SELECT h.*, ROW_NUMBER() OVER (PARTITION BY h.id_atendimento ORDER BY h.dt_evento DESC) AS rn
  FROM atendimento_status_hist h
  WHERE h.dt_evento >= DATE_TRUNC('month', CURRENT_DATE)
)
SELECT id_atendimento, dt_evento, status
FROM x
WHERE rn = 1;

Exercício 3: Detecção de inconsistência com anti-join

Objetivo: listar infrações cujo veículo está inativo ou inexistente.

• Implemente com LEFT JOIN e filtro.
• Verifique se o plano usa índice em infracao.id_veiculo e veiculo.id_veiculo.

EXPLAIN ANALYZE
SELECT i.id_infracao, i.id_veiculo, i.dt_infracao
FROM infracao i
LEFT JOIN veiculo v ON v.id_veiculo = i.id_veiculo
WHERE v.id_veiculo IS NULL
   OR v.ativo = 'N';