| 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) AS code_commune,\r\n epci::text AS code_epci\r\n FROM \"26a76aaa-bd2e-47ad-b10c-50f146c7112b\"\r\n WHERE epci::text = '200033579'\r\n),\r\n\r\nref_etab_cua AS (\r\n SELECT\r\n e.numero_d_uai AS uai,\r\n e.code_insee_de_la_commune AS code_commune_ref,\r\n e.libelle_de_la_commune AS commune_ref,\r\n ce.code_epci\r\n FROM \"fdd6b3cd-d459-41bf-86d8-5ea9b404ecf2\" e\r\n JOIN communes_epci ce\r\n ON TRIM(e.code_insee_de_la_commune) = ce.code_commune\r\n),\r\n\r\necoles AS (\r\n SELECT\r\n 'ecole' AS type_etablissement,\r\n (substring(e.rentree_scolaire::text from '[0-9]{4}'))::integer AS rentree_scolaire,\r\n e.numero_de_l_ecole AS uai,\r\n r.code_commune_ref AS code_commune,\r\n r.code_epci,\r\n COALESCE(r.commune_ref, e.commune) AS commune,\r\n e.code_departement,\r\n e.departement,\r\n e.academie,\r\n e.secteur,\r\n e.rep::text AS rep,\r\n e.rep__::text AS rep_plus,\r\n e.denomination_principale,\r\n e.patronyme,\r\n NULLIF(TRIM(CONCAT_WS(' ', e.denomination_principale, e.patronyme)), '') AS nom_etablissement,\r\n e.nombre_total_d_eleves AS effectif_total,\r\n e.nombre_total_de_classes AS nombre_classes,\r\n e.nombre_d_eleves_en_pre_elementaire_hors_ulis AS effectif_pre_elementaire,\r\n e.nombre_d_eleves_en_elementaire_hors_ulis AS effectif_elementaire,\r\n e.nombre_d_eleves_en_ulis AS effectif_ulis,\r\n e.nombre_d_eleves_en_ueea AS effectif_ueea,\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 FROM \"12835e3f-6034-456a-ae0f-ac5a020d10cc\" e\r\n JOIN ref_etab_cua r\r\n ON e.numero_de_l_ecole = r.uai\r\n),\r\n\r\ncolleges AS (\r\n SELECT\r\n 'college' AS type_etablissement,\r\n (substring(c.rentree_scolaire::text from '[0-9]{4}'))::integer AS rentree_scolaire,\r\n c.uai,\r\n c.code_commune,\r\n ce.code_epci,\r\n c.commune,\r\n c.code_departement,\r\n c.departement,\r\n c.academie,\r\n c.secteur,\r\n c.rep::text AS rep,\r\n c.rep__::text AS rep_plus,\r\n c.denomination_principale,\r\n c.patronyme,\r\n NULLIF(TRIM(CONCAT_WS(' ', c.denomination_principale, c.patronyme)), '') AS nom_etablissement,\r\n c.nombre_eleves_total AS effectif_total,\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 AS effectif_ulis,\r\n NULL::numeric AS effectif_ueea,\r\n c.unsafe_6emes_total AS effectif_6e,\r\n c.unsafe_5emes_total AS effectif_5e,\r\n c.unsafe_4emes_total AS effectif_4e,\r\n c.unsafe_3emes_total 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 FROM \"fce7b015-bfe0-4b4f-a370-10030576a87c\" c\r\n JOIN communes_epci ce\r\n ON TRIM(c.code_commune) = ce.code_commune\r\n),\r\n\r\nlycees_gt AS (\r\n SELECT\r\n 'lycee_gt' AS type_etablissement,\r\n (substring(l.rentree_scolaire::text from '[0-9]{4}'))::integer AS rentree_scolaire,\r\n l.uai,\r\n l.code_commune,\r\n ce.code_epci,\r\n l.commune,\r\n l.code_departement_pays AS code_departement,\r\n l.departement,\r\n l.academie,\r\n l.secteur,\r\n NULL::text AS rep,\r\n NULL::text AS rep_plus,\r\n l.denomination_principale,\r\n l.patronyme,\r\n NULLIF(TRIM(CONCAT_WS(' ', l.denomination_principale, l.patronyme)), '') AS nom_etablissement,\r\n l.nombre_d_eleves AS effectif_total,\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 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 l.unsafe_2ndes_gt AS effectif_seconde,\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 ) AS effectif_premiere,\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 ) AS effectif_terminale,\r\n NULL::numeric AS effectif_cap\r\n FROM \"e520061c-e43e-49d3-aa34-6a1e80dab8d7\" l\r\n JOIN communes_epci ce\r\n ON TRIM(l.code_commune) = ce.code_commune\r\n),\r\n\r\nlycees_pro AS (\r\n SELECT\r\n 'lycee_pro' AS type_etablissement,\r\n (substring(l.rentree_scolaire::text from '[0-9]{4}'))::integer AS rentree_scolaire,\r\n l.uai,\r\n l.code_commune,\r\n ce.code_epci,\r\n l.commune,\r\n l.code_departement,\r\n l.departement,\r\n l.academie,\r\n l.secteur,\r\n NULL::text AS rep,\r\n NULL::text AS rep_plus,\r\n l.denomination_principale,\r\n l.patronyme,\r\n NULLIF(TRIM(CONCAT_WS(' ', l.denomination_principale, l.patronyme)), '') AS nom_etablissement,\r\n l.nombre_d_eleves AS effectif_total,\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 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 l.unsafe_2ndes_pro AS effectif_seconde,\r\n l.unsafe_1eres_pro AS effectif_premiere,\r\n l.terminales_pro AS effectif_terminale,\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 ) AS effectif_cap\r\n FROM \"888cc687-04e7-4131-80e5-4122ae6b664f\" l\r\n JOIN communes_epci ce\r\n ON TRIM(l.code_commune) = ce.code_commune\r\n)\r\n\r\nSELECT *\r\nFROM ecoles\r\nUNION ALL\r\nSELECT *\r\nFROM colleges\r\nUNION ALL\r\nSELECT *\r\nFROM lycees_gt\r\nUNION ALL\r\nSELECT *\r\nFROM lycees_pro\r\nORDER BY commune, type_etablissement, nom_etablissement, rentree_scolaire;"} |