QUALIFIER : L'instruction de filtrage SQL dont vous ne saviez pas que vous aviez besoin

Concept de base et syntaxe de la clause SQL QUALIFY

QUALIFY est une clause utilisée pour filtrer les résultats d'une fonction de fenêtre. Par conséquent, pour utiliser avec succès la clause QUALIFY, il doit y avoir au moins une fonction WINDOW dans la liste SELECT ou la clause QUALIFY - seules les lignes pour lesquelles l'expression booléenne est évaluée à VRAI seront renvoyées.

Conseil: Consultez l'aide-mémoire sur les fonctions de fenêtre SQL.

S'il n'y avait pas de clause QUALIFY, le filtrage des résultats d'une fonction WINDOW nécessiterait une imbrication. Les développeurs SQL recommandent généralement de ne pas utiliser de requêtes imbriquées dans la mesure du possible, car elles rendent le code moins lisible par l'homme tout en augmentant la complexité du débogage et de la collaboration.

En d'autres termes, l'idée principale derrière la clause QUALIFY était de simplifier les requêtes qui nécessitent un filtrage sur le résultat des fonctions de fenêtre ; cela signifie que QUALIFY est évalué après que les fonctions de fenêtre sont calculées.

Voici l'ordre d'exécution typique d'une requête avec une clause d'instruction QUALIFY :

• DE
• OÙ
• GROUP BY et agrégation
• AVOIR
• FENÊTRE
• QUALIFIER
• DISTINCT
• ORDER BY
• LIMITE

La syntaxe générale d'une déclaration QUALIFY est la suivante :

QUALIFY <predicate>

Dans sa forme générale, il ressemblerait à quelque chose comme ceci :

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

Remarque: <prédicat> est une expression utilisée pour filtrer le résultat après le calcul des agrégations et des fonctions de fenêtre.

Quand utiliser SQL QUALIFY

La clause d'instruction QUALIFY ressemble beaucoup à la clause HAVING en ce sens qu'elle évite d'avoir recours à une sous-requête pour effectuer le filtrage. Par exemple, vous pouvez utiliser QUALIFY pour filtrer les résultats d'une fonction analytique - une fonction utilisée pour calculer une valeur agrégée basée sur un groupe de lignes.

Pour mieux comprendre quand vous devez utiliser QUALIFY, voici un petit scénario :

Supposons que vous travailliez en tant que scientifique des données et qu'une partie prenante vous demande les dernières informations de connexion de chaque client. Votre requête initiale peut ressembler à ceci :

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 

C'est un bon début, mais il ne répond pas aux demandes des parties prenantes, car il renvoie toutes les connexions et pas seulement la plus récente. Pour renvoyer la dernière connexion, nous devons ajouter un filtre.

Les novices en SQL peuvent tomber dans le piège de la tentative de filtrage à l'aide de la clause de l'instruction WHERE, comme suit :

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

Malheureusement, ce code ne s'exécutera pas ; il en va de même si vous essayez de GROUP BY et HAVING.

Cela s'explique par l'ordre des opérations dont nous avons parlé dans la section précédente.

WHERE, GROUP BY et HAVING sont tous évalués avant les fonctions WINDOW ; cela signifie qu'ils ne peuvent pas filtrer les fonctions WINDOW parce qu'ils ne savent pas qu'elles existent - la fonction WINDOW n'a pas été évaluée au moment où ces trois clauses sont exécutées.

Une façon de faire venir le filtre après la fonction WINDOW est d'utiliser une expression de tableau commune (CTE).

Voici à quoi ressemblera notre code :

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

Techniquement parlant, ce code est valide - il s'exécutera parfaitement et renverra les résultats recherchés par votre partie prenante.

Cependant, nous avons dû introduire deux requêtes, et il y a quelques lignes de code supplémentaires, qui peuvent devenir redondantes si vous répétez ce processus régulièrement.

Le meilleur moyen de résoudre ce problème est d'utiliser QUALIFY.

Voici à quoi ressemblera le code :

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

Cette solution fonctionne parce que les clauses QUALIFY sont évaluées après les fonctions WINDOW dans l'ordre des opérations SQL, ce qui signifie qu'elles sont conscientes de leur existence et qu'elles peuvent donc être filtrées dans la même requête.

