Lezione 6: Window Functions — Offriamo alle Tue Query una Vista Panoramica!
Bentornato, esploratore di SQL! Oggi ci immergeremo nel mondo delle Window Functions — la suite panoramica d’élite di SQL. Se hai mai desiderato aggiungere un tocco di raffinatezza alla tua analisi dei dati, le funzioni finestra sono qui per offrirti la visuale. Immaginale come un telescopio: ti permettono di osservare i tuoi dati con una lente particolare, focalizzandosi su una riga mentre vedi l’intera scena.
Cosa sono le Funzioni Finestra?
Le funzioni finestra, conosciute anche come funzioni analitiche, eseguono calcoli su un insieme di righe di una tabella in relazione alla riga corrente. È come confrontare le vittorie di ogni cavaliere mentre mantieni d’occhio l’intero regno. Le funzioni finestra ti aiutano a:
- Classificare i cavalieri in base ai loro successi.
- Calcolare somme cumulative, medie o totali progressivi.
- Trovare percentuali e totali correnti per le missioni.
Sintassi di una Funzione Finestra
Le funzioni finestra hanno una sintassi semplice, simile a questa:
SELECT nome_colonna,
funzione_finestra(nome_colonna) OVER (PARTITION BY nome_colonna ORDER BY nome_colonna)
FROM nome_tabella;
La parte più importante di una funzione finestra è la clausola OVER. Dice a PostgreSQL quale “finestra” (o contesto) usare per il calcolo.
RANK e DENSE_RANK: Perché Ogni Cavaliere Merita un Titolo
Iniziamo con alcune funzioni di ranking. Immagina di avere una tabella di cavalieri con il numero di missioni completate e di voler vedere chi è il più forte (o il più feroce sterminatore di draghi):
SELECT nome, missioni_completate,
RANK() OVER (ORDER BY missioni_completate DESC) AS rank
FROM cavalieri;
Questo darà un rango ai cavalieri in base al numero di missioni completate. Ma attenzione: RANK salta numeri in caso di parità. Quindi, se due cavalieri sono al primo posto, il prossimo sarà classificato come terzo, non secondo!
Vuoi che ogni cavaliere si senta speciale? Usa DENSE_RANK:
SELECT nome, missioni_completate,
DENSE_RANK() OVER (ORDER BY missioni_completate DESC) AS dense_rank
FROM cavalieri;
Così si mantiene un po’ più di galateo — niente salti di classifica!
ROW_NUMBER: Perché Ogni Riga Merita Attenzione
Se desideri solo una numerazione semplice per ogni riga, ROW_NUMBER è il tuo miglior amico:
SELECT nome, missioni_completate,
ROW_NUMBER() OVER (ORDER BY missioni_completate DESC) AS numero_riga
FROM cavalieri;
Perfetto quando vuoi che ogni cavaliere abbia un numero unico, indipendentemente da quante missioni ha completato.
PARTITION BY: Organizzare il Grande Banchetto Reale
Ora facciamo qualcosa di più sofisticato. PARTITION BY ti permette di raggruppare le righe in finestre separate, come far sedere i cavalieri per regno a un grande banchetto. Supponiamo che tu voglia classificare i cavalieri all’interno di ogni regno:
SELECT nome, regno, missioni_completate,
RANK() OVER (PARTITION BY regno ORDER BY missioni_completate DESC) AS classifica_regno
FROM cavalieri;
Ora ogni cavaliere è classificato all’interno del proprio regno, così Sir Galahad può sentirsi ancora il numero uno a Camelot, anche se Sir Arthur lo supera globalmente.
Somme Cumulative: Contare l’Oro nel Tesoro
Se vuoi vedere i totali progressivi, le funzioni finestra sono perfette. Supponiamo di voler sommare le missioni completate dai cavalieri, ordinate per la loro performance:
SELECT nome, missioni_completate,
SUM(missioni_completate) OVER (ORDER BY missioni_completate DESC) AS missioni_cumulative
FROM cavalieri;
È come impilare monete d’oro nel tesoro: ogni contributo dei cavalieri si aggiunge alla pila, dandoti un conteggio in tempo reale.
Medie Mobili: Una Tavola Rotonda di Calcoli
Le medie mobili sono utili quando vuoi smussare le fluttuazioni. Troviamo la media mobile su 3 cavalieri per le missioni:
SELECT nome, missioni_completate,
AVG(missioni_completate) OVER (ORDER BY missioni_completate DESC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS media_mobile
FROM cavalieri;
Questo calcolerà il numero medio di missioni per ogni cavaliere, considerando le proprie missioni e quelle dei due cavalieri precedenti. È come una discussione a tavola rotonda: l’opinione di tutti viene presa in considerazione!
LEAD e LAG: Occhiata al Passato e al Futuro
Vuoi vedere come un cavaliere si è comportato nella missione precedente o prevedere la prossima? Usa LEAD e LAG per scrutare la linea temporale:
SELECT nome, missioni_completate,
LAG(missioni_completate, 1) OVER (ORDER BY missioni_completate DESC) AS missione_precedente,
LEAD(missioni_completate, 1) OVER (ORDER BY missioni_completate DESC) AS missione_successiva
FROM cavalieri;
Ti permette di confrontare la performance di ogni cavaliere con quello prima e dopo di lui — perfetto per individuare tendenze e anomalie.
Tiriamo le Somme: Un Gran Finale delle Finestre!
Per riassumere, ecco una query che usa più funzioni finestra per classificare, sommare e analizzare:
WITH statistiche_cavalieri AS (
SELECT nome, regno, missioni_completate,
RANK() OVER (PARTITION BY regno ORDER BY missioni_completate DESC) AS classifica_regno,
SUM(missioni_completate) OVER (ORDER BY missioni_completate DESC) AS missioni_cumulative,
LAG(missioni_completate, 1) OVER (ORDER BY missioni_completate DESC) AS missione_precedente
FROM cavalieri
)
SELECT * FROM statistiche_cavalieri;
Questa raffinata composizione ti dà una classifica per regno, una somma cumulativa delle missioni e il conteggio della missione precedente — tutto in un unico colpo!
Cosa Abbiamo Imparato Oggi?
Oggi abbiamo esplorato le seguenti funzioni finestra:
- Funzioni di Classifica:
RANK,DENSE_RANKeROW_NUMBER. - Funzioni Cumulative:
SUM,AVGeCOUNT. - LEAD e LAG: Uno sguardo alla riga precedente o successiva.
Cosa Ci Aspetta?
Nella prossima lezione esploreremo la Ricerca Full-Text in PostgreSQL — è ora di dare voce alle tue query e far parlare i tuoi dati!
Allora, sei pronto a regalare alle tue query una vista mozzafiato? Vai avanti, e che le tue funzioni finestra siano sempre perfette!