QUALIFICAR: A instrução de filtragem SQL que você nunca soube que precisava

A Structured Query Language (SQL) é uma das linguagens de programação mais importantes do mundo. Essa afirmação é particularmente verdadeira para quem trabalha com dados, pois é a linguagem preferida na comunicação com bancos de dados.

À medida que mais pessoas recorrem aos dados para impulsionar seus negócios, a demanda por conhecimento especializado em SQL só tem aumentado ao longo dos anos. Felizmente, a linguagem é fácil de aprender e está bem estabelecida, o que significa que ela tem uma grande comunidade de usuários para ajudá-lo em caso de dificuldades.

Neste artigo, abordaremos uma cláusula específica da linguagem SQL chamada QUALIFY. Ao final deste artigo, você saberá:

• O conceito básico e a sintaxe do QUALIFY
• Quando usar a cláusula QUALIFY
• Como ele difere de outros métodos de filtragem.

Conceito básico e sintaxe da cláusula SQL QUALIFY

QUALIFY é uma cláusula usada para filtrar os resultados de uma função de janela. Portanto, para usar a cláusula QUALIFY com êxito, deve haver pelo menos uma função WINDOW na lista SELECT ou na cláusula QUALIFY - somente as linhas em que a expressão booleana for avaliada como TRUE serão retornadas.

Dica: Confira a folha de dicas sobre as funções de janela do SQL.

Se não houvesse uma cláusula QUALIFY, a filtragem dos resultados de uma função WINDOW exigiria aninhamento. Normalmente, os desenvolvedores de SQL recomendam não usar consultas aninhadas o máximo possível, pois elas tornam o código menos legível e aumentam a complexidade da depuração e da colaboração.

Em outras palavras, a ideia principal por trás da cláusula QUALIFY era simplificar as consultas que exigem filtragem no resultado das funções de janela; isso significa que a QUALIFY é avaliada depois que as funções de janela são computadas.

Esta é a ordem típica de execução de uma consulta com uma cláusula de declaração QUALIFY:

• DE
• ONDE
• GROUP BY e agregação
• TER
• JANELA
• QUALIFICAR
• DISTINTO
• ORDER BY
• LIMIT

A sintaxe geral de uma instrução QUALIFY é a seguinte:

QUALIFY <predicate>

Em sua forma geral, seria algo parecido com isto:

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

Observação: <predicado> é uma expressão usada para filtrar o resultado depois que as agregações e as funções de janela são computadas.

Quando usar o SQL QUALIFY

A cláusula de declaração QUALIFY é muito parecida com a cláusula HAVING no sentido de que evita a necessidade de uma subconsulta para realizar a filtragem. Por exemplo, você pode usar QUALIFY para filtrar os resultados de uma função analítica - uma função usada para calcular um valor agregado com base em um grupo de linhas.

Para deixar mais claro quando exatamente você usaria o QUALIFY, aqui está um breve cenário:

Digamos que você esteja trabalhando como cientista de dados e uma parte interessada lhe peça as últimas informações de login de cada cliente. Sua consulta inicial pode ser mais ou menos assim:

SELECT 
  user_id, 
  ip, 
  country_code,
  os,
  RANK() over (
    PARTITION BY user_id ORDER BY log_datetime DESC
  ) as previous_logins
FROM login_logs
WHERE TRUE 

Esse é um bom começo, mas não atende às solicitações das partes interessadas, pois retornará todos os logins e não apenas o mais recente. Para retornar o último login, precisamos adicionar um filtro.

Os recém-chegados ao SQL podem cair na armadilha de tentar filtrar isso usando a cláusula de instrução WHERE, como segue:

SELECT 
  user_id, 
  ip, 
  country_code,
  os,
  RANK() over (
    PARTITION BY user_id ORDER BY log_datetime DESC
  ) AS previous_logins
FROM login_logs
WHERE TRUE 
  AND last_login = 1

Infelizmente, esse código não será executado; o mesmo acontece se você tentar GROUP BY e HAVING.

A razão pela qual isso ocorre é a ordem das operações que discutimos na seção anterior.

WHERE, GROUP BY e HAVING são todos avaliados antes das funções WINDOW; isso significa que eles não podem filtrar as funções WINDOW porque não sabem que elas existem - a função WINDOW não foi avaliada no momento em que essas três cláusulas são executadas.

Uma maneira de fazer o filtro vir depois da função WINDOW é usar uma expressão de tabela comum (CTE).

Veja como ficará nosso código:

WITH 
logins AS (
  SELECT 
      user_id, 
      ip, 
      country_code,
      os,
      RANK() OVER (
        PARTITION BY user_id ORDER BY log_datetime DESC
      ) AS previous_logins
  FROM login_logs
  WHERE TRUE
)

SELECT user_id, ip, country_code, os 
FROM logins
WHERE previous_logins = 1

Tecnicamente falando, esse código é válido - ele será executado perfeitamente bem e retornará os resultados que a parte interessada está buscando.

