Cos'è JOIN in SQL?

In SQL, JOIN viene usato per unire le righe di due o più tabelle, sulla base di una colonna condivisa tra loro.

Ci sono quattro diversi tipi di JOIN: INNER JOIN, LEFT JOIN, RIGHT JOIN, e FULL OUTER JOIN. In questo articolo, parleremo di FULL OUTER JOIN.

Cos'è un Full Outer Join in SQL?

Il FULL OUTER JOIN (anche noto come OUTER JOIN) è usato per ritornare tutti i record che presentano valori in una delle due tabelle, quella di sinistra o di destra.

Ad esempio, un full outer join tra una tabella di clienti e una tabella di ordini potrebbe restituire tutti i clienti, inclusi quella senza alcun ordine, così come tutti gli ordini. I clienti che hanno effettuato ordini sarebbero uniti ai loro ordini utilizzando il loro numero identificativo di cliente (id).

Un full outer join può restituire molti dati, quindi prima di utilizzarlo, considera se un metodo più conservativo potrebbe soddisfare le tue esigenze.

Dataset di Esempio

Immagina di insegnare ad un corso di Letteratura Americana. Hai dieci studenti, e vuoi che ognuno di loro legga un libro diverso da una lista di romanzi classici americani pre-approvati. Alcuni studenti hanno già scelto il libro che leggeranno, mentre altri non lo hanno ancora fatto.

Hai creato una tabella che elenca gli studenti insieme ai loro numeri di ID studente, e un'altra tabella che elenca i libri con il loro titolo, autore, ISBN, e l'ID dello studente che leggerà il libro, se qualcuno lo ha scelto.

Come fare un Outer Join in SQL

Per fare un outer join con il nostro dataset di esempio, potremmo utilizzare la seguente query:

SELECT students.name, books.title
FROM students
FULL OUTER JOIN books ON students.student_id=books.student_id;

In questo esempio, stiamo selezionando i nomi dalla tabella students e i titoli dei libri dalla tabella books. I record sono abbinati utilizzando la colonna student_id in entrambe le tabelle.

Ed ecco il risultato:

Con il full outer join, siamo in grado di vedere tutti gli studenti, inclusi quelli che non hanno ancora scelto un libro. Possiamo anche vedere tutti i libri, inclusi quelli che non sono ancora stati scelti.

Nel nostro esempio, potresti utilizzare questi dati per vedere chi deve ancora scegliere un libro e quali libri sono ancora disponibili per loro da scegliere.

Conclusione

Utilizzare un full outer join in SQL può aiutarti ad ottenere una visione completa dei dati da più tabelle correlate.

Ma tieni presente che con un grande set di dati, questa query può restituire una quantità di informazioni difficile da gestire, quindi usa questo potere con saggezza!

In un database relazionale, tutte le informazioni dovrebbero essere presenti una sola volta. Tuttavia, potresti avere delle informazioni correlate tra loro separate in tabelle differenti.

E potresti voler mettere insieme queste informazioni correlate per analizzarne i dati, ovvero potresti voler unire tutti i dati (o parte di essi). In questo caso, avrai bisogno di utilizzare l'istruzione JOIN di SQL. Impariamo come funziona.

Cos'è JOIN in SQL?

L'operatore JOIN ti permette di combinare informazioni correlate in modi diversi, come spiegato precedentemente in breve. Ci sono diversi tipi di join, divisi in due categorie principali - INNER join e OUTER join.

La differenza principale tra un INNER JOIN e un OUTER JOIN è che l'inner join manterrà (nella tabella risultante) solo le informazioni derivanti da entrambe le tabelle che sono correlate tra loro. Un Outer Join, invece, manterrà anche le informazioni che non sono correlate all'altra tabella nella tabella risultante.

Cerchiamo di capire meglio INNER JOIN e OUTER JOIN vedendo come funzionano nel dettaglio.

Come usare INNER JOIN in SQL

L'inner join manterrà solo le informazioni correlate presenti nelle due tabelle unite. Se immagini le due tabelle come un diagramma di Venn, la tabella risultante dall'INNER JOIN corrisponderà alla sezione colorata in verde qui sotto, dove si verifica l'intersezione tra di esse:

Questa è la sintassi per un inner join:

SELECT * FROM tabella1
    JOIN tabella2
    ON relazione;

Vedremo di seguito come funziona con un esempio.

Come usare OUTER JOIN in SQL

Se vuoi mantenere tutti i dati, e non solo dati correlati tra loro, puoi usare un OUTER JOIN.

Ci sono tre tipi di Outer Join: LEFT JOIN, RIGHT JOIN e FULL JOIN. Le differenze tra loro riguardano quali dati non correlati mantengono - possono provenire dalla prima tabella, dalla seconda, o da entrambe. Le celle senza dati da riempire avranno valore NULL.

Nota: LEFT JOIN è quello universalmente più implementato in tutte le versioni di SQL. Non vale lo stesso per RIGHT JOIN e FULL JOIN, che non sono implementati in diverse versioni di SQL.

Vediamo individualmente il funzionamento di ciascuno di essi. Successivamente, vedremo come funzionano tutti con degli esempi riportati di seguito.

LEFT OUTER JOIN in SQL

Il LEFT OUTER JOIN, o semplicemente Left Join, manterrà i dati non correlati della tabella di sinistra (la prima).

