| dataset-model |
{"db_type":"pgsql","db_name":"datastore","host":"db","port":"5432","login":"ckan","password":"ckan","sql_query":"WITH communes_epci AS (\r\n SELECT\r\n TRIM(code::text) AS code_commune,\r\n epci::numeric AS code_epci\r\n FROM \"26a76aaa-bd2e-47ad-b10c-50f146c7112b\"\r\n WHERE epci::text = '200033579'\r\n),\r\n\r\n\/*\r\n R\u00e9f\u00e9rentiel \u00e9tablissements CUA d\u00e9j\u00e0 filtr\u00e9 \/ construit,\r\n valid\u00e9 par tes tests pour matcher les \u00e9coles via UAI.\r\n*\/\r\nref_etab_cua AS (\r\n SELECT\r\n UPPER(TRIM(g.uai::text)) AS uai_norm,\r\n TRIM(g.uai::text) AS uai,\r\n NULLIF(TRIM(g.code_commune::text), '')::numeric AS code_commune,\r\n COALESCE(ce.code_epci, 200033579::numeric) AS code_epci,\r\n NULLIF(TRIM(g.commune::text), '') AS commune,\r\n NULLIF(TRIM(g.nom_etablissement::text), '') AS nom_etablissement,\r\n NULLIF(TRIM(g.denomination_principale::text), '') AS denomination_principale,\r\n NULLIF(TRIM(g.patronyme::text), '') AS patronyme,\r\n NULLIF(TRIM(g.secteur::text), '') AS secteur\r\n FROM \"90710710-6467-4eff-9bb3-26e9073ed455\" g\r\n LEFT JOIN communes_epci ce\r\n ON TRIM(g.code_commune::text) = ce.code_commune\r\n),\r\n\r\necoles AS (\r\n SELECT\r\n 'ecole'::text AS type_etablissement,\r\n (substring(e.rentree_scolaire::text from '[0-9]{4}'))::numeric AS rentree_scolaire,\r\n TRIM(e.numero_de_l_ecole::text) AS uai,\r\n\r\n r.code_commune,\r\n r.code_epci,\r\n\r\n COALESCE(r.commune, NULLIF(TRIM(e.commune::text), '')) AS commune,\r\n\r\n NULLIF(TRIM(e.code_departement::text), '')::numeric AS code_departement,\r\n NULLIF(TRIM(e.departement::text), '') AS departement,\r\n NULLIF(TRIM(e.academie::text), '') AS academie,\r\n COALESCE(NULLIF(TRIM(e.secteur::text), ''), r.secteur) AS secteur,\r\n\r\n CASE\r\n WHEN lower(trim(e.rep::text)) IN ('1', 'true', 't', 'oui', 'o', 'yes', 'y', 'rep')\r\n THEN 1::numeric\r\n ELSE 0::numeric\r\n END AS rep,\r\n\r\n CASE\r\n WHEN lower(trim(e.rep__::text)) IN ('1', 'true', 't', 'oui', 'o', 'yes', 'y', 'rep+', 'rep plus', 'rep_plus')\r\n THEN 1::numeric\r\n ELSE 0::numeric\r\n END AS rep_plus,\r\n\r\n COALESCE(NULLIF(TRIM(e.denomination_principale::text), ''), r.denomination_principale) AS denomination_principale,\r\n COALESCE(NULLIF(TRIM(e.patronyme::text), ''), r.patronyme) AS patronyme,\r\n\r\n COALESCE(\r\n r.nom_etablissement,\r\n NULLIF(TRIM(CONCAT_WS(' ', e.denomination_principale, e.patronyme)), '')\r\n ) AS nom_etablissement,\r\n\r\n e.nombre_total_d_eleves::numeric AS effectif_total,\r\n\r\n e.nombre_total_de_classes::numeric AS nombre_classes,\r\n e.nombre_d_eleves_en_pre_elementaire_hors_ulis::numeric AS effectif_pre_elementaire,\r\n e.nombre_d_eleves_en_elementaire_hors_ulis::numeric AS effectif_elementaire,\r\n e.nombre_d_eleves_en_ulis::numeric AS effectif_ulis,\r\n e.nombre_d_eleves_en_ueea::numeric AS effectif_ueea,\r\n\r\n NULL::numeric AS effectif_6e,\r\n NULL::numeric AS effectif_5e,\r\n NULL::numeric AS effectif_4e,\r\n NULL::numeric AS effectif_3e,\r\n NULL::numeric AS effectif_seconde,\r\n NULL::numeric AS effectif_premiere,\r\n NULL::numeric AS effectif_terminale,\r\n NULL::numeric AS effectif_cap\r\n\r\n FROM \"12835e3f-6034-456a-ae0f-ac5a020d10cc\" e\r\n JOIN ref_etab_cua r\r\n ON UPPER(TRIM(e.numero_de_l_ecole::text)) = r.uai_norm\r\n),\r\n\r\ncolleges AS (\r\n SELECT\r\n 'college'::text AS type_etablissement,\r\n (substring(c.rentree_scolaire::text from '[0-9]{4}'))::numeric AS rentree_scolaire,\r\n TRIM(c.uai::text) AS uai,\r\n\r\n NULLIF(TRIM(c.code_commune::text), '')::numeric AS code_commune,\r\n ce.code_epci,\r\n\r\n NULLIF(TRIM(c.commune::text), '') AS commune,\r\n\r\n NULLIF(TRIM(c.code_departement::text), '')::numeric AS code_departement,\r\n NULLIF(TRIM(c.departement::text), '') AS departement,\r\n NULLIF(TRIM(c.academie::text), '') AS academie,\r\n NULLIF(TRIM(c.secteur::text), '') AS secteur,\r\n\r\n CASE\r\n WHEN lower(trim(c.rep::text)) IN ('1', 'true', 't', 'oui', 'o', 'yes', 'y', 'rep')\r\n THEN 1::numeric\r\n ELSE 0::numeric\r\n END AS rep,\r\n\r\n CASE\r\n WHEN lower(trim(c.rep__::text)) IN ('1', 'true', 't', 'oui', 'o', 'yes', 'y', 'rep+', 'rep plus', 'rep_plus')\r\n THEN 1::numeric\r\n ELSE 0::numeric\r\n END AS rep_plus,\r\n\r\n NULLIF(TRIM(c.denomination_principale::text), '') AS denomination_principale,\r\n NULLIF(TRIM(c.patronyme::text), '') AS patronyme,\r\n\r\n NULLIF(TRIM(CONCAT_WS(' ', c.denomination_principale, c.patronyme)), '') AS nom_etablissement,\r\n\r\n c.nombre_eleves_total::numeric AS effectif_total,\r\n\r\n NULL::numeric AS nombre_classes,\r\n NULL::numeric AS effectif_pre_elementaire,\r\n NULL::numeric AS effectif_elementaire,\r\n c.nombre_d_eleves_total_ulis::numeric AS effectif_ulis,\r\n NULL::numeric AS effectif_ueea,\r\n\r\n c.unsafe_6emes_total::numeric AS effectif_6e,\r\n c.unsafe_5emes_total::numeric AS effectif_5e,\r\n c.unsafe_4emes_total::numeric AS effectif_4e,\r\n c.unsafe_3emes_total::numeric AS effectif_3e,\r\n\r\n NULL::numeric AS effectif_seconde,\r\n NULL::numeric AS effectif_premiere,\r\n NULL::numeric AS effectif_terminale,\r\n NULL::numeric AS effectif_cap\r\n\r\n FROM \"fce7b015-bfe0-4b4f-a370-10030576a87c\" c\r\n JOIN communes_epci ce\r\n ON TRIM(c.code_commune::text) = ce.code_commune\r\n),\r\n\r\nlycees_gt AS (\r\n SELECT\r\n 'lycee_gt'::text AS type_etablissement,\r\n (substring(l.rentree_scolaire::text from '[0-9]{4}'))::numeric AS rentree_scolaire,\r\n TRIM(l.uai::text) AS uai,\r\n\r\n NULLIF(TRIM(l.code_commune::text), '')::numeric AS code_commune,\r\n ce.code_epci,\r\n\r\n NULLIF(TRIM(l.commune::text), '') AS commune,\r\n\r\n NULLIF(TRIM(l.code_departement_pays::text), '')::numeric AS code_departement,\r\n NULLIF(TRIM(l.departement::text), '') AS departement,\r\n NULLIF(TRIM(l.academie::text), '') AS academie,\r\n NULLIF(TRIM(l.secteur::text), '') AS secteur,\r\n\r\n NULL::numeric AS rep,\r\n NULL::numeric AS rep_plus,\r\n\r\n NULLIF(TRIM(l.denomination_principale::text), '') AS denomination_principale,\r\n NULLIF(TRIM(l.patronyme::text), '') AS patronyme,\r\n\r\n NULLIF(TRIM(CONCAT_WS(' ', l.denomination_principale, l.patronyme)), '') AS nom_etablissement,\r\n\r\n l.nombre_d_eleves::numeric AS effectif_total,\r\n\r\n NULL::numeric AS nombre_classes,\r\n NULL::numeric AS effectif_pre_elementaire,\r\n NULL::numeric AS effectif_elementaire,\r\n NULL::numeric AS effectif_ulis,\r\n NULL::numeric AS effectif_ueea,\r\n\r\n NULL::numeric AS effectif_6e,\r\n NULL::numeric AS effectif_5e,\r\n NULL::numeric AS effectif_4e,\r\n NULL::numeric AS effectif_3e,\r\n\r\n l.unsafe_2ndes_gt::numeric AS effectif_seconde,\r\n\r\n (\r\n COALESCE(l.unsafe_1eres_g, 0)\r\n + COALESCE(l.unsafe_1eres_sti2d, 0)\r\n + COALESCE(l.unsafe_1eres_stl, 0)\r\n + COALESCE(l.unsafe_1eres_stmg, 0)\r\n + COALESCE(l.unsafe_1eres_st2s, 0)\r\n + COALESCE(l.unsafe_1eres_std2a, 0)\r\n + COALESCE(l.unsafe_1eres_sthr, 0)\r\n + COALESCE(l.unsafe_1eres_tmd, 0)\r\n + COALESCE(l.unsafe_1eres_bt, 0)\r\n )::numeric AS effectif_premiere,\r\n\r\n (\r\n COALESCE(l.terminales_g, 0)\r\n + COALESCE(l.terminales_sti2d, 0)\r\n + COALESCE(l.terminales_stl, 0)\r\n + COALESCE(l.terminales_stmg, 0)\r\n + COALESCE(l.terminales_st2s, 0)\r\n + COALESCE(l.terminales_std2a, 0)\r\n + COALESCE(l.terminales_sthr, 0)\r\n + COALESCE(l.terminales_tmd, 0)\r\n + COALESCE(l.terminales_bt, 0)\r\n )::numeric AS effectif_terminale,\r\n\r\n NULL::numeric AS effectif_cap\r\n\r\n FROM \"e520061c-e43e-49d3-aa34-6a1e80dab8d7\" l\r\n JOIN communes_epci ce\r\n ON TRIM(l.code_commune::text) = ce.code_commune\r\n),\r\n\r\nlycees_pro AS (\r\n SELECT\r\n 'lycee_pro'::text AS type_etablissement,\r\n (substring(l.rentree_scolaire::text from '[0-9]{4}'))::numeric AS rentree_scolaire,\r\n TRIM(l.uai::text) AS uai,\r\n\r\n NULLIF(TRIM(l.code_commune::text), '')::numeric AS code_commune,\r\n ce.code_epci,\r\n\r\n NULLIF(TRIM(l.commune::text), '') AS commune,\r\n\r\n NULLIF(TRIM(l.code_departement::text), '')::numeric AS code_departement,\r\n NULLIF(TRIM(l.departement::text), '') AS departement,\r\n NULLIF(TRIM(l.academie::text), '') AS academie,\r\n NULLIF(TRIM(l.secteur::text), '') AS secteur,\r\n\r\n NULL::numeric AS rep,\r\n NULL::numeric AS rep_plus,\r\n\r\n NULLIF(TRIM(l.denomination_principale::text), '') AS denomination_principale,\r\n NULLIF(TRIM(l.patronyme::text), '') AS patronyme,\r\n\r\n NULLIF(TRIM(CONCAT_WS(' ', l.denomination_principale, l.patronyme)), '') AS nom_etablissement,\r\n\r\n l.nombre_d_eleves::numeric AS effectif_total,\r\n\r\n NULL::numeric AS nombre_classes,\r\n NULL::numeric AS effectif_pre_elementaire,\r\n NULL::numeric AS effectif_elementaire,\r\n NULL::numeric AS effectif_ulis,\r\n NULL::numeric AS effectif_ueea,\r\n\r\n NULL::numeric AS effectif_6e,\r\n NULL::numeric AS effectif_5e,\r\n NULL::numeric AS effectif_4e,\r\n NULL::numeric AS effectif_3e,\r\n\r\n l.unsafe_2ndes_pro::numeric AS effectif_seconde,\r\n l.unsafe_1eres_pro::numeric AS effectif_premiere,\r\n l.terminales_pro::numeric AS effectif_terminale,\r\n\r\n (\r\n COALESCE(l.cap_1ere_annee, 0)\r\n + COALESCE(l.cap_2nde_annee, 0)\r\n + COALESCE(l.cap_en_3_ans, 0)\r\n + COALESCE(l.cap_en_1_an, 0)\r\n )::numeric AS effectif_cap\r\n\r\n FROM \"888cc687-04e7-4131-80e5-4122ae6b664f\" l\r\n JOIN communes_epci ce\r\n ON TRIM(l.code_commune::text) = ce.code_commune\r\n)\r\n\r\nSELECT *\r\nFROM ecoles\r\nWHERE rentree_scolaire BETWEEN 2019 AND 2024\r\n\r\nUNION ALL\r\n\r\nSELECT *\r\nFROM colleges\r\n\r\nUNION ALL\r\n\r\nSELECT *\r\nFROM lycees_gt\r\n\r\nUNION ALL\r\n\r\nSELECT *\r\nFROM lycees_pro\r\n\r\nORDER BY\r\n commune,\r\n type_etablissement,\r\n nom_etablissement,\r\n rentree_scolaire;"} |