QUALIFY¶

Sintaxe¶

QUALIFY <predicate>

A forma geral de uma instrução com QUALIFY é semelhante à seguinte (algumas variações na ordem são permitidas, mas não são mostradas):

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

Parâmetros¶

column_list

Isso geralmente segue as regras da cláusula de projeção de uma instrução SELECT.

data_source

A fonte de dados é geralmente uma tabela, mas pode ser outra fonte de dados semelhante a uma tabela, como uma exibição, UDTF (função de tabela definida pelo usuário) etc.

predicate

O predicado é uma expressão que filtra o resultado depois que os agregados e as funções de janela são computados. O predicado deve ser semelhante a uma cláusula HAVING, mas sem a palavra-chave HAVING. Além disso, o predicado também pode conter funções de janela.

Consulte a seção Exemplos (neste tópico) para obter exemplos.

Notas de uso¶

• A cláusula QUALIFY exige que pelo menos uma função de janela seja especificada em pelo menos uma das seguintes cláusulas da instrução SELECT:

  • A lista de colunas SELECT.

  • O predicado do filtro da cláusula QUALIFY.

  Exemplos de cada um deles são mostrados na seção Exemplos abaixo.

• As expressões na lista SELECT, incluindo funções de janela, podem ser referidas pelo alias da coluna definido na lista SELECT.

• QUALIFY oferece suporte a agregados e subconsultas no predicado. Para os agregados, aplicam-se as mesmas regras que para a cláusula HAVING.

• A palavra QUALIFY é uma palavra reservada.

• A sintaxe Snowflake para QUALIFY não faz parte da norma ANSI.

Exemplos¶

A cláusula QUALIFY simplifica as consultas que exigem filtragem sobre o resultado das funções de janela. Sem QUALIFY, a filtragem exige um ninho. O exemplo abaixo usa a função ROW_NUMBER() para retornar apenas a primeira linha em cada partição.

Criar e carregar uma tabela:

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

Esta consulta utiliza o aninhamento em vez de 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 |
+---+---+---+---------+

Esta consulta utiliza 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 |
+---+---+---+

Você também pode usar QUALIFY para funções de janela de referência que estão na lista de colunas 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 |
+---+---+---+---------+

Você pode ver como o QUALIFY assume a função de um filtro removendo-o da consulta anterior e comparando a saída:

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

A cláusula QUALIFY também pode ser combinada com as funções de agregação e as subconsultas no predicado. Nesse tipo de consulta, HAVING filtra as linhas após a agregação de GROUP BY, enquanto QUALIFY filtra as linhas após a computação das funções de janela. Ambas as cláusulas podem aparecer juntas quando uma consulta exige os dois tipos de filtragem. Por exemplo:

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