Puoi immaginarlo come un diagramma di Venn con due insiemi, con la tabella risultante data dalla parte evidenziata in verde che include la parte in comune tra i due insiemi (l'intersezione), e la parte rimanente dell'insieme sulla sinistra.

La sintassi è la seguente. Puoi notare che è simile alla sintassi dell'Inner Join, ma con l'aggiunta della parola chiave LEFT.

SELECT colonne
  FROM tabella1
  LEFT JOIN tabella2
  ON relazione;

RIGHT OUTER JOIN in SQL

Il RIGHT OUTER JOIN, o semplicemente Right Join, manterrà i dati della seconda tabella che non sono correlati con la prima.

Lo puoi immaginare come un diagramma di Venn con due insiemi, con la tabella risultante corrispondente alla parte evidenziata in verde che include l'intersezione dei due insiemi e il resto dell'insieme sulla destra.

La sintassi è la seguente, l'unica differenza è la parola chiave RIGHT.

SELECT colonne
  FROM tabella1
  RIGHT JOIN tabella2
  ON relazione;

FULL OUTER JOIN in SQL

Puoi pensare al FULL OUTER JOIN come la combinazione di Left Join e Right Join. Manterrà dati da entrambe le tabelle, e i dati mancanti saranno riempiti con NULL.

Puoi immaginarlo con un diagramma di Venn con due insiemi, con la tabella risultante corrispondente alla parte evidenziata in verde che include tutto: l'intersezione dei due insiemi, l'insieme a sinistra e quello a destra.

La sintassi è la seguente, usando la parola chiave FULL.

SELECT colonne
  FROM tabella1
  FULL JOIN tabella2
  ON relazione;

Esempi dell'operatore JOIN in SQL

Un possibile database per una clinica veterinaria potrebbe avere una tabella per gli animali domestici (pets) e uno per i proprietari (owners). Dato che un proprietario potrebbe avere più animali domestici, la tabella degli animali domestici (pets) avrà una colonna owner_id che punta alla tabella dei proprietari (owners).

Potresti usare una semplice query per ottenere una tabella con il nome dell'animale domestico e il nome del proprietario uno accanto all'altro. Facciamolo usando tutti i diversi tipi di JOIN.

Esempio INNER JOIN in SQL

Iniziamo utillizzando JOIN.

In questo caso, occorre selezionare (SELECT) la colonna name dalla tabella pets (rinominandola pet_name). E, successivamente, selezionare la colonna name dalla tabella owners, rinomandola owner. Il codice si presenta così: SELECT pets.name AS pet_name, owners.name AS owner.

Si utilizza FROM per indicare che le colonne provengono dalla tabella degli animali domestici, e JOIN per dire che vuoi unirla con la tabella dei proprietari, usando questa sintassi: FROM pets JOIN owners.

Infine, occorre indicare che vuoi unire due righe insieme quando la colonna owner_id nella tabella degli animali domestici (pets) è uguale alla colonna id nella tabella dei proprietari (owners), con ON pets.owner_id = owners.id.

Questo è il codice tutto assieme:

SELECT pets.name AS pet_name, owners.name AS owner
  FROM pets
  JOIN owners
  ON pets.owner_id = owners.id;

Si ottiene una tabella come la seguente, in cui solo gli animali domestici collegati ad un proprietario e i proprietari collegati ad un animale domestico sono inclusi.

Esempio LEFT JOIN in SQL

Facciamo la stessa query usando LEFT JOIN in modo che tu possa vedere le differenze. La query è uguale alla precedente aggiungendo la parola chiave LEFT.

SELECT pets.name AS pet_name, owners.name AS owner
  FROM pets
  LEFT JOIN owners
  ON pets.owner_id = owners.id;

In questo caso, le righe dalla tabella a sinistra, pets, sono tutte mantenute, e i valori mancati provenienti dalla tabella owners sono riempiti con il valore NULL.

Sembra che ci sia un animale domestico che non è registrato con un proprietario.

Esempio RIGHT JOIN in SQL

Se realizzi la stessa query usando RIGHT JOIN ottieni un risultato differente.

SELECT pets.name AS pet_name, owners.name AS owner
  FROM pets
  RIGHT JOIN owners
  ON pets.owner_id = owners.id;

In questo caso tutte le righe dalla tabella a destra, owners, sono mantenute, e se c'è un valore mancante è riempito con il valore NULL.

Sembra ci sia un proprietario che non ha animali domestici registrati.

Esempio FULL JOIN in SQL

Puoi realizzare la stessa query nuovamente, usando FULL JOIN.

SELECT pets.name AS pet_name, owners.name AS owner
  FROM pets
  FULL JOIN owners
  ON pets.owner_id = owners.id;

La tabella risultante è ancora diversa – in questo caso tutte le righe dalle due tabelle sono mantenute.

Sembra che nel nostro database ci sia un animale domestico senza proprietario e un proprietario senza animali domestici.

Conclusione

In un database relazionale, tutti i dati dovrebbero essere scritti solo una volta. Per poter analizzare questi dati, hai bisogno di qualcosa per unire i dati correlati insieme.

In questo articolo hai imparato come utilizzare l'operatore JOIN per farlo. Spero che ti sarà utile, divertiti!

Hai creato il tuo database e le tue tabelle e dopo tutto questo lavoro ti accorgi che hai bisogno  di aggiungere o rinominare una colonna. Bene, per farlo puoi usare l'istruzione ALTER TABLE.

Ma tieni a mente che nel farlo devi essere molto prudente. Se la tua tabella ha molte righe, può causare problemi di prestazioni al database.

Nota: se la sintassi presentata qui non funziona, controlla nella documentazione relativa all'implementazione di SQL che stai usando. Le cose funzionano per lo più allo stesso modo, ma ci sono delle differenze.  

Come aggiungere una nuova colonna con ALTER TABLE

Per aggiungere una nuova colonna, devi prima selezionare la tabella, con ALTER TABLE nome_tabella, poi scrivere il nome della nuova colonna e il tipo di dato che deve aspettarsi, con ADD nome_colonna tipo_dato. Il codice, messo insieme, è così:

ALTER TABLE nome_tabella
ADD nome_colonna tipo_dato;

Esempio: ALTER TABLE per aggiungere una colonna

Abbiamo un database di utenti come segue:

Abbiamo raggiunto un punto in cui occorre conservare il numero del documento d'identità dei nostri utenti, quindi abbiamo bisogno di aggiungere una nuova colonna.

Per aggiungere una nuova colonna alla nostra tabella users, dobbiamo selezionare la tabella con ALTER TABLE users e specificare il nome della nuova colonna e il suo tipo di dato con ADD id_number TEXT. Tutto insieme risulta così:

ALTER TABLE users
ADD id_number TEXT;

La tabella con la nuova colonna sarà come quella qui sotto:

Dovrai usare un'istruzione UPDATE per aggiungere l'informazione mancante relativa all'utente esistente, una volta che verrà fornita.

Come creare una nuova colonna con un valore predefinito invece di NULL

Puoi anche creare una colonna con un valore predefinito usando la parola chiave default seguita dal valore che vuoi assegnare. Gli utenti vedranno allora il valore di default invece di un valore mancante compilato con NULL.

Diciamo che avremo presto utenti internazionali e che vogliamo aggiungere una colonna country. Tutti i nostri utenti già inseriti vengono dagli Stati Uniti, allora potremmo usare questo valore come valore predefinito.

ALTER TABLE users
ADD country TEXT default "United States";

La tabella poi risulterà così:

Sii prudente quando aggiungi nuove colonne alle tabelle

Se la tua tabella ha già molte righe - come quando hai molti utenti o molti dati in memoria - aggiungere una nuova colonna può essere dispendioso in termini di risorse. Quindi assicurati di svolgere questa operazione con attenzione.

Come rinominare una colonna con ALTER TABLE

Puoi rinominare una colonna con il codice qui sotto. Seleziona la tabella con ALTER TABLE nome_tabella e poi scrivi la colonna da rinominare e con quale nome: RENAME COLUMN vecchio_nome TO nuovo_nome.

ALTER TABLE nome_tabella
RENAME COLUMN vecchio_nome TO nuovo_nome;

Esempio di come rinominare una colonna

Consideriamo la stessa tabella che abbiamo usato nell'esempio precedente:

Per evitare confusione tra le colonne id e id_number, rinominiamo la prima in user_id.

Prima selezioniamo la tabella con ALTER TABLE users e poi dichiariamo il nome della colonna in modo che venga modificato come vogliamo con RENAME COLUMN id TO user_id.

ALTER TABLE users
RENAME COLUMN id TO user_id;

Dopo questa query, la tabella apparirà così:

Sii prudente quando rinomini una colonna in una tabella

Quando rinomini delle colonne usando ALTER TABLE, rischi di rompere delle dipendenze all'interno del database.

Se invece di usare ALTER TABLE, per modificare il nome della colonna, utilizzi uno strumento di refactoring, questo aggiornerà il nome e saprà anche gestire tutte le dipendenze.

Se hai un piccolo database non hai bisogno di preoccuparti, ma è importante tenerlo a mente.

Conclusione

In questo articolo hai imparato a usare ALTER TABLE per aggiungere una colonna e rinominare una colonna in una tabella.

Ricorda solo che entrambe le operazioni comportano dei rischi che è importante conoscere. Come ha detto qualcuno, da un grande potere deriva una grande responsabilità – e ALTER TABLE è un grande potere, perciò usalo con cautela!

In questo articolo, ti mostrerò alcuni esempi di codice su come ordinare i tuoi dati in ordine ascendente usando la clausola SQL ORDER BY.

Sintassi di ORDER BY

Questa è la sintassi base per ordinare i tuoi dati in ordine ascendente:

SELECT colonne FROM tabella
ORDER BY colonna;

Se vuoi un ordinamento discendente, devi usare la parola chiave DESC.

SELECT colonne FROM tabella
ORDER BY colonna DESC;

L'istruzione SELECT in SQL consente di ottenere dati da una tabella.

La clausola FROM specifica da quale tabella vogliamo ottenere i dati da elencare.

In questo esempio, abbiamo una tabella di dati su dei musicisti con le colonne: id (identificativo), name (nome), age (età), instrument (strumento) e city (città):

Attualmente la tabella viene ordinata automaticamente in ordine di id ascendente.

Se volessimo ordinare i dati in modo ascendente per la colonna name, dovremmo usare questa sintassi:

SELECT * FROM musicians
ORDER BY name;

Il carattere * consente di selezionare tutte le colonne della tabella.

Puoi vedere che ora i nomi sono ordinati alfabeticamente e l'id non è più nel corretto ordine ascendente.

Se volessimo ordinare i dati per città (city), dovremo utilizzare questa sintassi.

SELECT * FROM musicians
ORDER BY city;

Puoi anche ordinare più colonne in ordine ascendente con lo stesso comando.

In questo nuovo esempio, otteniamo un ordinamento ascendente per età (age) e città (city).

SELECT * FROM musicians
ORDER BY age, city;

Possiamo vedere che nella tabella ci sono tre musicisti diciannovenni con le loro rispettive città ordinate alfabeticamente. Anche i musicisti che hanno 38 anni hanno le loro città correttamente ordinate alfabeticamente.

Se avessimo voluto visualizzare una parte dei dati con ordinamento ascendente e un'altra parte con ordinamento discendente, avremmo dovuto usare le parole chiave ASC e DESC.

In questo nuovo esempio, vogliamo elencare i dati in ordine discendente per età (age ) e in ordine ascendente per strumento (instrument).

Ecco la sintassi:

SELECT * FROM musicians
ORDER BY age DESC, instrument ASC;

Dobbiamo usare entrambe le parole chiave ASC e DESC accanto ai nomi di colonna per specificare come vogliamo ordinare i dati.

Il risultato è il seguente:

Possiamo vedere nella nostra tabella che sia Oscar che Jenny sono i più anziani. Oscar è il primo risultato in quanto il nome del suo strumento (drums) alfabeticamente viene prima del nome dello strumento di Jenny (trombone).

Osserviamo la stessa situazione con Jess e Dave. Anche se sono di pari età, Jess compare più in alto nella tabella in quanto flute precede alfabeticamente trumpet.

Conclusione

Puoi ordinare i dati di una tabella in ordine ascendente usando la clausola di SQL ORDER BY .

SELECT colonne FROM tabella
ORDER BY colonna;

Se vuoi un ordinamento discendente dovrai usare la parola chiave DESC.

SELECT colonne FROM tabella
ORDER BY colonna DESC;

Il carattere * indica che si vogliono selezionare tutte le colonne nella tabella.

SELECT * FROM tabella
ORDER BY colonna;

Se vuoi un ordinamento discendente su colonne multiple dovrai elencare le colonne oggetto di ordinamento dopo la clausola ORDER BY.

SELECT * FROM tabella
ORDER BY colonna1, colonna2;

Se vuoi una parte di dati in ordine ascendente e un'altra parte in ordine discendente dovrai usare le parole chiave ASC e DESC.

SELECT * FROM tabella
ORDER BY colonna1 ASC, colonna2 DESC;

Ecco come usare la clausola ORDER BY in SQL per ordinare i dati.

In SQL, esistono due funzioni integrate per sommare o calcolare la media dei dati in una tabella.

In questo articolo ti mostrerò come usare le funzioni SUM e AVG in SQL usando degli esempi di codice.

Come usare la funzione SUM in SQL

Se devi aggiungere un gruppo di numeri a una tabella, puoi usare la funzione SUM in SQL.

Questa è la sintassi di base:

SELECT SUM(nome_colonna) FROM nome_tabella;

L'istruzione SELECT in SQL dice al computer di prendere i dati da una tabella.

La clausola FROM in SQL specifica da quale tabella vuoi prendere i dati.

In questo esempio, abbiamo una tabella chiamata studenti con le colonne id, nome, data e totale. Vogliamo aggiungere il numero totale di caramelle vendute da tutti gli studenti.

Possiamo usare questa sintassi per ottenere il numero totale di barrette vendute:

SELECT SUM(totale) FROM studenti;

Il risultato sarà 41.

Possiamo anche ottenere la somma per ogni studente usando la clausola GROUP BY.

La prima parte è per selezionare il nome e sommare  il numero totale di caramelle vendute, così:

SELECT nome, SUM(totale)

La seconda parte è per raggruppare la somma rispetto al nome:

FROM studenti GROUP BY nome;

Ecco il codice completo per raggruppare il numero totale di caramelle vendute da ogni studente.

SELECT nome, SUM(totale) FROM studenti GROUP BY nome;

Ecco come apparirà il risultato:

Al momento i risultati sono raggruppati alfabeticamente in base al nome dello studente.

Possiamo modificare il codice per ordinare la lista di risultati dal più grande al più piccolo usando la clausola ORDER BY.

SELECT nome, SUM(totale) FROM studenti GROUP BY nome ORDER BY totale DESC;

La parola chiave DESC dice al computer di ordinare il totale dal più grande al più piccolo.

Se vogliamo ordinare il totale dal più piccolo al più grande, basta omettere la parola chiave DESC.

SELECT nome, SUM(totale) FROM studenti GROUP BY nome ORDER BY totale;

Come usare la funzione AVG in SQL

La funzione AVG trova la media aritmetica per un gruppo di entrate in una tabella SQL. La media è la somma di un insieme di numeri divisa per il numero di elementi sommati.

Ad esempio, 2+4+4+6+6+8 fa 30, diviso 6 restituisce una media di 5.

Questa è la sintassi di base della funzione AVG:

SELECT AVG(nome_colonna) FROM nome_tabella;
   

In questo esempio, abbiamo una tabella chiamata studenti, con le colonne id , nome, data e voti. Vogliamo trovare la media dei voti di tutti gli studenti nella tabella.

Dobbiamo usare questa sintassi per ottenere la media dei voti:

SELECT AVG(voti) FROM studenti; 

La media è 85.333.

Possiamo anche usare la funzione ROUND per arrotondare il risultato all'intero più vicino.

SELECT ROUND(AVG(voti)) FROM studenti; 

Possiamo ottenere anche la media di ogni studente usando la clausola GROUP BY.

La prima parte è per selezionare il nome e la media dei voti, in questo modo:

SELECT nome, ROUND(AVG(voti))

La seconda parte è per raggruppare le medie in base al nome:

FROM studenti GROUP BY nome;

Ecco il codice completo:

SELECT nome, ROUND(AVG(voti)) FROM studenti GROUP BY nome;

E questo sarà il risultato finale:

Conclusione

A volte potresti aver bisogno di trovare la somma o la media delle entrate di una tabella.

Se devi aggiungere un gruppo di numeri a una tabella in SQL puoi usare la funzione SUM.

Questa è la sintassi di base:

SELECT SUM(nome_colonna) FROM nome_tabella;

Se devi disporre i dati in gruppi, puoi usare la clausola GROUP BY.

La funzione AVG trova la media aritmetica di un gruppo di entrate di una tabella SQL. La media è la somma di un insieme di numeri divisa per il numero di elementi sommati.

Questa è la sintassi di base:

SELECT AVG(nome_colonna) FROM nome_tabella;

Spero che questo tutorial ti sia piaciuto e buona fortuna con il tuo viaggio in SQL.

In SQL, puoi effettuare una interrogazione sul database e usare la funzione COUNT per ottenere il numero di righe per un particolare gruppo in una tabella.

In questo articolo, ti mostrerò come usare la funzione COUNT con alcuni esempi di codice.

Cos'è la funzione COUNT in SQL?

Questa funzione SQL restituirà il conteggio del numero di righe per un dato gruppo.

Ecco la sintassi di base:

SELECT COUNT(nome_colonna) FROM nome_tabella;

L'istruzione SELECT  in SQL serve per ottenere dati da una tabella nel database.

COUNT(nome_colonna) non includerà i valori NULL nel conteggio.

Un valore NULL in SQL fa riferimento a un campo di una tabella in cui non è stata immessa una voce.

Talvolta puoi usare un asterisco * come argomento della funzione COUNT.

SELECT COUNT(*) FROM nome_tabella;

In questo caso, la funzione COUNT(*) restituirà il numero totale di elementi nel gruppo compresi i valori NULL.

La clausola FROM in SQL specifica da quale tabella vuoi ottenere i dati.

Puoi anche usare la parola chiave ALL nella funzione COUNT .

SELECT COUNT(ALL nome_colonna) FROM nome_tabella;

La parola chiave ALL conterà tutti i valori nella tabella, duplicati inclusi. Puoi omettere questa parola chiave, in quanto COUNT usa ALL nella modalità predefinita, che tu la scriva o no.

Talvolta vedrai la parola chiave DISTINCT usata con la funzione COUNT.

SELECT COUNT(DISTINCT nome_colona) FROM nome_tabella;

La parola chiave DISTINCT serve per contare solo i valori univoci che non sono NULL. I valori duplicati verranno ignorati.

Come usare la funzione COUNT in SQL

In questo esempio, abbiamo una tabella campers (che contiene un elenco di campeggiatori), con le colonne id, name, age e counselor.

Se vogliamo selezionare tutte le righe nella nostra tabella possiamo usare la seguente sintassi:

SELECT COUNT(*) FROM campers;

Come puoi vedere, l'interrogazione ha restituito il numero 12 che rappresenta il numero totale di righe nella tabella campers.

Usare la clausola WHERE

Possiamo usare la clausola WHERE per ottenere il numero di righe nelle quali un particolare nominativo si trova nel campo counselor.

In questo esempio, vogliamo contare il numero di righe che contengono il nome "Ashley" nel campo counselor.

Per la clausola WHERE dobbiamo specificare counselor con il valore "Ashley".

 WHERE counselor="Ashley";

Ecco l'istruzione completa:

SELECT COUNT(*) FROM campers WHERE counselor="Ashley";

Questo è il risultato restituito:

Se diamo un'occhiata alla nostra tabella possiamo vedere che "Ashley" appare 4 volte.

Possiamo modificare l'interrogazione per contare quanti campeggiatori hanno 11 anni.

Nella clausola WHERE dobbiamo specificare la colonna age (età) con un valore di 11.

WHERE age=11;

Ecco l'istruzione completa:

SELECT COUNT(*) FROM campers WHERE age=11;

Ecco il risultato:

Se verifichiamo il contenuto della tabella qui sopra, possiamo vedere che ci sono 3 campeggiatori che hanno 11 anni di età.

Come usare la clausola GROUP BY

Possiamo usare la clausola GROUP BY con la funzione COUNT per ottenere il conteggio dei campeggiatori raggruppati per età.

Prima selezioniamo la colonna age e usiamo la funzione COUNT:

SELECT age, COUNT(*)

Poi specifichiamo il nome della tabella (campers ) e il raggruppamento per il campo age:

FROM campers GROUP BY age;

Di seguito l'istruzione completa:

SELECT age, COUNT(*) FROM campers GROUP BY age;

Ecco il risultato:

Come usare la clausola ORDER BY

Possiamo modificare il nostro esempio precedente usando la clausola ORDER BY per elencare il risultato in ordine ascendente rispetto al numero di campeggiatori per gruppo di età.

Ecco il codice per la clausola ORDER BY:

ORDER BY COUNT(*);

Aggiungiamo questa clausola alla fine dell'istruzione SELECT in questo modo:

SELECT age, COUNT(*) FROM campers GROUP BY age ORDER BY COUNT(*);

Ecco il risultato dell'istruzione qui sopra:

Se avessimo voluto presentare i dati in ordine discendente, avremmo dovuto usare la parola chiave DESC .

Ecco il codice per la clausola ORDER BY usando la parola chiave DESC :

ORDER BY COUNT(*) DESC;

Questo è il codice completo:

SELECT age, COUNT(*) FROM campers GROUP BY age ORDER BY COUNT(*) DESC;

Il nuovo risultato è questo:

Come usare la clausola HAVING

Possiamo usare la clausola HAVING per specificare una condizione per la funzione COUNT.

Possiamo modificare il codice per mostrare solo il risultato del raggruppamento per età quando il valore è minore di 5.

Il codice per la clausola HAVING è questo:

HAVING COUNT(*)<5;

L'istruzione completa è questa:

SELECT age, COUNT(*) FROM campers GROUP BY age HAVING COUNT(*)<5;

Otteniamo il seguente risultato:

Possiamo vedere che la riga contenente il conteggio dei campeggiatori di 12 anni non compare più, in quanto il valore del conteggio è maggiore di 5.

Conclusione

In SQL puoi interrogare un database usando la funzione COUNT per ottenere il numero di righe per un particolare gruppo in una tabella.

La sintassi base è:

SELECT COUNT(nome_colonna) FROM nome_tabella;

COUNT(column_name) non comprende valori NULL nel conteggio.

Un valore NULL  in SQL si riferisce a un campo di una tabella in cui non è stato immessa una voce.

Talvolta puoi usare un asterisco * come argomento della funzione COUNT .

SELECT COUNT(*) FROM nome_tabella;

La funzione COUNT(*) restituirà il conteggio del numero totale di elementi in un dato gruppo compresi i valori NULL.

Spero che tu abbia apprezzato questo articolo, ti auguro buona fortuna per il tuo viaggio all'interno di SQL.

Python e SQL sono due dei linguaggi più importanti per l'analisi di dati.

In questo articolo, ti mostrerò tutto ciò di cui hai bisogno per collegare Python e SQL.

Imparerai come prendere i dati da un database relazionale, memorizzare dati da un'applicazione Python in un database che possiedi e altri casi di utilizzo pratico in cui potresti imbatterti.

Parleremo di:

• Come imparare a usare Python e SQL insieme
• Come configurare il tuo ambiente Python e il Server MySQL
• Connettere il Server MySQL in Python
• Creare un nuovo database
• Creare tabelle e relazioni
• Inserire dati nelle tabelle
• Leggeri i dati
• Aggiornare i dati
• Cancellare i dati
• Creare dati da liste in Python
• Creare funzioni riutilizzabili per svolgere tutte queste operazioni

C'è un bel po' di materiale utile e interessante. Iniziamo!

Una breve nota prima di partire: su questo repository GitHub, c'è un Jupiter Notebook con tutto il codice usato in questo tutorial. Seguire scrivendo il codice passo passo è altamente consigliato.

Se non hai familiarità con SQL e i concetti dietro i database relazionali, c'è una grande quantità di risorse disponibile qui su freeCodeCamp!

Perché Python con SQL?

Per gli analisti e gli scienziati dei dati, Python ha molti vantaggi. Un enorme numero di librerie open source lo rendono uno strumento incredibilmente utile per chi maneggia dei dati.

Ci sono pandas, NumPy e Vaex per l'analisi dei dati, Matplotlib, seaborn e Bokeh per la visualizzazione, TensorFlow, scikit-learn e PyTorch per le applicazioni del machine learning (e tanti altri ancora).

Con una curva di apprendimento (relativamente) buona e una certa versatilità, non è una sorpresa che Python sia uno dei linguaggi di programmazione più rapidi da apprendere.

Quindi se stiamo usando Python per l'analisi di dati, vale la pena chiedersi da dove provengono i dati.

Mentre c'è un'ampia varietà di risorse di set di dati, in molti casi – particolarmente nelle attività imprenditoriali – i dati sono conservati in un database relazionale. I database relazionali sono estremamente efficienti, hanno grandi potenzialità e vengono ampiamente usati per creare, leggere, aggiornare e cancellare dati di ogni tipo.

I sistemi di gestione di database relazionali (RDBMS) utilizzati più di frequente – Oracle, MySQL, Microsoft SQL Server, PostgreSQL, IBM DB2 – usano tutti SQL (Structured Query Language) per consultare e apportare modifiche ai dati.

Nota che ogni RDBMS utilizza implementazioni leggermente diverse di SQL, quindi il codice SQL scritto per uno non funziona per gli altri senza qualche modifica (di solito piccola). I concetti, le strutture e le operazioni però, sono sostanzialmente uguali.

Ciò vuol dire che per un analista dei dati, una profonda comprensione di SQL è di fondamentale importanza. E conoscere come usare Python e SQL insieme, conferisce dei vantaggi ancora più consistenti quando si tratta di lavorare con dei dati.

Il resto di questo articolo sarà dedicato a mostrarti esattamente come possiamo farlo.

Per iniziare

Requisiti & Installazione

Per seguire passo passo questo tutorial, avrai bisogno di un ambiente Python configurato.

Io utilizzo Anaconda, ma ci sono molte altre opzioni. Puoi semplicemente cercare su google "come installare Python" se hai bisogno di un aiuto extra. Puoi anche usare Binder per programmare con il Jupyter Notebook associato.

Utilizzeremo MySQL Community Server dato che è gratuito e ampiamente utilizzato nell'industria. Se stai usando Windows, questa guida ti aiuterà a fare la configurazione, mentre qui puoi trovare le guide per gli utenti Mac e Linux.

Una volta terminata la configurazione avremo bisogno di metterli in comunicazione tra loro.

Per questo, dovremo installare la libreria Python MySQL Connector, usando pip:

pip install mysql-connector-python

Utilizzeremo anche pandas, quindi assicurati di averlo installato.

pip install pandas

Importare le librerie

Come per ogni progetto in Python, la prima cosa da fare è importare le librerie che ci servono.

È una buona pratica importare tutte le librerie che andremo a utilizzare all'inizio del progetto, così che chi legge o revisiona il nostro codice sa grossomodo cosa aspettarsi senza troppe sorprese.

Per questo tutorial, utilizzeremo due librerie – MySQL Connector e pandas.

import mysql.connector
from mysql.connector import Error
import pandas as pd

Importiamo la funzione di errore separatamente in modo da potervi accedere facilmente per le nostre funzioni.

Connettersi al Server MySQL

A questo punto dovremmo avere MySQL Community Server configurato sul nostro sistema. Adesso dobbiamo scrivere del codice Python che ci permetta di stabilire un collegamento al server.

def create_server_connection(host_name, user_name, user_password):
    connection = None
    try:
        connection = mysql.connector.connect(
            host=host_name,
            user=user_name,
            passwd=user_password
        )
        print("MySQL Database connection successful")
    except Error as err:
        print(f"Error: '{err}'")

    return connection

Creare funzioni riutilizzabili come questa è una buona pratica. In questo modo possiamo riusarla ancora e ancora senza sforzo. Una volta che una funzione è stata scritta, puoi riutilizzarla in tutti i tuoi progetti. Il futuro te ti sarà grato per questo!

Analizziamo il codice di questa funzione riga per riga per capire cosa sta succedendo:

Nella prima riga diamo il nome alla funzione (create_server_connection) e agli argomenti che la funzione accetterà (host_name, user_name e user_password).

La riga successiva chiude ogni connessione esistente così che il server non possa confondersi con connessioni multiple aperte.

In seguito, usiamo un blocco try-except per gestire dei potenziali errori. La prima parte tenta di creare una connessione con il server usando il metodo mysql.connector.connect() in base ai dettagli specificati dall'utente negli argomenti. In caso di successo, la funzione stampa un messaggio che comunica che la connessione è avvenuta.

La parte except del blocco stampa l'errore restituito dal server MySQL nel caso in cui si verifichi un errore durante la connessione.

Infine, se la connessione avviene con successo, la funzione restituisce un oggetto connection.

In pratica, lo utilizziamo assegnando l'output della funzione a una variabile, che poi diventa il nostro oggetto connection, a cui possiamo applicare altri metodi per creare altri oggetti utili.

connection = create_server_connection("localhost", "root", pw)

Dovremmo ottenere il messaggio che ci dice che la connessione è andata a buon fine:

Creare un nuovo database

Ora che abbiamo stabilito una connessione, il prossimo passo è creare un nuovo database sul server.

In questo tutorial, lo faremo solo una volta, ma anche in questo caso scriveremo tutto come una funzione riutilizzabile in modo da poterla riusare per progetti futuri.

def create_database(connection, query):
    cursor = connection.cursor()
    try:
        cursor.execute(query)
        print("Database created successfully")
    except Error as err:
        print(f"Error: '{err}'")

Questa funzione accetta due argomenti, connection (il nostro oggetto connection) e query (una query SQL che scriveremo nel prossimo passaggio), ed esegue la query nel server attraverso la connessione.

Utilizziamo il metodo cursor sull'oggetto connection per creare un oggetto cursor (MySQL Connector utilizza un paradigma di programmazione orientata agli oggetti, quindi ci sono molti oggetti che ereditano le proprietà dagli oggetti genitori).

L'oggetto cursor possiede metodi come execute, executemany (che useremo in questo tutorial) insieme ad altri metodi utili.

Possiamo pensare all'oggetto cursor come a un modo per accedere al cursore lampeggiante in una finestra del terminale del server MySQL.

Definiamo una query per creare il database e chiamare la funzione:

Probabilmente questa è la query SQL più semplice.

Eseguire la funzione create_database con questi argomenti determina la creazione nel server del database chiamato school.

Perché il nostro database ha questo nome? Questo potrebbe essere un buon momento per guardare più nel dettaglio ciò che andremo a implementare in questo tutorial.

Il nostro database

Stiamo per implementare il database per la scuola internazionale di lingua – una scuola di lingua fittizia che offre lezioni di lingua professionali a clienti aziendali.

Questo diagramma entità-relazione stabilisce le entità (Teacher, Client, Course e Participant) e definisce le relazioni tra di loro.

Il codice SQL grezzo, i requisiti del database e i dati per entrare nel database sono contenuti in questo repository GitHub, ma li vedrai anche proseguendo con questo tutorial.

Connettersi al database

Ora che abbiamo creato un database nel server MySQL, possiamo modificare la funzione create_server_connection per connetterci direttamente a questo database.

Nota che è possibile – comune, in realtà – avere più database su un solo server MySQL, così da connetterci sempre e automaticamente al database al quale siamo interessati.

Possiamo farlo in questo modo:

def create_db_connection(host_name, user_name, user_password, db_name):
    connection = None
    try:
        connection = mysql.connector.connect(
            host=host_name,
            user=user_name,
            passwd=user_password,
            database=db_name
        )
        print("MySQL Database connection successful")
    except Error as err:
        print(f"Error: '{err}'")

    return connection

Questa è esattamente la stessa funzione, ma ora prendiamo un argomento in più – il nome del database (db_name) – e lo passiamo come argomento del metodo connect().

Creare una funzione per eseguire una query

La funzione finale che creeremo è di fondamentale importanza – una funzione per eseguire una query, che prenderà le nostre query SQL, conservate in Python come stringhe, e le passerà al metodo cursor.execute() per eseguirle sul server.

def execute_query(connection, query):
    cursor = connection.cursor()
    try:
        cursor.execute(query)
        connection.commit()
        print("Query successful")
    except Error as err:
        print(f"Error: '{err}'")

Questa funzione è esattamente la stessa funzione create_database creata precedentemente, tranne che per l'utilizzo del metodo connection.commit() per assicurare che i comandi definiti nelle query SQL siano implementati.

Questa funzione sarà il nostro cavallo di battaglia, che useremo (insieme a create_db_connection) per creare tabelle, stabilire relazioni tra tabelle e inserirvi dei dati, aggiornare e cancellare i dati nel nostro database.

Se sei un esperto di SQL, questa funzione ti permetterà di eseguire tutti i comandi complessi e le query che possono servirti, direttamente da uno script Python. È uno strumento estremamente utile per la gestione dei dati.

Creare tabelle

Ora, abbiamo tutto pronto per iniziare a eseguire dei comandi SQL sul nostro server e costruire un database. La prima cosa che vogliamo fare è creare le tabelle necessarie.

Partiamo con la tabella teacher:

create_teacher_table = """
CREATE TABLE teacher (
  teacher_id INT PRIMARY KEY,
  first_name VARCHAR(40) NOT NULL,
  last_name VARCHAR(40) NOT NULL,
  language_1 VARCHAR(3) NOT NULL,
  language_2 VARCHAR(3),
  dob DATE,
  tax_id INT UNIQUE,
  phone_no VARCHAR(20)
  );
 """

connection = create_db_connection("localhost", "root", pw, db) # Connettiti al database
execute_query(connection, create_teacher_table) # Esegui la query definita

Prima di tutto, assegniamo il nostro comando SQL a una variabile con un nome appropriato.

In questo caso, usiamo le virgolette triple per le stringhe multi-riga in Python per memorizzare la query SQL, che poi usiamo come argomento della funzione execute_query per implementarla.

Nota che la formattazione multi-riga è esclusivamente per migliorare la leggibilità del codice. A SQL o Python non interessa che il comando sia disposto in questo modo. Finché la sintassi è corretta, entrambi i linguaggi la accetteranno.

Tuttavia, a beneficio di chi legge il codice (anche se sarà soltanto il futuro te!), è molto utile fare questa operazione per rendere il codice più leggibile e comprensibile.

Ciò è vero anche per la scrittura degli operatori SQL in maiuscolo, una convenzione ampiamente utilizzata e fortemente raccomandata, anche se il software che esegue il codice non è case-sentive e tratterà CREATE TABLE teacher e create table teacher come comandi identici.

L'esecuzione del codice qui sopra ci dà questi messaggi, che possiamo anche verificare nella linea di comando client del server MySQL:

Ottimo! E ora creiamo le altre tabelle.

create_client_table = """
CREATE TABLE client (
  client_id INT PRIMARY KEY,
  client_name VARCHAR(40) NOT NULL,
  address VARCHAR(60) NOT NULL,
  industry VARCHAR(20)
);
 """

create_participant_table = """
CREATE TABLE participant (
  participant_id INT PRIMARY KEY,
  first_name VARCHAR(40) NOT NULL,
  last_name VARCHAR(40) NOT NULL,
  phone_no VARCHAR(20),
  client INT
);
"""

create_course_table = """
CREATE TABLE course (
  course_id INT PRIMARY KEY,
  course_name VARCHAR(40) NOT NULL,
  language VARCHAR(3) NOT NULL,
  level VARCHAR(2),
  course_length_weeks INT,
  start_date DATE,
  in_school BOOLEAN,
  teacher INT,
  client INT
);
"""


connection = create_db_connection("localhost", "root", pw, db)
execute_query(connection, create_client_table)
execute_query(connection, create_participant_table)
execute_query(connection, create_course_table)

In questo modo, abbiamo creato le quattro tabelle necessarie per le nostre quattro entità.

Adesso dobbiamo definire le relazioni tra di loro e creare una o più tabelle per gestire la relazione many-to-many tra le tabelle partecipant e course.

E lo facciamo esattamente in questo modo:

alter_participant = """
ALTER TABLE participant
ADD FOREIGN KEY(client)
REFERENCES client(client_id)
ON DELETE SET NULL;
"""

alter_course = """
ALTER TABLE course
ADD FOREIGN KEY(teacher)
REFERENCES teacher(teacher_id)
ON DELETE SET NULL;
"""

alter_course_again = """
ALTER TABLE course
ADD FOREIGN KEY(client)
REFERENCES client(client_id)
ON DELETE SET NULL;
"""

create_takescourse_table = """
CREATE TABLE takes_course (
  participant_id INT,
  course_id INT,
  PRIMARY KEY(participant_id, course_id),
  FOREIGN KEY(participant_id) REFERENCES participant(participant_id) ON DELETE CASCADE,
  FOREIGN KEY(course_id) REFERENCES course(course_id) ON DELETE CASCADE
);
"""

connection = create_db_connection("localhost", "root", pw, db)
execute_query(connection, alter_participant)
execute_query(connection, alter_course)
execute_query(connection, alter_course_again)
execute_query(connection, create_takescourse_table)

A questo punto, le tabelle sono state create con i vincoli appropriati, le chiavi primarie, e le relazioni di chiave esterna.

Aggiungere dati alle tabelle

Il prossimo passo è aggiungere dei dati alle tabelle. Usiamo ancora execute_query per eseguire i comandi SQL sul server, partendo di nuovo dalla tabella teacher.

pop_teacher = """
INSERT INTO teacher VALUES
(1,  'James', 'Smith', 'ENG', NULL, '1985-04-20', 12345, '+491774553676'),
(2, 'Stefanie',  'Martin',  'FRA', NULL,  '1970-02-17', 23456, '+491234567890'), 
(3, 'Steve', 'Wang',  'MAN', 'ENG', '1990-11-12', 34567, '+447840921333'),
(4, 'Friederike',  'Müller-Rossi', 'DEU', 'ITA', '1987-07-07',  45678, '+492345678901'),
(5, 'Isobel', 'Ivanova', 'RUS', 'ENG', '1963-05-30',  56789, '+491772635467'),
(6, 'Niamh', 'Murphy', 'ENG', 'IRI', '1995-09-08',  67890, '+491231231232');
"""

connection = create_db_connection("localhost", "root", pw, db)
execute_query(connection, pop_teacher)

Funziona tutto correttamente? Possiamo verificarlo dalla riga di comando del client di MySQL:

E adesso riempiamo le tabelle restanti.

pop_client = """
INSERT INTO client VALUES
(101, 'Big Business Federation', '123 Falschungstraße, 10999 Berlin', 'NGO'),
(102, 'eCommerce GmbH', '27 Ersatz Allee, 10317 Berlin', 'Retail'),
(103, 'AutoMaker AG',  '20 Künstlichstraße, 10023 Berlin', 'Auto'),
(104, 'Banko Bank',  '12 Betrugstraße, 12345 Berlin', 'Banking'),
(105, 'WeMoveIt GmbH', '138 Arglistweg, 10065 Berlin', 'Logistics');
"""

pop_participant = """
INSERT INTO participant VALUES
(101, 'Marina', 'Berg','491635558182', 101),
(102, 'Andrea', 'Duerr', '49159555740', 101),
(103, 'Philipp', 'Probst',  '49155555692', 102),
(104, 'René',  'Brandt',  '4916355546',  102),
(105, 'Susanne', 'Shuster', '49155555779', 102),
(106, 'Christian', 'Schreiner', '49162555375', 101),
(107, 'Harry', 'Kim', '49177555633', 101),
(108, 'Jan', 'Nowak', '49151555824', 101),
(109, 'Pablo', 'Garcia',  '49162555176', 101),
(110, 'Melanie', 'Dreschler', '49151555527', 103),
(111, 'Dieter', 'Durr',  '49178555311', 103),
(112, 'Max', 'Mustermann', '49152555195', 104),
(113, 'Maxine', 'Mustermann', '49177555355', 104),
(114, 'Heiko', 'Fleischer', '49155555581', 105);
"""

pop_course = """
INSERT INTO course VALUES
(12, 'English for Logistics', 'ENG', 'A1', 10, '2020-02-01', TRUE,  1, 105),
(13, 'Beginner English', 'ENG', 'A2', 40, '2019-11-12',  FALSE, 6, 101),
(14, 'Intermediate English', 'ENG', 'B2', 40, '2019-11-12', FALSE, 6, 101),
(15, 'Advanced English', 'ENG', 'C1', 40, '2019-11-12', FALSE, 6, 101),
(16, 'Mandarin für Autoindustrie', 'MAN', 'B1', 15, '2020-01-15', TRUE, 3, 103),
(17, 'Français intermédiaire', 'FRA', 'B1',  18, '2020-04-03', FALSE, 2, 101),
(18, 'Deutsch für Anfänger', 'DEU', 'A2', 8, '2020-02-14', TRUE, 4, 102),
(19, 'Intermediate English', 'ENG', 'B2', 10, '2020-03-29', FALSE, 1, 104),
(20, 'Fortgeschrittenes Russisch', 'RUS', 'C1',  4, '2020-04-08',  FALSE, 5, 103);
"""

pop_takescourse = """
INSERT INTO takes_course VALUES
(101, 15),
(101, 17),
(102, 17),
(103, 18),
(104, 18),
(105, 18),
(106, 13),
(107, 13),
(108, 13),
(109, 14),
(109, 15),
(110, 16),
(110, 20),
(111, 16),
(114, 12),
(112, 19),
(113, 19);
"""

connection = create_db_connection("localhost", "root", pw, db)
execute_query(connection, pop_client)
execute_query(connection, pop_participant)
execute_query(connection, pop_course)
execute_query(connection, pop_takescourse)

Molto bene! Abbiamo creato un database completo con relazioni, vincoli e dati in MySQL usando soltanto dei comando Python.

Abbiamo fatto tutto ciò gradualmente per rendere il procedimento comprensibile. Ma a questo punto puoi ben capire che è possibile inserire facilmente queste operazioni in uno script Python da eseguire con un comando sul terminale.

Leggere i dati

Adesso abbiamo un database funzionante su cui lavorare. Come analista, è probabile che tu ti trovi a lavorare su database esistenti. Sapere come estrarre i dati da un database per poterli usare in Python è di fondamentale importanza, ed è esattamente ciò che andremo ad affrontare come step successivo.

Avremo bisogno di un'altra funzione. Invece di cursor.commit(), questa volta useremo cursor.fetchall(), per leggere i dati dal database senza apportare alcun cambiamento.

def read_query(connection, query):
    cursor = connection.cursor()
    result = None
    try:
        cursor.execute(query)
        result = cursor.fetchall()
        return result
    except Error as err:
        print(f"Error: '{err}'")

Di nuovo, implementeremo la query in modo molto simile usando execute_query. Proviamo con una query semplice per vedere come funziona.

q1 = """
SELECT *
FROM teacher;
"""

connection = create_db_connection("localhost", "root", pw, db)
results = read_query(connection, q1)

for result in results:
  print(result)

Esattamente ciò che ci aspettavamo. La funzione può operare anche su query più complesse, come questa che contiene un'istruzione JOIN sulle tabelle course e client.

q5 = """
SELECT course.course_id, course.course_name, course.language, client.client_name, client.address
FROM course
JOIN client
ON course.client = client.client_id
WHERE course.in_school = FALSE;
"""

connection = create_db_connection("localhost", "root", pw, db)
results = read_query(connection, q5)

for result in results:
  print(result)

Per far sì che questi dati siano più utili e pronti da manipolare, possiamo visualizzarli con una formattazione differente.

Facciamo un paio di esempi per vedere come fare.

Formattare l'output in una lista

#Inizializza una lista vuota
from_db = []

# Itera sui risultati e aggiungili alla fine della lista

# Restituisci una lista di tuple
for result in results:
  result = result
  from_db.append(result)

Formattare l'output in una lista di liste

# Restituisci una lista di liste
from_db = []

for result in results:
  result = list(result)
  from_db.append(result)

Formattare l'output in un dataframe pandas

Quando si tratta di analizzare dati con Python, pandas è il nostro vecchio e fidato amico. È molto semplice convertire l'output del database in un dataframe, e da qui le possibilità sono infinite!

# Restituisci una lista di lista e poi crea un dataframe pandas
from_db = []

for result in results:
  result = list(result)
  from_db.append(result)


columns = ["course_id", "course_name", "language", "client_name", "address"]
df = pd.DataFrame(from_db, columns=columns)

Auspicabilmente, potrai vedere un gran numero di possibilità dispiegarsi davanti ai tuoi occhi. Con poche righe di codice, possiamo estrarre facilmente tutti i dati che possiamo maneggiare dal database relazionale e convogliarli in un apparato analitico d'avanguardia. Molto utile.

Aggiornare i dati

Durante la manutenzione di un database, potrebbe essere necessario apportare delle modifiche ai dati esistenti. In questa sezione vedremo come fare.

Ipotizziamo che alla scuola venga notificato che uno dei suoi clienti, Big Business Federation, sta trasferendo la propria sede all'indirizzo 23 Fingiertweg, 14534 Berlin. In questo caso, l'amministratore del database (cioè noi!) dovrà fare una modifica.

Fortunatamente, possiamo operare tramite la funzione execute_query con l'istruzione SQL UPDATE.

update = """
UPDATE client 
SET address = '23 Fingiertweg, 14534 Berlin' 
WHERE client_id = 101;
"""

connection = create_db_connection("localhost", "root", pw, db)
execute_query(connection, update)

Nota che la clausola WHERE è molto importante. Se eseguiamo questa query senza la clausola WHERE, tutti gli indirizzi della tabella client saranno aggiornati in 23 Fingiertweg. Non esattamente quello che volevamo fare.

Osserva anche che abbiamo usato WHERE client_id = 101 nella query UPDATE. Sarebbe stato possibile anche usare WHERE client_name = 'Big Business Federation' oppure WHERE address = '123 Falschungstraße, 10999 Berlin', o anche WHERE address LIKE '%Falschung%'.

La cosa importante è che la clausola WHERE ci permette di identificare in modo unico il dato (o i dati) che vogliamo aggiornare.

Cancellare i dati

È possibile eseguire la funzione execute_query per cancellare dei dati, usando l'istruzione DELETE.

Quando usiamo SQL con i database relazionali, dobbiamo stare attenti a utilizzare il comando DELETE. Questo non è Windows e non c'è nessun pop up di avvertimento "Sei sicuro di voler eliminare questa cosa?" e non c'è un cestino. Una volta eliminato un dato, è andato per sempre.

Detto ciò, a volte dobbiamo eliminare davvero delle cose. Vediamo come svolgere questa operazione eliminando un corso dalla tabella course.

Prima di tutto, vediamo quali corsi abbiamo:

Diciamo che il corso 20, Fortgeschrittenes Russisch (ovvero 'russo avanzato' per noi), sta terminando e vogliamo rimuoverlo dal database.

A questo punto, non sarai sorpreso di sapere come fare – salviamo il comando SQL come una stringa e poi lo usiamo nel nostro cavallo di battaglia, la funzione execute_query.

delete_course = """
DELETE FROM course 
WHERE course_id = 20;
"""

connection = create_db_connection("localhost", "root", pw, db)
execute_query(connection, delete_course)

Controlliamo che abbia avuto l'effetto desiderato:

'Russo avanzato' non c'è più, proprio come volevamo.

Possiamo eliminare intere colonne usando il comando DROP COLUMN e intere tabelle con DROP TABLE, ma non approfondiremo queste operazioni in questo tutorial.

Procedi pure e fai le tue prove – non ha importanza se cancelli una colonna o una tabella del database di una scuola immaginaria ed è una buona idea acquisire sicurezza con questi comandi prima di passare a un ambiente di produzione.

CRUD

Adesso siamo in grado di completare le quattro operazioni principali per l'archiviazione permanente di dati.

Abbiamo imparato come:

• Creare - un nuovo database con tabelle e dati
• Leggere - estrarre dati dal database e memorizzarli in vari formati
• Aggiornare - apportare cambiamenti ai dati esistenti nel database
• Cancellare - eliminare dati che non sono più necessari

Queste sono delle operazioni incredibilmente utili.

Prima di terminare, c'è un'ultima abilità piuttosto utile da conoscere.

Creare dei dati da liste

Quando abbiamo riempito le nostre tabelle abbiamo visto che possiamo usare il comando SQL INSERT nella funzione execute_query per inserire dati nel database.

Dato che stiamo usando Python per manipolare il database SQL, sarebbe utile prendere una struttura di dati di Python (come una lista) e inserirla direttamente nel nostro database.

Potrebbe essere comodo se vogliamo salvare dei log di attività utente sull'app di un social media che abbiamo scritto in Python, o degli input da utenti in un Wiki che abbiamo realizzato, ad esempio. Ci sono molti possibili utilizzi a cui possiamo pensare.

Questo metodo è anche più sicuro se il database è aperto agli utenti in ogni punto, dato che aiuta a evitare attacchi SQL injection che possono danneggiare o addirittura distruggere un'intero database.

Per fare ciò, scriveremo una funzione usando il metodo executemany() al posto del più semplice metodo execute() che abbiamo usato finora.

def execute_list_query(connection, sql, val):
    cursor = connection.cursor()
    try:
        cursor.executemany(sql, val)
        connection.commit()
        print("Query successful")
    except Error as err:
        print(f"Error: '{err}'")

Adesso che abbiamo la funzione, dobbiamo definire un comando SQL (sql) e una lista contenente i valori che vorremmo inserire nel database (val). I valori devono essere salvati come una lista di tuple, un modo piuttosto comune di archiviare dati in Python.

Per aggiungere due nuovi insegnanti al database, possiamo scrivere del codice come il seguente:

sql = '''
    INSERT INTO teacher (teacher_id, first_name, last_name, language_1, language_2, dob, tax_id, phone_no) 
    VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
    '''
    
val = [
    (7, 'Hank', 'Dodson', 'ENG', None, '1991-12-23', 11111, '+491772345678'), 
    (8, 'Sue', 'Perkins', 'MAN', 'ENG', '1976-02-02', 22222, '+491443456432')
]

Osserva che all'interno del codice sql usiamo %s come segnaposto per il nostro valore. La somiglianza con il segnaposto %s per una stringa in Python è solo casuale (e francamente, molto fuorviante). In MySQL Connector/Python usiamo %s per tutti i tipi di dati (stringhe, int, date, ecc.).

Puoi trovare un gran numero di domande su Stackoverflow in cui qualcuno si è confuso e ha provato a usare il segnaposto %d per degli interi, perché lo aveva usato in Python. Qui non funzionerà – dobbiamo usare %s per ogni colonna a cui vogliamo aggiungere un valore.

La funzione executemany() prende ogni tupla nella lista val e sostituisce al segnaposto i valori rilevanti per la colonna ed esegue il comando SQL per ogni tupla contenuta nella lista.

Questo può essere effettuato su più righe di dati, purché siano formattate correttamente. Nel nostro esempio, Aggiungeremo due nuovi insegnanti, a scopo illustrativo, ma in principio possiamo aggiungerne quanti ne desideriamo.

Proseguiamo eseguendo questa query per aggiungere gli insegnanti al database.

connection = create_db_connection("localhost", "root", pw, db)
execute_list_query(connection, sql, val)

Benvenuti alla scuola internazionale di lingue, Hank e Sue!

Questa è un'altra funzione estremamente utile, che ci permette di prendere i dati generati all'interno di script e applicazioni Python e di inserirli direttamente nel nostro database.

Conclusione

Abbiamo fatto parecchia strada in questo tutorial.

Abbiamo imparato come usare Python e MySQL Connector per creare un nuovo database sul server MySQL, creare tabelle nel database, definire le relazioni tra di loro e inserire dati.

Abbiamo parlato di come creare, leggere, aggiornare ed eliminare dati dal database.

Abbiamo visto come estrarre dati da un database esistente e caricarli in un dataframe pandas, pronti per analisi e ulteriori elaborazioni traendo vantaggio da tutte le possibilità offerte dal PyData stack.

Andando nella direzione opposta, abbiamo anche imparato come prendere dati generati da script e applicazioni Python, e aggiungerli in un database dove possono essere archiviati in modo sicuro per poi essere recuperati e manipolati in un secondo momento.

Spero che questo tutorial ti abbia aiutato a vedere come possiamo usare Python e SQL insieme per manipolare i dati al meglio!

La clausola GROUP BY è molto utile ma a volte può essere un po' fuorviante.

Anche dopo otto anni, ogni volta che uso GROUP BY devo fermarmi e pensare a cosa sta facendo realmente.

In questo articolo, vedremo come costruire una clausola GROUP BY, cosa fa alla tua query e come puoi usarla per eseguire raggruppamenti e raccogliere informazioni sui tuoi dati.

Parleremo di:

• Creare il database
• Aggiungere i dati
• Come funziona GROUP BY?
• Scrivere clausole GROUP BY
• Aggregazioni (COUNT, SUM, AVG)
• Lavorare con gruppi multipli
• Usare delle funzioni in GROUP BY
• Filtrare i gruppi con HAVING
• Aggregazioni con raggruppamento implicito

Creare il database

Prima di poter iniziare a scrivere le nostre query dobbiamo creare un database.

Per questi esempi, useremo PostgreSQL, ma le query e i concetti mostrati qui possono essere trasferiti facilmente in tutti i sistemi di database moderni (come MySQL, SQL Server e così via).

Per lavorare con il nostro database PostgreSQL, possiamo usare psql — il programma interattivo a riga di comando di PostgreSQL. Se hai un altro client di database con cui preferisci lavorare andrà bene lo stesso.

Per iniziare, creiamo il database. Con PostgreSQL già installato, possiamo eseguire sul terminale il comando createdb <nome-database> per creare un nuovo database. Ho chiamato il mio fcc:

$ createdb fcc

Poi, avviamo la console interattiva usando il comando psql e connettiamola al database appena creato con \c <nome-database>:

$ psql
psql (11.5)
Type "help" for help.

john=# \c fcc
You are now connected to database "fcc" as user "john".
fcc=#

Note: in questi esempi, ho ripulito l'output psql per migliorare la leggibilità, quindi non preoccuparti se l'output mostrato qui non è esattamente lo stesso che vedi nel tuo terminale.

Ti suggerisco di seguire questi esempi e di eseguire anche tu le query. Imparerai e ricorderai molto di più lavorando con questi esempi che leggendoli solamente.

Aggiungere i dati

Per i nostri esempi, useremo una tabella che contiene dei dati di vendite di vari prodotti in vari negozi.

Chiameremo questa tabella vendite e sarà una semplice rappresentazione del registro delle vendite di un negozio, contenente: nome del luogo, nome del prodotto, prezzo e momento della vendita (venduto_il).

Se stessimo costruendo questa tabella in una applicazione reale, imposteremo delle chiavi esterne ad altre tabelle, ma per illustrare il concetto alla base di GROUP BY useremo semplicemente delle colonne di testo.

Creiamo la tabella e inseriamo alcuni dati di vendita:

CREATE TABLE vendite(
  luogo TEXT,
  prodotto TEXT,
  prezzo DECIMAL,
  venduto_il TIMESTAMP
);

INSERT INTO vendite(luogo, prodotto, prezzo, venduto_il) VALUES
('HQ', 'Coffee', 2, NOW()),
('HQ', 'Coffee', 2, NOW() - INTERVAL '1 hour'),
('Downtown', 'Bagel', 3, NOW() - INTERVAL '2 hour'),
('Downtown', 'Coffee', 2, NOW() - INTERVAL '1 day'),
('HQ', 'Bagel', 2, NOW() - INTERVAL '2 day'),
('1st Street', 'Bagel', 3, NOW() - INTERVAL '2 day' - INTERVAL '1 hour'),
('1st Street', 'Coffee', 2, NOW() - INTERVAL '3 day'),
('HQ', 'Bagel', 3, NOW() - INTERVAL '3 day' - INTERVAL '1 hour');

Abbiamo tre luoghi: HQ, Downtown e 1st Street.

Abbiamo due prodotti, Coffee e Bagel, e inseriamo le vendite con diversi valori venduto_il per rappresentare le diverse volte in cui un prodotto è stato venduto.

Alcune vendite sono di oggi, alcuni di ieri e altre dall'altro ieri.

Come funziona GROUP BY?

Per illustrare come funziona la clausola GROUP BY, partiamo da un esempio.

Immagina una stanza piena di persone nate in diverse nazioni.

Se vogliamo trovare l'altezza media per nazione delle persone nella stanza, dovremmo prima chiedere alle persone di dividersi in gruppi in base al loro Paese di nascita.

Una volta separate nei vari gruppi, possiamo calcolare l'altezza media in ogni gruppo.

La clausola GROUP BY funziona in questo modo: prima definiamo come vogliamo raggruppare insieme le righe — poi possiamo svolgere dei calcoli o raggruppamenti sui gruppi.

Gruppi multipli

Possiamo raggruppare i dati in tanti gruppi o sotto-gruppi quanti ne desideriamo.

Ad esempio, dopo aver chiesto alle persone di dividersi in gruppi in base alla loro nazione di nascita, potremmo chiedere ai membri di ogni gruppo di suddividersi ulteriormente in altri gruppi in base al colore degli occhi.

Facendo questo, ci ritroveremo con persone divise in base alla combinazione di nazione di nascita e colore degli occhi.

Adesso possiamo trovare l'altezza media in ognuno dei gruppi più piccoli e avremo dei risultati più specifici: altezza media per nazione per colore degli occhi.

Le clausole GROUP BY sono spesso usate in situazioni in cui puoi usare la frase per qualcosa o per ogni qualcosa:

• altezza media per nazione di nascita
• numero totale di persone per ogni combinazione di colore di occhi e capelli
• vendite totali per prodotto

Scrivere clausole GROUP BY

Una clausola GROUP BY è molto semplice da scrivere — usiamo semplicemente le parole chiave GROUP BY e poi specifichiamo il campo (o i campi) che vogliamo raggruppare:

SELECT ...
FROM vendite
GROUP BY luogo;

Questa semplice query raggruppa i dati di vendite secondo la colonna luogo.

Abbiamo fatto il raggruppamento — ma cosa mettiamo in SELECT?

La cosa ovvia da selezionare è la colonna luogo — la stiamo usando per raggruppare, quindi vogliamo vedere almeno i nomi dei gruppi che abbiamo realizzato:

SELECT luogo
FROM vendite
GROUP BY luogo;

Il risultato è:

  luogo
------------
 1st Street
 HQ
 Downtown
(3 rows)

Se diamo un'occhiata alla tabella con i dati grezzi (SELECT * FROM vendite;), vedremo che abbiamo quattro righe per  HQ, due righe per Downtown e due righe per 1st Street:

 prodotto |    luogo   | prezzo |          venduto_il
---------+------------+-------+----------------------------
 Coffee   | HQ         |    2   | 2020-09-01 09:42:33.085995
 Coffee   | HQ         |    2   | 2020-09-01 08:42:33.085995
 Bagel    | Downtown   |    3   | 2020-09-01 07:42:33.085995
 Coffee   | Downtown   |    2   | 2020-08-31 09:42:33.085995
 Bagel    | HQ         |    2   | 2020-08-30 09:42:33.085995
 Bagel    | 1st Street |    3   | 2020-08-30 08:42:33.085995
 Coffee   | 1st Street |    2   | 2020-08-29 09:42:33.085995
 Bagel    | HQ         |    3   | 2020-08-29 08:42:33.085995
(8 rows)

Raggruppando sulla colonna luogo, il database prende queste righe di input e identifica i luoghi unici tra essi — questi luoghi unici saranno i nostri "gruppi".

E le altre colonne nella tabella?

Se proviamo a selezionare una colonna come prodotto, che non abbiamo raggruppato...

SELECT
  luogo,
  prodotto
FROM vendite
GROUP BY luogo;

...incapperemo in questo errore:

ERROR:  column "vendite.prodotto" must appear in the GROUP BY clause or be used in an aggregate function

Il problema è che abbiamo otto righe che abbiamo compresso in tre righe.

Non possiamo ottenere le altre colonne in modo normale — avevamo otto righe e ora ne abbiamo tre.

Cosa facciamo con le rimanenti cinque righe di dati? Quali delle otto righe di dati dovrebbero essere visualizzate nelle tre righe distinte con i luoghi?

Non c'è una risposta chiara e definitiva.

Per utilizzare il resto della tabella di dati, dobbiamo scegliere anche i dati dalle colonne restanti per ottenere tre gruppi di luoghi.

Questo significa che dobbiamo aggregare o svolgere un calcolo per produrre qualche tipo di informazione di riepilogo per i dati restanti.

Aggregazioni (COUNT, SUM, AVG)

Una volta che abbiamo deciso come raggruppare i dati, possiamo svolgere delle aggregazioni sulle altre colonne.

Stiamo parlando di operazioni come contare il numero di righe per gruppo, sommare un valore specifico all'interno del gruppo oppure mediare un'informazione nel gruppo.

Per iniziare, troviamo il numero di vendite per luogo.

Dato che ogni dato nella nostra tabella vendite è una vendita, il numero di vendite per luogo sarà il numero di righe di ogni gruppo, per ognuno di essi.

Per fare questa operazione useremo la funzione di aggregazione COUNT() per contare il numero di righe in ogni gruppo:

SELECT
  luogo,
  COUNT(*) AS numero_di_vendite
FROM vendite
GROUP BY luogo;

Utilizziamo COUNT(*) che conta tutte le righe di input per un gruppo (COUNT() funziona anche con delle espressioni, ma ha un comportamento leggermente diverso).

Ecco come il database esegue questa query:

• FROM vendite — prima di tutto, recupera tutti i dati dalla tabella vendite
• GROUP BY luogo — poi, determina i gruppi di luogo unici
• SELECT ... — infine, seleziona il nome dei luoghi e il conteggio del numero di righe nel gruppo

Abbiamo dato al conteggio delle righe un alias usando AS numero_di_vendite per rendere l'output più leggibile. Ed ecco quello che otteniamo:

  luogo     | numero_di_vendite
------------+-------------------
 1st Street |                2
 HQ         |                4
 Downtown   |                2
(3 rows)

Il luogo 1st Street ha due vendite, HQ ne ha quattro e Downtown ne ha due.

Qui possiamo vedere come abbiamo preso le colonne di dati rimanenti dalle otto righe indipendenti, condensandole in un'informazione riepilogativa utile per ogni luogo: il numero delle vendite.

SUM

Analogamente, invece di contare il numero delle righe in un gruppo, potremmo sommare le informazioni all'interno di ogni gruppo — ad esempio per ottenere il totale incassato dalle vendite di ogni luogo.

Per farlo possiamo usare la funzione SUM():

SELECT
  luogo,
  SUM(prezzo) AS incasso_totale
FROM vendite
GROUP BY luogo;

Invece di contare il numero di righe di ogni gruppo, sommiamo le entrate di ogni vendita per ottenere il totale delle vendite per luogo:

  luogo     | incasso_totale
------------+----------------
 1st Street |             5
 HQ         |             9
 Downtown   |             5
(3 rows)

Average (AVG)

Per trovare il prezzo medio delle vendite per luogo dobbiamo semplicemente sostituire la funzione  SUM() con la funzione AVG():

SELECT
  luogo,
  AVG(prezzo) AS incasso_medio_per_vendita
FROM vendite
GROUP BY luogo;

Lavorare con gruppi multipli

Finora abbiamo lavorato con un singolo gruppo: luogo.

E se volessimo dividere ulteriormente questo gruppo?

In modo simile al caso della "nazione di nascita e colore degli occhi" con cui abbiamo iniziato, come potremmo fare se volessimo trovare il numero di vendite per prodotto per luogo?

Tutto ciò di cui abbiamo bisogno è di una seconda condizione di raggruppamento nell'istruzione GROUP BY:

SELECT ...
FROM vendite
GROUP BY luogo, prodotto;

Aggiungendo una seconda colonna in GROUP BY dividiamo ulteriormente i gruppi luogo per prodotto.

Visto che ora stiamo raggruppando anche secondo la colonna proodotto, possiamo aggiungerla a SELECT!

SELECT
  luogo,
  prodotto
FROM vendite
GROUP BY luogo, prodotto
ORDER BY luogo, prodotto;

Nota: sto aggiungendo a queste query delle clausole ORDER BY per rendere l'output più leggibile.

Osservando il risultato del nuovo raggruppamento, possiamo vedere le combinazioni uniche luogo/prodotto:

   luogo    | prodotto
------------+-----------
 1st Street | Bagel
 1st Street | Coffee
 Downtown   | Bagel
 Downtown   | Coffee
 HQ         | Bagel
 HQ         | Coffee
(6 rows)

Ora che abbiamo i nostri gruppi, cosa vogliamo fare con il resto delle colonne di dati?

Bene, possiamo trovare il numero di vendite per prodotto per luogo usando le stesse funzioni di aggregazione di prima:

SELECT
  luogo,
  prodotto,
  COUNT(*) AS numero_di_vendite
FROM vendite
GROUP BY luogo, prodotto
ORDER BY luogo, prodotto;

   luogo    | prodotto | numero_di_vendite
------------+---------+-------------------
 1st Street |  Bagel   |               1
 1st Street |  Coffee  |               1
 Downtown   |  Bagel   |               1
 Downtown   |  Coffee  |               1
 HQ         |  Bagel   |               2
 HQ         |  Coffee  |               2
(6 rows)

Esercizio per il lettore: trova l'incasso totale per prodotto per luogo.

Usare delle funzioni in GROUP BY

Adesso, proviamo a trovare il numero totale di vendite per giorno.

Se seguiamo uno schema simile a quello usato per i luoghi e raggruppiamo la colonna venduto_il...

SELECT
  venduto_il,
  COUNT(*) AS vendite_per_giorno
FROM vendite
GROUP BY venduto_il
ORDER BY venduto_il;

...potremmo aspettarci di avere un gruppo per ogni giorno — invece otteniamo:

       venduto_il           | vendite_per_giorno
----------------------------+-------------------
 2020-08-29 08:42:33.085995 |               1
 2020-08-29 09:42:33.085995 |               1
 2020-08-30 08:42:33.085995 |               1
 2020-08-30 09:42:33.085995 |               1
 2020-08-31 09:42:33.085995 |               1
 2020-09-01 07:42:33.085995 |               1
 2020-09-01 08:42:33.085995 |               1
 2020-09-01 09:42:33.085995 |               1
(8 rows)

Sembra che i nostri dati non siano stati raggruppati affatto — otteniamo ogni riga singolarmente.

Ma i dati sono stati effettivamente raggruppati! Il problema è che ogni riga venduto_il è un valore unico — quindi ogni riga determina un gruppo!

GROUP BY sta funzionando correttamente, ma questo non è l'output che desideriamo.

Il responsabile è l'informazione unica ore/minuti/secondi della marca temporale.

Ogni marca temporale differisce per ore, minuti o secondi — così ogni vendita viene inserita in un nuovo gruppo.

Dobbiamo convertire ogni valore di data e ora in una data:

• 2020-09-01 08:42:33.085995 => 2020-09-01
• 2020-09-01 09:42:33.085995 => 2020-09-01

Una volta convertite in una data, tutte le marche temporali dello stesso giorno restituiranno la stessa data — e i dati delle vendite relative verranno inseriti nello stesso gruppo.

Per svolgere questa operazione, dobbiamo cambiare la marca temporale di venduto_il in una data:

SELECT
  venduto_il::DATE AS data,
  COUNT(*) AS vendite_per_giorno
FROM vendite
GROUP BY venduto_il::DATE
ORDER BY venduto_il::DATE;

Nella clausola GROUP BY utilizziamo ::DATE per troncare la marca temporale in corrispondenza del giorno. Questa espressione taglia effettivamente l'informazione ore/minuti/secondi della marca temporale restituendo solo il giorno.

In SELECT, possiamo includere la stessa espressione e darle un alias per avere un output più grazioso.

Per la stessa ragione per cui non potevamo restituire la colonna prodotto senza raggrupparla o eseguire qualche tipo di aggregazione, il database non ci permetterà di restituire venduto_il — tutto quello che si trova in SELECT deve essere in GROUP BY o in qualche tipo di aggregazione sui gruppi risultanti.

Il risultato è il numero di vendite per giorno che volevamo ottenere in origine:

    data    | vendite_per_giorno
------------+-------------------
 2020-08-29 |             2
 2020-08-30 |             2
 2020-08-31 |             1
 2020-09-01 |             3
(4 rows)

Filtrare i gruppi con HAVING

E ora vediamo come filtrare le righe raggruppate.

Per farlo, proviamo a trovare i giorni in cui c'è stata più di una vendita.

Di norma, senza raggruppare, avremmo filtrato le righe usando una clausola WHERE. Ad esempio:

SELECT *
FROM vendite
WHERE prodotto = 'Coffee';

Potremmo fare qualcosa simile a filtrare i gruppi in base al conteggio delle righe...

SELECT
  venduto_il::DATE AS data,
  COUNT(*) AS vendite_per_giorno
FROM vendite
WHERE COUNT(*) > 1      -- filtrare i gruppi?
GROUP BY venduto_il::DATE;

Sfortunatamente, questo approccio non funziona e otteniamo il seguente errore:

ERROR:  aggregate functions are not allowed in WHERE

Le funzioni di aggregazione non sono ammesse nelle clausole WHERE perché la clausola WHERE viene valutata prima della clausola GROUP BY — non ci sono ancora gruppi su cui effettuare delle operazioni.

Ma c'è un tipo di clausola che ci permette di filtrare, svolgere aggregazioni ed è valutata dopo la clausola GROUP BY: la clausola HAVING.

La clausola HAVING è come una clausola WHERE ma per i gruppi.

Per trovare i giorni in cui abbiamo più di una vendita, possiamo aggiungere una clausola HAVING che verifica il conteggio delle righe del gruppo:

SELECT
  venduto_il::DATE AS data,
  COUNT(*) AS vendite_per_giorno
FROM vendite
GROUP BY venduto_il::DATE
HAVING COUNT(*) > 1;

Questa clausola HAVING esclude ogni riga in cui il conteggio delle righe del gruppo non è maggiore di uno, e il risultato è:

    data    | vendite_per_giorno
------------+-------------------
 2020-09-01 |             3
 2020-08-29 |             2
 2020-08-30 |             2
(3 rows)

Per completezza, ecco l'ordine di esecuzione di tutte le parti di un'istruzione SQL:

• FROM — recupera tutte le righe da una tabella
• JOIN — esegue un collegamento
• WHERE — filtra delle righe
• GROUP BY - forma dei gruppi
• HAVING - filtra dei gruppi
• SELECT - seleziona i dati da restituire
• ORDER BY - ordina le righe di output
• LIMIT - restituisce un certo numero di righe

Aggregazioni con raggruppamento implicito

L'ultimo argomento che affronterò riguarda le aggregazioni che possono essere eseguite con GROUP BY — o più precisamente le aggregazioni con raggruppamento implicito.

Queste aggregazioni sono utili in casi in cui vuoi trovare una particolare aggregazione in una tabella — come l'ammontare totale delle entrate o il valore più grande o più piccolo di una colonna.

Ad esempio, potremmo trovare il totale delle entrate di tutte le location selezionando la somma dall'intera tabella:

SELECT SUM(prezzo)
FROM vendite;

 sum
-----
  19
(1 row)

Finora abbiamo incassato €19 dalle vendite in tutti i luoghi.

Un'altra cosa utile potrebbe essere ottenere il primo o l'ultimo dato di un qualche tipo.

Ad esempio, qual è la data della prima vendita?

Per trovarla usiamo la funzione MIN():

SELECT MIN(venduto_il)::DATE AS prima_vendita
FROM vendite;

 prima_vendita
--------------
  2020-08-29
(1 row)

Per trovare la data dell'ultima vendita basta sostituire MAX() a MIN().

Usare MIN / MAX

Mentre queste semplici query possono essere utili come query indipendenti, fanno spesso parte del filtraggio di query più ampie.

Ad esempio, proviamo a trovare il totale delle vendite dell'ultimo giorno in cui ci sono state vendite.

Un modo in cui potremmo scrivere questa query è:

SELECT
  SUM(prezzo)
FROM vendite
WHERE venduto_il::DATE = '2020-09-01';

Questa query funziona, ma abbiamo dovuto includere esplicitamente la data 2020-09-01.

09/01/2020 potrebbe essere l'ultima data in cui abbiamo avuto una vendita, ma non sarà sempre quella la data corretta, quindi abbiamo bisogno di una soluzione dinamica.

Possiamo ottenerla combinando questa query con la funzione MAX() in una subquery:

SELECT
  SUM(prezzo)
FROM vendite
WHERE venduto_il::DATE = (
  SELECT MAX(venduto_il::DATE)
  FROM vendite
);

Nella clausola WHERE troviamo la data più recente della tabella con la subquery: SELECT MAX(venduto_il::DATE) FROM vendite.

Poi, usiamo la data trovata per filtrare e sommare i prezzi delle vendite.

Raggruppamento implicito

Ho parlato di raggruppamento implicito perché se proviamo a selezionare dei valori aggregati con una colonna non aggregata in questo modo:

SELECT
  SUM(prezzo),
  luogo
FROM vendite;

otteniamo l'errore ormai familiare:

ERROR:  column "vendite.luogo" must appear in the GROUP BY clause or be used in an aggregate function

GROUP BY è uno strumento

Come molti altri argomenti dello sviluppo software, GROUP BY è uno strumento.

Ci sono molti modi per scrivere e riscrivere queste query usando combinazioni di GROUP BY, funzioni di aggregazione o altri strumenti come DISTINCT, ORDER BY e LIMIT.

Comprendere e lavorare con GROUP BY richiede un po' di pratica, ma una volta che ne hai capito il funzionamento scoprirai di essere in grado di risolvere un'intera serie di nuovi problemi!

Grazie per aver letto questo articolo!

SQL sta per Structured Query Language. I comandi di SQL sono istruzioni utilizzate per comunicare con un database al fine di eseguire attività, funzioni e query con dei dati.

I comandi di SQL possono essere utilizzati per cercare nel database e per svolgere altre funzioni come creare o eliminare tabelle, e aggiungere o modificare dati.

Ecco una lista dei comandi SQL di base (a volte chiamati clausole) che dovresti conoscere se devi lavorare con SQL.

SELECT e FROM

Il comando SELECT costituisce la parte di una query che determina quali colonne di dati mostrare nei risultati. Ci sono varie opzioni che puoi applicare per mostrare anche dati che non fanno parte di una colonna della tabella.

L'esempio qui sotto mostra tre colonne selezionate dalla tabella "student" con il comando FROM e una colonna calcolata. Il database contiene studentID, FirstName e LastName degli studenti. Possiamo combinare le colonne FirstName e LastName per ottenere la colonna calcolata FullName.

SELECT studentID, FirstName, LastName, FirstName + ' ' + LastName AS FullName
FROM student;

+-----------+-------------------+------------+------------------------+
| studentID | FirstName         | LastName   | FullName               |
+-----------+-------------------+------------+------------------------+
|         1 | Monique           | Davis      | Monique Davis          |
|         2 | Teri              | Gutierrez  | Teri Gutierrez         |
|         3 | Spencer           | Pautier    | Spencer Pautier        |
|         4 | Louis             | Ramsey     | Louis Ramsey           |
|         5 | Alvin             | Greene     | Alvin Greene           |
|         6 | Sophie            | Freeman    | Sophie Freeman         |
|         7 | Edgar Frank "Ted" | Codd       | Edgar Frank "Ted" Codd |
|         8 | Donald D.         | Chamberlin | Donald D. Chamberlin   |
|         9 | Raymond F.        | Boyce      | Raymond F. Boyce       |
+-----------+-------------------+------------+------------------------+
9 rows in set (0.00 sec)

CREATE TABLE

Il comando CREATE TABLE serve per creare una tabella nel database. Puoi specificare il nome della tabella e le colonne che deve contenere.

CREATE TABLE table_name (
    column_1 datatype,
    column_2 datatype,
    column_3 datatype
);

ALTER TABLE

ALTER TABLE cambia la struttura di una tabella. Ecco come puoi aggiungere una colonna a un database:

ALTER TABLE table_name
ADD column_name datatype;

CHECK

La clausola CHECK viene usata per limitare l'intervallo dei valori che possono essere inseriti in una colonna.

Se definisci una clausola CHECK su una singola colonna, ammetti solo alcuni valori al suo interno. Se definisci una clausola CHECK su una tabella, puoi limitare i valori in determinate colonne sulla base dei valori presenti nella riga corrispondente di altre colonne.

Il seguente codice crea una clausola CHECK sulla colonna “Age” quando viene creata la tabella “Persons”, e fa sì che non ci possa essere nessuna persona al di sotto di 18 anni.

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CHECK (Age>=18)
);

Per dare un nome a una clausola CHECK o definirne una su colonne multiple puoi usare la seguente sintassi:

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    City varchar(255),
    CONSTRAINT CHK_Person CHECK (Age>=18 AND City='Sandnes')
);

WHERE

(AND, OR, IN, BETWEEN e LIKE)

La clausola WHERE viene utilizzata per limitare il numero di righe restituite.

Ad esempio, consideriamo un comando SELECT e i risultati senza la clausola WHERE, per poi aggiungere la clausola WHERE su tutti e cinque i qualifier.

SELECT studentID, FullName, sat_score, rcd_updated FROM student;

+-----------+------------------------+-----------+---------------------+
| studentID | FullName               | sat_score | rcd_updated         |
+-----------+------------------------+-----------+---------------------+
|         1 | Monique Davis          |       400 | 2017-08-16 15:34:50 |
|         2 | Teri Gutierrez         |       800 | 2017-08-16 15:34:50 |
|         3 | Spencer Pautier        |      1000 | 2017-08-16 15:34:50 |
|         4 | Louis Ramsey           |      1200 | 2017-08-16 15:34:50 |
|         5 | Alvin Greene           |      1200 | 2017-08-16 15:34:50 |
|         6 | Sophie Freeman         |      1200 | 2017-08-16 15:34:50 |
|         7 | Edgar Frank "Ted" Codd |      2400 | 2017-08-16 15:35:33 |
|         8 | Donald D. Chamberlin   |      2400 | 2017-08-16 15:35:33 |
|         9 | Raymond F. Boyce       |      2400 | 2017-08-16 15:35:33 |
+-----------+------------------------+-----------+---------------------+
9 rows in set (0.00 sec)

