Como atribuir corretamente o CNES final baseado nas regras de certificação no PostgreSQL/MySQL?

reinaldocosta
(usa Ubuntu)

Enviado em 19/03/2025 - 14:41h

Boa tarde, pessoal!

Estou trabalhando com um banco de dados PostgreSQL e preciso implementar uma lógica para definir o CNES final dos profissionais certificados, de acordo com regras de negócio específicas.

Tenho duas tabelas:

cadastro_certificacao = Contém todos os vínculos dos profissionais com diferentes unidades de saúde (CNES).

aprovados = Lista os CNES nos quais os profissionais foram certificados.

As tabelas foram criadas assim:

--create table cadastro_certificacao(id serial primary key,cnes varchar,cpf varchar,nome varchar);
--create table aprovados(id serial primary key,cnes varchar,cpf varchar,nome varchar);

--insert into cadastro_certificacao(cnes,cpf,nome) values
--('1000','1111','maria'),
--('2000','1111','maria'),
--('3000','2222','joao'),
--('4000','2222','joao'),
--('5000','3333','joanna'),
--('5000','3333','joanna'),
--('6000','4444','paulo'),
--('6000','4444','paulo'),
--('8000','5555','Heloisa');

--insert into aprovados(cnes,cpf,nome) values
--('1000','1111','maria'),
--('5000','2222','joao'),
--('5000','3333','joanna'),
--('7000','4444','paulo');

Regras de Negócio para definir o CNES final:
- Se um profissional tem dois vínculos distintos e foi certificado em pelo menos um deles, ambos os vínculos devem herdar a certificação.
- Se um profissional tem dois vínculos distintos e foi certificado em um CNES novo, esse passa a ser seu único vínculo válido.
- Se um profissional tem dois vínculos na mesma unidade, considerar apenas um (DISTINCTCOUNT do CPF).
- Se um profissional tem um vínculo e foi certificado em outro, considerar apenas o CNES certificado como vínculo válido.

Minha dúvida:
Qual seria a melhor forma de estruturar essa lógica no PostgreSQL ou MySQL?
Uma abordagem com CTEs, subqueries ou window functions seria mais eficiente?

Agradeço desde já por qualquer sugestão!

alexabolada
(usa Debian)

Enviado em 19/03/2025 - 14:41h

Boa tarde!

Para resolver a sua questão sobre a atribuição do CNES final com base nas regras de certificação, uma abordagem com CTEs (Common Table Expressions) pode ser bastante eficiente e clara, permitindo uma leitura mais fácil da lógica.

Vamos estruturar a consulta. Primeiro, você deve considerar as certificações e vínculos, e aplicar suas regras. Aqui está um exemplo de como você pode fazer isso:

```sql
WITH classificados AS (
SELECT
c.cpf,
c.cnes AS vinculo,
a.cnes AS cnes_certificado
FROM
cadastro_certificacao c
LEFT JOIN
aprovados a ON c.cpf = a.cpf
),

vinculos_distintos AS (
SELECT
cpf,
COUNT(DISTINCT vinculo) AS cont_vinculos,
COUNT(DISTINCT cnes_certificado) AS cont_certificados
FROM
classificados
GROUP BY
cpf
),

resultado_final AS (
SELECT
c.cpf,
CASE
WHEN v.cont_vinculos > 1 AND v.cont_certificados > 0 THEN MIN(c.cnes_certificado) -- Vínculos distintos com certificação
WHEN v.cont_vinculos > 1 AND v.cont_certificados = 0 THEN MIN(c.vinculo) -- Dois vínculos, mas sem certificação
WHEN v.cont_vinculos = 1 THEN MIN