Snowflake QUALIFY per principianti: cos’è e come usarlo

Se lavori con Snowflake come data professional, potresti dover filtrare i risultati delle query in base a funzioni finestra. Tradizionalmente, questo richiede sottoquery o logiche di filtro complesse. Tuttavia, Snowflake offre un’alternativa potente: la clausola QUALIFY. 

In questa guida ti mostrerò come usare QUALIFY per semplificare i filtri, con esempi passo passo e buone pratiche.

Che cos’è la clausola QUALIFY in Snowflake?

La clausola QUALIFY in Snowflake serve a filtrare i risultati con le funzioni finestra. Puoi definire le funzioni finestra sia nella lista SELECT sia richiamarle direttamente nella clausola QUALIFY.

In genere, WHERE filtra le righe grezze prima dell’aggregazione, mentre HAVING filtra dopo un’aggregazione GROUP BY.

Invece, QUALIFY consente di filtrare sui risultati di istruzioni SQL come ROW_NUMBER(), RANK() e DENSE_RANK(). Questo significa che possiamo usarla nelle nostre funzioni finestra per produrre output e filtrarli subito.

Puoi pensare a QUALIFY come a ciò che HAVING fa per gli aggregati: filtra il risultato dopo che è stato calcolato.

Ci sono diversi casi d’uso in cui la clausola QUALIFY è particolarmente utile: 

• Devi filtrare i risultati di una query in base all’output delle funzioni finestra.
• Vuoi semplificare le query SQL riducendo la necessità di sottoquery.
• Devi classificare, deduplicare o segmentare i dati in modo efficiente all’interno di partizioni.
• Stai preparando un report o una dashboard che richiede i record con le migliori performance per categoria.

> Se sei agli inizi con Snowflake, il nostro corso Introduction to Snowflake offre un punto di partenza pratico prima di immergerti in clausole come QUALIFY. Puoi anche esplorare le funzionalità più ampie di Snowflake con questo tutorial per principianti.

Sintassi di QUALIFY in Snowflake

Rivediamo la sintassi di base di QUALIFY per comprenderne le parti. Ecco come potrebbe apparire una semplice query che usa QUALIFY:

SELECT
 column1,
 column2, 
 window_function() OVER (PARTITION BY column3 ORDER BY column4) AS rank
FROM table_name
QUALIFY window_function_condition;

Hai la solita clausola SELECT seguita da alcune colonne. Poi la tua window_function() nella forma abituale per ottenere una colonna chiamata rank.

C’è la consueta istruzione FROM, che indica la tabella, e infine la nostra QUALIFY. Questa QUALIFY è seguita dalla “window_function_condition”, spesso un’uguaglianza, ad esempio QUALIFY rank = 1

Nota: puoi fare riferimento all’alias di una funzione finestra oppure riscrivere l’intera espressione della funzione finestra all’interno della clausola QUALIFY. Entrambi gli approcci funzionano.

> Se ti serve un ripasso delle funzioni finestra, dai un’occhiata a questo cheat sheet sulle funzioni finestra. 

Esempi di utilizzo di QUALIFY in Snowflake

Ora che conosci la struttura generale di QUALIFY, ti mostrerò alcuni esempi specifici che usano questa clausola.

Esempio 1: filtrare le prime N righe per gruppo

Supponiamo di voler trovare i 3 dipendenti più pagati in ogni reparto. Per prima cosa, vediamo un esempio che non usa QUALIFY, seguito da una versione più concisa che lo usa.

/* First we have to rank all the employees */
WITH ranked_employees AS (
SELECT employee_id, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees)

/* Then we query to subquery and filter using WHERE */
SELECT *
FROM ranked_employees
WHERE rank <= 3;

Come vedi, l’esempio sopra richiede una CTE, ma con QUALIFY la query è più lineare:

SELECT employee_id, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees
QUALIFY rank <= 3;

La query sopra assegna un rango a ciascun dipendente all’interno del proprio reparto e restituisce i primi 3 per reparto. Usa l’istruzione QUALIFY per filtrare immediatamente senza bisogno di una sottoquery o di una CTE. Questo è particolarmente utile in dashboard o API dove è importante ridurre la lunghezza della query e i tempi di risposta.

Esempio 2: filtrare i record duplicati

Se una tabella ha record duplicati e vogliamo mantenere solo la prima occorrenza in base a una funzione di ranking, possiamo usare QUALIFY con ROW_NUMBER():

SELECT 
order_id, 
customer_id, 
order_date,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS row_num
FROM orders
QUALIFY row_num = 1;

La colonna row_num viene generata partizionando per ciascun cliente e ordinando per data dell’ordine. Assegna un numero sequenziale a ogni ordine, partendo dal più antico. Filtrando con QUALIFY row_num = 1, ci assicuriamo di mantenere solo l’ordine più vecchio per ciascun cliente.

Questa tecnica è comunemente usata nelle attività di deduplicazione durante il data cleaning.

Esempio 3: filtrare basandosi su funzioni finestra in aggregazioni

Alziamo un po’ l’asticella. Usare QUALIFY con funzioni finestra aggregate consente filtri più flessibili e dinamici.

