Comandi SQL per data scientist

Come data scientist o data analyst, i dati sono naturalmente al centro del tuo lavoro. Le fonti da cui potresti raccogliere i dati sono molte. Spesso i dati risiedono in un database SQL: in tal caso, una buona conoscenza dei comandi di query SQL può essere essenziale per svolgere al meglio questo ruolo. Questo articolo ti presenterà alcuni dei comandi più basilari, fino a operazioni più avanzate che ti torneranno utili quando lavori come data analyst o data scientist.

Per capire i comandi elencati sopra, useremo il database open source DVD rental per mostrarti come possono essere utilizzati. Puoi trovare il diagramma delle relazioni tra entità (ERD) qui.

Ai fini di questo articolo, abbiamo configurato il database in locale ed eseguito tutte le query usando pgAdmin4, quindi stiamo usando postgreSQL. Molti, se non tutti, i comandi dovrebbero funzionare anche in altri server SQL. L'installazione di postgreSQL e la configurazione del database sono fuori dall'ambito di questo articolo; ti invitiamo a consultare altre risorse come la Guida per principianti a PostgreSQL di DataCamp per suggerimenti su questi processi.

Recupero dati semplice

SELECT FROM

L'esempio più semplice di recupero è ottenere tutti i contenuti di una specifica tabella del database. Se volessimo conoscere tutte le categorie di film nella tabella "Category", potresti provare a inserire questo comando:

SELECT *
FROM category

Che restituisce (mostrate solo le prime 10 righe):

Qui abbiamo specificato il nome della tabella dopo FROM. Poiché vogliamo tutto il contenuto della tabella, abbiamo usato * dopo SELECT per indicare che vogliamo ogni colonna.

DISTINCT

Ci sono casi in cui ci interessano solo i valori unici. Per esempio, e se volessimo conoscere tutte le classificazioni MPAA dalla tabella film? Usando DISTINCT possiamo ottenere solo i valori unici:

SELECT DISTINCT(rating)
FROM film

Che restituisce:

Recupero dati con condizioni semplici

WHERE

Ci sono anche situazioni in cui vogliamo recuperare solo le voci che soddisfano determinate condizioni. Possiamo fornire condizioni che devono essere soddisfatte prima di restituire i dati usando la clausola WHERE. In sostanza, stiamo filtrando le voci in base alla condizione. Per esempio, e se volessimo conoscere solo i film con classificazione PG-13?

SELECT title
FROM film
WHERE rating = 'PG-13'

Che restituisce (mostrate solo le prime 10 righe):

Inoltre, possiamo ottenere l'elenco dei film che hanno un prezzo di noleggio pari o superiore a 4,5:

SELECT title, rental_rate
FROM film
WHERE rental_rate >= 4.5

Che restituisce (mostrate solo le prime 10 righe):

ORDER BY

Ordinare il risultato restituito è spesso utile per comprendere meglio i dati. Che ne dici di ottenere lo stesso elenco di film PG-13 di sopra, ma ordinato per durata dal più lungo al più corto, con la relativa durata? Usando ORDER BY, si fa facilmente:

SELECT title, length
FROM film
WHERE rating = 'PG-13'
ORDER BY length DESC

Che restituisce (mostrate solo le prime 10 righe):

LIMIT

Inoltre, a volte siamo interessati solo a un numero limitato di voci. Qui, in effetti, ci interessano solo i 10 film PG-13 più lunghi, quindi possiamo usare la clausola LIMIT.

SELECT title, length
FROM film
WHERE rating = 'PG-13'
ORDER BY length DESC
LIMIT 10

Che restituisce:

Aggregazioni

GROUP BY & COUNT( )

Le aggregazioni sono spesso usate per ottenere un riepilogo del dataset e ricavarne insight. Di frequente si usano insieme alla clausola GROUP BY. Per esempio, se dobbiamo sapere quante volte ciascun cliente ha noleggiato finora, possiamo contare il numero di noleggi e ordinare i risultati con chi ha noleggiato di più in alto.

SELECT customer_id, COUNT(rental_id)
FROM rental
GROUP BY customer_id
ORDER BY COUNT(rental_id) DESC

Che restituisce (mostrate solo le prime 10 righe):

SUM( )

E per sapere quanti soldi ha speso ciascun cliente? Possiamo farlo usando la funzione di aggregazione SUM. Qui vogliamo chi ha speso di più in cima.

SELECT customer_id, SUM(amount)
FROM payment
GROUP BY customer_id
ORDER BY SUM(amount) DESC

Che restituisce (mostrate solo le prime 10 righe):

AVG( ) & HAVING