Ora ripetiamo la query ma limitando le righe restituite con la clausola WHERE.

STUDENT studentID, FullName, sat_score, recordUpdated
FROM student
WHERE (studentID BETWEEN 1 AND 5 OR studentID = 8)
        AND
        sat_score NOT IN (1000, 1400);

+-----------+----------------------+-----------+---------------------+
| studentID | FullName             | sat_score | rcd_updated         |
+-----------+----------------------+-----------+---------------------+
|         1 | Monique Davis        |       400 | 2017-08-16 15:34:50 |
|         2 | Teri Gutierrez       |       800 | 2017-08-16 15:34:50 |
|         4 | Louis Ramsey         |      1200 | 2017-08-16 15:34:50 |
|         5 | Alvin Greene         |      1200 | 2017-08-16 15:34:50 |
|         8 | Donald D. Chamberlin |      2400 | 2017-08-16 15:35:33 |
+-----------+----------------------+-----------+---------------------+
5 rows in set (0.00 sec)

UPDATE

Per aggiornare dei dati in una tabella puoi usare l'istruzione UPDATE.

Utilizza la clausola WHERE per specificare quali dati vuoi aggiornare. È possibile aggiornare più di una colonna alla volta usando la sintassi:

UPDATE table_name
SET column1 = value1, 
    column2 = value2, ...
