Snowflake QUALIFY für Anfänger/innen: Was es ist und wie man es benutzt

Als Datenexperte, der mit Snowflake arbeitet, musst du vielleicht Abfrageergebnisse anhand von Fensterfunktionen filtern. Traditionell erfordert dies Unterabfragen oder eine komplexe Filterlogik. Snowflake bietet jedoch eine leistungsstarke Alternative: die QUALIFY Klausel. 

In diesem Leitfaden erkläre ich dir, wie du mit QUALIFY das Filtern optimieren kannst, mit Schritt-für-Schritt-Beispielen und Best Practices.

Was ist die QUALIFY-Klausel in Snowflake?

Die QUALIFY Klausel in Snowflake wird verwendet, um Ergebnisse mit Fensterfunktionen zu filtern. Du kannst Fensterfunktionen entweder als Teil der SELECT Liste definieren oder sie direkt in der QUALIFY Klausel referenzieren.

Normalerweise filtert WHERE rohe Zeilen vor der Aggregation, während HAVING nach einer GROUP BY Aggregation filtert.

Stattdessen ermöglicht QUALIFY das Filtern der Ergebnisse von SQL-Anweisungen wie ROW_NUMBER(), RANK() und DENSE_RANK(). Das bedeutet, dass wir sie in unseren Fensterfunktionen für Ausgaben verwenden und sofort darauf filtern können.

Du kannst dir vorstellen, dass QUALIFY für Fensterfunktionen das tut, was HAVING für Aggregate tut - nämlich das Ergebnis zu filtern, nachdem es berechnet worden ist.

Es gibt mehrere Anwendungsfälle, in denen sich die QUALIFY Klausel als besonders nützlich erweist: 

• Du musst die Abfrageergebnisse nach den Ausgaben der Fensterfunktionen filtern.
• Du möchtest deine SQL-Abfragen vereinfachen, indem du die Notwendigkeit von Unterabfragen reduzierst.
• Du musst Daten innerhalb von Partitionen effizient ordnen, deduplizieren oder segmentieren.
• Du bereitest einen Bericht oder ein Dashboard vor, für das du die leistungsstärksten Datensätze pro Kategorie benötigst.

> Wenn du Snowflake noch nicht kennst, bietet dir unser Kurs Einführung in Snowflake einen praktischen Einstieg, bevor du dich mit Klauseln wie QUALIFY beschäftigst. Du kannst auch die breiteren Möglichkeiten von Snowflake mit diesem anfängerfreundlichen Tutorial erkunden.

Syntax von QUALIFY in Snowflake

Schauen wir uns die grundlegende Syntax von QUALIFY an, damit du ihre Bestandteile verstehst. So könnte eine einfache Abfrage mit QUALIFY aussehen:

SELECT
 column1,
 column2, 
 window_function() OVER (PARTITION BY column3 ORDER BY column4) AS rank
FROM table_name
QUALIFY window_function_condition;Bereitgestellt von

Du hast deine übliche SELECT Klausel, gefolgt von ein paar Spalten. Dann lässt du deine window_function() im üblichen Format schreiben, damit wir eine Spalte namens rank erhalten.

Es gibt die übliche FROM Anweisung, die uns die Tabelle angibt, und schließlich unsere QUALIFY. Auf diese QUALIFY folgt die "window_function_condition", die oft eine Gleichheit ist und etwa so aussehen kann QUALIFY rank = 1

Hinweis: Du kannst entweder auf den Alias einer Fensterfunktion verweisen oder den gesamten Ausdruck der Fensterfunktion innerhalb der QUALIFY Klausel umschreiben. Beide Ansätze funktionieren.

> Wenn du einen Überblick über die Fensterfunktionen brauchst, wirf einen Blick auf diesen Spickzettel für Fensterfunktionen. 

Beispiele für die Verwendung von QUALIFY in Snowflake

Jetzt, wo du die allgemeine Struktur von QUALIFY kennst, werde ich dir einige konkrete Beispiele für diese Klausel zeigen.

Beispiel 1: Filtern der obersten N Zeilen pro Gruppe

Angenommen, wir wollen die 3 bestbezahlten Mitarbeiter in jeder Abteilung finden. Schauen wir uns zunächst ein Beispiel an, das nicht QUALIFY verwendet, gefolgt von einer kürzeren Version, die dies tut.

/* First we have to rank all the employees */
WITH ranked_employees AS (
SELECT employee_id, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees)

