docente_por_formacao.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: docente, turma
-- SimCAQ: tabela de docentes por formação --

CREATE TABLE docente_por_formacao AS (

WITH
    docente_total AS
        (SELECT docente.id_docente AS "id_docente", docente.ano_censo AS ano_censo, estado.id AS estado_id,
            municipio.id AS municipio_id, docente.dependencia_adm_id as dependencia_adm_id, 0 as tipo_formacao
        FROM docente
            INNER JOIN turma ON (docente.turma_id=turma.id AND docente.ano_censo=turma.ano_censo)
            INNER JOIN estado ON (docente.escola_estado_id=estado.id)
            INNER JOIN municipio ON (docente.escola_municipio_id=municipio.id)
        WHERE (docente.tipo_docente = 1 OR docente.tipo_docente = 5) AND 
            ((docente.tipo_turma_id <= 3 AND docente.tipo_turma_atendimento_id IS NULL) OR 
                (docente.tipo_turma_atendimento_id <= 2 AND docente.tipo_turma_id IS NULL))
            AND (docente.ano_censo >= 2018) AND (docente.nivel_tipo_formacao IN (3, 4, 5, 6, 7, 8, 9, 10, 11, 12))
        GROUP BY docente.ano_censo, estado.nome, estado.id, municipio.nome, municipio.id, docente.dependencia_adm_id,
            docente.id_docente
        ORDER BY docente.ano_censo ASC, estado.nome ASC, estado.id ASC, municipio.nome ASC, municipio.id ASC
        ),
    docente_medio AS
        (SELECT docente.id_docente AS "id_docente", docente.ano_censo AS ano_censo, estado.id AS estado_id,
            municipio.id AS municipio_id, docente.dependencia_adm_id as dependencia_adm_id, 2 as tipo_formacao
        FROM docente
            INNER JOIN turma ON (docente.turma_id=turma.id AND docente.ano_censo=turma.ano_censo)
            INNER JOIN estado ON (docente.escola_estado_id=estado.id)
            INNER JOIN municipio ON (docente.escola_municipio_id=municipio.id)
        WHERE (docente.tipo_docente = 1 OR docente.tipo_docente = 5) AND 
            ((docente.tipo_turma_id <= 3 AND docente.tipo_turma_atendimento_id IS NULL) OR 
                (docente.tipo_turma_atendimento_id <= 2 AND docente.tipo_turma_id IS NULL))
            AND (docente.ano_censo >= 2018) AND (docente.nivel_tipo_formacao IN (3, 4))
        GROUP BY docente.ano_censo, estado.nome, estado.id, municipio.nome, municipio.id, docente.dependencia_adm_id,
            docente.id_docente
        ORDER BY docente.ano_censo ASC, estado.nome ASC, estado.id ASC, municipio.nome ASC, municipio.id ASC
        ),
    docente_superior AS
        (SELECT docente.id_docente AS "id_docente", docente.ano_censo AS ano_censo, estado.id AS estado_id,
            municipio.id AS municipio_id, docente.dependencia_adm_id as dependencia_adm_id, 4 as tipo_formacao
        FROM docente
            INNER JOIN turma ON (docente.turma_id=turma.id AND docente.ano_censo=turma.ano_censo)
            INNER JOIN estado ON (docente.escola_estado_id=estado.id)
            INNER JOIN municipio ON (docente.escola_municipio_id=municipio.id)
        WHERE (docente.tipo_docente = 1 OR docente.tipo_docente = 5)  AND 
            ((docente.tipo_turma_id <= 3 AND docente.tipo_turma_atendimento_id IS NULL) OR 
                (docente.tipo_turma_atendimento_id <= 2 AND docente.tipo_turma_id IS NULL))
            AND (docente.ano_censo >= 2017) AND (docente.nivel_tipo_formacao IN (5, 6))
        GROUP BY docente.ano_censo, estado.nome, estado.id, municipio.nome, municipio.id, docente.dependencia_adm_id,
            docente.id_docente
        ORDER BY docente.ano_censo ASC, estado.nome ASC, estado.id ASC, municipio.nome ASC, municipio.id ASC
        ),
    docente_especializacao AS
        (SELECT docente.id_docente AS "id_docente", docente.ano_censo AS ano_censo, estado.id AS estado_id,
            municipio.id AS municipio_id, docente.dependencia_adm_id as dependencia_adm_id, 6 as tipo_formacao
        FROM docente
            INNER JOIN turma ON (docente.turma_id=turma.id AND docente.ano_censo=turma.ano_censo)
            INNER JOIN estado ON (docente.escola_estado_id=estado.id)
            INNER JOIN municipio ON (docente.escola_municipio_id=municipio.id)
        WHERE (docente.tipo_docente = 1 OR docente.tipo_docente = 5)  AND 
            ((docente.tipo_turma_id <= 3 AND docente.tipo_turma_atendimento_id IS NULL) OR 
                (docente.tipo_turma_atendimento_id <= 2 AND docente.tipo_turma_id IS NULL))
            AND (docente.ano_censo >= 2017) AND (docente.nivel_tipo_formacao IN (7, 8))
        GROUP BY docente.ano_censo, estado.nome, estado.id, municipio.nome, municipio.id, docente.dependencia_adm_id,
            docente.id_docente
        ORDER BY docente.ano_censo ASC, estado.nome ASC, estado.id ASC, municipio.nome ASC, municipio.id ASC
        ),
    docente_mestrado AS
        (SELECT docente.id_docente AS "id_docente", docente.ano_censo AS ano_censo, estado.id AS estado_id,
            municipio.id AS municipio_id, docente.dependencia_adm_id as dependencia_adm_id, 7 as tipo_formacao
        FROM docente
            INNER JOIN turma ON (docente.turma_id=turma.id AND docente.ano_censo=turma.ano_censo)
            INNER JOIN estado ON (docente.escola_estado_id=estado.id)
            INNER JOIN municipio ON (docente.escola_municipio_id=municipio.id)
        WHERE (docente.tipo_docente = 1 OR docente.tipo_docente = 5)  AND 
            ((docente.tipo_turma_id <= 3 AND docente.tipo_turma_atendimento_id IS NULL) OR 
                (docente.tipo_turma_atendimento_id <= 2 AND docente.tipo_turma_id IS NULL))
            AND (docente.ano_censo >= 2017) AND (docente.nivel_tipo_formacao IN (9, 10))
        GROUP BY docente.ano_censo, estado.nome, estado.id, municipio.nome, municipio.id, docente.dependencia_adm_id,
            docente.id_docente
        ORDER BY docente.ano_censo ASC, estado.nome ASC, estado.id ASC, municipio.nome ASC, municipio.id ASC
        ),
    docente_doutorado AS
        (SELECT docente.id_docente AS "id_docente", docente.ano_censo AS ano_censo, estado.id AS estado_id,
            municipio.id AS municipio_id, docente.dependencia_adm_id as dependencia_adm_id, 8 as tipo_formacao
        FROM docente
            INNER JOIN turma ON (docente.turma_id=turma.id AND docente.ano_censo=turma.ano_censo)
            INNER JOIN estado ON (docente.escola_estado_id=estado.id)
            INNER JOIN municipio ON (docente.escola_municipio_id=municipio.id)
        WHERE (docente.tipo_docente = 1 OR docente.tipo_docente = 5)  AND 
            ((docente.tipo_turma_id <= 3 AND docente.tipo_turma_atendimento_id IS NULL) OR 
                (docente.tipo_turma_atendimento_id <= 2 AND docente.tipo_turma_id IS NULL))
            AND (docente.ano_censo >= 2017) AND (docente.nivel_tipo_formacao IN (11, 12)
        GROUP BY docente.ano_censo, estado.nome, estado.id, municipio.nome, municipio.id, docente.dependencia_adm_id,
            docente.id_docente
        ORDER BY docente.ano_censo ASC, estado.nome ASC, estado.id ASC, municipio.nome ASC, municipio.id ASC
        ),

SELECT * FROM docente_total
UNION ALL (SELECT * FROM docente_medio)
UNION ALL (SELECT * FROM docente_superior)
UNION ALL (SELECT * FROM docente_especializacao)
UNION ALL (SELECT * FROM docente_mestrado)
UNION ALL (SELECT * FROM docente_doutorado)
);