WHERE condition;

Ecco un esempio in cui aggiorniamo la colonna Name per la riga con Id 4:

UPDATE Person
SET Name = "Elton John"
WHERE Id = 4;

Puoi anche aggiornare le colonne in una tabella usando i valori di altre tabelle. Con la clausola JOIN puoi ottenere dati da tabelle multiple, secondo la sintassi:

UPDATE table_name1
SET table_name1.column1 = table_name2.columnA
    table_name1.column2 = table_name2.columnB
FROM table_name1
JOIN table_name2 ON table_name1.ForeignKey = table_name2.Key

Ecco un esempio di aggiornamento della colonna Manager per tutte le righe:

UPDATE Person
SET Person.Manager = Department.Manager
FROM Person
JOIN Department ON Person.DepartmentID = Department.ID

GROUP BY

GROUP BY ti permette di combinare righe e unire dati.

Ecco la sintassi di GROUP BY:

SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name;

HAVING

HAVING ti permette di filtrare i dati combinati con la clausola GROUP BY in modo tale da avere un set limitato di dati da visualizzare.

Ecco la sintassi di HAVING:

SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > value;

AVG()

Il comando AVG() viene usato per calcolare la media di una colonna contenente dati numerici, appartenenti a un set di righe individuate da un comando SQL.

