Inizi qui? Questa lezione fa parte di un tutorial completo sull’uso di SQL per l’analisi dei dati. Controlla l’inizio.
In questa lezione tratteremo:
- Introduzione alle funzioni delle finestre
- Sintassi delle finestre di base
- I soliti sospetti: SUM, COUNT e AVG
- ROW_NUMBER ()
- RANK () e DENSE_RANK ()
- NTILE
- LAG e LEAD
- Definizione di un alias di finestra
- Tecniche avanzate di creazione di finestre
Questa lezione utilizza i dati del programma Capital Bikeshare di Washington DC, che pubblica dati storici dettagliati a livello di viaggio sul loro sito web. I dati sono stati scaricati a febbraio 2014, ma sono limitati ai dati raccolti durante il primo trimestre del 2012. Ogni riga rappresenta una corsa. La maggior parte dei campi è autoesplicativa, tranne rider_type: “Registered” indica un abbonamento mensile al programma di rideshare, “Casual” indica che il ciclista ha acquistato un pass di 3 giorni. I campi start_time e end_time sono stati eliminati dai loro moduli originali per adattarsi alla formattazione della data SQL: sono memorizzati in questa tabella come timestamp.
Introduzione alle funzioni finestra
La documentazione di PostgreSQL fa un ottimo lavoro nell’introdurre il concetto di funzioni finestra:
Una funzione finestra esegue un calcolo su un insieme di righe della tabella che sono in qualche modo correlate alla riga corrente. Questo è paragonabile al tipo di calcolo che può essere fatto con una funzione aggregata. Ma a differenza delle normali funzioni aggregate, l’uso di una finestra la funzione non fa sì che le righe vengano raggruppate in una singola riga di output: le righe mantengono le loro identità separate. Dietro le quinte, la funzione finestra è in grado di accedere a più della semplice riga corrente del risultato della query.
L’esempio più pratico di questo è un totale parziale:
Puoi vedere che la query sopra crea un’aggregazione (running_total) senza utilizzare GROUP BY. Analizziamo la sintassi e vediamo come funziona.
Sintassi delle finestre di base
La prima parte dell’aggregazione di cui sopra, SUM(duration_seconds) , assomiglia molto a qualsiasi altra aggregazione. L’aggiunta di OVER la designa come funzione finestra. Puoi leggere l’aggregazione precedente come “prendi la somma di duration_seconds sull’intero set di risultati, in ordine start_time. “
Se” desideri restringere la finestra dall’intero set di dati ai singoli gruppi all’interno il set di dati, puoi utilizzare PARTITION BY per farlo:
La query precedente raggruppa e ordina la query da start_terminal . All’interno di ogni valore di start_terminal, è ordinato per start_time e le somme totali correnti nella riga corrente e in tutte le righe precedenti di duration_seconds. Scorri verso il basso fino a quando il valore start_terminal non cambia e noterai che running_total ricomincia. Questo è ciò che accade quando si raggruppa utilizzando PARTITION BY. Nel caso in cui ORDER BY rimanga perplesso, ordina semplicemente in base alla colonna designata (s) allo stesso modo della clausola ORDER BY, tranne per il fatto che tratta ogni partizione come separata. Crea anche il totale parziale: senza ORDER BY, ogni valore sarà semplicemente una somma di tutti i valori duration_seconds nel rispettivo start_terminal. Prova a eseguire la query precedente senza ORDER BY per avere un’idea:
ORDER e PARTITION definisce ciò che viene chiamato” finestra “: il sottoinsieme ordinato di dati su cui vengono effettuati i calcoli.
Nota: non è possibile” utilizzare le funzioni della finestra e le aggregazioni standard nella stessa query. Più specificamente, non puoi “includere funzioni finestra in una clausola GROUP BY.
Problema di pratica
Scrivi una modifica della query della query di esempio sopra che mostra la durata di ogni corsa come percentuale del tempo totale accumulato dai ciclisti da ogni start_terminal
Prova Vedi la risposta
I soliti sospetti: SUM, COUNT e AVG
Quando si utilizzano le funzioni della finestra, è possibile applicare gli stessi aggregati che si farebbero in circostanze normali: SUM, COUNT e AVG. Il modo più semplice per capirli è rieseguire l’esempio precedente con alcune funzioni aggiuntive. Crea
In alternativa, le stesse funzioni con ORDER BY:
Assicurati di collegare le due query precedenti in Mode ed eseguirle. Il prossimo problema pratico è molto simile agli esempi, quindi prova a modificare il codice sopra invece di iniziare da zero.
Problema pratico
Scrivi una query che mostri un totale corrente della durata dei giri in bicicletta (simile al ultimo esempio), ma raggruppati per end_terminal e con durata della corsa ordinata in ordine decrescente.
Prova Vedi la risposta
ROW_NUMBER ()
ROW_NUMBER() fa proprio quello che sembra: mostra il numero di una data riga. Inizia con 1 e numera le righe in base alla parte ORDER BY dell’istruzione window. ROW_NUMBER() non richiede di specificare una variabile tra parentesi:
L’uso della clausola PARTITION BY ti consentirà di ricominciare a contare 1 in ogni partizione. La seguente query ricomincia il conteggio per ogni terminale:
RANK () e DENSE_RANK ()
RANK() è leggermente diverso da ROW_NUMBER(). Se ordini per start_time, ad esempio, potrebbe accadere che alcuni terminal abbiano corse con due orari di inizio identici. In questo caso, viene assegnato loro lo stesso rango, mentre ROW_NUMBER() dà loro numeri diversi. Nella query seguente, noti la quarta e la quinta osservazione per start_terminal 31000: a entrambe viene assegnato un rango 4 e il risultato seguente riceve un rango 6:
Puoi anche utilizzare DENSE_RANK() invece di RANK() a seconda dell’applicazione. Immagina una situazione in cui tre voci hanno lo stesso valore. Utilizzando entrambi i comandi, otterranno tutti lo stesso grado. Per il bene di questo esempio, diciamo che “s” 2. ” Ecco come i due comandi valuterebbero i risultati successivi in modo diverso:
-
RANK()darebbe alle righe identiche un rango 2, quindi salterà i ranghi 3 e 4, quindi il risultato successivo sarebbe 5 -
DENSE_RANK()darebbe comunque a tutte le righe identiche un rango 2, ma la riga successiva sarebbe 3 – no i ranghi verrebbero saltati.
Problema pratico
Scrivi una query che mostri le 5 corse più lunghe da ciascun terminal di partenza, ordinato per terminal e dalle corse più lunghe a quelle più brevi all’interno di ogni terminal. Limita alle corse avvenute prima dell’8 gennaio 2012.
Prova Vedi la risposta
NTILE
Puoi utilizzare le funzioni della finestra per identificare in quale percentile (o quartile o qualsiasi altra suddivisione) cade una data riga. La sintassi è NTILE(*# of buckets*). In questo caso, ORDER BY determina quale colonna utilizzare per determinare i quartili (o qualsiasi numero di “tessere specificare). Ad esempio:
esaminando i risultati della query precedente, puoi vedere che la colonna percentile non calcola esattamente come potresti aspettarti. Se solo tu aveva due record e stavi misurando i percentili, ti aspetteresti che un record definisse il 1 ° percentile e l’altro record definisse il 100 ° percentile. Utilizzando la funzione NTILE, ciò che “vedresti in realtà è un record nel 1 ° percentile e uno nel 2 ° percentile. Puoi vederlo nei risultati per start_terminal 31000: la colonna percentile sembra solo una classifica numerica. Se scorri verso il basso fino a start_terminal 31007, può vedere che calcola correttamente i percentili perché ci sono più di 100 record per quel start_terminal. Se “stai lavorando con finestre molto piccole, tienilo a mente e considera l’utilizzo di quartili o di dimensioni simili bande.
Problema pratico
Scrivi una query che mostri solo la durata del viaggio e il percentile in cui tale durata cade (sull’intero set di dati, non partizionata dal terminale).
Provalo Vedi la risposta
LAG e LEAD
Spesso può essere utile confrontare le righe con le righe precedenti o seguenti, soprattutto se hai i dati in un ordine sensato. Puoi utilizzare LAG o LEAD per creare colonne che estraggono valori da altri righe: tutto ciò che devi fare è inserire da quale colonna eseguire il pull e quante righe di distanza desideri eseguire il pull. LAG estrae dalle righe precedenti e LEAD estrae dalle seguenti righe:
Ciò è particolarmente utile se si desidera calcolare differenze tra le righe:
La prima riga della colonna difference è nulla perché non esiste una riga precedente da cui estrarre. Allo stesso modo, l’utilizzo di LEAD creerà valori nulli alla fine del set di dati. Se vuoi rendere i risultati un po ‘più chiari, puoi racchiuderlo in una query esterna per rimuovere i valori nulli:
Definire un alias di finestra
Se stai “ripensando a scrivere diverse funzioni della finestra nella stessa query, utilizzando la stessa finestra, è possibile creare un alias.Prendi il NTILE esempio sopra:
Questo può essere riscritto come:
Il WINDOW la clausola, se inclusa, dovrebbe sempre venire dopo la WHERE clausola.
Tecniche di windowing avanzate
Puoi controllare un elenco completo di finestre funzioni in Postgres (la sintassi utilizzata dalla modalità) nella documentazione di Postgres. Se stai usando le funzioni della finestra su un database connesso, dovresti guardare la guida alla sintassi appropriata per il tuo sistema.