Possiamo aggiungere ulteriori condizioni usando HAVING dopo aver calcolato un'aggregazione raggruppata. Per esempio, vogliamo conoscere le categorie di classificazione MPAA che hanno film più lunghi di 115 minuti in media?

SELECT rating, AVG(length)
FROM film
GROUP BY rating
HAVING AVG(length) > 115

Che restituisce:

MIN( ) & Alias

Il raggruppamento non è sempre necessario quando si usa un'aggregazione. Se vuoi conoscere la durata del film più corto, possiamo semplicemente usare la funzione MIN. Vogliamo anche che la colonna si chiami 'shortest_movie_length', cosa che si ottiene assegnando un alias tramite 'AS'.

SELECT MIN(length) AS shortest_movie_length
FROM film

Che restituisce (mostrate solo le prime 10 righe):

Puoi anche usare un alias per i nomi delle tabelle. Questo è utile quando lavori con nomi di tabella lunghi, soprattutto quando fai join tra tabelle (vedi sotto). Facciamo anche attenzione al tipo di alias che utilizziamo, perché può cambiare drasticamente la leggibilità del codice.

Esistono altre funzioni di aggregazione utili, e puoi saperne di più nel corso Introduction to SQL.

Join

INNER JOIN (JOIN)

Avrai probabilmente notato che si può fare ben poco guardando una sola tabella. Spesso vogliamo osservare i dati provenienti da più tabelle. Qui entrano in gioco i JOIN. Possiamo unire tabelle in base a una chiave in comune. Per esempio, se stessimo cercando i film che non sono in inglese:

SELECT film.title, language.name
FROM film
JOIN language
    ON film.language_id = language.language_id
WHERE language.name != 'English'

