Snowflake QUALIFY para iniciantes: O que é e como usá-lo

Como um profissional de dados que trabalha com o Snowflake, você pode precisar filtrar os resultados da consulta com base em funções de janela. Tradicionalmente, isso requer subconsultas ou lógica de filtragem complexa. No entanto, o Snowflake oferece uma alternativa poderosa: a cláusula QUALIFY. 

Neste guia, explicarei como você pode usar o QUALIFY para otimizar a filtragem, com exemplos passo a passo e práticas recomendadas.

O que é a cláusula QUALIFY no Snowflake?

A cláusula QUALIFY no Snowflake é usada para filtrar resultados com funções de janela. Você pode definir funções de janela como parte da lista SELECT ou fazer referência a elas diretamente na cláusula QUALIFY.

Normalmente, o WHERE filtra as linhas brutas antes da agregação, enquanto o HAVING filtra após uma agregação do GROUP BY.

Em vez disso, o QUALIFY permite filtrar os resultados de instruções SQL como ROW_NUMBER(), RANK() e DENSE_RANK(). Isso significa que podemos utilizá-lo em nossas funções de janela para saídas e filtrá-las imediatamente.

Você pode pensar em QUALIFY como fazendo para as funções de janela o que HAVING faz para os agregados - filtrando o resultado depois que ele foi calculado.

Há vários casos de uso em que a cláusula QUALIFY se mostra particularmente útil: 

• Você precisa filtrar os resultados da consulta com base nas saídas da função de janela.
• Você deseja simplificar suas consultas SQL, reduzindo a necessidade de subconsultas.
• Você precisa classificar, desduplicar ou segmentar os dados com eficiência nas partições.
• Você está preparando um relatório ou painel que requer os registros de melhor desempenho por categoria.

> Se você é novo no Snowflake, nosso curso de Introdução ao Snowflake oferece um ponto de partida prático antes de mergulhar em cláusulas como QUALIFY. Você também pode explorar os recursos mais amplos do Snowflake com este tutorial para iniciantes.

Sintaxe de QUALIFY em Snowflake

Vamos revisar a sintaxe básica do QUALIFY para que você entenda suas partes. Veja como pode ser uma consulta simples usando QUALIFY:

SELECT
 column1,
 column2, 
 window_function() OVER (PARTITION BY column3 ORDER BY column4) AS rank
FROM table_name
QUALIFY window_function_condition;

Você tem a cláusula SELECT usual seguida de algumas colunas. Em seguida, você escreve window_function() no formato usual para obter uma coluna chamada rank.

Há a declaração usual FROM, que nos informa a tabela e, finalmente, nosso QUALIFY. Esse QUALIFY é seguido pela "window_function_condition", que geralmente é uma igualdade e pode ser algo como QUALIFY rank = 1

Observação: Você pode fazer referência ao alias de uma função de janela ou reescrever toda a expressão da função de janela dentro da cláusula QUALIFY. Ambas as abordagens funcionam.

> Se você precisar de uma revisão das funções de janela, dê uma olhada nesta folha de dicas sobre funções de janela. 

Exemplos de uso do QUALIFY no Snowflake

Agora que você conhece a estrutura geral do QUALIFY, mostrarei alguns exemplos específicos usando essa cláusula.

Exemplo 1: Filtragem das N primeiras linhas por grupo

Suponhamos que você queira encontrar os três funcionários mais bem pagos de cada departamento. Primeiro, vamos dar uma olhada em um exemplo que não usa QUALIFY, seguido de uma versão mais concisa que usa.

/* First we have to rank all the employees */
WITH ranked_employees AS (
SELECT employee_id, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees)

/* Then we query to subquery and filter using WHERE */
SELECT *
FROM ranked_employees
WHERE rank <= 3;

Como você pode ver, o exemplo acima requer um CTE, mas com QUALIFY, a consulta é mais simples:

SELECT employee_id, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees
QUALIFY rank <= 3;

A consulta acima atribui uma classificação a cada funcionário em seu departamento e retorna os três primeiros em cada departamento. Ele usa a instrução QUALIFY para filtrar imediatamente sem a necessidade de uma subconsulta ou CTE. Isso é especialmente útil em painéis ou APIs em que é importante minimizar o comprimento da consulta e o tempo de resposta.

Exemplo 2: Filtragem de registros duplicados

Se uma tabela tiver registros duplicados e quisermos manter apenas a primeira ocorrência com base em uma função de classificação, poderemos usar QUALIFY com ROW_NUMBER():

SELECT 
order_id, 
customer_id, 
order_date,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS row_num
FROM orders
QUALIFY row_num = 1;

A coluna row_num é gerada pelo particionamento de cada cliente e pela classificação por data do pedido. Isso atribui um número sequencial a cada pedido, começando pelo mais antigo. Ao filtrar usando QUALIFY row_num = 1, garantimos que apenas o pedido mais antigo de cada cliente seja mantido.

Essa técnica é comumente usada em tarefas de deduplicação durante a limpeza de dados.

Exemplo 3: Filtragem baseada na função de janela em agregados

Vamos nos tornar um pouco mais sofisticados. O uso do site QUALIFY com funções de janela agregadas permite uma filtragem mais flexível e dinâmica.

