Panoramica
- Scopri le funzioni della finestra SQL
- Comprendi quali funzioni aggregate mancano e perché abbiamo bisogno di funzioni finestra in SQL
introduzione
I dati stanno proliferando a un ritmo sorprendente, Crescendo 44 zettabytes it 2020! E va da sé che anche la tecnologia per gestire tali enormi quantità di dati sta cambiando in modo proporzionale..
Oggi disponiamo di un'ampia varietà di strumenti come Hive e Spark per gestire i Big Data. Ma, anche se differiscono per certi aspetti, utilizzare ancora le basi di SQL, rendendo molto facile per le persone di ogni ceto sociale manipolare i Big Data con un gioco da ragazzi. Sebbene continuiamo a fallire in alcuni aspetti di SQL. Quindi, in questo articolo, Parlerò di uno di questi aspetti in particolare: funzioni della finestra.
Giusto! Ci sono funzioni finestra in SQL, non è uno scherzo! E considerando l'espressione stupita sul suo viso, questo articolo sembra essere il bisogno del momento. Anche io non ero molto a conoscenza di queste funzioni fino a poco tempo, il che mostra quanto siano sottovalutate queste funzioni.
Ma aspetta fino alla fine di questo articolo perché Window Functions ti lascerà davvero a bocca aperta per la semplicità con cui risolvono problemi così complessi.. E se, ingegneri dei dati, scienziati dei dati, gli analisti di dati e tutti gli altri che flirtano con i dati dovrebbero dare a questi ruoli il merito dovuto.
Prima di andare avanti, Ti suggerisco di familiarizzare con le funzioni SQL di base rivedendo questo articolo: 24 funzioni SQL di uso comune. E se sei interessato ad imparare SQL in un formato di corso, controlla il nostro corso: Structured Query Language (SQL) per la scienza dei dati.
Sommario
- Vi presentiamo il set di dati
- Dove sono in ritardo le funzioni aggregate??
- Cosa sono le funzioni della finestra in SQL??
- Comprensione delle funzioni della finestra SQL – Clausola finita
- Finestre con PARTIZIONE PER
- Organizza le righe all'interno delle partizioni
- Funzioni della finestra
- Numero di riga
- Rango vs Dense_Rank
- Nth_Value
- Ntile
- Piombo e ritardo
- File di codice SQL
Vi presentiamo il set di dati
prima di continuare, lascia che ti presenti il set di dati fittizio su cui lavoreremo in questo articolo. Supponiamo che ci sia una società che mantiene i record dei nomi, lavoro e stipendio del dipendente come segue:
Useremo questo set di dati di esempio per comprendere i concetti in questo articolo.. Ottimo, Iniziamo!
Dove sono in ritardo le funzioni aggregate??
Supponiamo di voler determinare lo stipendio totale di tutti i dipendenti dell'azienda. Come lo faresti? Puoi semplicemente usare la funzione di aggregazione SOMMA () nella colonna STIPENDIO.
Facile.
Che ne dici di determinare lo stipendio totale dei dipendenti per categoria di lavoro?? Usa l'ultima query e aggiungi una clausola GROUP BY nella colonna JOB.
Eccellente!
Ora lascia che ti faccia altre due domande:
- Mostra stipendio totale e stipendio totale per categoria di lavoro insieme al valore di ogni riga.
- Organizza lo stipendio in ordine decrescente all'interno di ogni categoria di lavoro.
L'hai preso? No? Probabilmente?
Questi non erano sicuramente facili come i primi che potresti ottenere all'istante. Ma perché?
Bene, se ci pensi, le query precedenti richiedevano semplici funzioni aggregate per risolvere il problema. Le funzioni aggregate SQL ci danno solo un singolo valore per il gruppo di righe aggregate (pensa alla prima query che abbiamo scritto).
Ma le ultime query non possono essere risolte semplicemente utilizzando tali funzioni. Queste query vogliono che manteniamo l'identità originale delle singole righe, qualcosa che le funzioni aggregate non riescono ad affrontare. Perciò, per risolvere questo tipo di query, abbiamo bisogno di diversi tipi di funzioni: funzioni della finestra.
Cosa sono le funzioni della finestra in SQL??
Funzioni della finestra eseguire calcoli su un insieme di righe correlate tra loro. Ma, a differenza delle funzioni aggregate, le funzioni della finestra non comprimono il risultato delle righe in un singolo valore. Anziché, tutte le righe mantengono la loro identità originale e il risultato calcolato viene restituito per ogni riga.
Comprensione delle funzioni della finestra SQL – Clausola finita
Ad esempio, se dovessi mostrare lo stipendio totale dei dipendenti insieme al valore di ogni riga, sembrerebbe così:
il SU clausola indica una finestra di righe su cui viene applicata una funzione finestra. Può essere utilizzato con funzioni aggregate, come l'abbiamo usato qui con la funzione SOMMA, rendendolo così una funzione finestra. Oppure può essere utilizzato anche con funzioni non aggregate che vengono utilizzate solo come funzioni finestra (impareremo di più su di loro nelle sezioni successive).
Quindi, la sintassi per definire una semplice funzione finestra che emette lo stesso valore per tutte le righe è la seguente:
nome_funzione_finestra () TERMINATO ()
Ma, Che ne dici di applicare la funzione finestra a righe specifiche anziché all'intera tabella??
Finestre con PARTIZIONE PER
il DIVISIONE PER La clausola viene utilizzata insieme alla clausola OVER. Dividi le righe in diverse partizioni. Prossimo, la funzione finestra agisce su queste partizioni.
Ad esempio, per visualizzare lo stipendio totale per categoria di lavoro per tutte le righe, dovremmo modificare la nostra query SQL originale come segue:
Come potete vedere, il salario_di_lavoro_totale La colonna rappresenta la somma delle vendite per quella specifica categoria di lavoro e non per l'intera tabella.
Quindi, la sintassi per definire la funzione della finestra per la partizione di riga è la seguente:
nome_funzione_finestra () TERMINATO ()
Ora, Che ne dici di organizzare le righe all'interno di ogni partizione??
Organizza le righe all'interno delle partizioni
Sappiamo che per organizzare le righe in una tabella, possiamo usare la clausola ORDER BY. Quindi, per organizzare le righe all'interno di ogni partizione, dobbiamo modificare la clausola OVER con la clausola ORDER BY.
Qui, i gradi sono stati divisi in base alla loro categoria di lavoro, come indicato nella colonna LAVORO. Mentre scorre verso il basso, Noterai che la colonna SALARY è stata ordinata in ordine decrescente e il salario_di_lavoro_ordinato La colonna rappresenta il totale parziale per la categoria di lavoro (ricominciare dopo ogni partizione).
Quindi, La sintassi per definire la funzione della finestra per partizionare le righe e disporle in ordine è la seguente:
nome_funzione_finestra () TERMINATO ( )
Funzioni della finestra
Ora che sappiamo come definire le funzioni della finestra usando la clausola OVER e alcune delle sue versioni modificate, Possiamo finalmente passare a lavorare con le funzioni della finestra!
1. Riga_Numero
Qualche volta, il tuo set di dati potrebbe non avere una colonna che descriva l'ordine sequenziale delle righe, come nel caso del nostro set di dati. Quindi, possiamo utilizzare il NUMERO DI RIGA() funzione finestra. Assegna un numero sequenziale univoco a ciascuna riga della tabella.
Notare che la numerazione inizia da 1. Cosa c'è di più, per evitare qualsiasi conflitto con la parola chiave MySQL per la funzione, Ho messo il nome della colonna tra virgolette.
Ma, poiché è una funzione finestra, possiamo anche limitarlo alle partizioni e quindi ordinare quelle partizioni.
Qui, abbiamo suddiviso le righe nella colonna LAVORO e le abbiamo disposte in base allo STIPENDIO del dipendente. Nota come la numerazione ricomincia ogni volta che inizia una nuova partizione.
Ma supponiamo di voler classificare i dipendenti in base ai loro stipendi.
2. Classifica vs Dense_Rank
il CLASSIFICA() La funzione finestra, Come suggerisce il nome, ordina le righe all'interno della partizione in base alla condizione data.
Notare la parte evidenziata. Nel caso di ROW_NUMBER (), abbiamo un numero progressivo. In secondo luogo, nel caso di RANK (), abbiamo lo stesso intervallo per le righe con lo stesso valore.
Ma ecco un problema. Sebbene alle righe con lo stesso valore venga assegnato lo stesso rango, il grado successivo salta il grado mancante. Questo non ci darebbe i risultati desiderati se dovessimo restituire "primi N diversi" valori da una tabella. Perciò, abbiamo una funzione diversa per risolvere questo problema.
il DENSE_RANK () La funzione è simile a RANK () tranne una differenza, non salta alcun intervallo durante l'ordinamento delle righe.
Qui, tutti gli intervalli sono distinti e aumentano in sequenza all'interno di ciascuna partizione. Rispetto alla funzione RANK (), nessun intervallo è stato saltato all'interno di una partizione.
3. Nth_Value
Se vuoi recuperare l'ennesimo valore di una cornice di finestra per un'espressione, puoi usare la funzione finestra NTH_VALUE (espressione, n).
Ad esempio, recuperare il terzo stipendio più alto in ogni categoria di LAVORO, possiamo dividere le righe in base alla colonna LAVORO, quindi ordinare le righe all'interno delle partizioni in base allo stipendio decrescente e, Finalmente, usa la funzione NTH_VALUE per recuperare il valore. Il comando sarà il seguente:
Avrai notato qualcosa di diverso dopo la clausola Order By. Questo è il Clausola quadro. Determinare il sottoinsieme della partizione (la pietra miliare) che verrà utilizzato dalla funzione finestra per calcolare il valore della riga corrente.
Qui, Ho detto che tutte le righe precedenti e successive di una riga corrente verranno considerate all'interno del frame quando si applica la funzione finestra. Ma, Perché ho usato la clausola frame qui e non con altre funzioni?? Questo perché le altre funzioni della finestra funzionano sull'intera partizione, anche se viene fornita una clausola frame. Pero solo NTH_VALUE () può lavorare su frame all'interno di una partizione.
Supponiamo ora di voler generare il primo valore di ogni partizione. Anche se c'è un FIRST_VALUE () funzione anche, Userò NTH_VALUE per lo stesso.
Nello stesso modo, abbiamo anche un LAST_VALUE () funzione. Ma ho intenzione di determinare l'ultimo valore all'interno di ogni partizione come sopra, pur utilizzando un ordine di righe decrescente.
4. Ntile
Qualche volta, potresti voler ordinare le righe all'interno della partizione in un certo numero di gruppi. Questo è utile quando vuoi determinare il percentile, quartile, eccetera. in cui si trova una determinata riga. il NTILE () La funzione viene utilizzata per tali scopi. Restituisce il numero del gruppo per ogni riga nella partizione.
Ad esempio, Troviamo il quartile di ogni riga in base allo STIPENDIO del dipendente:
Allo stesso modo, puoi dividere le righe in un numero diverso di gruppi e calcolare il NTILE per diverse partizioni.
5. Piombo e ritardo
Spesso, potresti voler confrontare il valore della riga corrente con quello della riga precedente o successiva. Aiuta in una facile analisi dei dati. il GUIDA() e RITARDO() Le funzioni della finestra sono lì solo per questo scopo.
Qui, creiamo una nuova colonna contenente SALARY dalla riga successiva all'interno di ogni partizione ordinata per stipendio utilizzando la funzione LEAD. Si noti che l'ultima riga di ogni partizione contiene un valore null perché non esiste una riga successiva da cui estrarre i dati.
Ora, facciamo lo stesso con la funzione LAG.
Qui, creiamo due nuove colonne. La prima colonna contiene SALARY dalla riga precedente all'interno di ogni partizione ordinata per stipendio. Mentre la seconda colonna contiene la differenza tra STIPENDIO della riga precedente e della riga corrente. Come potete vedere, questo è molto utile per una rapida analisi della differenza tra i salari all'interno della stessa partizione.
File di codice SQL
È possibile trovare tutto il codice SQL relativo alle funzioni della finestra per questo articolo in questo link.
Note finali
Abbiamo già visto alcune funzioni della finestra e spero che ora tu possa apprezzare la bellezza delle funzioni della finestra in SQL. Ma l'apprendimento non si ferma qui. Dopo aver imparato i fondamenti, è ora di padroneggiare gli strumenti per gestire i Big Data.
Se vuoi passare al dominio Data Engineering, Suggerisco i seguenti articoli per una facile transizione:
