Filtre Window Functions de Forma Eficiente com o QUALIFY do Snowflake

A cláusula qualify no Snowflake, ou em SQL no geral, permite filtrar com base no resultado de uma window function. Como uma window function não pode ser usada diretamente na cláusula where, use o qualify sempre que precisar filtrar por window functions.

Uma revisão rápida sobre window functions

Uma window function é uma função que opera sobre um conjunto de linhas em relação à linha atual. Costumam ser usadas para calcular rankings, totais acumulados, médias móveis, contagens por partição etc. Sempre que precisar referenciar linhas acima ou abaixo da linha atual, você vai precisar de uma window function. E a cláusula qualify é só um atalho para filtrar pelo resultado dela.

Sintaxe do QUALIFY

A sintaxe do qualify é a seguinte:

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

Repare que o qualify aparece no final da query SQL, logo antes da cláusula opcional order by.

Com base nos dados abaixo, vamos responder a duas perguntas de negócio.

1. Quem é o melhor vendedor em cada região, em cada dia?

select
	salesperson_id,
	region,
	sales_amount,
	sales_date
from sales
qualify row_number() over (partition by region, sales_date order by sales_amount desc) = 1

O Snowflake vai retornar as linhas destacadas:

2. Quem é o melhor vendedor em cada dia?

select
	salesperson_id,
	region,
	sales_amount,
	sales_date
from sales
qualify row_number() over (partition by sales_date order by sales_amount desc) = 1

O Snowflake vai retornar as linhas destacadas:

Dica rápida: o reuso de alias funciona com a cláusula qualify! Se você precisar selecionar a window function de fato, e não só filtrar por ela, pode reaproveitar o alias dela.

select
	salesperson_id,
	region,
	sales_amount,
	sales_date,
	row_number() over (partition by sales_date order by sales_amount desc) sales_date_rank
from sales
qualify sales_date_rank = 1

A cláusula qualify é o jeito mais rápido de descobrir quais registros estão derrubando um teste de unicidade no dbt, ou de isolar duplicatas a partir de uma combinação de colunas.

Imagine que você tem uma coluna id que é testada quanto à unicidade, e esse teste falhou. Você quer analisar todos os registros duplicados, com as duplicatas agrupadas.

A forma mais rápida de selecionar todos os registros do teste que falhou é esta:

select *
from my_table
qualify count(*) over (partition by id) > 1
order by id

Vamos ver outras formas de resolver a consulta de registros duplicados.

Opção 1:

-- use CTE with Group By instead
with duplicates as
(
	select id
	group by 1
	having count(*) > 1
)
select *
from
my_table join
duplicates using (id);

Opção 2

-- use CTE to create a window function, then filter on the window function
with window_count as
(
	select *, count(*) over (partition by id) as id_count
	from my_table
)
select * exclude (id_count)
from window_count
where id_count > 1;

Existem outras formas de resolver o mesmo problema, mas nenhuma é tão rápida e simples quanto usar a cláusula qualify.

Conclusão importante: o grande valor da cláusula qualify está em eliminar a necessidade de uma CTE para filtrar por uma window function. Como window functions não podem ser usadas diretamente na cláusula where, sem o qualify seria preciso recorrer a uma CTE para fazer essa filtragem.

Já vimos que o qualify filtra uma window function e não pode ser usado na cláusula where, mas vale aprofundar na ordem de operações do SQL.

Esta é a ordem em que o Snowflake processa o seu select:

Vale destacar que qualquer filtragem nas cláusulas from e where acontece antes da filtragem no qualify. Além disso, as agregações e a filtragem por agregação (group by e having) também vêm antes.