No entanto, tivemos que introduzir duas consultas e há algumas linhas extras de código, que podem se tornar redundantes se você estiver repetindo esse processo regularmente.

A melhor solução para esse problema é usar o QUALIFY.

Veja como o código será exibido:

-- Starter code from @Jiho Choi on StackOverflow 
SELECT 
  user_id, 
  ip, 
  country_code,
  os,
  RANK() over (
    PARTITION BY user_id ORDER BY log_datetime DESC
  ) as previous_logins
FROM login_logs
WHERE TRUE 
QUALIFY previous_logins = 1

Essa solução funciona porque as cláusulas QUALIFY são avaliadas depois das funções WINDOW na ordem de operações do SQL, o que significa que elas estão cientes de sua existência para que possam filtrá-las na mesma consulta.

Você pode estar se perguntando quais são os benefícios do QUALIFY além de escrever menos linhas de código, e a resposta é que não há realmente nenhum benefício de desempenho.

A solução CTE que usamos e a solução QUALIFY são executadas em períodos de tempo semelhantes, portanto, não podemos dizer que a solução QUALIFY tenha melhorado muito o desempenho.

O principal benefício é a melhoria da qualidade de vida; há menos código e é mais fácil de ler.

Comparação do SQL QUALIFY com outros métodos de filtragem

A esta altura, você já sabe que a cláusula de declaração QUALIFY é outro método de filtragem disponível no SQL. Você também sabe qual é o melhor momento para usar a cláusula QUALIFY.

Mas qual é a diferença entre o QUALIFY e os outros métodos de filtragem?

Vamos recapitular cada método de filtragem.

A cláusula WHERE

WHERE é usado para filtrar registros em uma tabela; isso significa que ele implementa operações de linha. Usamos esse recurso quando queremos extrair os registros que atendem a uma condição específica. Por exemplo, talvez queiramos filtrar um conjunto de dados pela idade de uma pessoa. Veja como ele será exibido no código:

SELECT name, gender, height, weight, age 
FROM gym_members_info 
WHERE age < 23; 

Aqui estão os principais aspectos a serem observados sobre a cláusula WHERE.

1. É usado para filtrar registros com base em uma condição específica.
2. As operações são implementadas nas linhas.
3. Você pode usar a cláusula WHERE sem uma cláusula GROUP BY
4. WHERE pode ser usado com instruções SELECT, UPDATE, DELETE.

Vamos comparar isso com a cláusula HAVING.

A cláusula HAVING

HAVING é usado para filtrar registros de grupos definidos por uma cláusula GROUP BY com base em uma condição específica. Portanto, a diferença mais significativa entre WHERE e HAVING é que HAVING exige que uma cláusula GROUP BY esteja presente para que seja avaliada com êxito.

A principal vantagem disso é que você pode aplicar sua cláusula HAVING a um subconjunto de grupos agregados, o que não é possível em um bloco WHERE.

Por exemplo:

SELECT gender, AVG(height) 
FROM gym_members_info
GROUP BY gender 
HAVING AVG(height) > 170 

O código acima retornará os grupos de gênero em que a altura média é maior que 170 centímetros; em outras palavras, HAVING implementa uma operação de coluna.

Outro aspecto a ser observado é que o HAVING só pode ser usado com o comando SELECT, ao contrário do WHERE.

A cláusula QUALIFY

QUALIFY é equivalente a HAVING, exceto pelo fato de que ele realiza filtros nos resultados das funções WINDOW, enquanto HAVING realiza filtros em funções agregadas e cláusulas GROUP BY - saiba mais sobre Como usar GROUP BY.

Se quiséssemos saber informações sobre a pessoa mais alta em cada faixa etária, poderíamos usar uma função WINDOW para realizar o cálculo e uma cláusula QUALIFY para filtrar os resultados, de modo a descobrir apenas a pessoa mais alta de cada faixa etária.

Veja como isso ficaria no código:

SELECT name, gender, height, weight, age 
RANK() over (
    PARTITION BY age ORDER BY height DESC
  ) as ranked_ages
FROM gym_members_info
WHERE TRUE
QUALIFY ranked_ages = 1

Essencialmente, a cláusula QUALIFY especifica uma expressão condicional usada para filtrar a saída de uma função analítica ordenada que já foi calculada de acordo com os critérios especificados pelo usuário.

Conclusão e estudos futuros

O SQL desempenha um papel importante no fluxo de trabalho típico da ciência de dados; saber como implementar operações úteis no SQL, como a cláusula QUALIFY, é extremamente útil, pois permite que você escreva um código mais limpo, o que torna mais agradável a colaboração de outras pessoas com você.

Se você quiser dominar suas habilidades em SQL, recomendamos que consulte os recursos abaixo:

• Folha de dicas básicas de SQL
• Curso de introdução ao SQL
• Trilha de habilidades do SQL Fundamentals