Hyperskill Tool — Encode & Decode

Generate a hyperskill

Source URL:

Skill content:

---
title: "Analyser les amendements"
description: "Rechercher et analyser les amendements à partir des tables du schéma `assemblee` de `canutes`."
tags: ["amendements", "cosignataires", "députés", "sql", "jsonb"]
---

La base unique `canutes` expose les schémas `assemblee`, `legifrance` et `senat`. Cette recette cible `assemblee`, en travaillant directement sur `assemblee.amendements` :

- une partie des filtres est disponible dans des colonnes dénormalisées (`texte_loi_uid`, `legislature`),
- les signataires députés disposent d'une table de jointure `deputes_signataires_amendements`,
- il n'existe pas de table relationnelle dédiée pour toutes les cosignatures, il faut donc combiner jointure et JSONB selon le besoin.

## 1. Rechercher les amendements d'un député

La façon la plus robuste pour les députés est d'utiliser la table de jointure `deputes_signataires_amendements`.

### Amendements dont il est auteur principal

```sql
SELECT
  a.uid,
  a.texte_loi_uid,
  a.legislature,
  a.data->'identification'->>'numeroLong' AS numero_long,
  a.data->'cycleDeVie'->>'dateDepot' AS date_depot,
  a.data->'cycleDeVie'->'etatDesTraitements'->'etat'->>'libelle' AS sort,
  a.data->'pointeurFragmentTexte'->'division'->>'articleDesignationCourte' AS article
FROM assemblee.deputes_signataires_amendements dsa
JOIN assemblee.amendements a ON a.uid = dsa.amendement_uid
WHERE dsa.depute_uid = 'PA722142'
  AND dsa.auteur = true
ORDER BY a.data->'cycleDeVie'->>'dateDepot' DESC;
```

### Amendements qu'il a signés, auteur compris

```sql
SELECT
  a.uid,
  dsa.auteur,
  a.data->'identification'->>'numeroLong' AS numero_long,
  a.data->'signataires'->'auteur'->>'acteurRef' AS auteur_uid,
  a.data->'cycleDeVie'->>'dateDepot' AS date_depot
FROM assemblee.deputes_signataires_amendements dsa
JOIN assemblee.amendements a ON a.uid = dsa.amendement_uid
WHERE dsa.depute_uid = 'PA722142'
ORDER BY a.data->'cycleDeVie'->>'dateDepot' DESC;
```

### Amendements cosignés mais non écrits

```sql
SELECT
  a.uid,
  a.data->'identification'->>'numeroLong' AS numero_long,
  a.data->'signataires'->'auteur'->>'acteurRef' AS auteur_uid,
  a.data->'cycleDeVie'->>'dateDepot' AS date_depot
FROM assemblee.deputes_signataires_amendements dsa
JOIN assemblee.amendements a ON a.uid = dsa.amendement_uid
WHERE dsa.depute_uid = 'PA722142'
  AND dsa.auteur = false
ORDER BY a.data->'cycleDeVie'->>'dateDepot' DESC;
```

## 2. Fallback JSONB sur les signataires

Pour des analyses ponctuelles ou pour préparer une future recette Sénat, on peut repartir du JSONB :

```sql
SELECT uid
FROM assemblee.amendements
WHERE data->'signataires'->'auteur'->>'acteurRef' = 'PA722142';
```

```sql
SELECT uid
FROM assemblee.amendements
WHERE data->'signataires'->'cosignatairesRefs' ? 'PA722142'
  AND data->'signataires'->'auteur'->>'acteurRef' <> 'PA722142';
```

## 3. Filtrer les amendements d'un texte législatif

La colonne `texte_loi_uid` est plus pratique que le JSON quand on part d'un document.

```sql
SELECT
  uid,
  data->'identification'->>'numeroLong' AS numero_long,
  data->'cycleDeVie'->>'dateDepot' AS date_depot,
  data->'cycleDeVie'->'etatDesTraitements'->'etat'->>'libelle' AS sort
FROM assemblee.amendements
WHERE texte_loi_uid = 'PRJLANR5L16B0760'
ORDER BY data->'cycleDeVie'->>'dateDepot' DESC;
```

## 4. Relier les amendements au document et au dossier

Le couple `amendements.texte_loi_uid -> documents.uid` permet de relier un amendement à son document et à son dossier.

```sql
SELECT
  a.uid,
  a.data->'identification'->>'numeroLong' AS numero_long,
  d.uid AS document_uid,
  d.dossier_uid,
  d.data->'titres'->>'titrePrincipal' AS titre_document
FROM assemblee.amendements a
LEFT JOIN assemblee.documents d ON d.uid = a.texte_loi_uid
WHERE a.uid = 'AMANR5L16PO420120B0760P0D1N001227';
```

## 5. Regrouper les amendements d'un acteur par dossier

```sql
SELECT
  d.dossier_uid,
  dos.data->'titreDossier'->>'titre' AS titre_dossier,
  COUNT(*) AS nb_amendements
FROM assemblee.deputes_signataires_amendements dsa
JOIN assemblee.amendements a ON a.uid = dsa.amendement_uid
LEFT JOIN assemblee.documents d ON d.uid = a.texte_loi_uid
LEFT JOIN assemblee.dossiers dos ON dos.uid = d.dossier_uid
WHERE dsa.depute_uid = 'PA722142'
GROUP BY d.dossier_uid, dos.data->'titreDossier'->>'titre'
ORDER BY nb_amendements DESC, titre_dossier NULLS LAST;
```

## 6. Rechercher les députés qui collaborent sur un amendement

Il n'y a pas de table `coSignatairesAmendement` complète, mais on peut lister les députés liés à un amendement :

```sql
SELECT
  dsa.amendement_uid,
  dsa.auteur,
  a.uid AS acteur_uid,
  a.nom_complet,
  a.depute_groupe_parlementaire_uid
FROM assemblee.deputes_signataires_amendements dsa
JOIN assemblee.acteurs a ON a.uid = dsa.depute_uid
WHERE dsa.amendement_uid = 'AMANR5L16PO420120B0760P0D1N001227'
ORDER BY dsa.auteur DESC, a.nom_complet;
```

Compress (gzip)

Hyperskill:

Decode a hyperskill

Paste a hyperskill:

Source URL:

Content: