SQL per la scienza dei dati: comprensione e utilizzo dei join – KDnuggets

Immagine dell'autore
 

La scienza dei dati è un campo interdisciplinare che si basa in gran parte sull'estrazione di approfondimenti e sul prendere decisioni informate da grandi quantità di dati. Uno degli strumenti fondamentali nella cassetta degli attrezzi di un data scientist è SQL (Structured Query Language), un linguaggio di programmazione progettato per la gestione e la manipolazione di database relazionali.

In questo articolo, mi concentrerò su una delle funzionalità più potenti di SQL: i join.

I join SQL consentono di combinare dati da più tabelle di database in base a colonne comuni. In questo modo, puoi unire le informazioni e creare connessioni significative tra set di dati correlati.

Ci sono diversi tipi di join SQL:

• Unione interna
• Giunto esterno sinistro
• Giunto esterno destro
• Unione esterna completa
• Unione incrociata

Spieghiamo ogni tipo.

Un inner join restituisce solo le righe in cui è presente una corrispondenza in entrambe le tabelle che vengono unite. Combina le righe di due tabelle in base a una chiave o colonna condivisa, scartando le righe non corrispondenti.

Lo visualizziamo nel modo seguente.
 

Immagine dell'autore
 

In SQL, questo tipo di join viene eseguito utilizzando le parole chiave JOIN o INNER JOIN.

Un join esterno sinistro restituisce tutte le righe della tabella di sinistra (o prima) e le righe corrispondenti della tabella di destra (o seconda). Se non c'è corrispondenza, restituisce valori NULL per le colonne dalla tabella di destra.

Possiamo visualizzarlo in questo modo.

Immagine dell'autore
 

Quando si desidera utilizzare questo join in SQL, è possibile farlo utilizzando le parole chiave LEFT OUTER JOIN o LEFT JOIN. Ecco un articolo che ne parla join sinistro vs join esterno sinistro.

Un join destro è l'opposto di un join sinistro. Restituisce tutte le righe della tabella di destra e le righe corrispondenti della tabella di sinistra. Se non c'è corrispondenza, restituisce valori NULL per le colonne della tabella di sinistra.

Immagine dell'autore
 

In SQL, questo tipo di join viene eseguito utilizzando le parole chiave RIGHT OUTER JOIN o RIGHT JOIN.

Un full outer join restituisce tutte le righe di entrambe le tabelle, abbinando le righe ove possibile e inserendo i valori NULL per le righe non corrispondenti.

Immagine dell'autore
 

Le parole chiave in SQL per questo join sono FULL OUTER JOIN o FULL JOIN.

Questo tipo di join combina tutte le righe di una tabella con tutte le righe della seconda tabella. In altre parole, restituisce il prodotto cartesiano, cioè tutte le possibili combinazioni delle righe delle due tabelle.

Ecco la visualizzazione che renderà più facile la comprensione.

Immagine dell'autore
 

Quando si esegue il cross join in SQL, la parola chiave è CROSS JOIN.

Per eseguire un join in SQL, è necessario specificare le tabelle che vogliamo unire, le colonne utilizzate per la corrispondenza e il tipo di join che vogliamo eseguire. La sintassi di base per unire le tabelle in SQL è la seguente:

SELECT columns
FROM table1
JOIN table2
ON table1.column = table2.column;

Questo esempio mostra come utilizzare JOIN.

Fai riferimento alla prima (o sinistra) tabella nella clausola FROM. Quindi lo segui con JOIN e fai riferimento alla seconda tabella (oa destra).

Poi arriva la condizione di unione nella clausola ON. Qui è dove specifichi quali colonne utilizzerai per unire le due tabelle. Di solito, è una colonna condivisa che è una chiave primaria in una tabella e la chiave esterna nella seconda tabella.

Nota: una chiave primaria è un identificatore univoco per ogni record in una tabella. Una chiave esterna stabilisce un collegamento tra due tabelle, ovvero è una colonna nella seconda tabella che fa riferimento alla prima tabella. Ti mostreremo negli esempi cosa significa.

Se vuoi usare LEFT JOIN, RIGHT JOIN o FULL JOIN, usa semplicemente queste parole chiave invece di JOIN – tutto il resto nel codice è esattamente lo stesso!

Le cose sono leggermente diverse con CROSS JOIN. Nella sua natura è unire tutte le combinazioni di righe di entrambe le tabelle. Ecco perché la clausola ON non è necessaria e la sintassi è simile a questa.

SELECT columns
FROM table1
CROSS JOIN table2;

