QUALIFY (Beta) - Hologres: Central de Documentação do Alibaba Cloud

Avaliada após o cálculo de todas as window functions, a cláusula QUALIFY elimina a necessidade de subconsultas ou da cláusula WITH apenas para filtrar resultados de window functions.

As cláusulas em uma instrução SELECT seguem esta ordem de execução:

FROM
WHERE
GROUP BY
HAVING
WINDOW
QUALIFY
DISTINCT
ORDER BY
LIMIT

Sintaxe

QUALIFY <predicate>

Forma completa da instrução:

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

Parâmetros

Parâmetro	Descrição
column_list	Segue as regras da cláusula de projeção em uma instrução `SELECT`.
data_source	Tabela ou outra fonte tabular, como uma visualize.
predicate	Expressão que filtra resultados após o cálculo das funções de agregação e window functions. Estrutura-se como uma cláusula `HAVING`, mas sem a palavra-chave `HAVING`. Pode conter uma window function.

Notas de uso

QUALIFY exige pelo menos uma window function na column_list ou no predicate.
Referencie expressões da column_list, inclusive window functions, pelos aliases de coluna correspondentes.
QUALIFY aceita agregações e subconsultas no predicate. As regras de agregação seguem os mesmos padrões da cláusula HAVING.
QUALIFY é uma palavra-chave reservada.
A sintaxe do Snowflake para QUALIFY não faz parte do padrão ANSI.

Exemplos

Os exemplos a seguir usam a window function ROW_NUMBER() para retornar apenas a primeira linha de cada partição.

Crie primeiro a tabela de exemplo:

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

Sem QUALIFY: subconsulta aninhada

SELECT *
FROM (
    SELECT
        i,
        p,
        o,
        ROW_NUMBER() OVER (PARTITION BY p ORDER BY o) AS row_num
    FROM qt
)
WHERE row_num = 1;

Resultado:

+---+---+---+---------+
| I | P | O | ROW_NUM |
|---+---+---+---------|
| 1 | A | 1 |       1 |
| 3 | B | 1 |       1 |
+---+---+---+---------+

Com QUALIFY: window function no predicado

Defina a window function diretamente no predicado de QUALIFY:

SELECT
    i,
    p,
    o
FROM qt
QUALIFY ROW_NUMBER() OVER (PARTITION BY p ORDER BY o) = 1;

Resultado:

+---+---+---+
| I | P | O |
|---+---+---|
| 1 | A | 1 |
| 3 | B | 1 |
+---+---+---+

Com QUALIFY: alias de coluna no predicado

Defina a window function na column_list e referencie o alias no predicado de QUALIFY:

SELECT
    i,
    p,
    o,
    ROW_NUMBER() OVER (PARTITION BY p ORDER BY o) AS row_num
FROM qt
QUALIFY row_num = 1;

Resultado:

+---+---+---+---------+
| I | P | O | ROW_NUM |
|---+---+---+---------|
| 1 | A | 1 |       1 |
| 3 | B | 1 |       1 |
+---+---+---+---------+