| dataset-model |
{"db_type":"pgsql","db_name":"datastore","host":"db","port":"5432","login":"ckan","password":"ckan","sql_query":"WITH ips_ecoles AS (\r\n SELECT\r\n 'ecole' 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_etablissement AS nom_etablissement,\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 NULL::numeric AS effectifs,\r\n NULL::numeric AS ecart_type_ips\r\n FROM \"f53e280a-bf05-4660-92f8-7cc071b740c4\"\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 ips,\r\n effectifs,\r\n ecart_type_de_l_ips AS ecart_type_ips\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 \"ips_ensemble_gt-pro\" AS ips,\r\n \"effectifs_ensemble_gt-pro\" AS effectifs,\r\n NULL::numeric AS ecart_type_ips\r\n FROM \"824d0e74-fb7b-4975-a2e3-eff9be88d32b\"\r\n)\r\n\r\nSELECT ips.*\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) = TRIM(c.codgeo)\r\nWHERE c.epci = 200033579\r\nORDER BY commune, type_etablissement, nom_etablissement, rentree_scolaire;"} |