2.3 Processamento SQL

-- sumario do pcdt

create table db_sabeis.tf_pcdt as
select
  co_pcdt,
  'BR' as sg_uf,
  min(nu_competencia) as nu_competencia_min,
  max(nu_competencia) as nu_competencia_max,
  count(distinct co_ibge_estabelecimento) as qt_municipios_atendimento,
  count(distinct co_ibge_paciente) as qt_municipios_paciente,
  count(distinct co_ibge_gestao) as qt_secretarias,
  count(distinct co_cns_paciente) as qt_cns_pacientes,
  sum(case when nu_idade_paciente < 21 then 1 else 0 end) as qt_00a20,
  sum(case when nu_idade_paciente between 21 and 40 then 1 else 0 end) as qt_21a40,
  sum(case when nu_idade_paciente between 41 and 60 then 1 else 0 end) as qt_41a60,
  sum(case when nu_idade_paciente > 60 then 1 else 0 end) as qt_61a00,
  sum(case when sg_sexo_paciente = 'F' then 1 else 0 end) as qt_sexoF,
  sum(case when sg_sexo_paciente = 'M' then 1 else 0 end) as qt_sexoM,
  sum(st_obito_paciente) as qt_obito_paciente,
  count(distinct co_cid10_diagnostico_principal) as qt_cid,
  count(distinct co_sigtap_procedimento) as qt_sigtap,
  count(*) as qt_registros,
  sum(qt_aprovada) as qt_aprovada,
  sum(vl_aprovado_ipca) as vl_aprovado_ipca,
  sum(case when st_redesbr=1 then 1 else 0 end) as qt_redesbr1,
  sum(case when st_redesbr=2 then 1 else 0 end) as qt_redesbr2,
  sum(case when st_redesbr=3 then 1 else 0 end) as qt_redesbr3,
  sum(case when st_redesbr=4 then 1 else 0 end) as qt_redesbr4,
  sum(case when st_redesbr=5 then 1 else 0 end) as qt_redesbr5
from db_sabeis.tf_apac_medicamento
group by 1;

insert into db_sabeis.tf_pcdt
select
  co_pcdt,
  substr(co_ibge_paciente::text,1,2) as sg_uf,
  min(nu_competencia) as nu_competencia_min,
  max(nu_competencia) as nu_competencia_max,
  count(distinct co_ibge_estabelecimento) as qt_municipios_atendimento,
  count(distinct co_ibge_paciente) as qt_municipios_paciente,
  count(distinct co_ibge_gestao) as qt_secretarias,
  count(distinct co_cns_paciente) as qt_cns_pacientes,
  sum(case when nu_idade_paciente < 21 then 1 else 0 end) as qt_00a20,
  sum(case when nu_idade_paciente between 21 and 40 then 1 else 0 end) as qt_21a40,
  sum(case when nu_idade_paciente between 41 and 60 then 1 else 0 end) as qt_41a60,
  sum(case when nu_idade_paciente > 60 then 1 else 0 end) as qt_61a00,
  sum(case when sg_sexo_paciente = 'F' then 1 else 0 end) as qt_sexoF,
  sum(case when sg_sexo_paciente = 'M' then 1 else 0 end) as qt_sexoM,
  sum(st_obito_paciente) as qt_obito_paciente,
  count(distinct co_cid10_diagnostico_principal) as qt_cid,
  count(distinct co_sigtap_procedimento) as qt_sigtap,
  count(*) as qt_registros,
  sum(qt_aprovada) as qt_aprovada,
  sum(vl_aprovado_ipca) as vl_aprovado_ipca,
  sum(case when st_redesbr=1 then 1 else 0 end) as qt_redesbr1,
  sum(case when st_redesbr=2 then 1 else 0 end) as qt_redesbr2,
  sum(case when st_redesbr=3 then 1 else 0 end) as qt_redesbr3,
  sum(case when st_redesbr=4 then 1 else 0 end) as qt_redesbr4,
  sum(case when st_redesbr=5 then 1 else 0 end) as qt_redesbr5
from db_sabeis.tf_apac_medicamento
group by 1,2;

