Funzioni di Finestra Un Must-Know per gli Ingegneri e gli Scienziati dei Dati.
Window Functions A Must-Know for Engineers and Data Scientists.
Tornare alle basi | Demistificare le funzioni finestra SQL
La crescita dei dati è stata molto estesa negli ultimi anni e anche se abbiamo a disposizione un insieme diversificato di strumenti e tecnologie, SQL rimane ancora al centro della maggior parte di essi. È uno dei linguaggi fondamentali per l’analisi dei dati ed è ampiamente impiegato dalle aziende di tutte le dimensioni per risolvere le sfide legate ai dati.
Credo sempre che non sia necessario conoscere determinati concetti solo per il gusto di superare un colloquio o risolvere un problema particolare. Se si desidera imparare il concetto e l’architettura sottostante, aiuterà a conquistare il lavoro ovunque si vada. Le funzioni finestra sono un po’ complicate e si potrebbe sentirsi un po’ intimiditi all’inizio, ma una volta che se ne prende il controllo, sono molto divertenti da lavorare.
Sarebbe più facile capire il concetto di funzioni finestra se si è familiari con le funzioni di aggregazione SQL. Le funzioni di aggregazione eseguono il calcolo su un insieme di valori e restituiscono un valore; quando accoppiati con la clausola GROUP BY, restituiscono un singolo valore per ogni gruppo. Si può leggere di più a riguardo qui:
- Rendi i tuoi grafici fantastici con UTF-8
- MLOps Tempo come gli obiettivi strategici creano iterazioni più veloci?
- Fai contare ogni dollaro di marketing con la scienza dei dati.
Funzioni di aggregazione SQL per il prossimo colloquio di Data Science
Tornare alle basi | Fondamenti di SQL per principianti
towardsdatascience.com
Prima di andare avanti, permettimi di presentarti il database di esempio. Utilizzeremo i dati di una società di vendita di veicoli fasulli, puoi trovare i dati sorgente nel mio GitHub Repo,
Cosa sono le funzioni finestra?
La definizione tradizionale di una funzione finestra è:
Una funzione finestra esegue calcoli su un insieme di righe di tabella che sono in qualche modo correlate alla riga corrente.
Se la si scompone, le funzioni finestra ci consentono di eseguire calcoli su partizioni. La partizione è semplicemente un insieme, un sottogruppo o un bucket di righe definite dall’utente su cui la funzione finestra eseguirà i calcoli.
Sono anche ampiamente conosciute come funzioni analitiche.
Perché abbiamo bisogno di funzioni finestra?
Come sappiamo, le funzioni di aggregazione riassumono i dati di più righe in una singola riga (se usate insieme alla clausola GROUP BY, una singola riga per ogni gruppo); le funzioni finestra, invece, eseguono anche calcoli su un insieme di righe, ma a differenza delle funzioni di aggregazione, non riassumono il set di risultati in una singola riga. Invece, tutte le righe mantengono la loro forma/identità originale e la riga calcolata viene aggiunta al set di risultati per ogni riga.
Sembra divertente, eh? Scomponiamolo. Ecco i dati di esempio della tabella PRODOTTI:
--query tabella PRODOTTISELECT * FROM PRODOTTI LIMIT 10;
Supponiamo che abbiamo bisogno delle informazioni sul prezzo medio di acquisto per ogni CATEGORIA PRODOTTO,
--prezzo medio di acquisto per ogni categoria di prodottoSELECT CATEGORIA PRODOTTO, FORMAT(AVG(PREZZO DI ACQUISTO),2) COME PREZZO MEDIO DI ACQUISTO DA PRODOTTIGROUP BY CATEGORIA PRODOTTO;
Ora, queste informazioni da sole non saranno molto utili. Sì! Ora si conosce il prezzo medio di acquisto per ogni CATEGORIA PRODOTTO, ma cosa viene dopo? Come queste informazioni producono un’idea di business? Cosa succede se voglio confrontare il prezzo di acquisto di ogni prodotto con il prezzo medio di acquisto della particolare CATEGORIA PRODOTTO? Permettimi di riformulare la mia nuova richiesta,
- Visualizzare il prezzo di acquisto di ogni prodotto all’interno della CATEGORIAPRODOTTI insieme al prezzo di acquisto medio per quella CATEGORIAPRODOTTI.
- Organizzare il set di risultati raggruppato per CATEGORIAPRODOTTI.
Potrebbe essere possibile ottenere ciò solo con una normale funzione di aggregazione? Il requisito sopra indicato vuole visualizzare alcune informazioni così come sono (ad es. CATEGORIAPRODOTTI, NOMEPRODOTTO, PREZZOACQUISTO nella forma originale) e inoltre vuole una nuova colonna che visualizzi anche il prezzo di acquisto medio per ogni CATEGORIAPRODOTTI. Ecco dove entra in gioco l’eroica funzione di finestra,
--usando la funzione di finestraSELECT CATEGORIAPRODOTTI, NOMEPRODOTTO, PREZZOACQUISTO, FORMAT(AVG(PREZZOACQUISTO) OVER (PARTITION BY CATEGORIAPRODOTTI),2) AS PREZZOACQUISTOMEDIOFROM PRODOTTI;
Prima di passare alle funzioni di finestra comunemente utilizzate, comprendiamo la sintassi di base e le clausole abbinatvi.
La sintassi generale di una funzione di finestra è,
Dove,
- La clausola OVER() definisce un insieme di righe specifico dell’utente. Una funzione di finestra esegue quindi un calcolo solo su quell’insieme specifico. Viene utilizzata specificamente con le funzioni di finestra; tuttavia, può anche essere utilizzata con le funzioni di aggregazione, proprio come abbiamo fatto con la funzione AVG() sopra, trasformandola in una funzione di finestra. Se non si fornisce niente all’interno di OVER(), la funzione di finestra verrà applicata all’intero set di risultati.
- PARTITION BY viene utilizzato con la clausola OVER. Divide il set di risultati della query in partizioni e quindi la funzione di finestra si applica a ogni partizione. È facoltativo, quindi se non si specifica la clausola PARTITION BY, la funzione considera tutte le righe come una singola partizione.
- La clausola ORDER BY viene utilizzata per ordinare il set di risultati in ordine ascendente o discendente all’interno di ogni partizione del set di risultati. Per impostazione predefinita, è in ordine ascendente.
- ROWS/RANGE fa parte della clausola FRAME che definisce un sottoinsieme all’interno della partizione.
Puoi leggere una comparazione dettagliata tra le funzioni di finestra e di aggregazione e le clausole di funzioni di finestra qui, Anatomia delle funzioni di finestra SQL.
Ora che siamo familiari con l’anatomia di base di una funzione di finestra, esploriamo quella più comunemente utilizzata,
ROW_NUMBER()
ROW_NUMBER() assegna un numero intero sequenziale ad ogni riga di una tabella o di una partizione nel caso in cui stiamo utilizzando la clausola PARTITION BY. La sintassi comune è,
ROW_NUMBER() OVER ([clausola PARTITION BY] [clausola ORDER BY])
Ecco i dati di esempio dalla tabella PRODOTTI, che contiene i dati di una gamma di prodotti disponibili presso il rivenditore di veicoli.
--query tabella PRODOTTISELECT * FROM PRODOTTI LIMIT 10;Iniziamo con il semplice,
--assegna un numero di riga a ogni riga in una tabellaSELECT *, ROW_NUMBER() OVER() AS ROW_NUMFROM PRODOTTI;
Qui, ROW_NUMBER() ha assegnato un numero intero sequenziale a partire da 1 ad ogni riga della tabella PRODOTTI. Miglioriamolo un po’ aggiungendo numeri di riga per ogni CATEGORIAPRODOTTI, per questo dovremo utilizzare la clausola PARTITION BY.
--numero di riga per CATEGORIAPRODOTTISELECT *, ROW_NUMBER() OVER(PARTITION BY CATEGORIAPRODOTTI) AS ROW_NUMFROM PRODOTTI;
Abbiamo le 2 diverse PRODUCTCATEGORY disponibili nella tabella PRODUCTS,
ROW_NUMBER() ha generato un numero intero sequenziale per ogni riga e la clausola PARTITION BY ha suddiviso il risultato in blocchi in base a PRODUCTCATEGORY. Quindi fondamentalmente ROW_NUMBER() insieme a OVER e la clausola PARTITION BY ha generato una sequenza univoca di numeri per ogni PRODUCTCATEGORY.
Ora, utilizziamo anche la clausola ORDER BY. Questa è stata anche una delle domande di intervista più frequenti ai livelli di ingresso/intermedi. Diciamo, vogliamo sapere i primi 3 prodotti di ogni PRODUCTCATEGORY con la quantità più alta in magazzino disponibile.
--i primi 3 prodotti con la quantità più alta in ogni categoria di prodottoCON INVENTARIO_PRODOTTO COME(SELECT PRODUCTCATEGORY, PRODUCTNAME, QUANTITYINSTOCK, ROW_NUMBER() OVER (PARTITION BY PRODUCTCATEGORY ORDER BY QUANTITYINSTOCK DESC) AS ROW_NUMFROM PRODUCTS)SELECT PRODUCTCATEGORY, PRODUCTNAME, QUANTITYINSTOCK, ROW_NUM AS TOP_3_PRODOTTIDALLA INVENTARIO_PRODOTTOWHERE ROW_NUM <= 3;
Prima di tutto scomponiamo l’interrogazione in due parti, come mostrato nell’immagine seguente; prima, stiamo creando un INVENTARIO_PRODOTTO. I dati della tabella verranno suddivisi in partizioni/gruppi di ogni PRODUCTCATEGORY, ordinati in ordine decrescente della quantità disponibile in magazzino per ogni categoria di prodotto. ROW_NUMBER() genererà quindi numeri interi sequenziali per ogni partizione. La parte interessante qui è che il numero di riga per ogni riga viene sort of ripristinato per ogni PRODUCTCATEGORY.
L’interrogazione precedente restituirà il seguente risultato,
Ora la seconda parte della nostra interrogazione è piuttosto semplice. Utilizzerà questo insieme di risultati come input e prenderà i primi 3 prodotti di ogni PRODUCTCATEGORY in base alla condizione ROW_NUM ≤ 3. Il risultato finale è il seguente,
Ciò ci porta al risultato finale come,
RANK()
Come suggerisce il nome, RANK() assegna un rango a ogni riga della tabella o a ogni riga in una partizione. La sintassi generale è,
RANK() OVER ([clausola PARTITION BY] [clausola ORDER BY])
Continuando con l’esempio della tabella PRODUCTS, assegniamo un rango ai prodotti in base alla quantità disponibile in magazzino in ordine decrescente, suddiviso per PRODUCTCATEGORY.
--genera un rango per ogni categoria di prodottoSELECT PRODUCTCATEGORY, PRODUCTNAME, QUANTITYINSTOCK, RANK() OVER (PARTITION BY PRODUCTCATEGORY ORDER BY QUANTITYINSTOCK DESC) AS "RANK"FROM PRODUCTS;
Ho limitato il set di risultati per scopi dimostrativi. Ora, non confondere ROW_NUMBER() e RANK(). Il set di risultati per entrambi può sembrare simile; tuttavia, c’è una differenza. ROW_NUMBER() assegna un numero intero sequenziale univoco a ogni riga di una tabella o in una partizione; mentre RANK() genera anche un numero intero sequenziale per ogni riga di una tabella o in una partizione, ma assegna lo stesso rango per le righe con gli stessi valori.
Comprendiamolo con un esempio, qui ci sono i dati di esempio dalla tabella CUSTOMERS,
-- dati di esempio dalla tabella customersSELECT CUSTOMERID, CUSTOMERNAME, CREDITLIMITFROM CUSTOMERSLIMIT 10; 
Nella demo seguente, ho generato ROW_NUMBER() e RANK() per la tabella CUSTOMERS, ordinata in ordine decrescente della loro CREDITLIMIT.
--confronto tra row_number() e rank()SELECT CUSTOMERID, CUSTOMERNAME, CREDITLIMIT, ROW_NUMBER() OVER (ORDER BY CREDITLIMIT DESC) AS CREDIT_ROW_NUM, RANK() OVER (ORDER BY CREDITLIMIT DESC) AS CREDIT_RANKFROM CUSTOMERS;
Ho limitato il set di risultati per scopi dimostrativi. In verde è evidenziato ROW_NUMBER() e in blu RANK().
Ora, se ci si riferisce alle 3 righe evidenziate in rosso, qui il set di risultati per entrambe le funzioni differisce; ROW_NUMBER() ha generato un numero intero sequenziale univoco per tutte le righe. Ma d’altra parte, RANK() ha assegnato lo stesso rango, 20, per CUSTOMERID 239 e 321 poiché hanno lo stesso limite di credito, che è 105000,00. Non solo quello, per la prossima riga, ovvero CUSTOMERID 458, ha saltato il rango 21 e gli ha assegnato il rango 22.
DENSE_RANK()
Ora, se ti stai chiedendo, perché abbiamo bisogno di DENSE_RANK() se siamo già dotati di RANK()? Come abbiamo già visto nel precedente esempio, RANK() genera lo stesso rango per le righe con gli stessi valori e poi salta il rango consecutivo successivo (fare riferimento all’immagine sopra).
DENSE_RANK() è simile a RANK() tranne per questa differenza, non salta alcun rango durante il ranking delle righe. La sintassi comune è,
DENSE_RANK() OVER ([CLAUSOLA PARTITION BY] [CLAUSOLA ORDER BY])
Tornando alla tabella CUSTOMERS, confrontiamo il set di risultati per entrambi RANK() e DENSE_RANK(),
--confronto tra dense_rank() e rank()SELECT CUSTOMERID, CUSTOMERNAME, CREDITLIMIT, RANK() OVER (ORDER BY CREDITLIMIT DESC) AS CREDIT_RANK, DENSE_RANK() OVER (ORDER BY CREDITLIMIT DESC) AS CREDIT_DENSE_RANKFROM CUSTOMERS;
Come per RANK() (evidenziato in blu), DENSE_RANK() (evidenziato in verde) ha generato lo stesso rango per CUSTOMERID 239 e 321, ma DENSE_RANK() ha fatto una cosa diversa, invece di saltare il numero consecutivo successivo come ha fatto RANK(), ha mantenuto la sequenza e ha assegnato un rango di 21 a CUSTOMERID 458.
NTH_VALUE()
Questo è un po’ diverso da ciò che abbiamo discusso finora. NTH_VALUE() restituirà il valore della N-esima riga dall’espressione in una finestra specificata. La sintassi comune è,
NTH_VALUE(espressione, N) OVER ([CLAUSA PARTITION BY] [CLAUSA ORDER BY] [CLAUSA ROW/RANGE])
‘N’ deve essere un valore intero positivo. Se i dati non esistono in posizione N, la funzione restituirà NULL. Qui, se avete notato, abbiamo una clausola aggiuntiva nella sintassi che è la clausola ROW/RANGE .
RAW/RANGE fa parte della clausola Frame in Window Function che definisce un sottoinsieme all’interno di una partizione della finestra. ROW/RANGE definisce i punti di inizio e fine di questo sottoinsieme rispetto alla riga corrente, si prende la posizione della riga corrente come punto di partenza e, con quel riferimento, si definisce il frame all’interno della partizione.
- ROWS – Questo definisce l’inizio e la fine del frame specificando il numero di righe che precedono o seguono la riga corrente.
- RANGE – Contrariamente a ROWS , RANGE specifica l’intervallo di valori rispetto al valore della riga corrente per definire un frame all’interno della partizione.
La sintassi generica è,
{ROWS | RANGE} TRA <inizio_frame> E <fine_frame>
Ogni volta che si utilizza la clausola ORDER BY, imposta il frame predefinito come,
{ROWS/RANGE} TRA UNBOUNDED PRECEDING E CURRENT ROW
Senza la clausola ORDER BY, il frame predefinito è,
{ROWS/RANGE} TRA UNBOUNDED PRECEDING E UNBOUNDED FOLLOWING
Può sembrare troppo complicato al momento, ma non c’è bisogno di memorizzare la sintassi e il suo significato, basta esercitarsi! Potete leggere la dettagliata clausola Frame con un sacco di esempi qui,
Anatomia delle Funzioni di Finestra SQL
Torniamo alle basi | Fondamenti di SQL per principianti
towardsdatascience.com
Ora diciamo, abbiamo bisogno di trovare il NOMEPRODOTTO di ogni CATEGORIAPRODOTTO con il secondo prezzo di acquisto più alto,
--nomeprodotto per ogni categoriaprodotto con il secondo prezzo di acquisto più altoSELECT NOMEPRODOTTO, CATEGORIAPRODOTTO, PREZZOACQUISTO, NTH_VALUE(NOMEPRODOTTO,2) OVER(PARTITION BY CATEGORIAPRODOTTO ORDER BY PREZZOACQUISTO DESC) AS SECONDO_PREZZO_DI_ACQUISTO_PIÙ_ALTOFROM PRODOTTI;
Abbiamo altre 2 funzioni di valore simili a NTH_VALUE(); FIRST_VALUE() e LAST_VALUE(). Come suggerisce il nome, restituiscono rispettivamente i valori più alti (primo) e più bassi (ultimo) da un elenco ordinato in base all’espressione dell’utente. La sintassi comune è,
FIRST_VALUE(espressione) OVER ([CLAUSA PARTITION BY] [CLAUSA ORDER BY] [CLAUSA ROW/RANGE])
LAST_VALUE(espressione) OVER ([CLAUSA PARTITION BY] [CLAUSA ORDER BY] [CLAUSA ROW/RANGE])
Come nell’esempio precedente, potete ora trovare il NOMEPRODOTTO con il prezzo di acquisto più alto e più basso per ogni CATEGORIAPRODOTTO?
NTILE()
A volte ci sono scenari in cui si desidera ordinare le righe all’interno della partizione in un certo numero di gruppi o secchi. NTILE() viene utilizzato per questo scopo, divide le righe ordinate nella partizione in un numero specifico di secchi. Ogni secchio è assegnato un numero di gruppo a partire da 1. Cercherà di creare gruppi di dimensioni uguali quanto possibile. Per ogni riga, la funzione NTILE() restituisce un numero di gruppo che rappresenta il gruppo a cui quella riga appartiene.
La sintassi generale è:
NTILE(N) OVER ([CLAUSOLA PARTITION BY] [CLAUSOLA ORDER BY])
Dove ‘N’ è un intero positivo che definisce il numero di gruppi che si vogliono creare.
Ad esempio, vogliamo separare la PRODUCTCATEGORY -‘Auto’ in modo da avere un elenco di auto con prezzo di acquisto alto, medio e basso.
--separare le 'Auto' per prezzo di acquisto alto, medio e bassoSELECT PRODUCTNAME, BUYPRICE, NTILE(3) OVER (ORDER BY BUYPRICE DESC) AS BUYPRICE_BUCKETSFROM PRODUCTSWHERE PRODUCTCATEGORY = 'Auto';
LAG() & LEAD()
Spesso ci troviamo di fronte a scenari in cui è richiesta una qualche forma di analisi comparativa, ad esempio confrontare le vendite dell’anno selezionato con l’anno precedente o successivo. Tali confronti sono molto utili quando si lavora con dati in serie temporali e si calcolano le differenze nel tempo.
LAG() estrae i dati dalla riga che precede la riga corrente. Se non c’è una riga precedente, restituisce NULL. La sintassi comune è:
LAG(espressione, offset) OVER ([CLAUSOLA PARTITION BY] [CLAUSOLA ORDER BY])
LEAD() recupera i dati dalla riga che segue la riga corrente. Se non c’è una riga successiva, restituisce NULL. La sintassi comune è:
LEAD(espressione, offset) OVER ([CLAUSOLA PARTITION BY] [CLAUSOLA ORDER BY])
Dove offset è opzionale ma, quando usato, il suo valore deve essere 0 o un intero positivo,
- Quando specificato come 0, LAG() e LEAD() valutano l’espressione per la riga corrente.
- Quando omesso, 1 è considerato un valore predefinito, che prende la riga immediatamente precedente o successiva alla riga corrente.
--vendite totali annuali per ogni categoria di prodottoWITH YEARLY_SALES AS(SELECT PROD.PRODUCTCATEGORY, YEAR(ORDERDATE) AS SALES_YEAR, SUM(ORDET.QUANTITYORDERED * ORDET.COSTPERUNIT) AS TOTAL_SALESFROM PRODUCTS PRODINNER JOIN ORDERDETAILS ORDET ON PROD.PRODUCTID = ORDET.PRODUCTIDINNER JOIN ORDERS ORD ON ORDET.ORDERID = ORD.ORDERIDGROUP BY PRODUCTCATEGORY, SALES_YEAR )SELECT PRODUCTCATEGORY, SALES_YEAR, LAG(TOTAL_SALES) OVER (PARTITION BY PRODUCTCATEGORY ORDER BY SALES_YEAR) AS LAG_PREVIOUS_YEAR, TOTAL_SALES, LEAD(TOTAL_SALES) OVER (PARTITION BY PRODUCTCATEGORY ORDER BY SALES_YEAR) AS LEAD_FOLLOWING_YEARFROM YEARLY_SALES;Qui abbiamo prima usato CTE (Common Table Expression) per ottenere i dati delle vendite totali per ogni PRODUCTCATEGORY anno per anno. Usiamo quindi questi dati con LAG() e LEAD() per recuperare i dati delle vendite totali suddivisi per PRODUCTCATEGORY e ordinati per anno solare precedente e successivo rispettivamente.
Conclusione
Le funzioni di finestra sono davvero utili quando si desidera analizzare i propri dati in modi diversi. I diversi tipi di SQL possono avere implementazioni leggermente diverse, quindi è sempre una buona idea fare riferimento alla documentazione ufficiale di un particolare tipo di SQL. Ecco alcune risorse per iniziare:
- Anatomia delle funzioni di finestra
- Concetti e sintassi delle funzioni di finestra
- Limitazioni delle funzioni di finestra MySQL
- Cheat Sheet delle funzioni di finestra SQL
Se ricordi qualcosa molto bene, devi averlo esercitato bene,
- HackerRank o LeetCode per esercitarti con problemi SQL di base/intermedia/avanzata.
Diventa un membro e leggi ogni storia su Nisoo .
Buon apprendimento!
