Help to simplify query sql

Question

I have a huge sql query:

select
l.no_cidadao,
extract(year from l.dt_cad_individual) ano,
extract(month from l.dt_cad_individual) mes,
date_part('year',age(now(), l.dt_cad_individual)) diferenca
from
tb_unidade_saude p,
tb_cds_cidadao_resposta a,
tb_cds_prof as c,
tb_pessoa_fisica as d,
tb_equipe as e,
(SELECT *,
   ROW_NUMBER() OVER (PARTITION BY no_cidadao
   ORDER BY st_atualizacao desc) AS linha
   FROM tb_cds_cad_individual) l
WHERE LINHA = 1
AND l.co_seq_cds_cad_individual = a.co_cds_cad_individual
and c.nu_cns = d.nu_cns
and c.co_seq_cds_prof = l.co_cds_prof_cadastrante
and a.co_pergunta = 19
and a.st_resposta = 1
and c.nu_ine = e.nu_ine
and e.co_unidade_saude = p.co_ator_papel
and p.nu_cnes like '%".$cnes."%'
group by
p.nu_cnes, l.no_cidadao, l.dt_cad_individual, e.no_equipe
order by
e.no_equipe, l.no_cidadao, l.dt_cad_individual

All this to return me a list of names of women who are pregnant in a system.

After listing all of them, I need to do everything again to see which ones had an appointment scheduled in a given month:

select
p.nu_cnes,
a.no_cidadao,
count(case when(extract(month from d.dt_ficha) = ".$mesAtual." and extract(year from d.dt_ficha) = ".$anoAtual." and b.co_cds_ficha_atend_individual = d.co_seq_cds_ficha_atend_indivdl) then a.no_cidadao else null end) as a2,
count(case when(extract(month from d.dt_ficha) = ".$mesAnterior." and extract(year from d.dt_ficha) = ".$anoAnterior." and b.co_cds_ficha_atend_individual = d.co_seq_cds_ficha_atend_indivdl) then a.no_cidadao else null end) as a1
from
tb_unidade_saude p,
tb_cds_cidadao_resposta e,
tb_cds_prof as g,
tb_equipe as f,
tb_cds_ficha_atend_individual d,
tb_cds_atend_individual b,
(SELECT *,
   ROW_NUMBER() OVER (PARTITION BY no_cidadao
   ORDER BY st_atualizacao desc) AS linha
   FROM tb_cds_cad_individual) a
WHERE LINHA = 1
AND a.co_seq_cds_cad_individual = e.co_cds_cad_individual
and g.co_seq_cds_prof = a.co_cds_prof_cadastrante
and e.co_pergunta = 19
and e.st_resposta = 1
and g.nu_ine = f.nu_ine
and f.co_unidade_saude = p.co_ator_papel
and a.nu_cns_cidadao = b.nu_cartao_sus
and ((extract(month from d.dt_ficha) = ".$mesAtual." and extract(year from d.dt_ficha) = ".$anoAtual.")
or (extract(month from d.dt_ficha) = ".$mesAnterior." and extract(year from d.dt_ficha) = ".$anoAnterior."))
and p.nu_cnes like '%".$cnes."%'
group by
p.nu_cnes, f.no_equipe, a.no_cidadao
order by
a.no_cidadao

After this, there are others who use the same list of pregnant women to get other information about them.

What I want is this: since I have the list of pregnant women in the first consultation, how to do the second consultation comparing to the first to save processing, like this:

select * from ... where no_cidadao in [lista da primeira consulta]

Answer 1

You can run a query on another query. For example, assuming you have these two queries, where the second is basically a copy of the first with further refinement.

Raw form

Transform:

select
    foo, bar
from
    mulheres
where
    gravida = 1
    and cadastro_ativo = 1
    and... -- 500 condicoes aqui

--------------------------------------

select
    foo, bar, ni
from
    mulheres
    inner join consultas on consultas.mulher = mulheres.id
where
    mulheres.gravida = 1
    and mulheres.cadastro_ativo = 1
    and... -- 500 condicoes aqui
    and consultas.data > getdate()

In:

select
    foo, bar, ni