Ecco la sintassi:

SELECT groupingField, AVG(num_field)
FROM table1
GROUP BY groupingField

Ed ecco un altro esempio usando la tabella student:

SELECT studentID, FullName, AVG(sat_score) 
FROM student 
GROUP BY studentID, FullName;

AS

AS ti permette di rinominare una colonna o una tabella.

SELECT user_only_num1 AS AgeOfServer, (user_only_num1 - warranty_period) AS NonWarrantyPeriod FROM server_table

Qui sotto puoi vedere l'output:

+-------------+------------------------+
| AgeOfServer | NonWarrantyPeriod      | 
+-------------+------------------------+
|         36  |                     24 |
|         24  |                     12 | 
|         61  |                     49 |
|         12  |                      0 | 
|          6  |                     -6 |
|          0  |                    -12 | 
|         36  |                     24 |
|         36  |                     24 | 
|         24  |                     12 | 
+-------------+------------------------+

Puoi usare AS anche per assegnare un nome a una tabella per riferirti ad essa più facilmente nell'istruzione JOIN.

SELECT ord.product, ord.ord_number, ord.price, cust.cust_name, cust.cust_number FROM customer_table AS cust

JOIN order_table AS ord ON cust.cust_number = ord.cust_number

Ecco il risultato dell'output:

+-------------+------------+-----------+-----------------+--------------+
| product     | ord_number | price     | cust_name       | cust_number  |
+-------------+------------+-----------+-----------------+--------------+
|     RAM     |   12345    |       124 | John Smith      |  20          |
|     CPU     |   12346    |       212 | Mia X           |  22          |
|     USB     |   12347    |        49 | Elise Beth      |  21          |
|     Cable   |   12348    |         0 | Paul Fort       |  19          |
|     Mouse   |   12349    |        66 | Nats Back       |  15          |
|     Laptop  |   12350    |       612 | Mel S           |  36          |
|     Keyboard|   12351    |        24 | George Z        |  95          |
|     Keyboard|   12352    |        24 | Ally B          |  55          |
|     Air     |   12353    |        12 | Maria Trust     |  11          |
+-------------+------------+-----------+-----------------+--------------+

ORDER BY

ORDER BY offre un modo per ordinare i risultati secondo una o più colonne nella sezione SELECT. Nell'esempio qui sotto, gli studenti sono stati messi in ordine discendente secondo il loro FullName. L'ordine di default è ascendente (ASC) e per ordinarli al contrario devi usare DESC.

SELECT studentID, FullName, sat_score
FROM student
ORDER BY FullName DESC;

COUNT

COUNT viene usato per contare il numero di righe e restituire il risultato come una colonna.