(Nota: in questo database non ci sono film in lingue diverse dall'inglese. Con questa query otterrai una tabella vuota.)

Puoi anche unire più tabelle. Per esempio, sotto vogliamo conoscere le categorie e la loro valutazione media corrispondente. Abbiamo anche restituito i valori ordinati per valutazione media, dalla più alta.

SELECT category.name, AVG(film.rental_rate) AS average_rating
FROM film
JOIN film_category
    ON film.film_id = film_category.film_id
JOIN category
    ON film_category.category_id = category.category_id
GROUP BY category.name
ORDER BY average_rating DESC

Che restituisce (mostrate solo le prime 10 righe):

Esistono molti tipi di join: INNER JOIN, LEFT JOIN, RIGHT JOIN, OUTER JOIN, CROSS JOIN. Qui abbiamo usato JOIN, che di default è un INNER JOIN, in cui ottieni le voci solo se sono presenti in entrambe le tabelle. Se vuoi saperne di più sulle differenze tra questi join, dai un'occhiata al corso Joining Data in SQL.

Cambio di tipi di dato

CAST( )

Nell'esempio usato sopra in cui abbiamo calcolato importi in dollari, potresti aver notato che SQL vede i valori solo come numeri. Possiamo convertirli in importi in dollari usando la funzione CAST:

SELECT customer_id, CAST(SUM(amount) AS money)
FROM payment
GROUP BY customer_id
ORDER BY SUM(amount) DESC

Che restituisce (mostrate solo le prime 10 righe):

Il denaro non è l'unica cosa in cui possiamo convertire i valori. Possiamo trasformare numeri in float, testo e persino in data e ora.

ROUND( )

Allo stesso modo, possiamo anche arrotondare i numeri. Nell'esempio sopra in cui abbiamo calcolato la valutazione media di noleggio, ci sono molte cifre decimali nei valori calcolati. Possiamo usare la funzione ROUND per arrotondare a due decimali:

SELECT category.name, ROUND(AVG(film.rental_rate), 2) AS average_rating
FROM film
JOIN film_category
    ON film.film_id = film_category.film_id
JOIN category
    ON film_category.category_id = category.category_id
GROUP BY category.name
ORDER BY average_rating DESC

Che restituisce (mostrate solo le prime 10 righe):

Condizioni complesse

Istruzione CASE

Puoi immaginare che, combinando molte delle funzioni e clausole sopra, sia possibile eseguire query piuttosto complesse. Tuttavia, ci sono situazioni in cui le basi non bastano. Per esempio, se volessimo aggiungere una colonna con categorie di durata dei film. Possiamo etichettare i film come 'Short Film' se sotto i 50 minuti, 'Long Film' se oltre 150 minuti e 'Medium Film' per il resto. Possiamo farlo usando un'istruzione CASE:

SELECT title, length,
CASE
    WHEN length < 50 THEN 'Short Film'
    WHEN length > 150 THEN 'Long Film'
    ELSE 'Medium Film'
END AS Movie_Length_Category
FROM film

Che restituisce (mostrate solo le prime 10 righe):

Come puoi vedere, il risultato viene restituito quando è soddisfatta la condizione specificata. È simile alle istruzioni 'if-else' nei linguaggi di programmazione.

Subquery

Ora potremmo voler sapere quali film hanno la durata maggiore e i relativi titoli. Per questo, possiamo usare la clausola WHERE introdotta sopra, solo con una condizione più complessa:

SELECT title, length
FROM film
WHERE length = (SELECT max(length)
                FROM film)

Che restituisce:

Query complesse possono basarsi su subquery. In questo esempio, vogliamo conoscere l'elenco dei titoli dei film che hanno un prezzo di noleggio superiore alla valutazione media di noleggio di tutti i film comici:

SELECT title, rental_rate
FROM film
WHERE rental_rate >
(SELECT AVG(film.rental_rate) AS Average_Rating
FROM film
JOIN film_category AS cat_id
    ON film.film_id=cat_id.film_id
JOIN category AS cat
    ON cat_id.category_id=cat.category_id
GROUP BY cat.name, cat_id.category_id
HAVING cat.name = 'Comedy')

Che restituisce (mostrate solo le prime 10 righe):

In questo esempio, per prima cosa troviamo la valutazione media di noleggio dei film comici. Il valore trovato in questa query (visto tra parentesi sopra) viene poi usato come condizione nella clausola WHERE con cui possiamo trovare i titoli dei film.

Common Table Expressions (CTE)

In un esempio più complesso, possiamo usare una common table expression (CTE) per salvare una tabella temporanea da cui estrarre le informazioni necessarie. In questo esempio, vogliamo conoscere il numero medio di noleggi che ogni membro dello staff elabora a settimana.

WITH weekly_rentals AS(
    SELECT
        staff_id,
        DATE_PART('week', payment_date) AS week,
        COUNT(rental_id) AS rental_numbers
    FROM payment
    GROUP BY staff_id, week)

SELECT staff_id, AVG(rental_numbers)
FROM weekly_rentals
GROUP BY staff_id

Che restituisce:

Nel caso sopra, salviamo il numero di noleggi per settimana in una tabella temporanea chiamata 'weekly_rentals' sulla quale possiamo poi eseguire un'altra query per ottenere il risultato finale.

Funzioni finestra

Le funzioni finestra sono un altro insieme di competenze SQL importanti che possono farti fare un salto di livello. Anche se sono considerate competenze da intermedie ad avanzate, molti data analyst e data scientist le ritengono essenziali per il loro lavoro. Vale quindi la pena dedicare del tempo a familiarizzare con esse.

OVER( ) & PARTITION BY Poniamo di voler trovare, per ciascun membro dello staff, il numero settimanale di noleggi e il numero totale complessivo di noleggi, insieme all'ID dello staff e al numero della settimana. Possiamo riutilizzare la CTE generata nell'esempio sopra, come mostrato qui sotto:

WITH weekly_rentals AS(
    SELECT
        staff_id,
        DATE_PART('week', payment_date) AS week,
        COUNT(rental_id) AS rental_numbers
    FROM payment
    GROUP BY staff_id, week)

SELECT *, SUM(rental_numbers) OVER() AS total_rentals
FROM weekly_rentals

Che restituisce (mostrate solo le prime 10 righe):

Avere OVER nel codice specifica che si tratta di una funzione finestra. Qui stiamo dicendo che la funzione finestra (SUM in questo caso) è applicata a ogni riga. Possiamo anche usare la funzione finestra insieme a PARTITION BY per applicarla a "partizioni" specifiche. Diciamo che ora vogliamo calcolare il numero totale di noleggi per settimana invece del totale complessivo (oltre alle altre colonne). Anche qui, nell'esempio sotto, stiamo usando la CTE generata in precedenza.

WITH weekly_rentals AS(
    SELECT
        staff_id,
        DATE_PART('week', payment_date) AS week,
        COUNT(rental_id) AS rental_numbers
    FROM payment
    GROUP BY staff_id, week)

SELECT *, SUM(rental_numbers) OVER(PARTITION BY week) AS total_weekly_rentals
FROM weekly_rentals

Che restituisce (mostrate solo le prime 10 righe):

La colonna aggiuntiva contiene il totale settimanale dei noleggi invece del totale progressivo.

Qui abbiamo introdotto solo un paio di semplici esempi di funzioni finestra, importanti per chi lavora come data analyst o data scientist in molte aziende. Ti invitiamo a saperne di più su questi comandi usando i link forniti. Puoi anche dare un'occhiata al corso Intermediate SQL per imparare o ripassare le tue competenze SQL. Inoltre, puoi fare pratica prendendo un database open source e provando questi diversi comandi mentre pensi a domande rilevanti da cui partire.