CALIFICAR: La sentencia de filtrado SQL que nunca supo que necesitaba

El lenguaje de consulta estructurado (SQL) es uno de los lenguajes de programación más importantes del mundo. Esta afirmación es especialmente cierta para quienes trabajan con datos, ya que es el lenguaje preferido para comunicarse con bases de datos.

A medida que más personas recurren a los datos para impulsar sus negocios, la demanda de conocimientos de SQL no ha hecho más que aumentar con el paso de los años. Afortunadamente, el lenguaje es fácil de aprender y está bien consolidado, lo que significa que cuenta con una gran comunidad de usuarios que te ayudarán si te quedas atascado.

En este artículo, trataremos una cláusula específica del lenguaje SQL llamada QUALIFY. Al final de este artículo, lo sabrá:

• Concepto básico y sintaxis de QUALIFY
• Cuándo utilizar la cláusula QUALIFY
• En qué se diferencia de otros métodos de filtrado.

Concepto básico y sintaxis de la cláusula SQL QUALIFY

QUALIFY es una cláusula utilizada para filtrar los resultados de una función ventana. Por lo tanto, para utilizar correctamente la cláusula QUALIFY, debe haber al menos una función WINDOW en la lista SELECT o en la cláusula QUALIFY: sólo se devolverán las filas en las que la expresión booleana se evalúe como TRUE.

Consejo: Echa un vistazo a la hoja de trucos de las funciones de ventana SQL.

Si no existiera la cláusula QUALIFY, el filtrado de los resultados de una función WINDOW requeriría anidamiento. Los desarrolladores de SQL suelen recomendar que no se utilicen consultas anidadas en la medida de lo posible, ya que hacen que el código sea menos legible para las personas y, al mismo tiempo, aumentan la complejidad de la depuración y la colaboración.

En otras palabras, la idea principal de la cláusula QUALIFY era simplificar las consultas que requieren filtrar el resultado de las funciones ventana; esto significa que QUALIFY se evalúa después de calcular las funciones ventana.

Este es el orden típico de ejecución de una consulta con una cláusula QUALIFY:

• DESDE
• DONDE
• Agrupación por y agregación
• TENIENDO
• WINDOW
• CALIFICAR
• DISTINTO
• ORDENAR POR
• LÍMITE

La sintaxis general de una sentencia QUALIFY es la siguiente:

QUALIFY <predicate>

En su forma general, sería algo así:

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

Nota: <predicado> es una expresión que se utiliza para filtrar el resultado después de calcular las agregaciones y las funciones ventana.

Cuándo utilizar SQL QUALIFY

La cláusula de sentencia QUALIFY es muy parecida a la cláusula HAVING en el sentido de que evita la necesidad de una subconsulta para realizar el filtrado. Por ejemplo, puede utilizar QUALIFY para filtrar los resultados de una función analítica - una función utilizada para calcular un valor agregado basado en un grupo de filas.

Para que quede más claro cuándo utilizar exactamente QUALIFY, he aquí un breve escenario:

Supongamos que trabajas como científico de datos y una parte interesada te pide los últimos datos de acceso de cada cliente. Su consulta inicial puede ser algo parecido a esto:

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 

Es un buen comienzo, pero no satisface las peticiones de los interesados, ya que devolverá todos los inicios de sesión y no sólo el más reciente. Para devolver el último inicio de sesión, tenemos que añadir un filtro.

Los recién llegados a SQL pueden caer en la trampa de intentar filtrar esto utilizando la cláusula de la sentencia WHERE, como se indica a continuación:

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

Lamentablemente, este código no se ejecutará; lo mismo ocurre si se intenta GROUP BY y HAVING.

Esto se debe al orden de las operaciones que hemos comentado en el apartado anterior.

WHERE, GROUP BY y HAVING se evalúan antes que las funciones WINDOW; esto significa que no pueden filtrar las funciones WINDOW porque no saben que existen: la función WINDOW no se ha evaluado en el momento en que se ejecutan esas tres cláusulas.

Una forma de hacer que el filtro aparezca después de la función VENTANA es utilizar una Expresión de tabla común (CTE).

Este es el aspecto que tendrá nuestro 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

Técnicamente hablando, este código es válido: se ejecutará perfectamente y devolverá los resultados que busca el interesado.