Ecco alcuni esempi delle situazioni in cui COUNT risulta utile:

• Contare tutte le righe di una tabella (senza usare GROUP BY)
• Contare il totale di sottogruppi di dati (richiede una sezione GROUP BY nell'istruzione)

Questo codice fornisce il conteggio di tutte le righe. Nota che puoi dare un nome alla colonna COUNT restituita usando AS.

SELECT count(*) AS studentCount FROM student; 

DELETE

DELETE viene usato per cancellare un dato in una tabella.

Fai attenzione, perché puoi cancellare tutti i dati di una tabella o soltanto alcuni di essi. Usa la condizione WHERE per specificare quali dati vuoi eliminare, secondo la sintassi:

DELETE FROM table_name
WHERE condition;

Ecco un esempio in cui eliminiamo i dati con Id 3 dalla colonna Person:

DELETE FROM Person
WHERE Id = 3;

INNER JOIN

JOIN, o anche detto Inner Join, seleziona dati che hanno valori corrispondenti in due tabelle.

SELECT * FROM A x JOIN B y ON y.aId = x.Id

LEFT JOIN

L'istruzione LEFT JOIN restituisce tutte le righe della tabella di sinistra e le righe corrispondenti di quella di destra. Le righe della tabella di sinistra vengono restituite anche se non c'è corrispondenza con la tabella di destra. Le righe della tabella di sinistra senza corrispondenza presenteranno null per i valori della tabella di destra.

SELECT * FROM A x LEFT JOIN B y ON y.aId = x.Id

RIGHT JOIN

RIGHT JOIN restituisce tutte le righe della tabella di destra e le corrispondenti di quella di sinistra. In contrapposizione a LEFT JOIN, restituisce tutte le righe della tabella di destra anche se non c'è corrispondenza con la tabella di sinistra. Le righe della tabella di destra senza corrispondenza presenteranno null per i valori della tabella sinistra.

SELECT * FROM A x RIGHT JOIN B y ON y.aId = x.Id

FULL OUTER JOIN

Il comando FULL OUTER JOIN restituisce tutte le righe in cui c'è corrispondenza in entrambe le tabelle.

Se ci sono righe nella tabella di sinistra non corrispondenti alla tabella di destra, non verranno incluse, e se ci sono righe della tabella di destra non corrispondenti alla tabella di sinistra anche queste non verranno incluse verranno incluse.

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName

INSERT

INSERT ti permette di inserire dei dati in una tabella.

INSERT INTO table_name (column_1, column_2, column_3) 
VALUES (value_1, 'value_2', value_3);

LIKE

LIKE viene usato insieme a un comando WHERE o HAVING (come parte di GROUP BY) per limitare le righe selezionate quando una colonna presenta un certo pattern di caratteri al suo interno.

Questo codice seleziona gli studenti che hanno FullName che inizia con “Monique” o termina con “Greene”.

SELECT studentID, FullName, sat_score, rcd_updated
FROM student 
WHERE 
    FullName LIKE 'Monique%' OR 
    FullName LIKE '%Greene'; 

+-----------+---------------+-----------+---------------------+
| studentID | FullName      | sat_score | rcd_updated         |
+-----------+---------------+-----------+---------------------+
|         1 | Monique Davis |       400 | 2017-08-16 15:34:50 |
|         5 | Alvin Greene  |      1200 | 2017-08-16 15:34:50 |
+-----------+---------------+-----------+---------------------+
2 rows in set (0.00 sec)

Puoi inserire NOT prima diLIKE per escludere le righe contenenti il pattern invece di selezionarle. Questo codice esclude le righe contenenti "cer Pau" e "Ted" nella colonna FullName.

SELECT studentID, FullName, sat_score, rcd_updated
FROM student 
WHERE FullName NOT LIKE '%cer Pau%' AND FullName NOT LIKE '%"Ted"%';

+-----------+----------------------+-----------+---------------------+
| studentID | FullName             | sat_score | rcd_updated         |
+-----------+----------------------+-----------+---------------------+
|         1 | Monique Davis        |       400 | 2017-08-16 15:34:50 |
|         2 | Teri Gutierrez       |       800 | 2017-08-16 15:34:50 |
|         4 | Louis Ramsey         |      1200 | 2017-08-16 15:34:50 |
|         5 | Alvin Greene         |      1200 | 2017-08-16 15:34:50 |
|         6 | Sophie Freeman       |      1200 | 2017-08-16 15:34:50 |
|         8 | Donald D. Chamberlin |      2400 | 2017-08-16 15:35:33 |
|         9 | Raymond F. Boyce     |      2400 | 2017-08-16 15:35:33 |
+-----------+----------------------+-----------+---------------------+
7 rows in set (0.00 sec)