Window-Funktionen mit Snowflake QUALIFY gezielt filtern

Mit der QUALIFY-Klausel lassen sich in Snowflake – und in SQL allgemein – Ergebnisse auf Basis einer Window-Funktion filtern. Da Window-Funktionen in der where-Klausel nicht direkt zulässig sind, kommt stattdessen qualify ins Spiel, sobald Sie auf das Ergebnis einer Window-Funktion filtern möchten.

Kurze Auffrischung zu Window-Funktionen

Eine Window-Funktion arbeitet auf einer Gruppe von Zeilen im Verhältnis zur aktuellen Zeile. Typische Einsatzgebiete sind Ranglisten, laufende Summen, gleitende Durchschnitte oder Zählungen pro Partition. Immer dann, wenn Sie auf Zeilen oberhalb oder unterhalb der aktuellen Zeile zugreifen müssen, brauchen Sie eine Window-Funktion. Und QUALIFY ist dabei schlicht die Abkürzung, um auf deren Ergebnis zu filtern.

QUALIFY-Syntax

Die Syntax von qualify sieht so aus:

SELECT <column_list>
FROM <data_source>
where <conditions>
[GROUP BY ...]
[HAVING ...]
QUALIFY  <predicate>
ORDER BY

Wie Sie sehen, steht qualify am Ende der SQL-Abfrage, direkt vor der optionalen order by-Klausel.

Anhand der folgenden Daten beantworten wir zwei typische Fragen aus dem Geschäftsalltag.

1. Wer ist pro Region und pro Tag der Top-Vertriebsmitarbeiter?

select
	salesperson_id,
	region,
	sales_amount,
	sales_date
from sales
qualify row_number() over (partition by region, sales_date order by sales_amount desc) = 1

Snowflake liefert die hervorgehobenen Zeilen:

2. Wer ist pro Tag der Top-Vertriebsmitarbeiter?

select
	salesperson_id,
	region,
	sales_amount,
	sales_date
from sales
qualify row_number() over (partition by sales_date order by sales_amount desc) = 1

Snowflake liefert die hervorgehobenen Zeilen:

Kleiner Tipp: In der QUALIFY-Klausel funktioniert die Wiederverwendung von Aliasen. Wenn Sie die Window-Funktion nicht nur zum Filtern brauchen, sondern auch in der Ausgabe sehen wollen, können Sie deren Alias direkt erneut verwenden.

select
	salesperson_id,
	region,
	sales_amount,
	sales_date,
	row_number() over (partition by sales_date order by sales_amount desc) sales_date_rank
from sales
qualify sales_date_rank = 1

Die qualify-Klausel ist der schnellste Weg, um herauszufinden, welche Datensätze einen Uniqueness-Test in dbt scheitern lassen, oder um Duplikate anhand einer Spaltenkombination zu isolieren.

Angenommen, Sie haben eine Spalte id, die auf Eindeutigkeit getestet wird, und der Test schlägt fehl. Nun möchten Sie alle doppelten Datensätze analysieren – idealerweise mit den Duplikaten direkt nebeneinander sortiert.

Der schnellste Weg, alle Datensätze des fehlgeschlagenen Tests zu selektieren, ist dieser:

select *
from my_table
qualify count(*) over (partition by id) > 1
order by id

Sehen wir uns alternative Wege an, dieselbe Abfrage nach Duplikaten zu lösen.

Variante 1:

-- use CTE with Group By instead
with duplicates as
(
	select id
	group by 1
	having count(*) > 1
)
select *
from
my_table join
duplicates using (id);

Variante 2:

-- use CTE to create a window function, then filter on the window function
with window_count as
(
	select *, count(*) over (partition by id) as id_count
	from my_table
)
select * exclude (id_count)
from window_count
where id_count > 1;

Es gibt noch weitere Lösungswege, doch keiner ist so schnell und elegant wie die qualify-Klausel.

Fazit: Der eigentliche Sinn von qualify ist, dass beim Filtern auf eine Window-Funktion kein CTE mehr nötig ist. Da Window-Funktionen in der where-Klausel nicht direkt zulässig sind, käme man ohne qualify nicht um ein CTE herum.

Dass qualify auf Window-Funktionen filtert und nicht in der WHERE-Klausel funktioniert, haben wir bereits geklärt. Werfen wir nun einen genaueren Blick auf die Reihenfolge der SQL-Operationen.

In dieser Reihenfolge verarbeitet Snowflake Ihr select-Statement:

Wichtig: Jegliches Filtern in from und where findet vor dem Filtern in qualify statt. Auch Aggregationen und das Filtern darauf (group by und having) laufen vorher ab.