Sin embargo, hemos tenido que introducir dos consultas, y hay algunas líneas de código adicionales, que pueden llegar a ser redundantes si se repite este proceso con regularidad.

La mejor solución para este problema es utilizar QUALIFY.

Este es el aspecto que tendrá el código:

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

Esta solución funciona porque las cláusulas QUALIFY se evalúan después de las funciones WINDOW en el orden de operaciones de SQL, lo que significa que conocen su existencia para poder filtrarlas en la misma consulta.

Puede que se pregunte cuáles son las ventajas de QUALIFY aparte de escribir menos líneas de código, y la respuesta es que en realidad no hay ninguna ventaja en cuanto al rendimiento.

Tanto la solución CTE que utilizamos como la solución QUALIFY se ejecutan en plazos similares, por lo que no podemos decir que la solución QUALIFY mejore mucho el rendimiento.

La principal ventaja es la mejora de la calidad de vida; hay menos código y es más fácil de leer.

Comparación de SQL QUALIFY con otros métodos de filtrado

A estas alturas, ya sabe que la cláusula QUALIFY es otro método de filtrado disponible en SQL. También sabe cuál es el mejor momento para utilizar la cláusula QUALIFY.

Pero, ¿cuál es la diferencia entre QUALIFY y los demás métodos de filtrado?

Recapitulemos cada método de filtrado.

La cláusula WHERE

WHERE se utiliza para filtrar registros en una tabla; esto significa que implementa operaciones de fila. Lo utilizamos cuando queremos extraer los registros que cumplen una condición específica. Por ejemplo, podemos querer filtrar un conjunto de datos por la edad de alguien. Así es como se verá en código:

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

Éstas son las principales cosas que hay que tener en cuenta sobre la cláusula WHERE.

1. Se utiliza para filtrar registros en función de una condición específica.
2. Las operaciones se ejecutan en las filas.
3. Puede utilizar la cláusula WHERE sin la cláusula GROUP BY
4. WHERE puede utilizarse con sentencias SELECT, UPDATE, DELETE.

Comparemos esto con la cláusula HAVING.

La cláusula HAVING

HAVING se utiliza para filtrar registros de grupos definidos por una cláusula GROUP BY en función de una condición específica. Por lo tanto, la diferencia más evidente entre WHERE y HAVING es que HAVING requiere la presencia de una cláusula GROUP BY para que se evalúe correctamente.

La principal ventaja es que puede aplicar la cláusula HAVING a un subconjunto de grupos agregados, lo que no es posible en un bloque WHERE.

Por ejemplo:

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

El código anterior devolverá los grupos de género en los que la altura media sea superior a 170 centímetros; en otras palabras, HAVING implementa una operación de columna.

Otra cosa que hay que tener en cuenta es que HAVING sólo se puede utilizar con la sentencia SELECT, a diferencia de WHERE.

La cláusula QUALIFY

QUALIFY es el equivalente de HAVING excepto que realiza filtros en los resultados de las funciones WINDOW, mientras que HAVING realiza filtros en las funciones agregadas y en las cláusulas GROUP BY - más información sobre Cómo utilizar GROUP BY.

Si quisiéramos conocer información sobre la persona más alta dentro de cada grupo de edad, podríamos utilizar una función WINDOW para realizar el cálculo y una cláusula QUALIFY para filtrar los resultados de forma que sólo descubriéramos a la persona más alta de cada grupo de edad.

Así es como se vería en 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

Esencialmente, la cláusula QUALIFY especifica una expresión condicional utilizada para filtrar la salida de una función analítica ordenada que ya ha sido calculada de acuerdo con los criterios especificados por el usuario.

Conclusión y estudios complementarios

SQL desempeña un papel importante en el flujo de trabajo típico de la ciencia de datos; saber cómo implementar operaciones útiles en SQL, como la cláusula QUALIFY, es extremadamente útil, ya que le permite escribir un código más limpio, lo que hace que sea más agradable para los demás colaborar con usted.

Si desea dominar sus conocimientos de SQL, le recomendamos que consulte los recursos que figuran a continuación:

• Básicos de SQL
• Curso de introducción a SQL
• Conocimientos básicos de SQL