| 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 AS (\r\n SELECT\r\n e.numero_d_uai AS uai,\r\n e.appellation_officielle AS nom_etablissement_ref,\r\n e.denomination_principale,\r\n e.denomination_complementaire_ou_patronyme AS patronyme,\r\n e.secteur AS secteur_ref,\r\n e.code_insee_de_la_commune AS code_commune_ref,\r\n ce.code_epci AS code_epci_ref,\r\n e.libelle_de_la_commune AS commune_ref,\r\n e.code_insee_du_departement_ou_de_la_collectivite AS code_departement_ref,\r\n e.libelle_du_departement_ou_de_la_collectivite AS departement_ref,\r\n e.code_insee_de_la_region AS code_region_ref,\r\n e.libelle_de_la_region AS region_ref,\r\n e.libelle_de_la_nature_de_l_uai AS nature_etablissement,\r\n e.libelle_de_l_etat_de_l_etablissement AS etat_etablissement,\r\n\r\n CASE\r\n WHEN REPLACE(e.latitude_wgs84, ',', '.') ~ '^-?[0-9]+(\\.[0-9]+)?$'\r\n THEN REPLACE(e.latitude_wgs84, ',', '.')::numeric\r\n ELSE NULL\r\n END AS latitude,\r\n\r\n CASE\r\n WHEN REPLACE(e.longitude_wgs84, ',', '.') ~ '^-?[0-9]+(\\.[0-9]+)?$'\r\n THEN REPLACE(e.longitude_wgs84, ',', '.')::numeric\r\n ELSE NULL\r\n END AS longitude\r\n FROM \"fdd6b3cd-d459-41bf-86d8-5ea9b404ecf2\" e\r\n LEFT JOIN communes_epci ce\r\n ON TRIM(e.code_insee_de_la_commune) = ce.code_commune\r\n WHERE e.code_insee_du_departement_ou_de_la_collectivite = '62'\r\n),\r\n\r\neffectifs_lignes 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_ref AS 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 NULLIF(TRIM(CONCAT_WS(' ', e.denomination_principale, e.patronyme)), '') AS nom_etablissement,\r\n e.nombre_total_d_eleves AS effectif_total\r\n FROM \"12835e3f-6034-456a-ae0f-ac5a020d10cc\" e\r\n JOIN ref_etab r\r\n ON e.numero_de_l_ecole = r.uai\r\n JOIN communes_epci ce\r\n ON r.code_commune_ref = ce.code_commune\r\n\r\n UNION ALL\r\n\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 NULLIF(TRIM(CONCAT_WS(' ', c.denomination_principale, c.patronyme)), '') AS nom_etablissement,\r\n c.nombre_eleves_total AS effectif_total\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 UNION ALL\r\n\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 NULLIF(TRIM(CONCAT_WS(' ', l.denomination_principale, l.patronyme)), '') AS nom_etablissement,\r\n l.nombre_d_eleves AS effectif_total\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 UNION ALL\r\n\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 NULLIF(TRIM(CONCAT_WS(' ', l.denomination_principale, l.patronyme)), '') AS nom_etablissement,\r\n l.nombre_d_eleves AS effectif_total\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\neffectifs_derniere_rentree AS (\r\n SELECT *\r\n FROM (\r\n SELECT\r\n e.*,\r\n ROW_NUMBER() OVER (\r\n PARTITION BY e.uai, e.type_etablissement\r\n ORDER BY e.rentree_scolaire DESC\r\n ) AS rn\r\n FROM effectifs_lignes e\r\n ) x\r\n WHERE rn = 1\r\n),\r\n\r\nips_lignes AS (\r\n SELECT\r\n 'ecole' AS type_etablissement,\r\n (substring(rentree_scolaire::text from '[0-9]{4}'))::integer AS rentree_scolaire,\r\n uai,\r\n code_insee_de_la_commune AS code_commune,\r\n nom_de_la_commune AS commune,\r\n secteur,\r\n CASE\r\n WHEN REPLACE(ips, ',', '.') ~ '^-?[0-9]+(\\.[0-9]+)?$'\r\n THEN REPLACE(ips, ',', '.')::numeric\r\n ELSE NULL\r\n END AS ips\r\n FROM \"f53e280a-bf05-4660-92f8-7cc071b740c4\"\r\n\r\n UNION ALL\r\n\r\n SELECT\r\n 'college' AS type_etablissement,\r\n (substring(rentree_scolaire::text from '[0-9]{4}'))::integer AS rentree_scolaire,\r\n uai,\r\n code_insee_de_la_commune AS code_commune,\r\n nom_de_la_commune AS commune,\r\n secteur,\r\n ips\r\n FROM \"d33a2cc8-58c0-4a2d-b5a2-3f54ebbbb1db\"\r\n\r\n UNION ALL\r\n\r\n SELECT\r\n 'lycee' AS type_etablissement,\r\n (substring(rentree_scolaire::text from '[0-9]{4}'))::integer AS rentree_scolaire,\r\n uai,\r\n code_insee_de_la_commune AS code_commune,\r\n nom_de_la_commune AS commune,\r\n secteur,\r\n \"ips_ensemble_gt-pro\" AS ips\r\n FROM \"824d0e74-fb7b-4975-a2e3-eff9be88d32b\"\r\n),\r\n\r\nips_derniere_rentree AS (\r\n SELECT *\r\n FROM (\r\n SELECT\r\n i.*,\r\n ROW_NUMBER() OVER (\r\n PARTITION BY i.uai\r\n ORDER BY i.rentree_scolaire DESC\r\n ) AS rn\r\n FROM ips_lignes i\r\n ) x\r\n WHERE rn = 1\r\n),\r\n\r\nep AS (\r\n SELECT\r\n uai,\r\n ep_2022_2023 AS statut_education_prioritaire,\r\n qp_a_proximite_o_n,\r\n qp_a_proximite,\r\n nom_du_qp\r\n FROM \"816a0970-e5ae-4b9c-8c9e-f6c637bfb691\"\r\n)\r\n\r\nSELECT\r\n e.uai,\r\n COALESCE(r.nom_etablissement_ref, e.nom_etablissement) AS nom_etablissement,\r\n COALESCE(r.denomination_principale, e.type_etablissement) AS denomination_principale,\r\n r.patronyme,\r\n COALESCE(r.nature_etablissement, e.type_etablissement) AS nature_etablissement,\r\n COALESCE(r.secteur_ref, e.secteur) AS secteur,\r\n\r\n COALESCE(r.code_commune_ref, e.code_commune) AS code_commune,\r\n COALESCE(r.code_epci_ref, e.code_epci) AS code_epci,\r\n COALESCE(r.commune_ref, e.commune) AS commune,\r\n COALESCE(r.code_departement_ref, e.code_departement) AS code_departement,\r\n COALESCE(r.departement_ref, e.departement) AS departement,\r\n r.code_region_ref AS code_region,\r\n r.region_ref AS region,\r\n\r\n r.latitude,\r\n r.longitude,\r\n e.type_etablissement,\r\n e.rentree_scolaire AS derniere_rentree,\r\n e.effectif_total,\r\n\r\n ips.rentree_scolaire AS derniere_rentree_ips,\r\n ips.ips,\r\n\r\n ep.statut_education_prioritaire,\r\n ep.qp_a_proximite_o_n,\r\n ep.qp_a_proximite,\r\n ep.nom_du_qp\r\n\r\nFROM effectifs_derniere_rentree e\r\nLEFT JOIN ref_etab r\r\n ON e.uai = r.uai\r\nLEFT JOIN ips_derniere_rentree ips\r\n ON e.uai = ips.uai\r\nLEFT JOIN ep\r\n ON e.uai = ep.uai\r\n\r\nORDER BY commune, type_etablissement, nom_etablissement;"} |