So entfernen Sie Duplikate in Postgres SQL

---

Crossposting auf meinem Blog
Sie können es hier lesen

---

Unser Schema

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)
)

Jetzt möchten wir sicherstellen, dass nicht jeder Benutzer denselben Beitrag mehr als einmal liken kann.
Dies kann verhindert werden durch:

• Verwendung einer eindeutigen Einschränkung für das Paar post_id user_id-Spalten der post_like-Tabelle.
• Oder Entfernen der ID-Spalte der post_like-Tabelle und Verwenden eines zusammengesetzten Primärschlüssels für post_id user_id

Aber wenn wir an einem Punkt angelangt sind, an dem bereits Duplikate vorhanden sind, müssen wir sie entfernen.

Überprüfen Sie, ob Duplikate vorhanden sind

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     |

Diese Ausgabe sagt uns, dass Benutzer 2 Beitrag 3 mehr als einmal, nämlich 2 Mal, mit „Gefällt mir“ markiert hat.

Duplikate entfernen

Da wir nun wissen, dass es Duplikate gibt, können wir sie entfernen.

Wir teilen diesen Prozess in zwei Schritte auf:

• Duplikate lesen
• Duplikate entfernen (Probelauf)
• Duplikate entfernen (echter Durchlauf)

Duplikate lesen

Transaktions-Rollback

Um unsere Abfragen zu testen, ohne echte Daten zu entfernen, verwenden wir die Transaktions-Rollback-Funktion, bis wir sicher sind, dass die Abfrage korrekt ist.

Auf diese Weise wird unsere Abfrage niemals festgeschrieben, ähnlich wie
„Trockenlauf“-Konzept, das Sie in anderen Anwendungen finden können (wie
rsync).

CTE

Wir verwenden CTE, weil es einen guten DX bietet.

Mit CTE können wir eine Abfrage ausführen, die Ergebnisse in einer temporären Tabelle speichern und dann dieselbe Tabelle für nachfolgende Abfragen verwenden.

Dieses mentale Modell ähnelt dem, was wir normalerweise beim Codieren tun, indem wir eine temporäre Variable erstellen.

Die CTE-Syntax lautet

 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>

Sowohl mit Transaktion als auch mit CTE können wir Folgendes tun:

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       |

Die neueste Ergebniszeile, bei der der Gruppenindex 2 ist, bedeutet, dass diese Zeile die zweite in der Gruppe mit post_id = 3 und user_id = 2 ist.

Was passiert hier mit der Syntax?

row_number() over (partition by ...) as group_index ist eine Fensterfunktion, die zunächst Zeilen nach den Spalten in der Partition by-Klausel gruppiert und dann jeder Zeile basierend auf dem Index der Zeile eine Nummer zuweist in der Gruppe.

Partition ähnelt „Gruppieren nach“, da die Zeilen nach einer gemeinsamen Spalte gruppiert werden. Wenn „Gruppieren nach“ jedoch nur eine Zeile für jede Gruppe zurückgibt, können wir mit der Partition neue Spalten basierend auf Gruppen zur Quelltabelle hinzufügen.

group_index ist ein Spaltennamen-Alias, reguläre SQL-Syntax.

Nur ​​Duplikate filtern

Jetzt behalten wir nur Elemente mit Gruppenindex > 1, was bedeutet, dass die Zeile nicht die erste in der Gruppe ist, oder mit anderen Worten, es handelt sich um ein Duplikat.

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
;

Wir müssen nur diese Zeile mit der ID 4 entfernen.

Duplikate entfernen – Probelauf

Jetzt formulieren Sie die letzte Abfrage um, sodass wir aus der post_like-Tabelle lesen und nicht mehr aus der cte-duplicates_info.
Wir verwenden immer noch die cte-duplicates_info, um die ID der Duplikate zu erhalten.

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

Wir sehen die Datensätze, die wir entfernen möchten.

Nachdem wir überprüft haben, dass sie korrekt sind, tauschen wir select mit 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>

Diese letzte Abfrage wollen wir endlich ausführen.

Da wir jedoch immer noch eine Rollback-Anweisung haben, werden diese Änderungen simuliert und nicht auf die Datenbank angewendet.

Duplikate entfernen – Echtlauf

Endlich können wir die Duplikate wirklich entfernen.
Hier verwenden wir Commit statt Rollback, damit die Änderungen auf die Datenbank angewendet werden.

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 

Endgültiger Code

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

Abschluss

Ich schreibe Artikel hauptsächlich, um meiner Zukunft zu helfen oder um das Wachstum der Werkzeuge zu unterstützen, die ich in meiner Arbeit verwende.

Wenn dieser Artikel für Sie hilfreich war, hinterlassen Sie ein Like.

Möchten Sie, dass ich über ein bestimmtes Thema spreche?

Sag es mir in den Kommentaren!

Das obige ist der detaillierte Inhalt vonSo entfernen Sie Duplikate in Postgres SQL. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!