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

Paramètres¶

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

+-------------------------+
| number of rows inserted |
|-------------------------|
|                       4 |
+-------------------------+

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 le supprimant de la requête précédente et en comparant la sortie :

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 fonctions d’agrégation et des sous-requêtes dans le prédicat. Dans une telle requête, HAVING filtre les lignes après l’agrégation GROUP BY, tandis que QUALIFY filtre les lignes après le calcul des fonctions de fenêtre. Les deux clauses peuvent apparaître ensemble lorsqu’une requête nécessite les deux types de filtrage. Par exemple :

SELECT p, SUM(o) OVER (PARTITION BY p) AS r
  FROM qt
  WHERE o < 4
  GROUP BY p, o
  HAVING SUM(i) > 3
  QUALIFY r IN (
    SELECT MIN(i)
      FROM qt
      GROUP BY p
      HAVING MIN(i) > 3);