insert into db_sabeis.tf_pcdt
select
  '0',
  'BR' as sg_uf,
  min(nu_competencia) as nu_competencia_min,
  max(nu_competencia) as nu_competencia_max,
  count(distinct co_ibge_estabelecimento) as qt_municipios_atendimento,
  count(distinct co_ibge_paciente) as qt_municipios_paciente,
  count(distinct co_ibge_gestao) as qt_secretarias,
  count(distinct co_cns_paciente) as qt_cns_pacientes,
  sum(case when nu_idade_paciente < 21 then 1 else 0 end) as qt_00a20,
  sum(case when nu_idade_paciente between 21 and 40 then 1 else 0 end) as qt_21a40,
  sum(case when nu_idade_paciente between 41 and 60 then 1 else 0 end) as qt_41a60,
  sum(case when nu_idade_paciente > 60 then 1 else 0 end) as qt_61a00,
  sum(case when sg_sexo_paciente = 'F' then 1 else 0 end) as qt_sexoF,
  sum(case when sg_sexo_paciente = 'M' then 1 else 0 end) as qt_sexoM,
  sum(st_obito_paciente) as qt_obito_paciente,
  count(distinct co_cid10_diagnostico_principal) as qt_cid,
  count(distinct co_sigtap_procedimento) as qt_sigtap,
  count(*) as qt_registros,
  sum(qt_aprovada) as qt_aprovada,
  sum(vl_aprovado_ipca) as vl_aprovado_ipca,
  sum(case when st_redesbr=1 then 1 else 0 end) as qt_redesbr1,
  sum(case when st_redesbr=2 then 1 else 0 end) as qt_redesbr2,
  sum(case when st_redesbr=3 then 1 else 0 end) as qt_redesbr3,
  sum(case when st_redesbr=4 then 1 else 0 end) as qt_redesbr4,
  sum(case when st_redesbr=5 then 1 else 0 end) as qt_redesbr5
from db_sabeis.tf_apac_medicamento;


update db_sabeis.tf_pcdt A 
   set sg_uf=B.sg_uf
  from db_aux.tb_uf B
 where A.sg_uf=B.co_uf_ibge;

-- perfil de usuarios por pdcb
create table tmp.tm_paciente as 
       select 
              A.co_cns_paciente,
              A.co_cns_paciente,
              A.co_cns_paciente,
              A.co_cns_paciente,
              A.co_pcdt,
              B.sg_sexo_paciente,
              B.nu_ano_nascimento,
              case when sum(A.st_obito_paciente) > 0 then 1 else 0 end as st_obito_paciente,
              substring(min(A.nu_competencia_min)::text,1,4)::int as nu_ano_min
         from db_sabeis.tf_evento_medicamento A
         left join db_sabeis.tf_paciente B
           on A.co_cns_paciente=B.co_cns_paciente
        group by 1,2,3,4
        order by 1,2,3,4;

CREATE INDEX  tf_tm_paciente_cns
          ON tmp.tm_paciente (co_cns_paciente);

create table tmp.tm_paciente_pcdt as         
select co_pcdt, count(distinct co_cns_paciente) as qt_registros_pcdt from tmp.tm_paciente group by 1;
         
drop table if exists db_sabeis.tf_pcdt_paciente;
create table db_sabeis.tf_pcdt_paciente as
select
       A.co_pcdt,
       sum(case when sg_sexo_paciente ='M' then 1 else 0 end) as qt_paciente_m,
       sum(case when sg_sexo_paciente ='F' then 1 else 0 end) as qt_paciente_f,
       sum(case when sg_sexo_paciente ='M' and st_obito_paciente=1 then 1 else 0 end) as qt_obito_m,
       sum(case when sg_sexo_paciente ='F' and st_obito_paciente=1 then 1 else 0 end) as qt_obito_f,
       case 
         when nu_ano_min-nu_ano_nascimento between 0 and 4 then '00-04'
         when nu_ano_min-nu_ano_nascimento between 5 and 9 then '05-09'
         when nu_ano_min-nu_ano_nascimento between 10 and 14 then '10-14'
         when nu_ano_min-nu_ano_nascimento between 15 and 19 then '15-19'
         when nu_ano_min-nu_ano_nascimento between 20 and 24 then '20-24'
         when nu_ano_min-nu_ano_nascimento between 25 and 29 then '25-29'
         when nu_ano_min-nu_ano_nascimento between 30 and 34 then '30-34'
         when nu_ano_min-nu_ano_nascimento between 35 and 39 then '35-39'
         when nu_ano_min-nu_ano_nascimento between 40 and 44 then '40-44'
         when nu_ano_min-nu_ano_nascimento between 45 and 49 then '45-49'
         when nu_ano_min-nu_ano_nascimento between 50 and 54 then '50-54'
         when nu_ano_min-nu_ano_nascimento between 55 and 59 then '55-59'
         when nu_ano_min-nu_ano_nascimento between 60 and 64 then '60-64'
         when nu_ano_min-nu_ano_nascimento between 65 and 69 then '65-69'
         when nu_ano_min-nu_ano_nascimento between 70 and 74 then '70-74'
         when nu_ano_min-nu_ano_nascimento between 75 and 79 then '75-79'
         when nu_ano_min-nu_ano_nascimento between 80 and 84 then '80-84'
         when nu_ano_min-nu_ano_nascimento between 85 and 89 then '85-89'
         when nu_ano_min-nu_ano_nascimento >=90 then '90+' 
         else 'SemInfo' 
       end as sg_faixa_etaria,
       qt_registros_pcdt
  from tmp.tm_paciente A
  left join tmp.tm_paciente_pcdt B
    on A.co_pcdt=B.co_pcdt
 group by 1,6,qt_registros_pcdt
 order by 1,6;

