Snowflake QUALIFIE pour les débutants : Qu'est-ce que c'est et comment l'utiliser ?

En tant que professionnel des données travaillant avec Snowflake, vous aurez peut-être besoin de filtrer les résultats des requêtes en fonction des fonctions des fenêtres. Traditionnellement, cela nécessite des sous-requêtes ou une logique de filtrage complexe. Cependant, Snowflake propose une alternative puissante : la clause QUALIFY. 

Dans ce guide, je vais vous expliquer comment utiliser QUALIFY pour rationaliser le filtrage, avec des exemples pas à pas et des bonnes pratiques.

Qu'est-ce que la clause QUALIFY dans Snowflake ?

La clause QUALIFY dans Snowflake est utilisée pour filtrer les résultats avec des fonctions de fenêtre. Vous pouvez définir les fonctions de fenêtre dans la liste SELECT ou les référencer directement dans la clause QUALIFY.

En règle générale, WHERE filtre les lignes brutes avant l'agrégation, tandis que HAVING filtre après l'agrégation GROUP BY.

Au lieu de cela, QUALIFY permet de filtrer les résultats d'instructions SQL telles que ROW_NUMBER(), RANK() et DENSE_RANK(). Cela signifie que nous pouvons l'utiliser dans nos fonctions de fenêtre pour les sorties et les filtrer immédiatement.

Vous pouvez considérer que QUALIFY fait pour les fonctions de fenêtre ce que HAVING fait pour les agrégats - filtrer le résultat après qu'il a été calculé.

Il existe plusieurs cas d'utilisation où la clause QUALIFY s'avère particulièrement utile : 

• Vous devez filtrer les résultats de la requête sur la base des sorties de la fonction de fenêtre.
• Vous souhaitez simplifier vos requêtes SQL en réduisant le nombre de sous-requêtes.
• Vous devez classer, dédupliquer ou segmenter efficacement les données au sein des partitions.
• Vous préparez un rapport ou un tableau de bord qui requiert les enregistrements les plus performants par catégorie.

> Si vous ne connaissez pas Snowflake, notre cours Introduction à Snowflake vous offre un point de départ pratique avant de plonger dans des clauses telles que QUALIFY. Vous pouvez également explorer les possibilités plus larges de Snowflake grâce à ce tutoriel destiné aux débutants.

Syntaxe de QUALIFY dans Snowflake

Passons en revue la syntaxe de base de QUALIFY afin que vous puissiez en comprendre les éléments. Voici à quoi peut ressembler une requête simple utilisant QUALIFY:

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

Vous disposez de la clause habituelle SELECT suivie de quelques colonnes. Vous devez ensuite écrire votre window_function() dans le format habituel pour obtenir une colonne appelée rank.

Il y a la déclaration habituelle FROM, qui nous indique le tableau, et enfin notre QUALIFY. Cette adresse QUALIFY est suivie de la "window_function_condition", qui est souvent une égalité et peut ressembler à quelque chose comme QUALIFY rank = 1

Note : Vous pouvez faire référence à l'alias d'une fonction window ou réécrire toute l'expression de la fonction window dans la clause QUALIFY. Les deux approches fonctionnent.

> Si vous avez besoin de revoir les fonctions des fenêtres, jetez un coup d'œil à cet aide-mémoire sur les fonctions des fenêtres. 

Exemples d'utilisation de QUALIFY dans Snowflake

Maintenant que vous connaissez la structure générale de QUALIFY, je vais vous présenter quelques exemples spécifiques utilisant cette clause.

Exemple 1 : Filtrage des N premières lignes par groupe

Supposons que nous voulions trouver les 3 employés les mieux payés dans chaque département. Voyons d'abord un exemple qui n'utilise pas QUALIFY, suivi d'une version plus concise qui l'utilise.

