Filtrer efficacement les window functions avec QUALIFY dans Snowflake

La clause QUALIFY dans Snowflake, ou plus généralement en SQL, permet de filtrer à partir du résultat d'une window function. Une window function ne pouvant pas être utilisée directement dans la clause where, on passe par qualify dès lors qu'il faut filtrer sur une window function.

Petit rappel sur les window functions

Une window function est une fonction qui opère sur un ensemble de lignes par rapport à la ligne courante. On y a souvent recours pour calculer des rangs, des cumuls, des moyennes mobiles, des comptages par partition, etc. Dès qu'il faut faire référence aux lignes situées au-dessus ou en dessous de la ligne courante, une window function s'impose. Et la clause QUALIFY n'est qu'un raccourci pour filtrer sur une window function.

Syntaxe de QUALIFY

Voici la syntaxe de qualify :

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

Comme on peut le constater, qualify se place à la fin de la requête SQL, juste avant la clause optionnelle order by.

À partir des données ci-dessous, répondons à deux questions métier.

1. Qui est le meilleur commercial de chaque région, chaque jour ?

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 renvoie les lignes surlignées :

2. Qui est le meilleur commercial chaque jour ?

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 renvoie les lignes surlignées :

Astuce : la réutilisation d'alias fonctionne avec la clause QUALIFY. Si vous voulez réellement sélectionner la window function et pas seulement filtrer dessus, vous pouvez réutiliser son alias.

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

La clause qualify est le moyen le plus rapide d'identifier les enregistrements qui font échouer un test d'unicité dans dbt, ou d'isoler les doublons sur une combinaison de colonnes.

Supposons une colonne id testée pour son unicité, et le test échoue. Vous souhaitez analyser l'ensemble des doublons, regroupés entre eux.

Le moyen le plus rapide de sélectionner tous les enregistrements concernés par l'échec du test est le suivant :

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

Voyons d'autres façons de traiter la requête sur les doublons.

Option 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);

Option 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;

Il existe encore plusieurs autres approches pour répondre à la même question, mais aucune n'est aussi rapide et simple que la clause qualify.

À retenir : tout l'intérêt de la clause qualify est qu'elle évite de recourir à une CTE pour filtrer sur une window function. Les window functions ne pouvant pas être utilisées directement dans la clause where, une CTE serait indispensable pour les filtrer si qualify n'existait pas.

Nous avons déjà vu que qualify filtre une window function et ne peut pas être utilisé dans la clause WHERE. Examinons à présent de plus près l'ordre des opérations en SQL.

Voici l'ordre dans lequel Snowflake traite une instruction select :

Il faut noter que tout filtrage opéré dans les clauses from et where intervient avant celui de qualify. Les agrégations et les filtres d'agrégat (group by et having) le précèdent eux aussi.