main.deputes_l17 vue

Colonnes

Définition

CREATE VIEW deputes_l17 AS WITH mandats AS (SELECT * FROM amo30.acteur_mandat WHERE ((_legislature = 17) AND (type_organe = 'ASSEMBLEE') AND (legislature = 17))), sieges AS (SELECT uid AS mandat_uid, lag(uid) OVER (PARTITION BY election_lieu_num_departement, election_lieu_num_circo ORDER BY mandature_date_prise_fonction, date_debut) AS predecesseur_mandat_uid FROM mandats), groupes AS (SELECT m.acteur_ref, m.date_debut, m.date_fin, m.infos_qualite_lib_qualite AS lib_qualite, o.uid AS groupe_uid, o.libelle, o.libelle_abrege, o.couleur_associee AS couleur FROM amo30.acteur_mandat AS m INNER JOIN amo30.organe AS o ON (((o._legislature = 17) AND (o.uid = m.organes_organe_ref[1]))) WHERE ((m._legislature = 17) AND (m.type_organe = 'GP') AND (m.legislature = 17))), emails AS (SELECT _racine_id, "first"(val_elec ORDER BY _ordre) AS email FROM amo30.acteur_adresse WHERE ((_legislature = 17) AND (xsi_type = 'AdresseMail_Type') AND (val_elec IS NOT NULL)) GROUP BY ALL)SELECT m.uid AS mandat_uid, a.uid AS acteur_uid, a.etat_civil_ident_civ AS civilite, a.etat_civil_ident_prenom AS prenom, a.etat_civil_ident_nom AS nom, a.etat_civil_ident_alpha AS nom_alpha, a.etat_civil_info_naissance_date_nais AS date_naissance, a.profession_libelle_courant AS profession, m.election_lieu_region AS region, m.election_lieu_departement AS departement, m.election_lieu_num_departement AS num_departement, m.election_lieu_num_circo AS num_circo, m.election_ref_circonscription AS circonscription_uid, m.date_debut, m.mandature_date_prise_fonction AS date_prise_fonction, m.date_fin, m.election_cause_mandat AS cause_debut, m.mandature_cause_fin AS cause_fin, (m.mandature_premiere_election = 1) AS premiere_election, m.mandature_mandat_remplace_ref AS mandat_remplace_uid, s.predecesseur_mandat_uid, m.mandature_place_hemicycle AS place_hemicycle, g.groupe_uid, g.libelle AS groupe, g.libelle_abrege AS groupe_abrege, g.couleur AS groupe_couleur, g.lib_qualite AS groupe_qualite, e.email, ('https://www.assemblee-nationale.fr/dyn/deputes/' || a.uid) AS url_an, a.uri_hatvp FROM mandats AS m INNER JOIN amo30.acteur AS a ON ((a._id = m._racine_id)) INNER JOIN sieges AS s ON ((s.mandat_uid = m.uid)) LEFT JOIN emails AS e ON ((e._racine_id = a._id)) LEFT JOIN groupes AS g ON (((g.acteur_ref = m.acteur_ref) AND (g.date_debut <= COALESCE(m.date_fin, CAST('9999-12-31' AS DATE))) AND (COALESCE(g.date_fin, CAST('9999-12-31' AS DATE)) >= COALESCE(m.mandature_date_prise_fonction, m.date_debut)))) QUALIFY (row_number() OVER (PARTITION BY m.uid ORDER BY g.date_debut DESC NULLS LAST) = 1);

Aperçu