Выражение QUALIFY

Выражение QUALIFY используется для фильтрации результатов оконных функций. Эта фильтрация результатов аналогична тому, как выражение HAVING фильтрует результаты агрегатных функций, применяемых в запросах с выражением GROUP BY.

Выражение QUALIFY позволяет избежать необходимости использования подзапроса или выражения с WITH для выполнения этой фильтрации (так же, как HAVING позволяет избегать подзапросов).

Синтаксис

QUALIFY <predicate>

Общая форма выражений с QUALIFY обычно выглядит так (допускаются некоторые вариации в порядке):

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

Параметры

  • <column_list>
    Список выражения SELECT.

  • <data_source>
    Источником данных обычно является таблица, но это может быть и другой источник данных, подобный таблице, например представление, пользовательская табличная функция и т.д.

  • <predicate>
    Предикат — это выражение, которое фильтрует результат после вычисления агрегатных и оконных функций. Предикат подобен выражению HAVING, но без самого ключевого слова HAVING. Кроме того, предикат может содержать оконные функции.

Примеры

Создадим и заполним таблицу:

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

В этом запросе используется вложенная структура, а не 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 |
+---+---+---+---------+

А в этом запросе используется 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 |
+---+---+---+

Используем QUALIFY для ссылки на оконные функции, которые находятся в выражении 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 |
+---+---+---+---------+

Выражение QUALIFY также может сочетаться с агрегатными функциями и может содержать подзапросы:

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