Comment supprimer les doublons dans Postgres SQL

---

Possation croisée sur mon blog
Vous pouvez le lire ici

---

Notre schéma

create table "post" (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  content TEXT NOT NULL
);

create table "user" (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL
)

create table "post_like" (
  id SERIAL PRIMARY KEY,
  post_id INTEGER NOT NULL REFERENCES post(id),
  user_id INTEGER NOT NULL REFERENCES user(id)
)

Maintenant, nous voulons nous assurer que chaque utilisateur ne peut pas aimer la même publication plus d'une fois.
Cela peut être évité par :

• en utilisant une contrainte unique sur la paire de colonnes post_id user_id de la table post_like.
• ou supprimer la colonne id de la table post_like et utiliser une clé primaire composite sur post_id user_id

Mais, en supposant que nous en soyons à un point où les doublons sont déjà là, nous devons les supprimer.

Vérifiez s'il y a des doublons

select 
  post_id, 
  user_id,
  count(*)
from post_like
group by post_id, user_id
having count(*) > 2
;

| post_id | user_id | count |
| ------- | ------- | ----- |
| 3       | 2       | 2     |

Ce résultat nous indique que l'utilisateur 2 a aimé la publication 3 plus d'une fois, plus précisément 2 fois.

Supprimer les doublons

Maintenant que nous savons qu'il y a des doublons, nous pouvons les supprimer.

Nous divisons ce processus en deux étapes :

• lire les doublons
• supprimer les doublons (essai à sec)
• supprimer les doublons (véritable exécution)

Lire les doublons

Annulation des transactions

Pour tester nos requêtes sans supprimer les données réelles, jusqu'à ce que nous soyons sûrs que la requête est correcte, nous utilisons la fonction d'annulation de transaction.

En faisant cela, notre requête ne sera jamais validée, est similaire au
Concept de "dry run" que l'on retrouve sur d'autres applications (comme
rsync).

CTE

Nous utilisons CTE car il fournit un bon DX.

Avec CTE, nous pouvons exécuter une requête, stocker les résultats dans une table temporaire, puis utiliser la même table pour les requêtes suivantes.

Ce modèle mental est similaire à ce que nous faisons habituellement en codage en créant une variable temporaire.

La syntaxe CTE est

 with 
 <cte_name> as (
   <query>
 ),
 <cte_name_2> as (
   <query_2> -- here we can refernce <cte_name>
 )
 <final_query> -- here we can refernce <cte_name> and <cte_name_2>

Avec la transaction et le CTE, nous pouvons effectuer les opérations suivantes :

begin; -- start transaction

with
duplicates_info as (
  select
    row_number() over (
      partition by post_id, user_id order by user_id
    ) as group_index,
    id,
    post_id,
    user_id
  from post_like
)
select *
from duplicates_info
;

rollback; -- ends transaction discarding every changes to the database 

| group_index | id | post_id | user_id |
| ----------- | -- | ------- | ------- |
| 1           | 1  | 1       | 1       |
| 1           | 2  | 2       | 2       |
| 1           | 3  | 3       | 2       |
| 2           | 4  | 3       | 2       |

La dernière ligne de résultats, où group_index vaut 2, signifie que cette ligne est la deuxième du groupe avec post_id = 3 et user_id = 2.

Que se passe-t-il ici avec la syntaxe ?

row_number() over (partition by ...) as group_index est une fonction de fenêtre qui regroupe d'abord les lignes par colonnes dans la clause partition by, puis attribue un numéro à chaque ligne, en fonction de l'index de la ligne. dans le groupe.

la partition est similaire à group by, car elle regroupe les lignes par une colonne commune, mais si group by ne renvoie qu'une seule ligne pour chaque groupe, partitionnons ajoutons de nouvelles colonnes à la table source en fonction des groupes.

group_index est un alias de nom de colonne, syntaxe SQL normale.

Filtrer uniquement les doublons

Maintenant, gardons uniquement les éléments avec group_index > 1, ce qui signifie que la ligne n'est pas la première du groupe, ou en d'autres termes, c'est un doublon.

create table "post" (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  content TEXT NOT NULL
);

create table "user" (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL
)

create table "post_like" (
  id SERIAL PRIMARY KEY,
  post_id INTEGER NOT NULL REFERENCES post(id),
  user_id INTEGER NOT NULL REFERENCES user(id)
)

select 
  post_id, 
  user_id,
  count(*)
from post_like
group by post_id, user_id
having count(*) > 2
;

Nous devons supprimer uniquement cette ligne, avec l'identifiant 4.

Supprimer les doublons - essai à sec

Réécrivez maintenant la requête finale pour que nous lisions depuis la table post_like et non plus depuis le cte duplicates_info.
Nous utilisons toujours le cte duplicates_info pour obtenir l'identifiant des doublons.

| post_id | user_id | count |
| ------- | ------- | ----- |
| 3       | 2       | 2     |

Nous verrons les enregistrements que nous souhaitons supprimer.

Après avoir vérifié qu'ils sont corrects, nous échangeons select avec delete.

 with 
 <cte_name> as (
   <query>
 ),
 <cte_name_2> as (
   <query_2> -- here we can refernce <cte_name>
 )
 <final_query> -- here we can refernce <cte_name> and <cte_name_2>

Cette dernière requête est ce que nous voulons finalement exécuter.

Mais comme nous avons toujours une instruction rollback, ces modifications sont simulées et non appliquées à la base de données.

Supprimer les doublons - exécution réelle

Enfin nous pouvons supprimer les doublons pour de vrai.
Ici, nous utilisons commit au lieu de rollback, afin que les modifications soient appliquées à la base de données.

begin; -- start transaction

with
duplicates_info as (
  select
    row_number() over (
      partition by post_id, user_id order by user_id
    ) as group_index,
    id,
    post_id,
    user_id
  from post_like
)
select *
from duplicates_info
;

rollback; -- ends transaction discarding every changes to the database 

Code final

| group_index | id | post_id | user_id |
| ----------- | -- | ------- | ------- |
| 1           | 1  | 1       | 1       |
| 1           | 2  | 2       | 2       |
| 1           | 3  | 3       | 2       |
| 2           | 4  | 3       | 2       |

Conclusion

J'écris des articles principalement pour m'aider à mon avenir ou pour aider à développer les outils que j'utilise dans mon travail.

Si cet article vous a été utile, laissez un like.

Voulez-vous que je parle d'un sujet particulier ?

Dites-le-moi dans les commentaires !

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!