Filtrare le window function in modo efficace con QUALIFY di Snowflake

La clausola qualify in Snowflake, e più in generale in SQL, permette di filtrare in base al risultato di una window function. Dato che una window function non può essere usata direttamente nella clausola where, quando occorre filtrare su una window function si ricorre a qualify.

Un rapido ripasso sulle window function

Una window function è una funzione che opera su un insieme di righe in relazione alla riga corrente. Si usa tipicamente per calcolare ranking, totali progressivi, medie mobili, conteggi per partizione e così via. Ogni volta che serve fare riferimento alle righe precedenti o successive a quella corrente, occorre una window function. E la clausola qualify è semplicemente una scorciatoia per filtrarne il risultato.

Sintassi di QUALIFY

La sintassi di qualify è la seguente:

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

Come si nota, qualify si trova alla fine della query SQL, subito prima dell'opzionale order by.

Partendo dai dati qui sotto, proviamo a rispondere a due domande di business.

1. Chi è il miglior sales rep in ogni regione, giorno per giorno?

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 restituirà le righe evidenziate:

2. Chi è il miglior sales rep per ogni giorno?

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 restituirà le righe evidenziate:

Suggerimento rapido: con la clausola qualify si può riutilizzare l'alias! Se serve selezionare anche la window function e non limitarsi a filtrarla, basta riutilizzarne l'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

La clausola qualify è il modo più rapido per individuare i record che fanno fallire un test di unicità in dbt o per isolare i duplicati in base a una combinazione di colonne.

Supponiamo di avere una colonna id sottoposta a test di unicità e che il test fallisca. L'obiettivo è analizzare tutti i record duplicati, raggruppandoli tra loro nell'ordinamento.

Il modo più rapido per selezionare tutti i record che hanno fatto fallire il test è questo:

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

Vediamo modi alternativi per risolvere la stessa query sui duplicati.

Opzione 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);

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

Esistono altri modi per arrivare allo stesso risultato, ma nessuno è rapido e immediato come la clausola qualify.

In sintesi: il valore della clausola qualify sta nell'eliminare la necessità di una CTE quando si filtra su una window function. Poiché le window function non possono essere usate direttamente nella clausola where, senza qualify servirebbe una CTE per filtrarle.

Abbiamo già visto che qualify filtra una window function e non può essere usata nella clausola where; ora però vale la pena approfondire l'ordine delle operazioni in SQL.

Questo è l'ordine con cui Snowflake elabora un'istruzione select:

È importante notare che ogni filtro applicato nelle clausole from e where viene eseguito prima dei filtri di qualify. Anche le aggregazioni e i filtri sugli aggregati (group by e having) vengono valutati prima.