Catégories :

Syntaxe de requête

QUALIFY

Dans une instruction SELECT, la clause QUALIFY filtre les résultats des fonctions de fenêtre.

QUALIFY fait avec les fonctions de fenêtre ce que HAVING fait avec les fonctions d’agrégation et les clauses GROUP BY.

Dans l’ordre d’exécution d’une requête, QUALIFY est donc évalué après le calcul des fonctions de fenêtre. En règle générale, les clauses d’une instruction SELECT sont évaluées dans l’ordre indiqué ci-dessous :

  1. From

  2. Where

  3. Group by

  4. Having

  5. Window

  6. QUALIFY

  7. Distinct

  8. Order by

  9. Limit

Syntaxe

QUALIFY <predicate>

La forme générale d’une instruction avec QUALIFY est similaire à celle-ci (certaines variations dans l’ordre sont autorisées mais ne sont pas affichées) :

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

Cela suit généralement les règles de la clause de projection d’une instruction SELECT.

source_données

La source de données est généralement une table, mais peut être une autre source de données semblable à une table, telle qu’une vue, UDTF, etc.

prédicat

Le prédicat est une expression qui filtre le résultat après le calcul des agrégations et des fonctions de fenêtre. Le prédicat doit ressembler à une clause HAVING, mais sans le mot clé HAVING. En outre, le prédicat peut également contenir des fonctions de fenêtre.

Consultez la section Exemples (dans cette rubrique) pour obtenir des exemples de prédicats.

Notes sur l’utilisation

  • La clause QUALIFY requiert la spécification d’au moins une fonction de fenêtre dans au moins l’une des clauses suivantes de l’instruction SELECT :

    • La liste de colonnes SELECT.

    • Le prédicat de filtre de la clause QUALIFY.

    Des exemples de chacun d’entre eux sont présentés dans la section Exemples ci-dessous.

  • Les expressions de la liste SELECT, y compris les fonctions de fenêtre, peuvent être référencées par l’alias de colonne défini dans la liste SELECT.

  • QUALIFY prend en charge les agrégations et les sous-requêtes dans le prédicat. Pour les agrégations, les mêmes règles que pour la clause HAVING s’appliquent.

  • Le mot QUALIFY est un mot réservé.

  • La syntaxe Snowflake pour QUALIFY ne fait pas partie de la norme ANSI.

Exemples

La clause QUALIFY simplifie les requêtes nécessitant un filtrage du résultat des fonctions de fenêtre. Sans QUALIFY, le filtrage nécessite une imbrication. L’exemple ci-dessous utilise la fonction ROW_NUMBER() pour renvoyer uniquement la première ligne de chaque partition.

Créer et charger une table :

CREATE TABLE qt (i INTEGER, p CHAR(1), o INTEGER);
INSERT INTO qt (i, p, o) VALUES
    (1, 'A', 1),
    (2, 'A', 2),
    (3, 'B', 1),
    (4, 'B', 2);

Cette requête utilise une imbrication plutôt que QUALIFY :

SELECT * 
    FROM (
         SELECT i, p, o, 
                ROW_NUMBER() OVER (PARTITION BY p ORDER BY o) AS row_num
            FROM qt
        )
    WHERE row_num = 1
    ;
+---+---+---+---------+
| I | P | O | ROW_NUM |
|---+---+---+---------|
| 1 | A | 1 |       1 |
| 3 | B | 1 |       1 |
+---+---+---+---------+

Cette requête utilise QUALIFY :

SELECT i, p, o
    FROM qt
    QUALIFY ROW_NUMBER() OVER (PARTITION BY p ORDER BY o) = 1
    ;
+---+---+---+
| I | P | O |
|---+---+---|
| 1 | A | 1 |
| 3 | B | 1 |
+---+---+---+

Vous pouvez également utiliser QUALIFY pour référencer les fonctions de fenêtre figurant dans la liste de colonnes SELECT :

SELECT i, p, o, ROW_NUMBER() OVER (PARTITION BY p ORDER BY o) AS row_num
    FROM qt
    QUALIFY row_num = 1
    ;
+---+---+---+---------+
| I | P | O | ROW_NUM |
|---+---+---+---------|
| 1 | A | 1 |       1 |
| 3 | B | 1 |       1 |
+---+---+---+---------+

Vous pouvez voir comment QUALIFY agit en tant que filtre en supprimant QUALIFY de la requête précédente et en comparant le résultat :

SELECT i, p, o, ROW_NUMBER() OVER (PARTITION BY p ORDER BY o) AS row_num
    FROM qt
    ;
+---+---+---+---------+
| I | P | O | ROW_NUM |
|---+---+---+---------|
| 1 | A | 1 |       1 |
| 2 | A | 2 |       2 |
| 3 | B | 1 |       1 |
| 4 | B | 2 |       2 |
+---+---+---+---------+

La clause QUALIFY peut également être combinée avec des agrégations et peut avoir des sous-requêtes dans le prédicat, par exemple :

SELECT c2, SUM(c3) OVER (PARTITION BY c2) as r
  FROM t1
  WHERE c3 < 4
  GROUP BY c2, c3
  HAVING SUM(c1) > 3
  QUALIFY r IN (
    SELECT MIN(c1)
      FROM test
      GROUP BY c2
      HAVING MIN(c1) > 3);