¿Cómo usar una cláusula QUALIFY en SQL?

QUALIFY es quizás uno de los comandos más subestimados de Teradata SQL, como Data Analyst, lo uso mucho y espero que después de este tutorial vos también lo uses.

¿Qué es una cláusula Qualify?

Una cláusula Qualify es el último filtro de tu sentencia SELECT. Que va a calificar tus filas por el argumento que elijas. Esto significa que si una sentencia SELECT te da múltiples filas y solo querés una de ellas, la podés filtrar y mostrar solo el elemento que necesitás.

¿Dónde puedo usar una cláusula Qualify?

Podés usar una cláusula Qualify en cualquier consulta o subconsulta, lo que significa que podés filtrar antes o después de que tu sentencia SELECT haga un JOIN y filtrar los resultados de un subconjunto de la información que necesitás.

Es, en realidad, uno de los comandos SQL más útiles para el análisis de datos y, en mi opinión, no se usa tanto como debería.

¿Cómo hago un Qualify en una Query?

Digamos que tenés dos tablas, la primera tiene diez filas que contienen una primary key, un username, y un email (esto sería ID, User, Email).

La segunda tiene un millón de filas que contienen las acciones que hizo el usuario, usando la primary key de la primera como foreign key y un campo de tipo DateTime que te dice cuándo hizo la acción el usuario (esta tabla tendría una primary key llamada ID, ID_User como foreign key, date_field como campo DateTime y action como, por ejemplo, un string). En esta segunda tabla, los datos no están necesariamente ordenados y la primary key no es necesariamente un número incremental.

Solo necesitás la última acción que hizo este usuario. ¿Cómo lo harías?

Usando Qualify con ROW_NUMBER()

Una forma de lograr esto sería usando ROW_NUMBER(), que le asigna un número a cada fila. El código quedaría así:

SELECT * FROM first_table A
LEFT JOIN second_table B
ON A.ID = B.ID_User
QUALIFY ROW_NUMBER() OVER (PARTITION BY A.ID ORDER BY B.date_field DESC) = 1

Acá podés ver un “= 1”, pero ¿qué significa esto?

Esto significa que ROW_NUMBER() le va a asignar un número a cada fila, ordenadas desde la última hasta la primera, y el QUALIFY va a quedarse con la primera de ellas.

Ahora digamos que en vez de la última acción, querés las primeras cinco acciones que hizo el usuario.

SELECT * FROM first_table A
LEFT JOIN second_table B
ON A.ID = B.ID_User
QUALIFY ROW_NUMBER() OVER (PARTITION BY A.ID ORDER BY B.date_field ASC) <= 5

Solo tenemos que cambiar un poco la última parte del código, ¿no? Ahora obtuvimos justo las primeras 5 acciones que hizo el usuario.

Usando QUALIFY con ROW_NUMBER() en una subconsulta

También podemos usar esto dentro de una subconsulta, digamos que ahora tu segunda tabla tiene cien millones de filas, y solo necesitás la última acción del mes anterior. Una consulta con todos los datos y después filtrando agotaría la memoria (podés leer sobre cómo hacer una buena consulta en otro de nuestros posts).

Tu consulta en este caso debería quedar más o menos así:

SELECT * FROM first_table A
LEFT JOIN (SELECT * FROM second_table WHERE date_field BETWEEN date-30 and date-60
QUALIFY ROW_NUMBER() OVER (PARTITION BY ID ORDER BY B.date_field DESC) = 1) B
ON A.ID = B.ID_User

¡Ahora tenés una subconsulta con un QUALIFY adentro que va a reducir tu uso de memoria y va a tardar menos tiempo en ejecutarse!

Otras formas de usar QUALIFY

Aunque Qualify se usa mayormente con ROW_NUMBER(), se puede usar con otras opciones, como RANK o QUANTILE.

Sigamos con nuestro ejemplo y digamos que ahora querés las diez personas que hicieron menos acciones

SELECT A.ID, B.Qactions, RANK() OVER (ORDER BY B.Qactions ASC) FROM first_table A
LEFT JOIN (SELECT ID_User, count(*) as Qactions FROM second_table GROUP BY 1) B
ON A.ID = B.ID_User
QUALIFY RANK() OVER (ORDER BY B.Qactions DESC) <=10;

Acá estamos usando RANK para establecer el número de la fila, RANK va a poner las filas en orden según algún parámetro, en este caso, va a poner las filas en orden según la cantidad de acciones.

Y por último, digamos que querés el 10% de las personas que hicieron menos acciones

SELECT A.ID, B.Qactions, QUANTILE(10, B.Qactions) AS percentile
 FROM first_table A
LEFT JOIN (SELECT ID_User, count(*) as Qactions FROM second_table GROUP BY 1) B
ON A.ID = B.ID_User
QUALIFY percentile = 1;

En esta última consulta, dividimos a las personas en 10 cuantiles según su cantidad de acciones, así que el primero de los 10 cuantiles sería menos del 10% (el segundo sería el 20%, etc.)

Conclusión

QUALIFY es una cláusula poderosa, que deberías conocer y usar con fines analíticos. Me salvó la vida múltiples veces y espero que encuentres útil este tutorial y lo uses tanto como yo en el futuro.