Por exemplo, se quisermos encontrar os funcionários cujos salários estão acima da média do departamento, podemos usar QUALIFY com AVG() em uma função de janela:

SELECT 
employee_id, 
department, 
salary,
AVG(salary) OVER (PARTITION BY department) AS avg_salary
FROM employees
QUALIFY salary > avg_salary;

Esse tipo de lógica é excelente para identificar exceções ou os melhores desempenhos em grupos de colegas.

> Se você deseja aprimorar suas habilidades em SQL para consultas mais complexas como essas, confira nosso curso SQL Intermediário.

Práticas recomendadas para usar o QUALIFY no Snowflake

Aqui estão algumas práticas recomendadas e usos de uma cláusula QUALIFY. 

Simplificar consultas complexas

O poder do QUALIFY está em sua capacidade de simplificar as consultas, eliminando a necessidade de subconsultas e CTEs adicionais. Você pode se apoiar nessa funcionalidade. Use-o sempre que você escrever uma função de janela e precisar filtrar com base em sua saída, sem criar uma subconsulta.

Menos CTEs significam depuração mais fácil, lógica mais clara e melhor capacidade de manutenção.

Combine o QUALIFY com outras funções da janela

Você pode combinar várias funções de janela para obter uma filtragem mais refinada. Por exemplo, usando ROW_NUMBER() junto com RANK() para resolver empates na classificação e, ao mesmo tempo, limitar os resultados:

SELECT 
employee_id, 
department, 
salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC, employee_id) AS row_num
FROM employees
QUALIFY rank <= 3 AND row_num <= 3;

Essa consulta retorna os funcionários que estão entre as três primeiras posições salariais em seu departamento. No entanto, se houver mais de três funcionários empatados entre os três primeiros, ele mostrará apenas os três primeiros ordenados pelo ID do funcionário.

Considerações sobre o desempenho

Embora o site QUALIFY simplifique as consultas em termos de código escrito, alguns aspectos de desempenho devem ser considerados.

As funções de janela podem ser caras do ponto de vista computacional, e as consultas mal estruturadas podem exigir muitos recursos. Lembre-se de manter esses conceitos em mente:

• As funções de janela podem ser computacionalmente caras em grandes conjuntos de dados, portanto, tenha cuidado com o estágio do processo em que você as utiliza.
• Garanta a indexação e o particionamento adequados para otimizar o desempenho.
• Use o LIMIT em conjunto com o QUALIFY para obter ainda mais eficiência durante os testes.
• Use a ferramenta Query Profile do Snowflake para identificar gargalos de desempenho em consultas com janelas.

Solução de problemas de consultas QUALIFY

Pode ser fácil cair em armadilhas com os comandos QUALIFY, dada a complexidade das funções de janela. Aqui estão algumas dicas para ajudar com os problemas mais comuns que você pode encontrar.

1. Uso de funções de janela na cláusula WHERE

A tentativa de usar funções de janela diretamente na cláusula WHERE resultará em erros porque WHERE é avaliado antes que as funções de janela sejam processadas.

-- This will cause an error
SELECT employee_id, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees
WHERE rank = 1;

Solução: Em vez disso, use a cláusula QUALIFY, que é avaliada após as funções de janela.

SELECT employee_id, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees
QUALIFY rank = 1;

2. Omitir o predicado em QUALIFY

A cláusula QUALIFY requer um predicado para filtrar os resultados das funções de janela. Se você omitir o predicado, ocorrerão erros.

-- This will cause an error
SELECT employee_id, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees
QUALIFY RANK() OVER (PARTITION BY department ORDER BY salary DESC);

Solução: Certifique-se de que a cláusula QUALIFY inclua um predicado, como = 1, para filtrar a linha mais bem classificada.

SELECT employee_id, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees
QUALIFY rank = 1;

3. Aliases ambíguos

O uso do mesmo alias para uma coluna e uma função de janela pode gerar confusão e resultados inesperados.

-- Potentially ambiguous
SELECT salary AS rank,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees
QUALIFY rank = 1;

Solução: Use aliases distintos para evitar ambiguidade.

SELECT salary AS salary_amount,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM employees
QUALIFY salary_rank = 1;

4. Particionamento ou ordenação incorretos

A especificação incorreta das cláusulas PARTITION BY ou ORDER BY nas funções de janela pode levar a resultados inesperados.

Solução: Defina cuidadosamente o particionamento e a ordenação para que correspondam à lógica desejada. Por exemplo, para obter o salário mais alto por departamento:

SELECT employee_id, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees
QUALIFY rank = 1;

Conclusão

A cláusula QUALIFY no Snowflake é uma ferramenta poderosa para filtrar resultados com base em funções de janela, reduzindo a necessidade de subconsultas e tornando as consultas mais legíveis. Ao entender como usá-lo de forma eficaz, os cientistas de dados podem simplificar suas consultas e aumentar a eficiência ao trabalhar com dados classificados, agrupados ou agregados.

Você está pronto para aprofundar suas habilidades em Snowflake e SQL? Comece com nosso curso Introduction to Snowflake, aprimore suas consultas analíticas no curso PostgreSQL window functions ou avance em sua carreira com o Associate Data Engineer in SQL track!