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!