Vous vous demandez peut-être quels sont les avantages de QUALIFY, à part le fait d'écrire moins de lignes de code, et la réponse est qu'il n'y a pas vraiment d'avantages en termes de performances.

La solution CTE que nous avons utilisée et la solution QUALIFY s'exécutent toutes les deux dans des délais similaires, nous ne pouvons donc pas dire que la solution QUALIFY apporte une amélioration de la performance.

Le principal avantage est l'amélioration de la qualité de vie ; il y a moins de code et il est plus facile à lire.

Comparaison de SQL QUALIFY avec d'autres méthodes de filtrage

Vous comprenez maintenant que la clause d'instruction QUALIFY est une autre méthode de filtrage disponible en SQL. Vous savez également quel est le meilleur moment pour utiliser la clause QUALIFY.

Mais quelle est la différence entre QUALIFY et les autres méthodes de filtrage ?

Récapitulons chaque méthode de filtrage.

La clause WHERE

WHERE est utilisé pour filtrer les enregistrements d'un tableau, ce qui signifie qu'il met en œuvre des opérations sur les lignes. Nous l'utilisons lorsque nous voulons extraire les enregistrements qui répondent à une condition spécifique. Par exemple, nous pouvons vouloir filtrer un ensemble de données en fonction de l'âge d'une personne. Voici comment cela se présentera dans le code :

SELECT name, gender, height, weight, age 
FROM gym_members_info 
WHERE age < 23; 

Voici les principaux points à noter concernant la clause WHERE.

1. Il est utilisé pour filtrer les enregistrements en fonction d'une condition spécifique.
2. Les opérations sont effectuées sur les lignes.
3. Vous pouvez utiliser la clause WHERE sans clause GROUP BY.
4. WHERE peut être utilisé avec les instructions SELECT, UPDATE, DELETE.

Comparons cela à la clause HAVING.

La clause HAVING

HAVING est utilisé pour filtrer les enregistrements des groupes définis par une clause GROUP BY en fonction d'une condition spécifique. Ainsi, la différence la plus évidente entre WHERE et HAVING est que HAVING nécessite la présence d'une clause GROUP BY pour être évaluée avec succès.

Le principal avantage est que vous pouvez appliquer votre clause HAVING à un sous-ensemble de groupes agrégés, ce qui n'est pas possible dans un bloc WHERE.

Par exemple :

SELECT gender, AVG(height) 
FROM gym_members_info
GROUP BY gender 
HAVING AVG(height) > 170 

Le code ci-dessus renvoie les groupes de sexe dont la taille moyenne est supérieure à 170 centimètres ; en d'autres termes, HAVING met en œuvre une opération en colonne.

Il faut également savoir que l'option HAVING ne peut être utilisée qu'avec l'instruction SELECT, contrairement à l'option WHERE.

La clause QUALIFY

QUALIFY est l'équivalent de HAVING, sauf qu'il effectue des filtres sur les résultats des fonctions WINDOW, alors que HAVING effectue des filtres sur les fonctions d'agrégation et les clauses GROUP BY - pour en savoir plus sur l'utilisation de GROUP BY.

Si nous voulions obtenir des informations sur la personne la plus grande dans chaque groupe d'âge, nous pourrions utiliser une fonction WINDOW pour effectuer le calcul et une clause QUALIFY pour filtrer les résultats afin de ne découvrir que la personne la plus grande de chaque groupe d'âge.

Voici ce que cela donnerait dans le code :

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

Essentiellement, la clause QUALIFY spécifie une expression conditionnelle utilisée pour filtrer la sortie d'une fonction analytique ordonnée qui a déjà été calculée conformément aux critères spécifiés par l'utilisateur.

Conclusion et poursuite de l'étude

SQL joue un rôle majeur dans le flux de travail typique de la science des données ; savoir comment mettre en œuvre des opérations utiles en SQL, comme la clause QUALIFY, est extrêmement utile car cela vous permet d'écrire un code plus propre, ce qui rend plus agréable la collaboration avec d'autres personnes.

Si vous souhaitez maîtriser vos compétences en SQL, nous vous recommandons de consulter les ressources ci-dessous :

• Aide-mémoire sur les bases de SQL
• Cours d'introduction à SQL
• Le cursus de compétences SQL Fundamentals