Per esempio, se vogliamo trovare i dipendenti con stipendi superiori alla media del reparto, possiamo usare QUALIFY con AVG() su una funzione finestra:

SELECT 
employee_id, 
department, 
salary,
AVG(salary) OVER (PARTITION BY department) AS avg_salary
FROM employees
QUALIFY salary > avg_salary;

Questo tipo di logica è ottimo per identificare outlier o top performer all’interno di gruppi omogenei.

> Se vuoi rafforzare le tue skill SQL per query più complesse come queste, dai un’occhiata al nostro corso Intermediate SQL.

Best practice per usare QUALIFY in Snowflake

Ecco alcune buone pratiche e utilizzi per una clausola QUALIFY. 

Semplifica le query complesse

La forza di QUALIFY sta nella sua capacità di semplificare le query eliminando la necessità di sottoquery e CTE aggiuntive. Sfrutta questa funzionalità. Usala ogni volta che scrivi una funzione finestra e hai bisogno di filtrare in base al suo output, senza creare una sottoquery.

Meno CTE significa debugging più semplice, logica più chiara e migliore manutenibilità.

Combina QUALIFY con altre funzioni finestra

Puoi combinare più funzioni finestra per ottenere filtri più raffinati. Per esempio, usare ROW_NUMBER() insieme a RANK() per risolvere i pari merito limitando al contempo i risultati:

SELECT 
employee_id, 
department, 
salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC, employee_id) AS row_num
FROM employees
QUALIFY rank <= 3 AND row_num <= 3;

Questa query restituisce i dipendenti che rientrano nei primi 3 ranghi di stipendio del loro reparto. Tuttavia, se più di tre dipendenti sono a pari merito entro quei primi 3, mostrerà solo i primi tre ordinati per ID dipendente.

Considerazioni sulle performance

Sebbene QUALIFY semplifichi le query in termini di codice scritto, ci sono alcuni aspetti prestazionali da considerare.

Le funzioni finestra possono essere costose dal punto di vista computazionale e query strutturate male possono diventare onerose in termini di risorse. Tieni a mente questi concetti:

• Le funzioni finestra possono essere costose su dataset di grandi dimensioni, quindi fai attenzione a in quale fase del processo le utilizzi.
• Assicurati un partizionamento adeguato per ottimizzare le performance.
• Usa LIMIT insieme a QUALIFY per una maggiore efficienza durante i test.
• Usa lo strumento Query Profile di Snowflake per individuare i colli di bottiglia prestazionali nelle query con funzioni finestra.

Risoluzione dei problemi con query QUALIFY

Con le istruzioni QUALIFY è facile incappare in insidie, data la complessità delle funzioni finestra. Ecco alcuni suggerimenti per i problemi più comuni.

1. Usare funzioni finestra nella clausola WHERE

Provare a usare funzioni finestra direttamente nella clausola WHERE genererà errori perché WHERE viene valutata prima che le funzioni finestra vengano elaborate.

-- This will cause an error
SELECT employee_id, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees
WHERE rank = 1;

Soluzione: usa invece la clausola QUALIFY, che è valutata dopo le funzioni finestra.

SELECT employee_id, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees
QUALIFY rank = 1;

2. Omettere il predicato in QUALIFY

La clausola QUALIFY richiede un predicato per filtrare i risultati delle funzioni finestra. Ometterlo porterà a errori.

-- This will cause an error
SELECT employee_id, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees
QUALIFY RANK() OVER (PARTITION BY department ORDER BY salary DESC);

Soluzione: assicurati che la clausola QUALIFY includa un predicato, come = 1 per filtrare la riga con il rango più alto.

SELECT employee_id, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees
QUALIFY rank = 1;

3. Alias ambigui

Usare lo stesso alias per una colonna e per una funzione finestra può creare confusione e risultati inattesi.

-- Potentially ambiguous
SELECT salary AS rank,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees
QUALIFY rank = 1;

Soluzione: usa alias distinti per evitare ambiguità.

SELECT salary AS salary_amount,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM employees
QUALIFY salary_rank = 1;

4. Partizionamento o ordinamento errati

Specificare in modo errato le clausole PARTITION BY o ORDER BY nelle funzioni finestra può portare a risultati imprevisti.

Soluzione: definisci con attenzione partizionamento e ordinamento in base alla logica desiderata. Per esempio, per ottenere lo stipendio più alto per reparto:

SELECT employee_id, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees
QUALIFY rank = 1;

Conclusione

La clausola QUALIFY in Snowflake è uno strumento potente per filtrare i risultati basandosi su funzioni finestra, riducendo la necessità di sottoquery e rendendo le query più leggibili. Capendo come usarla in modo efficace, i data scientist possono semplificare le proprie query e migliorare l’efficienza quando lavorano con dati classificati, raggruppati o aggregati.

Pronto a fare un salto di qualità con le tue skill su Snowflake e SQL? Inizia con il nostro corso Introduction to Snowflake, affina le tue query analitiche nel corso PostgreSQL sulle funzioni finestra o fai avanzare la tua carriera con il percorso Associate Data Engineer in SQL!