6 esempi di funzione NTILE() in SQL

La funzione SQL NTILE() può semplificare notevolmente l'analisi e la reportistica dei dati. Seguite questi sei esempi per capire cos'è e quando usarla.

Le funzioni finestra sono molto utili e forniscono una grande potenza espressiva all'SQL. In questo articolo parliamo della funzione NTILE(), che ci permette di dividere un insieme di record in sottoinsiemi di dimensioni approssimativamente uguali. Questa funzione è molto utilizzata nei calcoli finanziari o economici.

Prima di entrare nel merito della funzione NTILE(), esaminiamo rapidamente le funzioni finestra in SQL.

Cosa sono le funzioni SQL Funzioni Finestra?

Le funzioni finestra sono funzioni che lavorano su un gruppo di righe chiamato finestra e restituiscono un valore basato su tale gruppo. La funzione NTILE(N) riceve un parametro intero(N) e divide l'insieme delle righe in sottoinsiemi. N sottoinsiemi. Ogni sottoinsieme ha approssimativamente lo stesso numero di righe ed è identificato da un numero compreso tra 1 e N. Questo numero identificativo è ciò che restituisce NTILE().

Se volete imparare le funzioni di SQL window, vi consiglio il nostro corso interattivo. Contiene 218 esercizi pratici che vi aiuteranno a mettere in pratica le diverse funzioni di finestra. Alla fine, vi sentirete a vostro agio nell'uso di questa tecnica SQL avanzata.

Se volete vedere le funzioni finestra in azione, date un'occhiata al nostro articolo Esempio di funzione finestra SQL con spiegazioni. E se volete approfondire le differenze tra GROUP BY e le funzioni finestra, leggete Funzioni SQL Window vs. GROUP BY.

Esempio di set di dati: Un negozio di calcio

Nel resto dell'articolo, baseremo i nostri esempi sul database di un negozio di calcio immaginario. Ci concentreremo sulla tabella sales che ha la struttura e i dati seguenti:

Credo che tutto quello che c'è in questa tabella si spieghi da sé, quindi passiamo agli esempi.

Esempio #1: Dividere le righe in 2 gruppi

Inizieremo con un esempio molto semplice. Vogliamo dividere le righe della tabella sales in due gruppi: il gruppo #1 e il gruppo #2. La query che segue esegue questa operazione:

SELECT NTILE(2) OVER() AS group, 
       sale_date, 
       product_id,
 soccer_team 
FROM sales;

Nella query, l'espressione NTILE(2) OVER() restituisce 1 per il primo 50% delle righe nel set di risultati e 2 per il secondo 50% delle righe. Le righe sono assegnate a ciascun gruppo in modo non deterministico, ossia non ci sono criteri per assegnare le righe a un particolare gruppo. Ecco un risultato parziale della query, che mostra ogni gruppo con un colore diverso:

Esempio n. 2: Distribuzione delle vendite del 2023

La tabella sales memorizza i record di ogni vendita completata. Il reparto marketing è interessato ad analizzare la distribuzione delle vendite in base all'importo speso. Hanno chiesto un report che raggruppi tutte le vendite dell'anno 2023 in quattro gruppi della stessa dimensione (il numero di vendite in ogni gruppo deve essere lo stesso). Ogni vendita deve essere assegnata in base all'importo della vendita.