/* 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;

Comme vous pouvez le constater, l'exemple ci-dessus nécessite un CTE, mais avec QUALIFY, la requête est plus simple :

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

La requête ci-dessus attribue un rang à chaque employé au sein de son département et renvoie les 3 premiers de chaque département. Il utilise l'instruction QUALIFY pour filtrer immédiatement sans avoir recours à une sous-requête ou à un CTE. Ceci est particulièrement utile dans les tableaux de bord ou les API où il est important de minimiser la longueur des requêtes et le temps de réponse.

Exemple 2 : Filtrage des enregistrements en double

Si un tableau contient des enregistrements en double et que nous voulons conserver uniquement la première occurrence sur la base d'une fonction de classement, nous pouvons utiliser QUALIFY avec ROW_NUMBER():

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;

La colonne row_num est générée en partitionnant chaque client et en les triant par date de commande. Cela permet d'attribuer un numéro séquentiel à chaque ordre, en commençant par le plus ancien. En filtrant à l'aide de QUALIFY row_num = 1, nous nous assurons que seule la première commande de chaque client est conservée.

Cette technique est couramment utilisée dans les tâches de déduplication lors du nettoyage des données.

Exemple 3 : Filtrage basé sur la fonction de fenêtre dans les agrégats

Soyons un peu plus fantaisistes. L'utilisation de QUALIFY avec des fonctions de fenêtres agrégées permet un filtrage plus souple et plus dynamique.

Par exemple, si nous voulons trouver les employés dont les salaires sont supérieurs à la moyenne du département, nous pouvons utiliser QUALIFY avec AVG() sur une fonction de fenêtre :

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

Ce type de logique est très utile pour identifier les valeurs aberrantes ou les personnes les plus performantes au sein d'un groupe de pairs.

> Si vous souhaitez renforcer vos compétences en SQL pour effectuer des requêtes plus complexes comme celles-ci, consultez notre cours SQL intermédiaire.

Meilleures pratiques pour l'utilisation de QUALIFY dans Snowflake

Voici quelques bonnes pratiques et utilisations de la clause QUALIFY. 

Simplifier les requêtes complexes

La puissance de QUALIFY réside dans sa capacité à simplifier les requêtes en supprimant les sous-requêtes et les CTE supplémentaires. Appuyez-vous sur cette fonctionnalité. Utilisez-la chaque fois que vous écrivez une fonction de fenêtre et que vous avez besoin de filtrer sur la base de son résultat, sans créer de sous-requête.

Moins d'ETC signifie un débogage plus facile, une logique plus claire et une meilleure maintenabilité.

Combinez QUALIFY avec d'autres fonctions de la fenêtre

Vous pouvez combiner plusieurs fonctions de fenêtre pour affiner le filtrage. Par exemple, l'utilisation de ROW_NUMBER() avec RANK() pour résoudre les problèmes de classement tout en limitant les résultats :

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;

Cette requête renvoie les salariés qui se situent dans les 3 premiers rangs salariaux de leur département. Toutefois, si plus de trois employés sont à égalité dans ce top 3, seuls les trois premiers seront affichés, classés par numéro d'identification de l'employé.

Considérations sur les performances

Bien que QUALIFY simplifie les requêtes en termes de code écrit, certains aspects liés à la performance doivent être pris en compte.

Les fonctions de fenêtre peuvent être coûteuses en termes de calcul et les requêtes mal structurées peuvent devenir gourmandes en ressources. Veillez à garder ces concepts à l'esprit :

• Les fonctions de fenêtre peuvent s'avérer coûteuses en termes de calcul pour les grands ensembles de données, il convient donc de faire preuve de prudence quant à l'étape du processus à laquelle vous les utilisez.
• Veillez à ce que l'indexation et le partitionnement soient corrects afin d'optimiser les performances.
• Utilisez LIMIT en conjonction avec QUALIFY pour améliorer encore l'efficacité des tests.
• Utilisez l 'outil Query Profile de Snowflake pour identifier les goulets d'étranglement des performances dans les requêtes fenêtrées.

Dépannage des requêtes QUALIFY

Il peut être facile de tomber dans des pièges avec les déclarations QUALIFY, étant donné la complexité des fonctions des fenêtres. Voici quelques conseils pour vous aider à résoudre les problèmes les plus courants que vous pourriez rencontrer.

1. Utilisation des fonctions de fenêtre dans la clause WHERE

Si vous tentez d'utiliser des fonctions de fenêtre directement dans la clause WHERE, des erreurs se produiront car WHERE est évalué avant que les fonctions de fenêtre ne soient traitées.

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

Solution : Utilisez plutôt la clause QUALIFY, qui est évaluée après les fonctions de fenêtre.

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

2. Omettre le prédicat dans QUALIFY

La clause QUALIFY requiert un prédicat pour filtrer les résultats des fonctions de la fenêtre. L'omission du prédicat entraînera des erreurs.

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

Solution : Veillez à ce que la clause QUALIFY comprenne un prédicat, tel que = 1, pour filtrer la ligne la mieux classée.

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

3. Alias ambigus

L'utilisation du même alias pour une colonne et une fonction de fenêtre peut entraîner des confusions et des résultats inattendus.

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

Solution : Utilisez des alias distincts pour éviter toute ambiguïté.

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

4. Partitionnement ou ordonnancement incorrect

La spécification incorrecte des clauses PARTITION BY ou ORDER BY dans les fonctions de fenêtre peut entraîner des résultats inattendus.

Solution : Définissez avec soin le partitionnement et l'ordonnancement pour qu'ils correspondent à la logique souhaitée. Par exemple, pour obtenir le salaire le plus élevé par département :

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

Conclusion

La clause QUALIFY de Snowflake est un outil puissant pour filtrer les résultats sur la base des fonctions de fenêtre, ce qui réduit le besoin de sous-requêtes et rend les requêtes plus lisibles. En comprenant comment l'utiliser efficacement, les scientifiques des données peuvent simplifier leurs requêtes et améliorer leur efficacité lorsqu'ils travaillent avec des données classées, groupées ou agrégées.

Prêt à approfondir vos compétences en matière de Snowflake et de SQL ? Commencez par notre cours Introduction à Snowflake, affinez vos requêtes analytiques dans le cours PostgreSQL window functions, ou faites avancer votre carrière avec le parcours Associate Data Engineer in SQL!