CREATE INDEX  tf_pcdt_paciente_pcdt
          ON db_sabeis.tf_pcdt_paciente (co_pcdt);

-- estabelecimentos por pcdt
drop table if exists db_sabeis.tf_pcdt_estabelecimento;
create table db_sabeis.tf_pcdt_estabelecimento as
select 
       co_pcdt,
       co_cnes_estabelecimento,
       substr(co_ibge_estabelecimento::text,1,2)::int as co_uf_ibge_estabelecimento,
       count(*) as qt_dispensacao,
       count(distinct co_cns_paciente) as qt_cns_paciente,
       min(nu_competencia) as nu_competencia_min,
       max(nu_competencia) as nu_competencia_max
  from db_sabeis.tf_apac_medicamento
 where qt_aprovada > 0
   and co_cns_paciente > 0
 group by 1,2,3
 order by 1,3,2;   
         
CREATE INDEX  tf_pcdt_estabelecimento_pcdt
          ON db_sabeis.tf_pcdt_estabelecimento (co_pcdt);
    
drop table if exists db_sabeis.tf_pcdt_estabelecimento_uf;
create table db_sabeis.tf_pcdt_estabelecimento_uf as         
select co_pcdt, 
       substr(co_ibge_estabelecimento::text,1,2)::int as co_uf_ibge_estabelecimento,
       substr(nu_competencia::text,1,4)::int as nu_ano_competencia,
       count(distinct co_cnes_estabelecimento) as qt_cnes_estabelecimento
  from db_sabeis.tf_apac_medicamento         
 group by 1,2,3 ;

-- municipios de residencia por pcdt         
drop table if exists db_sabeis.tf_pcdt_municipio_residencia;
create table db_sabeis.tf_pcdt_municipio_residencia as
select 
       co_pcdt,
       co_ibge_paciente,
       B.sg_uf,
       B.nu_populacao_2010,
       count(*) as qt_dispensacao,
       count(distinct co_cns_paciente) as qt_cns_paciente,
       min(nu_competencia) as nu_competencia_min,
       max(nu_competencia) as nu_competencia_max
  from db_sabeis.tf_apac_medicamento A
  left join db_sabeis.td_municipio B
    on A.co_ibge_paciente=B.co_municipio_ibge
 where qt_aprovada > 0
   and co_cns_paciente > 0
 group by 1,2,3,4
 order by 1,3,2;   
         
CREATE INDEX  tf_pcdt_municipio_residencia_pcdt
          ON db_sabeis.tf_pcdt_municipio_residencia (co_pcdt);

update db_sabeis.tf_pcdt_municipio_residencia set co_pcdt = 0 where co_pcdt is null;         
         
alter table db_sabeis.tf_pcdt_municipio_residencia
  add sg_regiao_redes varchar(15) default null,
  add sg_regiao_pais varchar(2) default null;

update db_sabeis.tf_pcdt_municipio_residencia A
   set sg_uf = B.sg_uf,
       nu_populacao_2010 = B.nu_populacao_2010,
       sg_regiao_pais = 
       case 
          when st_regiao_pais = '1' then 'N'
          when st_regiao_pais = '2' then 'NE'
          when st_regiao_pais = '3' then 'SE'
          when st_regiao_pais = '4' then 'S'
          when st_regiao_pais = '5' then 'CO'
        else 'ni'  
        end
  from db_sabeis.tb_municipio_ibge B
 where A.co_ibge_paciente=B.co_municipio_ibge; 

update db_sabeis.tf_pcdt_municipio_residencia A
   set sg_uf = B.sg_uf,
       sg_regiao_pais = B.sg_regiao_pais


update db_sabeis.tf_pcdt_municipio_residencia A
   set sg_uf = B.sg_uf,
       nu_populacao_2010 = qt_populacao
  from db_aux.tf_populacao_censo B 
 where co_ibge_paciente = B.co_municipio_ibge; 