In altre parole, fai semplicemente riferimento a una tabella in FROM e alla seconda in CROSS JOIN.

In alternativa, puoi fare riferimento a entrambe le tabelle in FROM e separarle con una virgola: questa è una scorciatoia per CROSS JOIN.

SELECT columns
FROM table1, table2;

C'è anche un modo specifico per unire i tavoli: unire il tavolo con se stesso. Questo è anche chiamato self joining the table.

Non è esattamente un tipo distinto di join, poiché uno qualsiasi dei tipi di join menzionati in precedenza può essere utilizzato anche per l'unione automatica.

La sintassi per l'unione automatica è simile a quella che ti ho mostrato prima. La differenza principale è che si fa riferimento alla stessa tabella in FROM e JOIN.

SELECT columns
FROM table1 t1
JOIN table1 t2
ON t1.column = t2.column;

Inoltre, è necessario assegnare alla tabella due alias per distinguerli. Quello che stai facendo è unire il tavolo con se stesso e trattarlo come due tavoli.

Volevo solo menzionarlo qui, ma non entrerò in ulteriori dettagli. Se sei interessato all'adesione autonoma, consulta questa guida illustrata su auto-unione in SQL.

È ora di mostrarti come tutto ciò che ho menzionato funziona nella pratica. userò Domande dell'intervista SQL JOIN da StrataScratch per mostrare ogni tipo distinto di join in SQL.

1. UNISCITI Esempio

Questa domanda di Microsoft vuole che tu elenchi ogni progetto e calcoli il budget del progetto in base al dipendente.

Progetti costosi

“Dato un elenco di progetti e dipendenti associati a ciascun progetto, calcolare l'importo del budget del progetto assegnato a ciascun dipendente . L'output dovrebbe includere il titolo del progetto e il budget del progetto arrotondato all'intero più vicino. Ordina prima il tuo elenco per progetti con il budget più alto per dipendente.

Dati

La domanda fornisce due tabelle.

ms_progetti

ms_emp_projects

Ora, l'id della colonna nella tabella ms_progetti è la chiave primaria della tabella. La stessa colonna si trova nella tabella ms_emp_projects, anche se con un nome diverso: project_id. Questa è la chiave esterna della tabella, che fa riferimento alla prima tabella.

Userò queste due colonne per unire le tabelle nella mia soluzione.

Code

SELECT title AS project, ROUND((budget/COUNT(emp_id)::FLOAT)::NUMERIC, 0) AS budget_emp_ratio
FROM ms_projects a
JOIN ms_emp_projects b ON a.id = b.project_id
GROUP BY title, budget
ORDER BY budget_emp_ratio DESC;

Ho unito le due tabelle usando JOIN. La tavola ms_progetti è referenziato in FROM, mentre ms_emp_projects viene referenziato dopo JOIN. Ho assegnato a entrambe le tabelle un alias, permettendomi di non utilizzare i nomi lunghi della tabella in seguito.

Ora, devo specificare le colonne su cui voglio unire le tabelle. Ho già menzionato quali colonne sono la chiave primaria in una tabella e la chiave esterna in un'altra tabella, quindi le userò qui.

Uguagliamo queste due colonne perché voglio ottenere tutti i dati in cui l'ID del progetto è lo stesso. Ho anche usato gli alias delle tabelle davanti a ciascuna colonna.

Ora che ho accesso ai dati in entrambe le tabelle, posso elencare le colonne in SELECT. La prima colonna è il nome del progetto e la seconda colonna viene calcolata.

Questo calcolo utilizza la funzione COUNT() per contare il numero di dipendenti per ogni progetto. Quindi divido il budget di ogni progetto per il numero di dipendenti. Inoltre converto il risultato in valori decimali e lo arrotondo a zero cifre decimali.

Uscita

Ecco cosa restituisce la query.

2. LEFT JOIN Esempio

Esercitiamoci con questo join sul file Domanda intervista Airbnb. Vuole che tu trovi il numero di ordini, il numero di clienti e il costo totale degli ordini per ogni città.

Ordini e dettagli dei clienti

“Trova il numero di ordini, il numero di clienti e il costo totale degli ordini per ogni città. Includi solo le città che hanno effettuato almeno 5 ordini e conta tutti i clienti in ciascuna città anche se non hanno effettuato un ordine.

Emetti ogni calcolo insieme al nome della città corrispondente.

Dati

Ti vengono dati i tavoli clienti, ed ordini.

clienti

ordini

Le colonne condivise sono id dalla tabella clienti e cust_id dalla tabella ordini. Userò queste colonne per unire le tabelle.

Code

