QUALIFY¶

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>
  [ ... ]

column_list

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

data_source

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, une UDTF (fonction de table définie par l’utilisateur), etc.

predicate

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

Copy

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

Copy

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

Copy

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

Copy

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

Copy

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

Copy