| dataset-model |
{"db_type":"pgsql","db_name":"datastore","host":"db","port":"5432","login":"ckan","password":"ckan","sql_query":"WITH effectifs_ecoles AS (\r\n SELECT\r\n regexp_replace(\r\n upper(TRIM(numero_de_l_ecole::text)),\r\n '[^A-Z0-9]',\r\n '',\r\n 'g'\r\n ) AS uai_norm,\r\n\r\n substring(TRIM(rentree_scolaire::text) from '([0-9]{4})') AS annee_rentree,\r\n\r\n SUM(\r\n CASE\r\n WHEN REPLACE(nombre_total_d_eleves::text, ',', '.') ~ '^-?[0-9]+(\\.[0-9]+)?$'\r\n THEN REPLACE(nombre_total_d_eleves::text, ',', '.')::numeric\r\n ELSE NULL\r\n END\r\n ) AS effectifs\r\n\r\n FROM \"12835e3f-6034-456a-ae0f-ac5a020d10cc\"\r\n GROUP BY\r\n regexp_replace(\r\n upper(TRIM(numero_de_l_ecole::text)),\r\n '[^A-Z0-9]',\r\n '',\r\n 'g'\r\n ),\r\n substring(TRIM(rentree_scolaire::text) from '([0-9]{4})')\r\n),\r\n\r\nips_ecoles AS (\r\n SELECT\r\n 'ecole' AS type_etablissement,\r\n i.rentree_scolaire,\r\n i.academie,\r\n i.code_du_departement AS code_departement,\r\n i.departement,\r\n i.code_insee_de_la_commune AS code_commune,\r\n i.nom_de_la_commune AS commune,\r\n i.uai,\r\n i.nom_de_l_etablissement AS nom_etablissement,\r\n i.secteur,\r\n\r\n CASE\r\n WHEN REPLACE(i.ips::text, ',', '.') ~ '^-?[0-9]+(\\.[0-9]+)?$'\r\n THEN REPLACE(i.ips::text, ',', '.')::numeric\r\n ELSE NULL\r\n END AS ips,\r\n\r\n e.effectifs,\r\n\r\n NULL::numeric AS ecart_type_ips\r\n\r\n FROM \"f53e280a-bf05-4660-92f8-7cc071b740c4\" i\r\n LEFT JOIN effectifs_ecoles e\r\n ON regexp_replace(\r\n upper(TRIM(i.uai::text)),\r\n '[^A-Z0-9]',\r\n '',\r\n 'g'\r\n ) = e.uai_norm\r\n AND substring(TRIM(i.rentree_scolaire::text) from '([0-9]{4})') = e.annee_rentree\r\n),\r\n\r\nips_colleges AS (\r\n SELECT\r\n 'college' AS type_etablissement,\r\n rentree_scolaire,\r\n academie,\r\n code_du_departement AS code_departement,\r\n departement,\r\n code_insee_de_la_commune AS code_commune,\r\n nom_de_la_commune AS commune,\r\n uai,\r\n nom_de_l_etablissment AS nom_etablissement,\r\n secteur,\r\n\r\n CASE\r\n WHEN REPLACE(ips::text, ',', '.') ~ '^-?[0-9]+(\\.[0-9]+)?$'\r\n THEN REPLACE(ips::text, ',', '.')::numeric\r\n ELSE NULL\r\n END AS ips,\r\n\r\n CASE\r\n WHEN REPLACE(effectifs::text, ',', '.') ~ '^-?[0-9]+(\\.[0-9]+)?$'\r\n THEN REPLACE(effectifs::text, ',', '.')::numeric\r\n ELSE NULL\r\n END AS effectifs,\r\n\r\n CASE\r\n WHEN REPLACE(ecart_type_de_l_ips::text, ',', '.') ~ '^-?[0-9]+(\\.[0-9]+)?$'\r\n THEN REPLACE(ecart_type_de_l_ips::text, ',', '.')::numeric\r\n ELSE NULL\r\n END AS ecart_type_ips\r\n\r\n FROM \"d33a2cc8-58c0-4a2d-b5a2-3f54ebbbb1db\"\r\n),\r\n\r\nips_lycees AS (\r\n SELECT\r\n 'lycee' AS type_etablissement,\r\n rentree_scolaire,\r\n academie,\r\n code_du_departement AS code_departement,\r\n departement,\r\n code_insee_de_la_commune AS code_commune,\r\n nom_de_la_commune AS commune,\r\n uai,\r\n nom_de_l_etablissment AS nom_etablissement,\r\n secteur,\r\n\r\n CASE\r\n WHEN REPLACE(\"ips_ensemble_gt-pro\"::text, ',', '.') ~ '^-?[0-9]+(\\.[0-9]+)?$'\r\n THEN REPLACE(\"ips_ensemble_gt-pro\"::text, ',', '.')::numeric\r\n ELSE NULL\r\n END AS ips,\r\n\r\n CASE\r\n WHEN REPLACE(\"effectifs_ensemble_gt-pro\"::text, ',', '.') ~ '^-?[0-9]+(\\.[0-9]+)?$'\r\n THEN REPLACE(\"effectifs_ensemble_gt-pro\"::text, ',', '.')::numeric\r\n ELSE NULL\r\n END AS effectifs,\r\n\r\n NULL::numeric AS ecart_type_ips\r\n\r\n FROM \"824d0e74-fb7b-4975-a2e3-eff9be88d32b\"\r\n)\r\n\r\nSELECT ips.*, c.codgeo\r\nFROM (\r\n SELECT * FROM ips_ecoles\r\n UNION ALL\r\n SELECT * FROM ips_colleges\r\n UNION ALL\r\n SELECT * FROM ips_lycees\r\n) ips\r\nJOIN \"a69528ba-9dac-43fd-b974-c9c62aec2c41\" c\r\n ON TRIM(ips.code_commune::text) = TRIM(c.codgeo::text)\r\nWHERE c.epci = 200033579\r\nORDER BY commune, type_etablissement, nom_etablissement, rentree_scolaire;"} |
| tooltip |
{"resources":[{"type":"standard","resource_id":"04a07c00-a91b-4550-8825-43469165c451","value":{"title":"","fields":"_id,type_etablissement,rentree_scolaire,academie,code_departement,departement,code_commune,commune,uai,nom_etablissement,secteur,ips,effectifs,ecart_type_ips,codgeo"}}]} |