Ecco come risolvere questo problema usando LEFT JOIN.

SELECT c.city, COUNT(DISTINCT o.id) AS orders_per_city, COUNT(DISTINCT c.id) AS customers_per_city, SUM(o.total_order_cost) AS orders_cost_per_city
FROM customers c
LEFT JOIN orders o ON c.id = o.cust_id
GROUP BY c.city
HAVING COUNT(o.id) >=5;

Faccio riferimento alla tabella clienti in FROM (questa è la nostra tabella di sinistra) e LEFT JOIN con ordini nelle colonne ID cliente.

Ora posso selezionare la città, utilizzare COUNT() per ottenere il numero di ordini e clienti per città e utilizzare SUM() per calcolare il costo totale degli ordini per città.

Per ottenere tutti questi calcoli per città, raggruppo l'output per città.

C'è una richiesta in più nella domanda: “Includi solo le città che hanno fatto almeno 5 ordini…” Uso HAVING per mostrare solo le città con cinque o più ordini per raggiungere questo obiettivo.

La domanda è: perché l'ho usato LEFT JOIN e non ISCRIVITI? L'indizio è nella domanda: "...e contare tutti i clienti in ogni città anche se non hanno effettuato un ordine". È possibile che non tutti i clienti abbiano effettuato ordini. Ciò significa che voglio mostrare tutti i clienti dal tavolo clienti, che si adatta perfettamente alla definizione di LEFT JOIN.

Se avessi usato JOIN, il risultato sarebbe stato sbagliato, poiché avrei perso i clienti che non hanno effettuato alcun ordine.

Nota: la complessità dei join in SQL non si riflette nella loro sintassi ma nella loro semantica! Come hai visto, ogni join è scritto allo stesso modo, cambia solo la parola chiave. Tuttavia, ogni join funziona in modo diverso e, pertanto, può produrre risultati diversi a seconda dei dati. Per questo motivo, è fondamentale comprendere appieno cosa fa ogni join e scegliere quello che restituirà esattamente ciò che desideri!

Uscita

Ora, diamo un'occhiata all'output.

3. JOIN DESTRA Esempio

RIGHT JOIN è l'immagine speculare di LEFT JOIN. Ecco perché avrei potuto facilmente risolvere il problema precedente usando RIGHT JOIN. Lascia che ti mostri come farlo.

Dati

I tavoli rimangono gli stessi; Userò solo un diverso tipo di join.

Code

SELECT c.city, COUNT(DISTINCT o.id) AS orders_per_city, COUNT(DISTINCT c.id) AS customers_per_city, SUM(o.total_order_cost) AS orders_cost_per_city
FROM orders o
RIGHT JOIN customers c ON o.cust_id = c.id GROUP BY c.city
HAVING COUNT(o.id) >=5;

Ecco cosa è cambiato. Dato che sto usando RIGHT JOIN, ho cambiato l'ordine delle tabelle. Ora il tavolo ordini diventa quello di sinistra, e il tavolo clienti quello giusto. La condizione di unione rimane la stessa. Ho appena cambiato l'ordine delle colonne per riflettere l'ordine delle tabelle, ma non è necessario farlo.

Cambiando l'ordine dei tavoli e utilizzando RIGHT JOIN, emetterò nuovamente tutti i clienti, anche se non hanno effettuato alcun ordine.

Il resto della query è lo stesso dell'esempio precedente. Lo stesso vale per l'uscita.

Nota: in pratica, GIUSTO UNISCITI è usato relativamente raramente. LEFT JOIN sembra più naturale per gli utenti SQL, quindi lo usano molto più spesso. Tutto ciò che può essere fatto con RIGHT JOIN può essere fatto anche con LEFT JOIN. Per questo motivo, non esiste una situazione specifica in cui si potrebbe preferire RIGHT JOIN.

Uscita

4. FULL JOIN Esempio

La domanda di Salesforce e Tesla chiede di contare la differenza netta tra il numero di società di prodotti lanciate nel 2020 con il numero di società di prodotti lanciate nell'anno precedente.

Nuovi Prodotti

“Ti viene fornita una tabella dei lanci di prodotti per azienda per anno. Scrivi una query per contare la differenza netta tra il numero di società di prodotti lanciate nel 2020 e il numero di società di prodotti lanciate nell'anno precedente. Uscita il nome delle aziende e una netta differenza di prodotti rilasciati netti per il 2020 rispetto all'anno precedente.

Dati

La domanda fornisce una tabella con le seguenti colonne.

car_lancia

Come diavolo faccio a unirmi ai tavoli quando c'è solo un tavolo? Hmm, vediamo anche quello!