from
    mulheres inner join consultas on consultas.mulher = mulheres.id
where
    consultas.data > getdate()
    and mulheres.id in (
        select
            foo, bar
        from
            mulheres
        where
            gravida = 1
            and cadastro_ativo = 1
            and... -- 500 condicoes aqui
    )

You will still have a code mountain, but it will be a smaller mountain than the one you have today. Disadvantage: complexity at best remains the same, but generally increases.

Fashionable

Follow the following steps:

• Turn the first query into a View.
• Select this view with the second query, adding only the conditions they don’t have in common.
• Encapsulate everything in a stored Procedure, to make it easier for the bank to optimize queries.
• Don’t forget to index the most frequently used fields for searches.
• If there is a programming language at a level above the application, maybe you can transfer some of the logic to the higher level.

Answer 2

You can store the result of these queries with the With:

with gravidas as (
    select
    l.no_cidadao,
    extract(year from l.dt_cad_individual) ano,
    extract(month from l.dt_cad_individual) mes,
    date_part('year',age(now(), l.dt_cad_individual)) diferenca
    from
    tb_unidade_saude p,
    tb_cds_cidadao_resposta a,
    tb_cds_prof as c,
    tb_pessoa_fisica as d,
    tb_equipe as e,
    (SELECT *,
       ROW_NUMBER() OVER (PARTITION BY no_cidadao
       ORDER BY st_atualizacao desc) AS linha
       FROM tb_cds_cad_individual) l
    WHERE LINHA = 1
    AND l.co_seq_cds_cad_individual = a.co_cds_cad_individual
    and c.nu_cns = d.nu_cns
    and c.co_seq_cds_prof = l.co_cds_prof_cadastrante
    and a.co_pergunta = 19
    and a.st_resposta = 1
    and c.nu_ine = e.nu_ine
    and e.co_unidade_saude = p.co_ator_papel
    and p.nu_cnes like '%".$cnes."%'
    group by
    p.nu_cnes, l.no_cidadao, l.dt_cad_individual, e.no_equipe
    order by
    e.no_equipe, l.no_cidadao, l.dt_cad_individual), 

atendidas as (

    select
p.nu_cnes,
a.no_cidadao,
count(case when(extract(month from d.dt_ficha) = ".$mesAtual." and extract(year from d.dt_ficha) = ".$anoAtual." and b.co_cds_ficha_atend_individual = d.co_seq_cds_ficha_atend_indivdl) then a.no_cidadao else null end) as a2,
count(case when(extract(month from d.dt_ficha) = ".$mesAnterior." and extract(year from d.dt_ficha) = ".$anoAnterior." and b.co_cds_ficha_atend_individual = d.co_seq_cds_ficha_atend_indivdl) then a.no_cidadao else null end) as a1
from
tb_unidade_saude p,
tb_cds_cidadao_resposta e,
tb_cds_prof as g,
tb_equipe as f,
tb_cds_ficha_atend_individual d,
tb_cds_atend_individual b,
(SELECT *,
   ROW_NUMBER() OVER (PARTITION BY no_cidadao
   ORDER BY st_atualizacao desc) AS linha
   FROM tb_cds_cad_individual) a
WHERE LINHA = 1
AND a.co_seq_cds_cad_individual = e.co_cds_cad_individual
and g.co_seq_cds_prof = a.co_cds_prof_cadastrante
and e.co_pergunta = 19
and e.st_resposta = 1
and g.nu_ine = f.nu_ine
and f.co_unidade_saude = p.co_ator_papel
and a.nu_cns_cidadao = b.nu_cartao_sus
and ((extract(month from d.dt_ficha) = ".$mesAtual." and extract(year from d.dt_ficha) = ".$anoAtual.")
or (extract(month from d.dt_ficha) = ".$mesAnterior." and extract(year from d.dt_ficha) = ".$anoAnterior."))
and p.nu_cnes like '%".$cnes."%'
group by
p.nu_cnes, f.no_equipe, a.no_cidadao
order by
a.no_cidadao)



select * from gravidas;

select * from atendidas;

The second query could already be simplified with the results of the first, but then I need to understand all your select before