Kurs
QUALIFIZIEREN: Die SQL-Filteranweisung, von der du nie wusstest, dass du sie brauchst
Die Structured Query Language (SQL) ist eine der wichtigsten Programmiersprachen der Welt und wird häufig von Datenwissenschaftlern oder Dateningenieuren verwendet.
Da immer mehr Menschen ihr Geschäft auf Daten stützen, ist die Nachfrage nach SQL-Kenntnissen im Laufe der Jahre immer größer geworden. Zum Glück ist die Sprache leicht zu erlernen und gut etabliert, was bedeutet, dass es eine große Gemeinschaft von Nutzern gibt, die dich unterstützen, wenn du nicht weiterkommst.
In diesem Artikel werden wir eine spezielle Klausel der SQL-Sprache namens QUALIFY behandeln. Am Ende dieses Artikels wirst du es wissen:
- Das Grundkonzept und die Syntax für QUALIFY
- Wann wird die QUALIFY-Klausel verwendet?
- Wie sie sich von anderen Filtermethoden unterscheidet.
Grundkonzept und Syntax der SQL QUALIFY-Klausel
QUALIFY ist eine Klausel, mit der du die Ergebnisse einer Fensterfunktion filtern kannst. Um die QUALIFY-Klausel erfolgreich anwenden zu können, muss also mindestens eine WINDOW-Funktion in der SELECT-Liste oder der QUALIFY-Klausel enthalten sein - nur die Zeilen, in denen der boolesche Ausdruck TRUE ergibt, werden zurückgegeben.
Tipp: Schau dir das SQL Window Functions Cheat Sheet an.
Wenn es keine QUALIFY-Klausel gäbe, würde das Filtern der Ergebnisse einer WINDOW-Funktion eine Verschachtelung erfordern. SQL-Entwickler raten in der Regel davon ab, verschachtelte Abfragen so oft wie möglich zu verwenden, da sie den Code weniger gut lesbar machen und die Komplexität der Fehlersuche und Zusammenarbeit erhöhen.
Mit anderen Worten: Die Hauptidee hinter der QUALIFY-Klausel war es, Abfragen zu vereinfachen, bei denen das Ergebnis von Fensterfunktionen gefiltert werden muss; das bedeutet, dass QUALIFY ausgewertet wird, nachdem die Fensterfunktionen berechnet wurden.
Hier ist die typische Ausführungsreihenfolge für eine Abfrage mit einer QUALIFY-Anweisungsklausel:
- FROM
- WHERE
- GROUP BY und Aggregation
- HABEN
- FENSTER
- QUALIFY
- DISTINCT
- ORDER BY
- LIMIT
Die allgemeine Syntax einer QUALIFY-Anweisung lautet wie folgt:
QUALIFY <predicate>
Im Großen und Ganzen würde es etwa so aussehen:
SELECT <column_list>
FROM <data_source>
[GROUP BY ...]
[HAVING ...]
QUALIFY <predicate>
[ ... ]
Hinweis: <Prädikat> ist ein Ausdruck, der verwendet wird, um das Ergebnis zu filtern, nachdem die Aggregationen und Fensterfunktionen berechnet wurden.
Wann wird SQL QUALIFY verwendet?
Die QUALIFY-Anweisungsklausel ähnelt der HAVING-Klausel in dem Sinne, dass sie die Notwendigkeit einer Unterabfrage zur Filterung vermeidet. Du kannst QUALIFY zum Beispiel verwenden, um die Ergebnisse einer analytischen Funktion zu filtern - eine Funktion, mit der ein Gesamtwert auf der Grundlage einer Gruppe von Zeilen berechnet wird.
Um zu verdeutlichen, wann genau du QUALIFY verwenden würdest, ist hier ein kurzes Szenario:
Angenommen, du arbeitest als Datenwissenschaftler und ein Stakeholder fragt dich nach den letzten Anmeldedaten jedes Kunden. Deine erste Abfrage könnte etwa so aussehen:
SELECT
user_id,
ip,
country_code,
os,
RANK() over (
PARTITION BY user_id ORDER BY log_datetime DESC
) as previous_logins
FROM login_logs
WHERE TRUE
Das ist ein guter Anfang, erfüllt aber nicht die Anforderungen der Stakeholder, denn es werden alle Logins zurückgegeben und nicht nur der letzte. Um die letzte Anmeldung zurückzugeben, müssen wir einen Filter hinzufügen.
SQL-Neulinge können in die Falle tappen, wenn sie versuchen, dies mithilfe der WHERE-Anweisungsklausel zu filtern, wie folgt:
SELECT
user_id,
ip,
country_code,
os,
RANK() over (
PARTITION BY user_id ORDER BY log_datetime DESC
) AS previous_logins
FROM login_logs
WHERE TRUE
AND last_login = 1
Leider wird dieser Code nicht ausgeführt; dasselbe gilt, wenn du GROUP BY und HAVING versuchst.
Der Grund dafür liegt in der Reihenfolge der Operationen, die wir im vorherigen Abschnitt besprochen haben.
WHERE, GROUP BY und HAVING werden alle vor den WINDOW-Funktionen ausgewertet; das bedeutet, dass sie die WINDOW-Funktionen nicht filtern können, weil sie nicht wissen, dass sie existieren - die WINDOW-Funktion wurde zum Zeitpunkt der Ausführung dieser drei Klauseln noch nicht ausgewertet.
Eine Möglichkeit, den Filter nach der WINDOW-Funktion zu platzieren, ist die Verwendung einer Common Table Expression (CTE).
So wird unser Code aussehen:
WITH
logins AS (
SELECT
user_id,
ip,
country_code,
os,
RANK() OVER (
PARTITION BY user_id ORDER BY log_datetime DESC
) AS previous_logins
FROM login_logs
WHERE TRUE
)
SELECT user_id, ip, country_code, os
FROM logins
WHERE previous_logins = 1
Technisch gesehen ist dieser Code gültig - er wird einwandfrei ausgeführt und liefert die Ergebnisse, die dein Stakeholder sucht.
Allerdings mussten wir zwei Abfragen einführen, und es gibt einige zusätzliche Codezeilen, die überflüssig werden können, wenn du diesen Prozess regelmäßig wiederholst.
Die beste Abhilfe für dieses Problem ist die Verwendung von QUALIFY.
So wird der Code aussehen:
-- Starter code from @Jiho Choi on StackOverflow
SELECT
user_id,
ip,
country_code,
os,
RANK() over (
PARTITION BY user_id ORDER BY log_datetime DESC
) as previous_logins
FROM login_logs
WHERE TRUE
QUALIFY previous_logins = 1
Diese Lösung funktioniert, weil QUALIFY-Klauseln in der SQL-Operationsreihenfolge nach den WINDOW-Funktionen ausgewertet werden, d.h. sie wissen von ihrer Existenz und können sie in derselben Abfrage filtern.
Du fragst dich vielleicht, welche Vorteile QUALIFY außer dem Schreiben von weniger Codezeilen noch hat, und die Antwort ist, dass es eigentlich keine Leistungsvorteile gibt.
Die CTE-Lösung, die wir verwendet haben, und die QUALIFY-Lösung werden beide in einem ähnlichen Zeitrahmen ausgeführt, daher können wir nicht sagen, dass die QUALIFY-Lösung einen großen Leistungszuwachs bringt.
Der größte Vorteil ist die Verbesserung der Lebensqualität: Es gibt weniger Code und er ist leichter zu lesen.
Vergleich von SQL QUALIFY mit anderen Filtermethoden
Du weißt jetzt, dass die QUALIFY-Anweisungsklausel eine weitere Filtermethode in SQL ist. Du kennst auch den besten Zeitpunkt, um die QUALIFY-Klausel zu verwenden.
Aber was ist der Unterschied zwischen QUALIFY und den anderen Filtermethoden?
Fassen wir die einzelnen Filtermethoden zusammen.
Die WHERE-Klausel
WHERE wird verwendet, um Datensätze in einer Tabelle zu filtern; das heißt, es implementiert Zeilenoperationen. Wir verwenden sie, wenn wir die Datensätze extrahieren wollen, die eine bestimmte Bedingung erfüllen. Wir möchten zum Beispiel einen Datensatz nach dem Alter einer Person filtern. So wird es im Code aussehen:
SELECT name, gender, height, weight, age
FROM gym_members_info
WHERE age < 23;
Hier sind die wichtigsten Dinge, die du bei der WHERE-Klausel beachten solltest.
- Sie wird verwendet, um Datensätze auf der Grundlage einer bestimmten Bedingung zu filtern.
- Die Operationen werden auf den Zeilen ausgeführt.
- Du kannst die WHERE-Klausel ohne eine GROUP BY-Klausel verwenden
- WHERE kann mit SELECT-, UPDATE- und DELETE-Anweisungen verwendet werden.
Vergleichen wir dies mit der HAVING-Klausel.
Die HAVING-Klausel
HAVING wird verwendet, um Datensätze aus Gruppen zu filtern, die durch eine GROUP BY-Klausel auf der Grundlage einer bestimmten Bedingung definiert wurden. Der wichtigste Unterschied zwischen WHERE und HAVING ist also, dass HAVING eine GROUP BY-Klausel benötigt, um erfolgreich ausgewertet zu werden.
Der Hauptvorteil ist, dass du deine HAVING-Klausel auf eine Teilmenge von aggregierten Gruppen anwenden kannst, was in einem WHERE-Block nicht möglich ist.
Zum Beispiel:
SELECT gender, AVG(height)
FROM gym_members_info
GROUP BY gender
HAVING AVG(height) > 170
Der obige Code gibt die Geschlechtergruppen zurück, bei denen die durchschnittliche Körpergröße mehr als 170 Zentimeter beträgt; mit anderen Worten: HAVING implementiert eine Spaltenoperation.
Eine weitere Sache, die du beachten musst, ist, dass HAVING nur mit der SELECT-Anweisung verwendet werden kann, im Gegensatz zu WHERE.
Die QUALIFY-Klausel
QUALIFY ist das Äquivalent zu HAVING, mit dem Unterschied, dass es Filter auf die Ergebnisse von WINDOW-Funktionen anwendet, während HAVING Filter auf Aggregatfunktionen und GROUP BY-Klauseln anwendet - erfahre mehr über die Verwendung von GROUP BY.
Wenn wir Informationen über die größte Person in jeder Altersgruppe haben wollen, könnten wir eine WINDOW-Funktion verwenden, um die Berechnung durchzuführen, und eine QUALIFY-Klausel, um die Ergebnisse so zu filtern, dass wir nur die größte Person aus jeder Altersgruppe finden.
So würde es im Code aussehen:
SELECT name, gender, height, weight, age
RANK() over (
PARTITION BY age ORDER BY height DESC
) as ranked_ages
FROM gym_members_info
WHERE TRUE
QUALIFY ranked_ages = 1
Im Wesentlichen gibt die QUALIFY-Klausel einen bedingten Ausdruck an, der verwendet wird, um die Ausgabe einer geordneten analytischen Funktion zu filtern, die bereits nach den vom Benutzer festgelegten Kriterien berechnet wurde.
Schlussfolgerung und weitere Studien
SQL spielt im typischen Data-Science-Workflow eine große Rolle. Zu wissen, wie man nützliche Operationen in SQL implementiert, wie z.B. die QUALIFY-Klausel, ist äußerst hilfreich, da du dadurch saubereren Code schreiben kannst, was die Zusammenarbeit mit anderen angenehmer macht.
Wenn du deine SQL-Kenntnisse verbessern möchtest, empfehlen wir dir, die folgenden Ressourcen zu nutzen:
Werde Dateningenieur

Beginne deine SQL-Reise noch heute!
Kurs
SQL für Fortgeschrittene
Kurs