QUALIFY: l’istruzione di filtraggio SQL di cui non sapevi di avere bisogno

Concetti di base e sintassi della clausola SQL QUALIFY

QUALIFY è una clausola usata per filtrare i risultati di una funzione finestra (window function). Quindi, per usare correttamente la clausola QUALIFY, deve esserci almeno una funzione finestra nell’elenco SELECT o nella clausola QUALIFY: verranno restituite solo le righe per cui l’espressione booleana risulta TRUE.

Suggerimento: Dai un’occhiata alla cheat sheet sulle funzioni finestra in SQL.

Senza la clausola QUALIFY, per filtrare i risultati di una funzione finestra sarebbe necessario l’annidamento. Gli sviluppatori SQL in genere sconsigliano di usare query annidate quando possibile, perché rendono il codice meno leggibile e aumentano la complessità del debug e della collaborazione.

In altre parole, l’idea principale dietro la clausola QUALIFY è semplificare le query che richiedono il filtraggio del risultato di funzioni finestra; ciò significa che QUALIFY viene valutata dopo che le funzioni finestra sono state calcolate.

Ecco il tipico ordine di esecuzione per una query con una clausola QUALIFY:

• FROM
• WHERE
• GROUP BY e aggregazioni
• HAVING
• WINDOW
• QUALIFY
• DISTINCT
• ORDER BY
• LIMIT

La sintassi generale di un’istruzione QUALIFY è la seguente:

QUALIFY <predicate>Offerto da

Nella sua forma generale, potrebbe apparire così:

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

Nota: <predicate> è un’espressione usata per filtrare il risultato dopo che aggregazioni e funzioni finestra sono state calcolate.

Quando usare SQL QUALIFY

La clausola QUALIFY è molto simile alla clausola HAVING, nel senso che evita la necessità di una sottoquery per eseguire il filtraggio. Ad esempio, puoi usare QUALIFY per filtrare i risultati di una funzione analitica, cioè una funzione usata per calcolare un valore aggregato basato su un gruppo di righe.

Per chiarire meglio quando useresti esattamente QUALIFY, ecco un breve scenario:

Mettiamo che tu stia lavorando come data scientist e un stakeholder ti chieda le informazioni sull’ultimo accesso di ciascun cliente. La tua query iniziale potrebbe essere simile a questa:

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

È un buon inizio, ma non soddisfa la richiesta dello stakeholder perché restituirà tutti gli accessi e non solo il più recente. Per restituire l’ultimo accesso, dobbiamo aggiungere un filtro.

I nuovi utenti SQL potrebbero cadere nella trappola di provare a filtrare usando la clausola WHERE, come segue:

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 = 1Offerto da

Purtroppo, questo codice non verrà eseguito; lo stesso vale se provi con GROUP BY e HAVING.

Il motivo dipende tutto dall’ordine delle operazioni di cui abbiamo parlato nella sezione precedente.

WHERE, GROUP BY e HAVING vengono valutati prima delle funzioni finestra; ciò significa che non possono filtrare le funzioni finestra perché “non sanno” che esistono: al momento in cui queste tre clausole vengono eseguite, la funzione finestra non è ancora stata valutata.

Un modo per far sì che il filtro avvenga dopo la funzione finestra è usare una Common Table Expression (CTE).

Ecco come apparirà il nostro codice:

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 = 1Offerto da

Tecnicamente, questo codice è valido: si eseguirà senza problemi e restituirà i risultati richiesti dal tuo stakeholder.

Tuttavia, abbiamo dovuto introdurre due query e qualche riga di codice in più, che possono diventare ridondanti se ripeti spesso questo processo.

La soluzione migliore a questo problema è usare QUALIFY.

Ecco come apparirà il codice:

-- 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 = 1Offerto da

Questa soluzione funziona perché le clausole QUALIFY vengono valutate dopo le funzioni finestra nell’ordine di esecuzione di SQL, il che significa che “conoscono” la loro esistenza e possono quindi filtrarle nella stessa query.

Potresti chiederti quali siano i vantaggi di QUALIFY oltre a scrivere meno righe di codice; la risposta è che non ci sono veri benefici in termini di prestazioni.

La soluzione con CTE e quella con QUALIFY vengono eseguite in tempi simili, quindi non si può dire che ci sia un grande vantaggio prestazionale nell’uso di QUALIFY.

Il principale beneficio è il miglioramento della qualità della vita: meno codice e maggiore leggibilità.

Confronto tra SQL QUALIFY e altri metodi di filtraggio

A questo punto, hai capito che la clausola QUALIFY è un altro metodo di filtraggio disponibile in SQL. Sai anche quando è meglio usarla.

Ma qual è la differenza tra QUALIFY e gli altri metodi di filtraggio?

Facciamo un riepilogo di ciascun metodo di filtraggio.

La clausola WHERE

WHERE viene usata per filtrare i record in una tabella; questo significa che implementa operazioni sulle righe. La usiamo quando vogliamo estrarre i record che soddisfano una condizione specifica. Per esempio, potremmo voler filtrare un dataset in base all’età di una persona. Ecco come apparirà nel codice:

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

Ecco i punti principali da ricordare sulla clausola WHERE.

1. Si usa per filtrare i record in base a una condizione specifica.
2. Le operazioni vengono applicate sulle righe.
3. Puoi usare la clausola WHERE senza una clausola GROUP BY.
4. WHERE può essere usata con le istruzioni SELECT, UPDATE, DELETE.

Confrontiamola con la clausola HAVING.

La clausola HAVING

HAVING si usa per filtrare i record da gruppi definiti da una clausola GROUP BY, in base a una condizione specifica. Quindi, la differenza più evidente tra WHERE e HAVING è che HAVING richiede la presenza di una clausola GROUP BY per essere valutata correttamente.

Il principale vantaggio è che puoi applicare la clausola HAVING a un sottoinsieme di gruppi aggregati, cosa non possibile in un blocco WHERE.

Per esempio:

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

Il codice sopra restituirà i gruppi per genere in cui l’altezza media è maggiore di 170 centimetri; in altre parole, HAVING implementa un’operazione sulla colonna.

Un’altra cosa da sapere è che HAVING può essere usata solo con l’istruzione SELECT, a differenza di WHERE.

La clausola QUALIFY

QUALIFY è l’equivalente di HAVING, ma applica filtri sui risultati delle funzioni finestra, mentre HAVING applica filtri su funzioni di aggregazione e clausole GROUP BY. Scopri di più su come usare GROUP BY.

Se volessimo conoscere le informazioni sulla persona più alta all’interno di ciascun gruppo di età, potremmo usare una funzione finestra per eseguire il calcolo e una clausola QUALIFY per filtrare i risultati, così da ottenere solo la persona più alta per ogni fascia d’età.

Ecco come apparirebbe nel codice:

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 = 1Offerto da

In sostanza, la clausola QUALIFY specifica un’espressione condizionale usata per filtrare l’output di una funzione analitica ordinata che è già stata calcolata secondo i criteri specificati dall’utente.

Conclusioni e approfondimenti

SQL svolge un ruolo fondamentale nel tipico workflow di data science; sapere come implementare operazioni utili in SQL, come la clausola QUALIFY, è estremamente utile perché ti permette di scrivere codice più pulito, rendendo più piacevole la collaborazione con gli altri.

Se vuoi padroneggiare le tue competenze SQL, ti consigliamo di consultare le risorse qui sotto:

• SQL Basics Cheat Sheet
• Corso Introduction to SQL
• Percorso di competenze SQL Fundamentals