Il primo gruppo (sale_group #1) deve contenere le vendite con l'importo più basso, mentre l'ultimo gruppo (sale_group #4) deve contenere le vendite con l'importo più alto. Per ogni vendita, il report deve includere il numero del gruppo di vendita, customer_id, product_id e soccer_team. La query per ottenere questo risultato è la seguente:

  SELECT
      NTILE(4) OVER ( ORDER BY amount ) AS sale_group,
      product_id,
      product_category,
      soccer_team,
      amount as sales_amount
  FROM sales
  WHERE sale_date >= '2023-12-01' AND sale_date <= '2023-12-31';

Questa query utilizza una clausola WHERE per filtrare le vendite avvenute nel 2023. Poi la funzione NTILE(4) cerca di creare quattro gruppi di righe con lo stesso numero di righe ciascuno. Si parla di "tentativi" perché non è sempre possibile creare gruppi della stessa dimensione; alcuni gruppi possono avere una riga in meno rispetto agli altri.

Come si fa a definire a quale gruppo appartiene ogni riga? La clausola OVER (ORDER BY amount) indica che, prima di assegnare le righe ai gruppi, tutte le righe devono essere ordinate in base all'importo della vendita. Una volta ordinate, la funzione NTILE(4) prenderà il primo trimestre delle vendite e gli assegnerà il valore 1, quindi prenderà il trimestre successivo delle vendite e gli assegnerà il valore 2 e così via. Di seguito è riportato un risultato parziale che mostra i record di ciascun gruppo sale_group:

Si può notare che nel gruppo più economico (1) sono presenti solo vendite della categoria Accessori. Questo perché i prodotti accessori sono solitamente più economici, mentre le magliette sono solitamente più costose. Possiamo anche notare che i prodotti per il Barcellona sono presenti in tutti e quattro i gruppi di vendita, il che suggerisce che questa squadra ha un'offerta a tutti i livelli di prezzo.

Esempio n. 3: Vendite mensili per ogni categoria e squadra

Nella prossima query creeremo un report sulle vendite mensili. Il reparto marketing vuole dividere le vendite mensili per ogni categoria di prodotto e per ogni squadra in quattro gruppi. Il primo gruppo conterrà le categorie di prodotti, le squadre di calcio e i mesi con le vendite totali più basse. Il gruppo successivo avrà il livello successivo di total_sales e così via. In questo modo, gli esperti di marketing potranno analizzare la distribuzione delle vendite nei diversi mesi e categorie. Di seguito è riportata la query:

WITH monthly_sales_stats AS (
   SELECT
        EXTRACT(MONTH FROM sale_date) as month,
        product_category,
        soccer_team,
        SUM(amount) AS total_sales
   FROM sales
   WHERE sale_date >= '2023-01-01' and sale_date <= '2023-12-31'
   GROUP BY EXTRACT(MONTH FROM sale_date), product_category, soccer_team
)
SELECT
     NTILE(4) OVER ( ORDER BY total_sales ) AS group,
     month,
     product_category,
     soccer_team,
     total_sales
FROM monthly_sales_stats
ORDER BY group DESC, total_sales DESC;

La query ha un'espressione di tabella comune (CTE) chiamata monthly_sales_stats, che calcola le vendite totali per ogni combinazione di mese, product_category e soccer_team.

Si noti che nella clausola WHERE vengono filtrate le righe dell'anno 2023. L'espressione EXTRACT(MONTH FROM sale_date) viene utilizzata per ottenere il valore del mese nel campo sale_date.

Nella query principale, si chiama NTILE(4) OVER (ORDER BY total_sales) per assegnare a ogni riga della CTE un numero di gruppo. Di seguito è riportato un risultato parziale della query che mostra le prime tre righe di ciascun gruppo:

Nel risultato si può notare che le vendite più alte per ogni squadra di calcio si registrano a dicembre, probabilmente a causa dello shopping natalizio. La categoria "Accessori" si trova alla fine della tabella dei risultati, perché gli accessori hanno solitamente un prezzo più basso.

Esempio n. 4: Vendite più basse e più alte delle squadre

Come altre funzioni di finestra, è possibile utilizzare NTILE() con la clausola PARTITION BY. Ecco un esempio.

Il team di marketing vuole analizzare la distribuzione delle vendite all'interno degli articoli di ciascuna squadra di calcio. L'idea è quella di dividere le vendite di ogni squadra in insiemi basati sull'importo. Ancora una volta, metteremo le vendite più basse nel primo gruppo e poi le vendite più alte nel quarto gruppo.

Questa è la query:

SELECT	soccer_team,
      	NTILE(4) OVER (PARTITION BY soccer_team ORDER BY amount) AS group_number,
product_id, 
product_category,
amount 
FROM sales ; 

La query è molto semplice: ha un SELECT con un elenco di colonne e un FROM con un nome di tabella. La funzione NTILE() utilizza PARTITION BY e ORDER BY. La funzione PARTITION BY soccer_team mette tutte le righe con lo stesso valore in soccer_team nella stessa partizione. La somma ORDER BY ordina i record in ogni serie di squadre, mettendo per primi quelli con i valori più bassi. Quindi NTILE(4) restituisce 1 per il primo 25% delle righe del gruppo di righe, 2 per il secondo 25% delle righe del gruppo e così via.

Di seguito sono riportati alcuni risultati parziali:

Esempio #5: Comportamento delle vendite natalizie per squadra di calcio

Questo esempio è molto simile all'Esempio 3, con la differenza che la funzione NTILE() utilizza una sottoclausola PARTITION BY soccer_team. Ciò significa che NTILE() creerà gruppi di righe per ogni soccer_team invece di creare gruppi dall'insieme completo dei risultati (come nell'Esempio 3). Di conseguenza, ogni squadra di calcio avrà quattro gruppi.

La query è:

WITH monthly_sales_stats AS (
       SELECT
            EXTRACT(MONTH FROM sale_date) as month,
            product_category,
            soccer_team,
            SUM(amount) AS total_sales
      FROM sales
      WHERE sale_date >= '2023-01-01' and sale_date <= '2023-12-31'
      GROUP BY EXTRACT(MONTH FROM sale_date), product_category, soccer_team
)
  SELECT
     NTILE(4) OVER(PARTITION BY soccer_team ORDER BY total_sales DESC) AS group,
     month,
     product_category,
     soccer_team,
     total_sales
  FROM monthly_sales_stats
  ORDER BY total_sales DESC;

La CTE monthly_sales_stats è esattamente la stessa dell'esempio precedente. Ha una clausola GROUP BY che aiuta a calcolare l'importo totale delle vendite per ogni combinazione di mese, product_category e soccer_team.

Dopo aver creato la CTE, si scrive una SELECT con la seguente espressione NTILE():

NTILE(4) OVER(PARTITION BY soccer_team ORDER BY total_sales DESC) AS group

Questa espressione divide le righe della CTE in insiemi che hanno lo stesso valore nel campo soccer_team. Per ciascun insieme di squadre di calcio, NTILE(4) cerca di creare quattro sottoinsiemi uguali, assegnando a ciascuna riga un valore da 1 a 4.

Le altre colonne del risultato provengono dalla CTE. Di seguito sono riportati i risultati parziali che mostrano due righe per ogni gruppo per le squadre di Barcellona e Manchester.

Esempio n. 6: Ottenere gruppi di clienti distribuiti in modo uniforme

Supponiamo che il reparto marketing voglia creare tre gruppi di clienti distribuiti uniformemente per eseguire tre diverse campagne di marketing; ogni campagna sarà mirata a un gruppo. I clienti di ciascun gruppo sono scelti in modo casuale. Poi il reparto marketing confronterà i risultati delle campagne e valuterà quale campagna è migliore.

Per simulare una selezione casuale dei clienti, un'idea è quella di utilizzare i secondi dell'ultimo acquisto di ciascun cliente. Ordinati in base ai secondi, creeremo tre gruppi di clienti. Vediamo la query:

WITH customer_last_transaction_timestamp AS (
  SELECT customer_id, 
       max(sales_date) AS last_ts
  FROM   sales
  GROUP BY customer_id
)
SELECT NTILE(3) OVER (ORDER BY EXTRACT(SECOND FROM last_ts)) AS group_number, 
       customer_id 
FROM   customer_last_transaction_timestamp 
ORDER BY group_number;

La query precedente restituisce tutti i clienti con un group_number da 1 a 3; questo rappresenta il gruppo di marketing a cui il cliente è stato assegnato. La CTE customer_last_transaction_timestamp memorizza ogni cliente con il timestamp dell'ultima transazione (ottenuto con MAX(sales_date)).

La query principale utilizza la funzione NTILE(3) per creare tre gruppi di clienti di dimensioni approssimativamente uguali:

NTILE(3) OVER (ORDER BY EXTRACT(SECOND FROM last_ts))

L'istruzione precedente restituisce 1 per il 33% dei clienti con un valore di timestamp nel terzo inferiore. Restituisce 2 per il 33% successivo e così via. La sottoespressione EXTRACT(SECOND FROM last_ts) prende la parte dei secondi (cioè 22) da un timestamp (cioè '2023-03-30 10:30:22'). Di seguito è riportato un risultato parziale:

Continuare a conoscere NTILE() e altri SQL Funzioni Finestra

In questo articolo abbiamo mostrato diversi esempi di utilizzo della funzione NTILE(). Abbiamo anche mostrato diverse clausole OVER. La clausola OVER è comune a tutte le funzioni finestra di SQL. Se desiderate fare un po' di esperienza pratica con questi comandi, vi suggerisco il nostro corso interattivo Funzioni Finestra interattivo.

Se utilizzate spesso le funzioni finestra, il nostro foglio informativo gratuito su SQL Funzioni Finestra è una risorsa molto utile. Infatti, l'ho appeso alla parete del mio ufficio, pronto per essere utilizzato quando ho dei dubbi sulla sintassi. Lo consiglio vivamente.

Infine, l'articolo Le 10 migliori domande di intervista sulle funzioni SQL è ottimo se avete un colloquio di lavoro e volete essere preparati sugli argomenti SQL.

Buon apprendimento e continuate a progredire con le funzioni finestra di SQL!