| dataset-model |
{"db_type":"pgsql","db_name":"datastore","host":"db","port":"5432","login":"ckan","password":"ckan","sql_query":"WITH communes_cua AS (\r\n SELECT DISTINCT\r\n TRIM(UPPER(e.libelle_de_la_commune)) AS commune\r\n FROM \"fdd6b3cd-d459-41bf-86d8-5ea9b404ecf2\" e\r\n JOIN \"a69528ba-9dac-43fd-b974-c9c62aec2c41\" c\r\n ON TRIM(e.code_insee_de_la_commune) = TRIM(c.codgeo)\r\n WHERE c.epci = 200033579\r\n),\r\n\r\nivac_colleges AS (\r\n SELECT\r\n 'college' AS type_etablissement,\r\n session::integer AS annee,\r\n uai,\r\n nom_de_l_etablissement AS nom_etablissement,\r\n commune,\r\n NULL::text AS code_commune,\r\n code_departement,\r\n departement,\r\n academie,\r\n secteur,\r\n nb_candidats_g AS nb_candidats,\r\n taux_de_reussite_g AS taux_reussite,\r\n va_du_taux_de_reussite_g AS valeur_ajoutee_taux_reussite,\r\n taux_d_acces_6eme_3eme AS taux_acces,\r\n note_a_l_ecrit_g AS note_ecrit,\r\n va_de_la_note_g AS valeur_ajoutee_note,\r\n nb_mentions_global_g AS nb_mentions,\r\n 'IVAC coll\u00e8ges' AS source_indicateur\r\n FROM \"fb7d465f-0b2e-4253-a72a-6c3a7b60ffe0\"\r\n WHERE code_departement = '62'\r\n AND TRIM(UPPER(commune)) IN (\r\n SELECT commune FROM communes_cua\r\n )\r\n),\r\n\r\nival_lycees AS (\r\n SELECT\r\n 'lycee_gt' AS type_etablissement,\r\n annee::integer AS annee,\r\n uai,\r\n etablissement AS nom_etablissement,\r\n commune,\r\n code_commune,\r\n code_departement,\r\n departement,\r\n academie,\r\n secteur,\r\n \"presents_-_toutes_series\" AS nb_candidats,\r\n \"taux_de_reussite_-_toutes_series\" AS taux_reussite,\r\n \"valeur_ajoutee_du_taux_de_reussite_-_toutes_series\" AS valeur_ajoutee_taux_reussite,\r\n taux_d_acces_2nde_bac AS taux_acces,\r\n NULL::numeric AS note_ecrit,\r\n NULL::numeric AS valeur_ajoutee_note,\r\n NULL::numeric AS nb_mentions,\r\n 'IVAL lyc\u00e9es GT' AS source_indicateur\r\n FROM \"fbe31dd6-5295-45e6-b15f-ad753814d221\"\r\n WHERE code_departement = '62'\r\n AND TRIM(UPPER(commune)) IN (\r\n SELECT commune FROM communes_cua\r\n )\r\n)\r\n\r\nSELECT *\r\nFROM ivac_colleges\r\n\r\nUNION ALL\r\n\r\nSELECT *\r\nFROM ival_lycees\r\n\r\nORDER BY commune, type_etablissement, nom_etablissement, annee;"} |