matricula_por_localizacao.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: matricula 2018+
-- SimCAQ: tabela de projeção de matrículas --

CREATE TABLE matricula_por_localizacao AS (

WITH
    day_count AS
        (SELECT count(*) as dia_total, ano_censo, estado_id, municipio_id, serie_ano_id, localizacao_id,
            dependencia_adm_id
         FROM matricula
         WHERE dependencia_adm_id < 4 AND ano_censo >= 2018 AND (tipo_atendimento_turma <= 2 OR tipo <= 3) AND
            (turma_turno_id IN (1, 2, 99) OR turma_turno_id IS null)
         GROUP BY ano_censo, estado_id, municipio_id, serie_ano_id, localizacao_id, dependencia_adm_id
         ORDER BY ano_censo, estado_id, municipio_id, serie_ano_id, localizacao_id, dependencia_adm_id
        ),

    night_count AS
        (SELECT count(*) as noite_total, ano_censo, estado_id, municipio_id, serie_ano_id, localizacao_id,
            dependencia_adm_id
         FROM matricula
         WHERE dependencia_adm_id < 4 AND ano_censo >= 2018 AND turma_turno_id = 3 AND (tipo_atendimento_turma <= 2 OR tipo <= 3)
         GROUP BY ano_censo, estado_id, municipio_id, serie_ano_id, localizacao_id, dependencia_adm_id
         ORDER BY ano_censo, estado_id, municipio_id, serie_ano_id, localizacao_id, dependencia_adm_id
        )

SELECT matricula.ano_censo, matricula.estado_id, matricula.municipio_id,
    matricula.serie_ano_id, COALESCE(dia_total, 0) AS dia_total,
    COALESCE(noite_total, 0) AS noite_total, matricula.localizacao_id, matricula.dependencia_adm_id
FROM matricula
    LEFT JOIN day_count ON matricula.ano_censo=day_count.ano_censo AND matricula.estado_id=day_count.estado_id
        AND matricula.municipio_id=day_count.municipio_id AND matricula.serie_ano_id=day_count.serie_ano_id
        AND matricula.localizacao_id=day_count.localizacao_id
        AND matricula.dependencia_adm_id=day_count.dependencia_adm_id
    LEFT JOIN night_count ON matricula.ano_censo=night_count.ano_censo AND matricula.estado_id=night_count.estado_id
        AND matricula.municipio_id=night_count.municipio_id AND matricula.serie_ano_id=night_count.serie_ano_id
        AND matricula.localizacao_id=night_count.localizacao_id
        AND matricula.dependencia_adm_id=night_count.dependencia_adm_id
WHERE matricula.ano_censo >= 2018 AND matricula.serie_ano_id IS NOT NULL AND matricula.dependencia_adm_id < 4 AND (tipo_atendimento_turma <= 2 OR tipo <= 3)
GROUP BY  matricula.ano_censo, matricula.estado_id, matricula.municipio_id, matricula.serie_ano_id, dia_total,
    noite_total, matricula.localizacao_id, matricula.dependencia_adm_id
);

-- Create serie mod
INSERT INTO
    matricula_por_localizacao(ano_censo, estado_id, municipio_id, serie_ano_id, dia_total, noite_total,
    localizacao_id, dependencia_adm_id)
SELECT
    ano_censo, estado_id, municipio_id, LEFT(serie_ano_id, 1) as serie, sum(dia_total), sum(noite_total),
    localizacao_id, dependencia_adm_id
FROM matricula_por_localizacao
GROUP BY ano_censo, estado_id, municipio_id, serie, localizacao_id, dependencia_adm_id;