Filtrado eficaz de window functions con QUALIFY de Snowflake

La cláusula qualify en Snowflake, o en SQL en general, permite filtrar a partir del resultado de una window function. Una window function no se puede usar directamente en la cláusula where, así que recurre a qualify cuando necesites filtrar sobre window functions.

Un repaso rápido a las window functions

Una window function es una función que opera sobre un conjunto de filas en relación con la fila actual. Suelen usarse para calcular rankings, totales acumulados, promedios móviles, conteos por partición, etc. Siempre que necesites hacer referencia a las filas anteriores o posteriores a la actual, vas a necesitar una window function. Y la cláusula qualify no es más que un atajo para filtrar sobre una window function.

Sintaxis de QUALIFY

La sintaxis de qualify es la siguiente:

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

Como ves, qualify aparece al final de la consulta SQL, justo antes de la cláusula opcional order by.

Con los datos de abajo, vamos a responder dos preguntas de negocio.

1. ¿Quién es el mejor representante de ventas en cada región por día?

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

Snowflake devolverá las filas resaltadas:

2. ¿Quién es el mejor representante de ventas cada día?

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

Snowflake devolverá las filas resaltadas:

Tip rápido: ¡La reutilización de alias funciona con la cláusula qualify! Si además de filtrar necesitas seleccionar la window function, puedes reutilizar su alias.

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

Usar la cláusula qualify es la forma más rápida de detectar qué registros están haciendo fallar un test de unicidad en dbt, o de aislar duplicados a partir de una combinación de columnas.

Supongamos que tienes una columna llamada id sobre la que corre un test de unicidad y ese test falla. Quieres analizar todos los registros duplicados, con los duplicados agrupados entre sí.

La forma más rápida de seleccionar todos los registros del test fallido es esta:

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

Veamos otras formas de resolver la consulta de registros duplicados.

Opción 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);

Opción 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;

Hay algunas formas más de resolver esta misma pregunta, pero ninguna es tan rápida y sencilla como usar la cláusula qualify.

Para tener en cuenta: la gran ventaja de la cláusula qualify es que evita tener que usar un CTE al filtrar sobre una window function. Como las window functions no se pueden usar directamente en la cláusula where, sin qualify haría falta un CTE para poder filtrarlas.

Ya vimos que qualify filtra una window function y no se puede usar en la cláusula where, pero vale la pena profundizar en el orden de operaciones de SQL.

Este es el orden en el que Snowflake procesa tu sentencia select:

Es importante destacar que cualquier filtrado en las cláusulas from y where ocurre antes del filtrado en qualify. Además, la agregación y el filtrado de agregados (group by y having) también suceden antes.