Code

Questa domanda è un po' più complicata, quindi la rivelerò gradualmente.

SELECT company_name, product_name AS brand_2020
FROM car_launches
WHERE YEAR = 2020;

La prima istruzione SELECT trova l'azienda e il nome del prodotto nel 2020. Questa query verrà successivamente trasformata in una sottoquery.

La domanda vuole che tu trovi la differenza tra il 2020 e il 2019. Quindi scriviamo la stessa query ma per il 2019.

SELECT company_name, product_name AS brand_2019
FROM car_launches
WHERE YEAR = 2019;

Ora trasformerò queste query in sottoquery e le unirò utilizzando FULL OUTER JOIN.

SELECT *
FROM (SELECT company_name, product_name AS brand_2020 FROM car_launches WHERE YEAR = 2020) a
FULL OUTER JOIN (SELECT company_name, product_name AS brand_2019 FROM car_launches WHERE YEAR = 2019) b ON a.company_name = b.company_name;

Le sottoquery possono essere trattate come tabelle e, pertanto, possono essere unite. Ho assegnato alla prima sottoquery un alias e l'ho inserito nella clausola FROM. Quindi utilizzo FULL OUTER JOIN per unirlo alla seconda sottoquery nella colonna del nome dell'azienda.

Utilizzando questo tipo di join SQL, tutte le aziende e i prodotti nel 2020 verranno uniti a tutte le aziende e i prodotti nel 2019.

Ora posso finalizzare la mia domanda. Selezioniamo il nome dell'azienda. Inoltre, utilizzerò la funzione COUNT() per trovare il numero di prodotti lanciati in ogni anno e quindi sottrarlo per ottenere la differenza. Infine, raggrupperò l'output per azienda e lo ordinerò anche per azienda in ordine alfabetico.

Ecco l'intera domanda.

SELECT a.company_name, (COUNT(DISTINCT a.brand_2020)-COUNT(DISTINCT b.brand_2019)) AS net_products
FROM (SELECT company_name, product_name AS brand_2020 FROM car_launches WHERE YEAR = 2020) a
FULL OUTER JOIN (SELECT company_name, product_name AS brand_2019 FROM car_launches WHERE YEAR = 2019) b ON a.company_name = b.company_name
GROUP BY a.company_name
ORDER BY company_name;

Uscita

Ecco l'elenco delle aziende e la differenza dei prodotti lanciati tra il 2020 e il 2019.

5. CROSS JOIN Esempio

Questa domanda di Deloitte è ottimo per mostrare come funziona CROSS JOIN.

Massimo di due numeri

“Data una singola colonna di numeri, considera tutte le possibili permutazioni di due numeri assumendo che le coppie di numeri (x,y) e (y,x) siano due diverse permutazioni. Quindi, per ogni permutazione, trova il massimo dei due numeri.

Emetti tre colonne: il primo numero, il secondo numero e il massimo dei due.

La domanda vuole che tu trovi tutte le possibili permutazioni di due numeri assumendo che le coppie di numeri (x, y) e (y, x) siano due permutazioni diverse. Quindi, dobbiamo trovare il massimo dei numeri per ogni permutazione.

Dati

La domanda ci fornisce una tabella con una colonna.

numeri_deloitte

Code

Questo codice è un esempio di CROSS JOIN, ma anche di self join.

SELECT dn1.number AS number1, dn2.number AS number2, CASE WHEN dn1.number > dn2.number THEN dn1.number ELSE dn2.number END AS max_number
FROM deloitte_numbers AS dn1
CROSS JOIN deloitte_numbers AS dn2;

Faccio riferimento alla tabella in FROM e gli do un alias. Quindi lo CROSS JOIN con se stesso facendo riferimento ad esso dopo CROSS JOIN e assegnando al tavolo un altro alias.

Ora è possibile utilizzare un tavolo in quanto sono due. Seleziono il numero di colonna da ogni tabella. Quindi utilizzo l'istruzione CASE per impostare una condizione che mostrerà il numero massimo dei due numeri.

Perché qui viene utilizzato CROSS JOIN? Ricorda, è un tipo di join SQL che mostrerà tutte le combinazioni di tutte le righe di tutte le tabelle. Questo è esattamente ciò che la domanda sta ponendo!

Uscita

Ecco l'istantanea di tutte le combinazioni e il numero più alto delle due.

Ora che sai come utilizzare i join SQL, la domanda è come utilizzare tale conoscenza nella scienza dei dati.

