| 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.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.libelle_de_la_nature_de_l_uai AS nature_etablissement\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.secteur,\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.secteur,\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.secteur,\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.secteur,\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 uai,\r\n (substring(rentree_scolaire::text from '[0-9]{4}'))::integer AS rentree_scolaire,\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 uai,\r\n (substring(rentree_scolaire::text from '[0-9]{4}'))::integer AS rentree_scolaire,\r\n ips\r\n FROM \"d33a2cc8-58c0-4a2d-b5a2-3f54ebbbb1db\"\r\n\r\n UNION ALL\r\n\r\n SELECT\r\n uai,\r\n (substring(rentree_scolaire::text from '[0-9]{4}'))::integer AS rentree_scolaire,\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 FROM \"816a0970-e5ae-4b9c-8c9e-f6c637bfb691\"\r\n),\r\n\r\nsynthese AS (\r\n SELECT\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 e.uai,\r\n e.type_etablissement,\r\n COALESCE(r.secteur_ref, e.secteur) AS secteur,\r\n r.nature_etablissement,\r\n e.effectif_total,\r\n ips.ips,\r\n ep.statut_education_prioritaire\r\n FROM effectifs_derniere_rentree e\r\n LEFT JOIN ref_etab r\r\n ON e.uai = r.uai\r\n LEFT JOIN ips_derniere_rentree ips\r\n ON e.uai = ips.uai\r\n LEFT JOIN ep\r\n ON e.uai = ep.uai\r\n)\r\n\r\nSELECT\r\n code_epci,\r\n code_commune,\r\n commune,\r\n\r\n COUNT(DISTINCT uai) AS nb_etablissements,\r\n\r\n COUNT(DISTINCT CASE\r\n WHEN secteur ILIKE '%public%'\r\n THEN uai\r\n END) AS nb_etablissements_publics,\r\n\r\n COUNT(DISTINCT CASE\r\n WHEN secteur ILIKE '%priv%' OR secteur ILIKE '%priv\u00e9%'\r\n THEN uai\r\n END) AS nb_etablissements_prives,\r\n\r\n COUNT(DISTINCT CASE\r\n WHEN type_etablissement = 'ecole'\r\n THEN uai\r\n END) AS nb_ecoles,\r\n\r\n COUNT(DISTINCT CASE\r\n WHEN type_etablissement = 'college'\r\n THEN uai\r\n END) AS nb_colleges,\r\n\r\n COUNT(DISTINCT CASE\r\n WHEN type_etablissement IN ('lycee_gt', 'lycee_pro')\r\n THEN uai\r\n END) AS nb_lycees,\r\n\r\n SUM(effectif_total) AS effectif_total,\r\n\r\n AVG(ips) AS ips_moyen,\r\n\r\n COUNT(DISTINCT CASE\r\n WHEN statut_education_prioritaire IS NOT NULL\r\n THEN uai\r\n END) AS nb_etablissements_education_prioritaire\r\n\r\nFROM synthese\r\nGROUP BY\r\n code_epci,\r\n code_commune,\r\n commune\r\nORDER BY commune;"} |