/* Then we query to subquery and filter using WHERE */
SELECT *
FROM ranked_employees
WHERE rank <= 3;Bereitgestellt von

Wie du siehst, erfordert das obige Beispiel eine CTE, aber mit QUALIFY ist die Abfrage viel einfacher:

SELECT employee_id, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees
QUALIFY rank <= 3;Bereitgestellt von

Die obige Abfrage weist jedem Mitarbeiter innerhalb seiner Abteilung einen Rang zu und gibt die Top 3 in jeder Abteilung zurück. Sie verwendet die Anweisung QUALIFY, um sofort zu filtern, ohne dass eine Subquery oder CTE erforderlich ist. Das ist vor allem bei Dashboards oder APIs nützlich, bei denen die Minimierung der Abfragelänge und der Antwortzeit wichtig ist.

Beispiel 2: Filtern von doppelten Datensätzen

Wenn eine Tabelle doppelte Datensätze enthält und wir nur das erste Vorkommen auf der Grundlage einer Rangfolgefunktion behalten wollen, können wir QUALIFY mit ROW_NUMBER() verwenden:

SELECT 
order_id, 
customer_id, 
order_date,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS row_num
FROM orders
QUALIFY row_num = 1;Bereitgestellt von

Die Spalte row_num wird durch Partitionierung auf jeden Kunden und Sortierung nach Bestelldatum erstellt. Dadurch wird jeder Bestellung eine fortlaufende Nummer zugewiesen, beginnend mit der frühesten. Durch das Filtern mit QUALIFY row_num = 1 stellen wir sicher, dass nur die früheste Bestellung für jeden Kunden gespeichert wird.

Diese Technik wird häufig bei Deduplizierungsaufgaben während der Datenbereinigung eingesetzt.

Beispiel 3: Filterung auf Basis der Fensterfunktion in Aggregaten

Lass uns ein bisschen ausgefallener werden. Die Verwendung von QUALIFY mit aggregierten Fensterfunktionen ermöglicht eine flexiblere und dynamischere Filterung.

Wenn wir zum Beispiel die Beschäftigten finden wollen, deren Gehälter über dem Durchschnitt der Abteilung liegen, können wir QUALIFY mit AVG() über eine Fensterfunktion verwenden:

SELECT 
employee_id, 
department, 
salary,
AVG(salary) OVER (PARTITION BY department) AS avg_salary
FROM employees
QUALIFY salary > avg_salary;Bereitgestellt von

Diese Art von Logik eignet sich hervorragend, um Ausreißer oder Top-Performer innerhalb von Vergleichsgruppen zu identifizieren.

> Wenn du deine SQL-Kenntnisse für komplexere Abfragen wie diese vertiefen möchtest, schau dir unseren SQL-Kurs für Fortgeschrittene an.

Best Practices für die Verwendung von QUALIFY in Snowflake

Hier sind einige bewährte Verfahren und Verwendungszwecke für eine QUALIFY Klausel. 

Vereinfachen Sie komplexe Abfragen

Die Stärke von QUALIFY liegt in seiner Fähigkeit, Abfragen zu vereinfachen, indem es zusätzliche Unterabfragen und CTEs überflüssig macht. Lehn dich an diese Funktion an. Verwende sie immer dann, wenn du eine Fensterfunktion schreibst und auf der Grundlage ihrer Ausgabe filtern musst, ohne eine Unterabfrage zu erstellen.

Weniger CTEs bedeuten einfacheres Debugging, klarere Logik und bessere Wartbarkeit.

Kombiniere QUALIFY mit anderen Fensterfunktionen

Du kannst mehrere Fensterfunktionen kombinieren, um die Filterung zu verfeinern. Zum Beispiel, indem du ROW_NUMBER() zusammen mit RANK() verwendest, um Gleichstände im Ranking aufzulösen und gleichzeitig die Ergebnisse einzuschränken:

SELECT 
employee_id, 
department, 
salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC, employee_id) AS row_num
FROM employees
QUALIFY rank <= 3 AND row_num <= 3;Bereitgestellt von

Diese Abfrage liefert Mitarbeiter, die zu den 3 besten Gehaltsgruppen in ihrer Abteilung gehören. Wenn jedoch mehr als drei Beschäftigte innerhalb dieser Top 3 gleich sind, werden nur die ersten drei, geordnet nach der Beschäftigten-ID, angezeigt.