update db_sabeis.tf_pcdt_municipio_residencia A
   set sg_regiao_redes = 
       case 
          when B.st_grupo_regiao = '1' then 'baixíssimo'
          when B.st_grupo_regiao = '2' then 'baixo'
          when B.st_grupo_regiao = '3' then 'médio'
          when B.st_grupo_regiao = '4' then 'alto'
          when B.st_grupo_regiao = '5' then 'altíssimo'
        else 'ni'  
        end
  from db_sabeis.td_redesbr B
 where A.co_ibge_paciente=B.co_municipio_ibge;

alter table db_sabeis.tf_pcdt_municipio_residencia add qt_meses smallint default null;

update db_sabeis.tf_pcdt_municipio_residencia A
   set qt_meses = B.qt_meses
  from (
select 
co_pcdt, co_ibge_paciente,
round(extract(year from age(to_date(nu_competencia_max::text, 'YYYYMM'),to_date(nu_competencia_min::text, 'YYYYMM'))) * 12 +
extract(month from age(to_date(nu_competencia_max::text, 'YYYYMM'),to_date(nu_competencia_min::text, 'YYYYMM')))+1)::int as qt_meses
from db_sabeis.tf_pcdt_municipio_residencia) B
where A.co_pcdt=B.co_pcdt and A.co_ibge_paciente = B.co_ibge_paciente;


alter table db_sabeis.tf_pcdt_municipio_residencia add qt_municipio_uf smallint default null;

update db_sabeis.tf_pcdt_municipio_residencia A
   set qt_municipio_uf = B.qt_municipio_uf
  from ( 
select sg_uf, count(*) as qt_municipio_uf 
  from (select distinct co_municipio_ibge, sg_uf from db_sabeis.tb_municipio_ibge) X
 group by 1) B
 where A.sg_uf=B.sg_uf
 ;


select 
      co_pcdt,
      sg_uf,
--      count(distinct co_ibge_paciente)::float/qt_municipio_uf as qt_ibge_paciente,
      sum(qt_cns_paciente)::float/qt_meses/sum(nu_populacao_2010) as qt_cns_mes_hab
from db_sabeis.tf_pcdt_municipio_residencia
group by 1,2, qt_meses -- , qt_municipio_uf;

create table db_sabeis.tf_pcdt_municipio_residencia_cobertura AS
select 
      co_pcdt,
      sg_uf,
      ROUND(case when sg_uf='DF' then 100 else count(distinct co_ibge_paciente)::float/max(qt_municipio_uf)*100 end)::INT as qt_cobertura_municipio_uf,
      sum(qt_cns_paciente)::float/round(extract(year from age(to_date(max(nu_competencia_max)::text, 'YYYYMM'),to_date(min(nu_competencia_min)::text, 'YYYYMM'))) * 12 +
extract(month from age(to_date(max(nu_competencia_max)::text, 'YYYYMM'),to_date(min(nu_competencia_min)::text, 'YYYYMM')))+1)::int/max(nu_populacao_2010)*1000000 as qt_cns_mes_habitante
from db_sabeis.tf_pcdt_municipio_residencia
group by 1,2;

-- produtos por pcdt         
drop table if exists db_sabeis.tf_pcdt_produto_ano;

create table db_sabeis.tf_pcdt_produto_ano as
select X.*,
       C.sg_forma_organizacao,
       DENSE_RANK() OVER(PARTITION by co_pcdt, nu_ano_competencia, sg_forma_organizacao ORDER BY qt_cns_paciente desc) as co_seq_qt_cns_forma_organizacao
from 
(
select A.*,
       DENSE_RANK() OVER(PARTITION by A.co_pcdt, A.nu_ano_competencia ORDER BY A.qt_cns_paciente desc) as co_seq_qt_cns,
       B.qt_cns_paciente    as qt_cns_paciente_total,
       B.qt_dispensacao as qt_dispensacao_total
from
(
select 
       coalesce(A.co_pcdt,0) as co_pcdt,
       B.sg_procedimento,
       substring(nu_competencia::text,1,4)::int as nu_ano_competencia,
       count(*) as qt_dispensacao,
       count(distinct co_cns_paciente) as qt_cns_paciente
  from db_sabeis.tf_apac_medicamento A
  left join db_sabeis.td_sigtap_medicamento_abrev B
    on A.co_sigtap_procedimento=B.nu_sigtap
 where qt_aprovada > 0
   and co_cns_paciente > 0
 group by 1,2,3
 order by 1,2,3
 ) A
  left join
  (select 
       coalesce(co_pcdt,0) as co_pcdt,
       substring(nu_competencia::text,1,4)::int as nu_ano_competencia,
       count(*) as qt_dispensacao,
       count(distinct co_cns_paciente) as qt_cns_paciente
  from db_sabeis.tf_apac_medicamento
 where qt_aprovada > 0
  group by 1,2
) B
  on A.co_pcdt=B.co_pcdt
 and A.nu_ano_competencia=B.nu_ano_competencia
 ) X
