Risolvere 5 problemi complessi di SQL Query complicate spiegate
Solve 5 complex SQL Query problems explained.
Le 5 cose più difficili che Josh Berry, un professionista dell’analisi dati da 15 anni, ha sperimentato durante il passaggio da Python a SQL. Offrendo esempi, codice SQL e una risorsa per personalizzare il SQL al proprio progetto.
Molti di noi hanno sperimentato il nucleo di potenza di velocità ed efficienza offerto dalla centralizzazione del calcolo all’interno del Cloud Data Warehouse. Sebbene ciò sia vero, molti di noi hanno anche capito che, come per qualsiasi cosa, questo valore ha i suoi svantaggi.
Uno dei principali svantaggi di questo approccio è che è necessario imparare ed eseguire query in lingue diverse, in particolare SQL. Sebbene scrivere SQL sia più veloce e meno costoso rispetto alla creazione di un’infrastruttura secondaria per eseguire Python (sul tuo laptop o sui server in ufficio), comporta molte complessità diverse a seconda delle informazioni che l’analista dei dati vuole estrarre dal cloud warehouse. Il passaggio ai data warehouse cloud aumenta l’utilità di SQL complesse rispetto a Python. Essendo passato attraverso questa esperienza personalmente, ho deciso di registrare le specifiche trasformazioni che sono le più dolorose da imparare ed eseguire in SQL e fornire il SQL effettivo necessario per alleviare parte di questo dolore per i miei lettori.
- Diventa un Ingegnere di ML con Declarative ML.
- Essential MLOps Un eBook Gratuito
- AgentGPT Agenti AI autonomi nel tuo Browser
Per aiutare il tuo flusso di lavoro, noterai che fornisco esempi della struttura dei dati prima e dopo che la trasformazione sia eseguita, in modo da poter seguire e convalidare il tuo lavoro. Ho anche fornito il SQL effettivo necessario per eseguire ciascuna delle 5 trasformazioni più difficili. Avrai bisogno di un nuovo SQL per eseguire la trasformazione su più progetti man mano che i tuoi dati cambiano. Abbiamo fornito collegamenti a SQL dinamico per ogni trasformazione in modo da poter continuare a catturare il SQL necessario per la tua analisi su base as needed!
Date Spines
Non è chiaro da dove sia originato il termine “date spine”, ma anche coloro che non conoscono il termine sono probabilmente familiari con ciò che è.
Immagina di analizzare i tuoi dati di vendita giornalieri e che appaia così:
| data_vendita | prodotto | vendite |
| 2022-04-14 | A | 46 |
| 2022-04-14 | B | 409 |
| 2022-04-15 | A | 17 |
| 2022-04-15 | B | 480 |
| 2022-04-18 | A | 65 |
| 2022-04-19 | A | 45 |
| 2022-04-19 | B | 411 |
Nessuna vendita è avvenuta il 16 e il 17, quindi le righe mancano completamente. Se stessimo cercando di calcolare le vendite giornaliere medie o di costruire un modello di previsione delle serie temporali, questo formato sarebbe un problema importante. Quello che dobbiamo fare è inserire righe per i giorni mancanti.
Ecco il concetto di base:
- Genera o seleziona date univoche
- Genera o seleziona prodotti univoci
- Unisci tutte le combinazioni di 1 e 2 (prodotto cartesiano)
- Unisci esternamente #3 ai tuoi dati originali
SQL personalizzabile per la creazione di date spine
Il risultato finale sarà così:
| data_vendita | prodotto | vendite |
| 2022-04-14 | A | 46 |
| 2022-04-14 | B | 409 |
| 2022-04-15 | A | 17 |
| 2022-04-15 | B | 480 |
| 2022-04-16 | A | 0 |
| 2022-04-16 | B | 0 |
| 2022-04-17 | A | 0 |
| 2022-04-17 | B | 0 |
| 2022-04-18 | A | 65 |
| 2022-04-18 | B | 0 |
| 2022-04-19 | A | 45 |
| 2022-04-19 | B | 411 |
Pivot / Unpivot
A volte, durante un’analisi, si desidera ristrutturare la tabella. Ad esempio, potremmo avere una lista di studenti, materie e voti, ma vogliamo separare le materie in ogni colonna. Tutti conosciamo e amiamo Excel per le sue tabelle pivot. Ma hai mai provato a farlo in SQL? Non solo ogni database ha fastidiose differenze su come PIVOT è supportato, ma la sintassi è poco intuitiva e facilmente dimenticabile.
Prima:
| Studente | Materia | Voto |
| Jared | Matematica | 61 |
| Jared | Geografia | 94 |
| Jared | Educazione Fisica | 98 |
| Patrick | Matematica | 99 |
| Patrick | Geografia | 93 |
| Patrick | Educazione Fisica | 4 |
SQL personalizzabile per Pivot
Risultato:
| Studente | Matematica | Geografia | Educazione Fisica |
| Jared | 61 | 94 | 98 |
| Patrick | 99 | 93 | 4 |
One-hot Encoding
Questo non è necessariamente difficile ma è dispendioso in termini di tempo. La maggior parte dei data scientist non considera di fare one-hot-encoding in SQL. Anche se la sintassi è semplice, preferiscono trasferire i dati dal data warehouse piuttosto che compiere l’arduo compito di scrivere una dichiarazione CASE di 26 righe. Non li biasimiamo!
Tuttavia, consigliamo di sfruttare il tuo data warehouse e la sua potenza di elaborazione. Ecco un esempio che utilizza STATE come colonna da one-hot-encoding.
Prima:
| NomeBambino | Stato | Qta |
| Alice | AL | 156 |
| Alice | AK | 146 |
| Alice | PA | 654 |
| … | … | … |
| Zelda | NY | 417 |
| Zelda | AL | 261 |
| Zelda | CO | 321 |
SQL personalizzabile per la codifica One-Hot
Risultato:
| Nome Bambino | Stato | Stato_AL | Stato_AK | … | Stato_CO | Quantità |
| Alice | AL | 1 | 0 | … | 0 | 156 |
| Alice | AK | 0 | 1 | … | 0 | 146 |
| Alice | PA | 0 | 0 | … | 0 | 654 |
| … | … | … | … | |||
| Zelda | NY | 0 | 0 | … | 0 | 417 |
| Zelda | AL | 1 | 0 | … | 0 | 261 |
| Zelda | CO | 0 | 0 | … | 1 | 321 |
Analisi del carrello della spesa
Quando si effettua un’analisi del carrello della spesa o si cercano regole di associazione, il primo passo è spesso formattare i dati per aggregare ogni transazione in un unico record. Questo può essere impegnativo per il tuo laptop, ma il tuo data warehouse è progettato per elaborare questi dati in modo efficiente.
Dati di transazione tipici:
| NUMEROORDINEDIVENDITA | CLIENTEKEY | NOMEPRODOTTOSINGOLO | PREZZOLISTINO | PESO | DATADIORDINE |
| SO51247 | 11249 | Mountain-200 Black | 2294,99 | 23,77 | 1/1/2013 |
| SO51247 | 11249 | Bottiglia d’acqua – 30 oz. | 4,99 | 1/1/2013 | |
| SO51247 | 11249 | Mountain Bottle Cage | 9,99 | 1/1/2013 | |
| SO51246 | 25625 | Sport-100 Helmet | 34,99 | 31/12/2012 | |
| SO51246 | 25625 | Bottiglia d’acqua – 30 oz. | 4,99 | 31/12/2012 | |
| SO51246 | 25625 | Road Bottle Cage | 8,99 | 31/12/2012 | |
| SO51246 | 25625 | Touring-1000 Blue | 2384,07 | 25,42 | 31/12/2012 |
SQL personalizzabile per il carrello della spesa
Risultato:
| NUMTRANSACTIONS | ENGLISHPRODUCTNAME_LISTAGG |
| 207 | Mountain Bottle Cage, Water Bottle – 30 oz. |
| 200 | Mountain Tire Tube, Patch Kit/8 Patches |
| 142 | LL Road Tire, Patch Kit/8 Patches |
| 137 | Patch Kit/8 Patches, Road Tire Tube |
| 135 | Patch Kit/8 Patches, Touring Tire Tube |
| 132 | HL Mountain Tire, Mountain Tire Tube, Patch Kit/8 Patches |
Aggregazioni di serie temporali
Le aggregazioni di serie temporali non sono utilizzate solo dai data scientist, ma anche per analisi. Ciò che le rende difficili è che le funzioni di finestra richiedono che i dati siano formattati correttamente.
Per esempio, se si vuole calcolare la media delle vendite degli ultimi 14 giorni, le funzioni di finestra richiedono di avere tutti i dati di vendita suddivisi in una riga per giorno. Sfortunatamente, chiunque abbia lavorato con dati di vendita sa che di solito sono memorizzati a livello di transazione. Ecco dove entrano in gioco le aggregazioni di serie temporali. È possibile creare metriche storiche aggregate senza riformattare l’intero dataset. Inoltre, è utile se vogliamo aggiungere più metriche contemporaneamente:
- Media delle vendite degli ultimi 14 giorni
- Acquisto più grande degli ultimi 6 mesi
- Conteggio dei tipi di prodotto distinti negli ultimi 90 giorni
Se si volesse utilizzare le funzioni di finestra, ogni metrica dovrebbe essere costruita indipendentemente con diversi passaggi.
Un modo migliore per gestire questo, è utilizzare le espressioni di tabella comuni (CTE) per definire ciascuna delle finestre storiche, pre-aggregate.
Per esempio:
| ID Transazione | ID Cliente | Tipo di prodotto | Importo d’acquisto | Data della transazione |
| 65432 | 101 | Grocery | 101,14 | 2022-03-01 |
| 65493 | 101 | Grocery | 98,45 | 2022-04-30 |
| 65494 | 101 | Automotive | 239,98 | 2022-05-01 |
| 66789 | 101 | Grocery | 86,55 | 2022-05-22 |
| 66981 | 101 | Pharmacy | 14 | 2022-06-15 |
| 67145 | 101 | Grocery | 93,12 | 2022-06-22 |
SQL personalizzabile per l’aggregazione di serie temporali SQL
Risultato:
| ID transazione | ID cliente | Tipo di prodotto | Importo di acquisto | Data transazione | Media vendite passate 14 giorni | Acquisto massimo degli ultimi 6 mesi | Conteggio distinti tipi di prodotto negli ultimi 90 giorni |
| 65432 | 101 | Alimentari | 101,14 | 2022-03-01 | 101,14 | 101,14 | 1 |
| 65493 | 101 | Alimentari | 98,45 | 2022-04-30 | 98,45 | 101,14 | 2 |
| 65494 | 101 | Automobili | 239,98 | 2022-05-01 | 169,21 | 239,98 | 2 |
| 66789 | 101 | Alimentari | 86,55 | 2022-05-22 | 86,55 | 239,98 | 2 |
| 66981 | 101 | Farmaci | 14 | 2022-06-15 | 14 | 239,98 | 3 |
| 67145 | 101 | Alimentari | 93,12 | 2022-06-22 | 53,56 | 239,98 | 3 |
Conclusione
Spero che questo articolo aiuti a fare luce sui diversi problemi che un professionista dei dati incontrerà quando lavora all’interno dello stack dati moderno. L’SQL è una spada a doppio taglio quando si tratta di interrogare il data warehouse nel cloud. Sebbene la centralizzazione del calcolo nel data warehouse nel cloud aumenti la velocità, talvolta richiede alcune competenze SQL aggiuntive. Spero che questo articolo abbia aiutato a rispondere alle domande e fornisca la sintassi e le informazioni di base necessarie per affrontare questi problemi.
Josh Berry ( @Twitter ) è responsabile della scienza dei dati orientata al cliente presso Rasgo ed è nel settore dei dati e dell’analisi dal 2008. Josh ha trascorso 10 anni in Comcast dove ha creato il team di scienze dei dati ed è stato uno dei proprietari chiave dell’archivio delle funzionalità di Comcast sviluppato internamente, uno dei primi archivi delle funzionalità a entrare in commercio. Dopo Comcast, Josh è stato un leader fondamentale nella creazione della scienza dei dati orientata al cliente presso DataRobot. Nel suo tempo libero, Josh esegue analisi complesse su argomenti interessanti come baseball, corse di F1, previsioni del mercato immobiliare e altro ancora.