Überlegungen zur Leistung

Auch wenn QUALIFY die Abfragen im Hinblick auf den geschriebenen Code vereinfacht, sollten einige Leistungsaspekte berücksichtigt werden.

Fensterfunktionen können rechenintensiv sein, und schlecht strukturierte Abfragen können ressourcenintensiv werden. Achte darauf, dass du diese Konzepte im Hinterkopf behältst:

• Fensterfunktionen können bei großen Datensätzen sehr rechenintensiv sein. Deshalb solltest du genau überlegen, in welcher Phase deines Prozesses du sie einsetzt.
• Sorge für die richtige Indizierung und Partitionierung, um die Leistung zu optimieren.
• Nutze LIMIT in Verbindung mit QUALIFY für noch mehr Effizienz beim Testen.
• Nutze das Abfrageprofil von Snowflake, um Leistungsengpässe bei Abfragen mit Fenstern zu erkennen.

Fehlersuche bei QUALIFY-Abfragen

Angesichts der Komplexität der Fensterfunktionen kann es bei den QUALIFY Anweisungen leicht zu Fallstricken kommen. Hier sind einige Tipps, die dir bei den häufigsten Problemen helfen können.

1. Verwendung von Fensterfunktionen in der WHERE-Klausel

Der Versuch, Fensterfunktionen direkt in der WHERE Klausel zu verwenden, führt zu Fehlern, da WHERE ausgewertet wird, bevor Fensterfunktionen verarbeitet werden.

-- This will cause an error
SELECT employee_id, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees
WHERE rank = 1;Bereitgestellt von

Lösung: Verwende stattdessen die QUALIFY Klausel, die nach den Fensterfunktionen ausgewertet wird.

SELECT employee_id, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees
QUALIFY rank = 1;Bereitgestellt von

2. Weglassen des Prädikats in QUALIFY

Die QUALIFY Klausel erfordert ein Prädikat, um die Ergebnisse der Fensterfunktionen zu filtern. Wenn du das Prädikat weglässt, führt das zu Fehlern.

-- This will cause an error
SELECT employee_id, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees
QUALIFY RANK() OVER (PARTITION BY department ORDER BY salary DESC);Bereitgestellt von

Lösung: Vergewissere dich, dass die QUALIFY Klausel ein Prädikat enthält, z.B. = 1, um nach der ranghöchsten Zeile zu filtern.

SELECT employee_id, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees
QUALIFY rank = 1;Bereitgestellt von

3. Zweideutige Aliasnamen

Die Verwendung desselben Alias für eine Spalte und eine Fensterfunktion kann zu Verwirrung und unerwarteten Ergebnissen führen.

-- Potentially ambiguous
SELECT salary AS rank,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees
QUALIFY rank = 1;Bereitgestellt von

Lösung: Verwende eindeutige Aliasnamen, um Mehrdeutigkeiten zu vermeiden.

SELECT salary AS salary_amount,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM employees
QUALIFY salary_rank = 1;Bereitgestellt von

4. Falsche Aufteilung oder Reihenfolge

Wenn du die Klauseln PARTITION BY oder ORDER BY in Fensterfunktionen falsch angibst, kann das zu unerwarteten Ergebnissen führen.

Lösung: Lege die Aufteilung und Reihenfolge sorgfältig fest, damit sie der gewünschten Logik entspricht. Zum Beispiel, um das höchste Gehalt pro Abteilung zu erhalten:

SELECT employee_id, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees
QUALIFY rank = 1;Bereitgestellt von

Fazit

Die QUALIFY Klausel in Snowflake ist ein leistungsfähiges Werkzeug, um Ergebnisse auf der Grundlage von Fensterfunktionen zu filtern, den Bedarf an Unterabfragen zu verringern und Abfragen lesbarer zu machen. Wenn du weißt, wie man sie effektiv einsetzt, können Datenwissenschaftler ihre Abfragen vereinfachen und die Effizienz bei der Arbeit mit geordneten, gruppierten oder aggregierten Daten verbessern.

Bist du bereit, deine Snowflake- und SQL-Kenntnisse zu vertiefen? Beginne mit unserem Kurs "Einführung in Snowflake", verbessere deine analytischen Abfragen im Kurs "PostgreSQL-Fensterfunktionen" oder steigere deine Karriere mit dem Kurs "Associate Data Engineer in SQL"!