left join db_sabeis.td_sigtap_medicamento_abrev_cmpmax C
  on X.sg_procedimento=C.sg_procedimento;


select * from db_sabeis.tf_pcdt_produto_ano where co_pcdt=10 order by nu_ano_competencia, co_seq_qt_cns; 
 
drop table if exists db_sabeis.tf_pcdt_produto_ano_outros;
create table db_sabeis.tf_pcdt_produto_ano_outros as
select A.*,
       B.qt_cns_paciente_total,
       B.qt_dispensacao_total,
       round(A.qt_cns_paciente::float/B.qt_cns_paciente_total*100)::int as qt_cns_paciente_percentual,
       round(A.qt_dispensacao::float/B.qt_dispensacao_total*100)::int as qt_dispensacao_percentual
from       
(
select 
       A.co_pcdt,
       A.nu_ano_competencia,
       case when co_seq_cns_paciente <= 7 then A.sg_procedimento else 'outros' end as sg_procedimento,
       'total' as sg_agregacao,
       STRING_AGG(distinct sg_procedimento::text, ', ')  as sg_procedimento_outros,
       sum(qt_cns_paciente) as qt_cns_paciente, 
       sum(qt_dispensacao)  as qt_dispensacao
 from  db_sabeis.tf_pcdt_produto_ano A
group by 1,2,3
) A
left join
(select 
       co_pcdt,
       nu_ano_competencia,
       sum(qt_cns_paciente) as qt_cns_paciente_total,
       sum(qt_dispensacao)  as qt_dispensacao_total
  from db_sabeis.tf_pcdt_produto_ano
 group by 1,2
) B
on A.co_pcdt=B.co_pcdt and A.nu_ano_competencia=B.nu_ano_competencia


update db_sabeis.tf_pcdt_produto_ano_outros A
   set qt_cns_paciente_percentual=B.qt_cns_paciente_percentual,
       qt_dispensacao_percentual=B.qt_dispensacao_percentual
  from 
(  
select 
  co_pcdt, 
  nu_ano_competencia, 
  100-sum(qt_cns_paciente_percentual) as qt_cns_paciente_percentual,
  100-sum(qt_dispensacao_percentual) as qt_dispensacao_percentual
 from db_sabeis.tf_pcdt_produto_ano_outros
where sg_procedimento <> 'outros'
group by 1,2
) B
where A.co_pcdt=B.co_pcdt 
  and A.nu_ano_competencia=B.nu_ano_competencia
  and A.sg_procedimento='outros';

alter table db_sabeis.tf_pcdt_produto_ano_outros add  sg_forma_organizacao varchar(10) default 'total';

update db_sabeis.tf_pcdt_produto_ano_outros A
   set sg_forma_organizacao=B.sg_forma_organizacao
  from db_sabeis.td_sigtap_medicamento_abrev B
 where A.sg_procedimento=B.sg_procedimento and B.nu_competencia_max='201905';

-- doencas por pcdt         
drop table if exists db_sabeis.tf_pcdt_doenca_trimestre;
create table db_sabeis.tf_pcdt_doenca_trimestre as
select 
      *,
      DENSE_RANK() OVER(PARTITION by co_pcdt, co_cid10_diagnostico_principal ORDER BY co_pcdt, nu_trimestre, co_cid10_diagnostico_principal) as co_seq_pcdt_cid10_trimestre
from (      
select 
       co_pcdt,
       co_cid10_diagnostico_principal,
       extract(year from to_date(nu_competencia_min::text, 'YYYYMM'))::text || '0' || extract(quarter from to_date(nu_competencia_min::text, 'YYYYMM'))::text as nu_trimestre,
       count(distinct co_cns_paciente) as qt_cns_paciente
  from
       (
       select 
              co_pcdt,
              co_cns_paciente,
              co_cid10_diagnostico_principal,
              min(nu_competencia_min) as nu_competencia_min
         from db_sabeis.tm_evento_cid 
        where co_pcdt > 0
          and co_cns_paciente > 0
        group by 1,2,3
        ) X
  group by 1,2,3
  order by 1,2,3
 ) X;