I join SQL svolgono un ruolo cruciale nelle attività di data science come l'esplorazione dei dati, la pulizia dei dati e l'ingegneria delle funzionalità.

Di seguito sono riportati alcuni esempi di come è possibile sfruttare i join SQL:

1. Combinazione di dati: L'unione delle tabelle consente di riunire diverse fonti di dati, consentendo di analizzare le relazioni e le correlazioni tra più set di dati. Ad esempio, l'unione di una tabella dei clienti con una tabella delle transazioni può fornire informazioni dettagliate sul comportamento dei clienti e sui modelli di acquisto.

2. Convalida dei dati: I join possono essere utilizzati per convalidare la qualità e l'integrità dei dati. Confrontando i dati di tabelle diverse, puoi identificare incoerenze, valori mancanti o valori anomali. Questo ti aiuta nella pulizia dei dati e garantisce che i dati utilizzati per l'analisi siano accurati e affidabili.

3. Ingegneria delle caratteristiche: I join possono essere fondamentali per creare nuove funzionalità per i modelli di machine learning. Unendo le tabelle pertinenti, puoi estrarre informazioni significative e generare funzionalità che acquisiscono relazioni importanti all'interno dei dati. Questo può migliorare il potere predittivo dei tuoi modelli.

4. Aggregazione e analisi: I join consentono di eseguire aggregazioni e analisi complesse su più tabelle. Combinando i dati provenienti da varie fonti, puoi ottenere una visione completa dei dati e ricavare preziose informazioni. Ad esempio, l'unione di una tabella delle vendite con una tabella dei prodotti può aiutarti ad analizzare le prestazioni delle vendite per categoria di prodotto o regione.

Come ho già detto, la complessità dei join non si vede nella loro sintassi. Hai visto che la sintassi è relativamente semplice.

Le migliori pratiche per i join riflettono anche questo, poiché non si occupano della codifica stessa ma di cosa fa il join e come si comporta.

Per ottenere il massimo dai join in SQL, considera le seguenti best practice.

1. Comprendi i tuoi dati: Acquisire familiarità con la struttura e le relazioni all'interno dei dati. Questo ti aiuterà a scegliere il tipo appropriato di join e selezionare le colonne giuste per la corrispondenza.

2. Usa indici: Se le tue tabelle sono grandi o vengono unite frequentemente, prendi in considerazione l'aggiunta di indici sulle colonne utilizzate per l'unione. Gli indici possono migliorare significativamente le prestazioni delle query.

3. Sii consapevole delle prestazioni: L'unione di tabelle di grandi dimensioni o di più tabelle può essere computazionalmente costosa. Ottimizza le query filtrando i dati, utilizzando i tipi di join appropriati e considerando l'utilizzo di tabelle o sottoquery temporanee.

4. Testare e convalidare: Convalida sempre i risultati del tuo join per assicurarne la correttezza. Esegui controlli di integrità e verifica che i dati uniti siano in linea con le tue aspettative e la logica aziendale.

I join SQL sono un concetto fondamentale che ti consente come data scientist di unire e analizzare i dati da più fonti. Comprendendo i diversi tipi di join SQL, padroneggiandone la sintassi e sfruttandoli in modo efficace, i data scientist possono sbloccare informazioni preziose, convalidare la qualità dei dati e guidare il processo decisionale basato sui dati.

Ti ho mostrato come farlo in cinque esempi. Ora tocca a te sfruttare la potenza di SQL e dei join per i tuoi progetti di data science e ottenere risultati migliori.
 
 
Nato Rosidi è un data scientist e nella strategia di prodotto. È anche un professore a contratto che insegna analisi ed è il fondatore di Strata Scratch, una piattaforma che aiuta i data scientist a prepararsi per le loro interviste con domande di interviste reali delle migliori aziende. Connettiti con lui su Twitter: Strata Scratch or LinkedIn.
 

• Distribuzione di contenuti basati su SEO e PR. Ricevi amplificazione oggi.
• PlatoData.Network Generativo verticale Ai. Potenzia te stesso. Accedi qui.
• PlatoAiStream. Intelligenza Web3. Conoscenza amplificata. Accedi qui.
• PlatoneESG. Automobilistico/VE, Carbonio, Tecnologia pulita, Energia, Ambiente, Solare, Gestione dei rifiuti. Accedi qui.
• BlockOffset. Modernizzare la proprietà della compensazione ambientale. Accedi qui.
• Fonte: https://www.kdnuggets.com/2023/08/sql-data-science-understanding-leveraging-joins.html?utm_source=rss&utm_medium=rss&utm_campaign=sql-for-data-science-understanding-and-leveraging-joins