idm.sql

/*
Copyright (C) 2016 Centro de Computacao Cientifica e Software Livre
Departamento de Informatica - Universidade Federal do Parana - C3SL/UFPR

This file is part of HOTMapper.

HOTMapper is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.

HOTMapper is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with HOTMapper.  If not, see <https://www.gnu.org/licenses/>.
*/

-- depends: matrícula
-- SimCAQ: índice de distribuição de matrículas--

CREATE TABLE indice_distribuicao_matriculas AS (

    WITH div_estado AS
        (SELECT count(serie_ano_id) AS total_estado, serie_ano_id, estado_id, ano_censo, localizacao_id,
            dependencia_adm_id
        FROM matricula
        WHERE ano_censo>=2017 AND tipo <= 3 AND dependencia_adm_id <=3
        GROUP BY serie_ano_id, estado_id, ano_censo, localizacao_id, dependencia_adm_id),

    div_estado_mod AS
        (SELECT count(serie_ano_id) AS total_estado_mod, cast(LEFT(serie_ano_id, 1) as integer) as serie_mod, estado_id,
            ano_censo, localizacao_id, dependencia_adm_id
        FROM matricula
        WHERE ano_censo >= 2017 AND tipo <= 3 AND dependencia_adm_id <= 3
        GROUP BY serie_mod, estado_id, ano_censo, localizacao_id, dependencia_adm_id),
    
    div_brasil AS
        (SELECT sum(total_estado) AS total_brasil, serie_ano_id, ano_censo, localizacao_id, dependencia_adm_id
        FROM div_estado
        GROUP BY serie_ano_id, ano_censo, localizacao_id, dependencia_adm_id),

    div_brasil_mod AS
        (SELECT sum(total_brasil) AS total_brasil_mod, cast(LEFT(serie_ano_id, 1) as integer) as serie_mod, ano_censo,
            localizacao_id, dependencia_adm_id
        FROM div_brasil
        GROUP BY serie_mod, ano_censo, localizacao_id, dependencia_adm_id),
    
    nivel_sim1 AS
        (SELECT matricula.ano_censo, matricula.estado_id, municipio_id, matricula.serie_ano_id, total_brasil AS total_etapa,
            cast(count(matricula.serie_ano_id) AS double)/total_etapa AS indice, 1 AS nivel_simulacao,
            matricula.localizacao_id, matricula.dependencia_adm_id
        FROM matricula JOIN div_brasil ON matricula.serie_ano_id=div_brasil.serie_ano_id
                                      AND matricula.ano_censo=div_brasil.ano_censo
                                      AND matricula.localizacao_id=div_brasil.localizacao_id
                                      AND matricula.dependencia_adm_id=div_brasil.dependencia_adm_id
        WHERE matricula.ano_censo>=2017 AND tipo <= 3 AND dependencia_adm_priv <=3
        GROUP BY matricula.ano_censo, matricula.estado_id, municipio_id, matricula.serie_ano_id, total_etapa,
            matricula.localizacao_id, matricula.dependencia_adm_id),
    
    nivel_sim2 AS
        (SELECT matricula.ano_censo, matricula.estado_id, municipio_id, matricula.serie_ano_id, total_estado AS total_etapa,
            cast(count(matricula.serie_ano_id) AS double)/total_etapa AS indice, 2 AS nivel_simulacao,
            matricula.localizacao_id, matricula.dependencia_adm_id
        FROM matricula JOIN div_estado ON matricula.serie_ano_id=div_estado.serie_ano_id
                                       AND matricula.estado_id=div_estado.estado_id
                                       AND matricula.ano_censo=div_estado.ano_censo
                                       AND matricula.localizacao_id=div_estado.localizacao_id
                                       AND matricula.dependencia_adm_id=div_estado.dependencia_adm_id
        WHERE matricula.ano_censo>=2017 AND tipo <= 3 AND dependencia_adm_priv <=3
        GROUP BY matricula.ano_censo, matricula.estado_id, municipio_id, matricula.serie_ano_id, total_etapa,
            matricula.localizacao_id, matricula.dependencia_adm_id),

    nivel_sim1_mod AS
        (SELECT matricula.ano_censo, matricula.estado_id, municipio_id, serie_mod as serie_ano_id,
            total_brasil_mod AS total_etapa, cast(count(matricula.serie_ano_id) AS double)/total_etapa AS indice,
            1 AS nivel_simulacao, matricula.localizacao_id, matricula.dependencia_adm_id
        FROM matricula JOIN div_brasil_mod ON LEFT(matricula.serie_ano_id, 1)=div_brasil_mod.serie_mod
                                           AND matricula.ano_censo=div_brasil_mod.ano_censo
                                           AND matricula.localizacao_id=div_brasil_mod.localizacao_id
                                           AND matricula.dependencia_adm_id=div_brasil_mod.dependencia_adm_id
        WHERE matricula.ano_censo>=2017 AND tipo <= 3 AND dependencia_adm_priv <=3
        GROUP BY matricula.ano_censo, matricula.estado_id, municipio_id, serie_mod, total_etapa,
            matricula.localizacao_id, matricula.dependencia_adm_id),

    nivel_sim2_mod AS
        (SELECT matricula.ano_censo, matricula.estado_id, municipio_id, serie_mod as serie_ano_id,
            total_estado_mod AS total_etapa, cast(count(matricula.serie_ano_id) AS double)/total_etapa AS indice,
            2 AS nivel_simulacao, matricula.localizacao_id, matricula.dependencia_adm_id
        FROM matricula JOIN div_estado_mod ON LEFT(matricula.serie_ano_id, 1)=div_estado_mod.serie_mod
                                           AND matricula.estado_id=div_estado_mod.estado_id
                                           AND matricula.ano_censo=div_estado_mod.ano_censo
                                           AND matricula.localizacao_id=div_estado_mod.localizacao_id
                                           AND matricula.dependencia_adm_id=div_estado_mod.dependencia_adm_id
        WHERE matricula.ano_censo>=2017 AND tipo <= 3 AND dependencia_adm_priv <=3
        GROUP BY matricula.ano_censo, matricula.estado_id, municipio_id, serie_mod, total_etapa,
            matricula.localizacao_id, matricula.dependencia_adm_id)

SELECT * FROM nivel_sim1 
UNION ALL 
SELECT * FROM nivel_sim2
UNION ALL
SELECT * FROM nivel_sim1_mod
UNION ALL
SELECT * FROM nivel_sim2_mod
);