-- sumario sigtap e cid
create table db_sabeis.tf_apac_medicamento_sumario as
select
       co_pcdt,
       co_cid10_diagnostico_principal,
       co_sigtap_procedimento,
       min(nu_competencia) as nu_competencia_min,
       max(nu_competencia) as nu_competencia_max,
       sum(qt_aprovada) as qt_aprovada,
       count(distinct nu_autorizacao) as qt_autorizacao,
       count(distinct co_cns_paciente) as qt_cns_paciente,
       count(distinct co_ibge_paciente) as qt_municipios_paciente,
       count(distinct co_cnes_estabelecimento) as qt_cnes
  from db_sabeis.tf_apac_medicamento
 where qt_aprovada > 0 
   and co_cns_paciente > 0
--   and co_sigtap_procedimento > 0
--   and co_cid10_diagnostico_principal is not null
 group by 1,2,3;

alter table  db_sabeis.tf_apac_medicamento_sumario 
  add sg_procedimento varchar(100) default null,
  add no_procedimento varchar(255) default null,
  add no_cid10_diagnostico_principal varchar(255) default null;

update db_sabeis.tf_apac_medicamento_sumario A
   set sg_procedimento = B.sg_procedimento,
       no_procedimento = B.no_procedimento
  from db_sabeis.td_sigtap_medicamento_abrev B
 where A.co_sigtap_procedimento = B.nu_sigtap;
 
update db_sabeis.tf_apac_medicamento_sumario A
   set no_cid10_diagnostico_principal = B.no_cid
  from db_sabeis.td_cid10 B
 where A.co_cid10_diagnostico_principal = B.co_cid; 

select * from db_sabeis.tf_apac_medicamento_sumario;

drop table if exists db_sabeis.tf_pcdt_cns;
create table db_sabeis.tf_pcdt_cns as
select distinct Z.sg_uf, coalesce(no_pcdt,'outros') as no_pcdt, qt_cns_mes::float/qt_populacao as qt_cns_mes
from (
select
      sg_uf,
      case when co_seq > 7 then 0 else co_pcdt end as co_pcdt,
      round(sum(qt_cns_mes))::int as qt_cns_mes
  from
  (
select 
      *,
      DENSE_RANK() OVER(PARTITION by sg_uf ORDER BY qt_cns_mes desc) as co_seq
  from
(
select 
co_pcdt,
sg_uf,
qt_cns_pacientes::float/round(extract(year from age(to_date(nu_competencia_max::text, 'YYYYMM'),to_date(nu_competencia_min::text, 'YYYYMM'))) * 12 +
extract(month from age(to_date(nu_competencia_max::text, 'YYYYMM'),to_date(nu_competencia_min::text, 'YYYYMM')))+1)::int as qt_cns_mes
from db_sabeis.tf_pcdt
where sg_uf <> 'BR'
) X
where co_pcdt is not null
) Y
group by 1,2
order by 1,3 desc) Z
left join db_sabeis.td_pcdt_cid C
  on Z.co_pcdt = C.co_seq_pcdt
left join db_aux.td_populacao_uf_2010 D
  on Z.sg_uf = D.sg_uf;

drop table if exists db_sabeis.tf_pcdt_medicamento_trimestre;
create table db_sabeis.tf_pcdt_medicamento_trimestre as
select 
       co_pcdt,
       extract(
          year from to_date(nu_competencia::text, 'YYYYMM'))::text ||
          '0' || 
          extract(quarter from to_date(nu_competencia::text, 'YYYYMM'))::text as nu_trimestre, 
       sg_procedimento, 
       sum(case when sg_sexo_paciente = 'F' then 1 else 0 end) as qt_sexo_f,
       sum(case when nu_idade_paciente > 60 then 1 else 0 end) as qt_idade_paciente60,
       sum(case when st_redesbr > 2 then 1 else 0 end) as qt_st_redesbr3,
       count(distinct co_ibge_gestao) as qt_ibge_gestao,
       sum(qt_aprovada) as qt_aprovada,
       count(*) as qt_dispensacao,
       count(distinct co_cns_paciente) as qt_cns_paciente
  from db_sabeis.tf_apac_medicamento A
  left join db_sabeis.tm_abrev B
    on A.co_sigtap_procedimento=B.nu_sigtap
 where A.qt_aprovada > 0
   and A.co_cns_paciente > 0    
 group by 1,2,3; 

