Wie finde ich IP-Adressen in einer Tabelle, die in einer anderen Tabelle in PostgreSQL fehlen?

In diesem Artikel wird gezeigt, wie Sie IP-Adressen, die in einer login_log-Tabelle vorhanden sind, aber in einer ip_location-Tabelle in einer PostgreSQL-Datenbank fehlen, effizient identifizieren können. Es werden mehrere Ansätze vorgestellt, jeder mit seinen eigenen Stärken und Schwächen.

Identifizieren fehlender IP-Adressen

Das Kernproblem besteht darin, Diskrepanzen zwischen zwei Tabellen zu finden: login_log (enthält Anmeldeversuche mit IP-Adressen) und ip_location (enthält einen bekannten Satz von IP-Adressen). Ziel ist es, angemeldete IP-Adressen login_log zu extrahieren, die nicht in ip_location.

erfasst sind

Optimale Abfragemethoden

Mehrere SQL-Techniken können dies erreichen; Hier sind die effizientesten und am häufigsten verwendeten:

1. NOT EXISTSUnterabfrage:Im Allgemeinen die schnellste Methode in PostgreSQL.

<code class="language-sql">SELECT ip
FROM   login_log l
WHERE  NOT EXISTS (
    SELECT 1 -- SELECT list is irrelevant, 1 is efficient
    FROM   ip_location i
    WHERE  i.ip = l.ip
);</code>

Diese Abfrage prüft effizient, ob für jede IP in ip_location eine entsprechende IP in login_log vorhanden ist. Das SELECT 1 innerhalb der Unterabfrage optimiert die Leistung.

2. LEFT JOIN mit IS NULL: Eine prägnante und oft schnelle Alternative.

<code class="language-sql">SELECT l.ip
FROM   login_log l
LEFT   JOIN ip_location i USING (ip) -- Equivalent to ON i.ip = l.ip
WHERE  i.ip IS NULL;</code>

Dadurch wird ein Links-Join durchgeführt, bei dem alle Zeilen von login_log erhalten bleiben. IP-Adressen, die in ip_location fehlen, haben NULL in der Spalte i.ip, die dann durch die WHERE-Klausel gefiltert wird.

3. EXCEPT ALL: Eine kompakte Methode, aber möglicherweise weniger effizient für komplexe Abfragen.

<code class="language-sql">SELECT ip
FROM   login_log
EXCEPT ALL -- "ALL" preserves duplicates, potentially faster
SELECT ip
FROM   ip_location;</code>

Dieser Satzvorgang ermittelt direkt den Unterschied zwischen den beiden Sätzen von IP-Adressen. EXCEPT ALL ist vorzuziehen, da es schneller ist und doppelte IPs verarbeitet.

4. NOT IN (mit Vorsicht verwenden): Obwohl NOT IN funktionsfähig ist, kann es langsam sein, insbesondere bei großen Datensätzen. Es ist im Allgemeinen weniger effizient als die vorherigen Methoden.

<code class="language-sql">SELECT ip
FROM   login_log
WHERE  ip NOT IN (
    SELECT DISTINCT ip -- DISTINCT is optional but can improve performance slightly
    FROM   ip_location
);</code>

Dieser Ansatz prüft direkt, ob eine IP nicht in der Liste der IPs von ip_location vorhanden ist. Allerdings kann die Leistung bei größeren Datensätzen deutlich nachlassen.

Weiterführende Literatur:

Weitere Informationen zu diesen Techniken und zugehörigen PostgreSQL-Optimierungsstrategien finden Sie in diesen Ressourcen:

• Was ist in EXISTS-Unterabfragen einfacher zu lesen?
• Suchen Sie nach Datensätzen, für die keine Verknüpfung vorhanden ist
• Wählen Sie Zeilen aus, in denen der Wert der zweiten Spalte nicht in der ersten Spalte vorhanden ist

Das obige ist der detaillierte Inhalt vonWie finde ich IP-Adressen in einer Tabelle, die in einer anderen Tabelle in PostgreSQL fehlen?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!