drop table if exists db_sabeis.tf_medicamento_trimestre;
create table db_sabeis.tf_medicamento_trimestre as
select 
       extract(
          year from to_date(nu_competencia::text, 'YYYYMM'))::text ||
          '0' || 
          extract(quarter from to_date(nu_competencia::text, 'YYYYMM'))::text as nu_trimestre, 
       sg_procedimento, 
       sum(case when sg_sexo_paciente = 'F' then 1 else 0 end) as qt_sexo_f,
       sum(case when nu_idade_paciente > 60 then 1 else 0 end) as qt_idade_paciente60,
       sum(case when st_redesbr > 2 then 1 else 0 end) as qt_st_redesbr3,
       count(distinct co_ibge_gestao) as qt_ibge_gestao,
       sum(qt_aprovada) as qt_aprovada,
       count(*) as qt_dispensacao,
       count(distinct co_cns_paciente) as qt_cns_paciente
  from db_sabeis.tf_apac_medicamento A
  left join db_sabeis.tm_abrev B
    on A.co_sigtap_procedimento=B.nu_sigtap
 where A.qt_aprovada > 0
   and A.co_cns_paciente > 0    
 group by 1,2,3; 


create table tmp.tf_apac_medicamento_sumario as
select * from db_sabeis.tf_apac_medicamento_sumario
 where co_sigtap_procedimento > 0
   and co_pcdt > 0
   and qt_cns_paciente > 0
   and no_cid10_diagnostico_principal is not null
 order by 1,2,3 ; 

drop table if exists db_sabeis.td_pcdt_abrev;
create table db_sabeis.td_pcdt_abrev as 
select distinct
       A.co_pcdt,
       A.sg_procedimento,
       B.no_procedimento
  from db_sabeis.tf_brasil_cid10_sigtap A
  left join db_sabeis.td_procedimento_abrev B
    on A.sg_procedimento=B.sg_procedimento
 where qt_cns_pacientes > 1000 and co_pcdt >= 0  
 order by 1,2;

-- troca de medicamentos
drop table db_sabeis.tf_troca;
create table db_sabeis.tf_troca as
select 
      co_pcdt,
      sg_procedimentoA,
      case when co_seq > 10 then 'outros' else sg_procedimentoB end as sg_procedimentoB,
      sum(qt_cns_paciente) as qt_cns_paciente,
      STRING_AGG(distinct sg_procedimentoB::text, ', ') as sg_procedimentoB2
from 
(
select *,
       DENSE_RANK() OVER(PARTITION by co_pcdt ORDER BY qt_cns_paciente desc) as co_seq
  from
 (
select co_pcdt,
       sg_procedimentoA,
       sg_procedimentoB,
       sum(qt_cns_paciente) as qt_cns_paciente
 from
      (
select A.co_pcdt,
       A.sg_procedimentoA,
       B.sg_procedimentoB,
       count(distinct A.co_cns_paciente) as qt_cns_paciente
  from     
(
select co_pcdt,
       co_cns_paciente,
       sg_procedimento as sg_procedimentoA
  from db_sabeis.tf_evento_medicamento
 where co_seq_evento=1
 ) A
  left join
(
select co_pcdt,
       sg_procedimento as sg_procedimentoB,
       co_cns_paciente
  from db_sabeis.tf_evento_medicamento
 where co_seq_evento=2
 ) B  
 on A.co_cns_paciente=B.co_cns_paciente
 where sg_procedimentoB is not null 
 group by 1,2,3
union
select A.co_pcdt,
       A.sg_procedimentoA,
       B.sg_procedimentoB,
       count(distinct A.co_cns_paciente) as qt_cns_paciente
  from     
(
select co_pcdt,
       co_cns_paciente,
       sg_procedimento as sg_procedimentoA
  from db_sabeis.tf_evento_medicamento
 where co_seq_evento=2
 ) A
  left join
(
select co_pcdt,
       sg_procedimento as sg_procedimentoB,
       co_cns_paciente
  from db_sabeis.tf_evento_medicamento
 where co_seq_evento=3
 ) B  
 on A.co_cns_paciente=B.co_cns_paciente
 where sg_procedimentoB is not null 
 group by 1,2,3
union
select A.co_pcdt,
       A.sg_procedimentoA,
       B.sg_procedimentoB,
       count(distinct A.co_cns_paciente) as qt_cns_paciente
  from     
(
select co_pcdt,
       co_cns_paciente,
       sg_procedimento as sg_procedimentoA
  from db_sabeis.tf_evento_medicamento
 where co_seq_evento=3
 ) A
  left join
(
select co_pcdt,
       sg_procedimento as sg_procedimentoB,
       co_cns_paciente
  from db_sabeis.tf_evento_medicamento
 where co_seq_evento=4
 ) B  
 on A.co_cns_paciente=B.co_cns_paciente
 where sg_procedimentoB is not null 
 group by 1,2,3
     ) X
 group by 1,2,3
 order by 1,4 desc
) Y 
) Z
where co_seq <=10
group by 1,2,3

create table db_sabeis.td_sigtap_medicamento_abrev_cmpmax as
select A.* from db_sabeis.td_sigtap_medicamento_abrev A,
(
select sg_procedimento, 
       max(nu_competencia_max) as nu_competencia_max 
  from db_sabeis.td_sigtap_medicamento_abrev
 group by 1
) B
where A.sg_procedimento=B.sg_procedimento
  and A.nu_competencia_max=B.nu_competencia_max
  order by 1,2;

-- pareto de pacientes e medicamentos
drop table if exists 
  db_sabeis.tf_procedimento_paciente_tempo, 
  db_sabeis.tm_procedimento_paciente_tempo;

create table db_sabeis.tm_procedimento_paciente_tempo as
select *,
       DENSE_RANK() OVER(PARTITION by co_pcdt, nu_ano_competencia ORDER BY qt_paciente_tempo desc) as co_seq_pcdt_ano
from (       
select co_pcdt,
       sg_procedimento,
       nu_ano_competencia,
       sum(qt_registros::float/12) as qt_paciente_tempo,
       avg(qt_registros) as qt_registros_avg,
       stddev(qt_registros) as qt_registros_std
  from (
select  
       A.co_pcdt,
       B.sg_procedimento,
       substring(A.nu_competencia::text,1,4)::int as nu_ano_competencia,
       co_cns_paciente,
       count(*) as qt_registros
  from db_sabeis.tf_apac_medicamento A
  left join db_sabeis.td_sigtap_medicamento_abrev B
    on A.co_sigtap_procedimento=B.nu_sigtap
 where qt_aprovada > 0
   and co_cns_paciente > 0  
 group by 1,2,3,4
) X
group by 1,2,3
) X;
 
drop table if exists db_sabeis.tf_procedimento_paciente_tempo;
create table db_sabeis.tf_procedimento_paciente_tempo as
select
       *,
       case 
         when qt_acum > 0.8 then 'A'
         when qt_acum between 0.5 and 0.8 then 'B'
       else 'C' end as sg_abc
from
(
select 
  A.*, 
  sum(A.qt_paciente_tempo/qt_paciente_tempo_total) OVER (PARTITION BY A.co_pcdt, A.nu_ano_competencia ORDER BY A.qt_paciente_tempo) as qt_acum
 from db_sabeis.tm_procedimento_paciente_tempo A
 left join
  (select co_pcdt, nu_ano_competencia, sum(qt_paciente_tempo) as qt_paciente_tempo_total
    from db_sabeis.tm_procedimento_paciente_tempo group by 1,2) B
   on A.co_pcdt=B.co_pcdt and A.nu_ano_competencia=B.nu_ano_competencia
) x
order by 1,3,4 desc;

select * from db_sabeis.tf_procedimento_paciente_tempo where nu_ano_competencia=2018;


drop table if exists db_sabeis.tf_procedimento_paciente_tempo;

select sg_procedimento,
        count(distinct A.co_cns_paciente) as qt_cns_paciente
   from db_sabeis.tf_evento_medicamento A,
  (
 select co_cns_paciente
   from db_sabeis.tf_evento_medicamento
  where sg_procedimento like '%INFL%' 
    and nu_competencia_min >= 201201
    and nu_competencia_max <= 201212
   ) B
where A.co_cns_paciente = B.co_cns_paciente
  and A.nu_competencia_min >= 201201
  and A.nu_competencia_max <= 201312
  and sg_procedimento not like '%INFL%'
group by 1
order by 2 desc

create table db_sabeis.tm_etl_am as
select * from db_sabeis.tm_etl where sg_sistema='SIA' and nu_competencia::int > 200800 and sg_tabela in ('PA','AM') order by sg_uf, nu_competencia::int;

select * from db_sabeis.tm_etl;

drop table if exists db_sabeis.tm_pcdt_qt;
create table db_sabeis.tm_pcdt_qt as
select A.*, B.no_pcdt
from
(
select 
  co_pcdt, 
  count(*) as qt_registros, 
  count(distinct co_cns_paciente) as qt_cns 
 from db_sabeis.tf_apac_medicamento
 where co_pcdt in (select co_pcdt from db_sabeis.td_pcdt_abrev)
group by co_pcdt 
having count(*) > 0 
order by 2 desc 
) A
left join (select distinct co_seq_pcdt, no_pcdt from db_sabeis.td_pcdt_cid) B
on A.co_pcdt=B.co_seq_pcdt;

alter table db_sabeis.tf_apac_medicamento_sumario add st_td_pcdt_abrev smallint default 0;

update db_sabeis.tf_apac_medicamento_sumario A
   set st_td_pcdt_abrev = 1
  from db_sabeis.td_pcdt_abrev B
 where A.co_pcdt=B.co_pcdt and A.sg_procedimento=B.sg_procedimento;