Progetto SQL per principianti: Costruire un report con BigQuery e Looker

Volete mettere in pratica le vostre abilità SQL su un set di dati reali? In questo progetto adatto ai principianti, userete BigQuery e Looker per esplorare i dati sulla criminalità di Chicago e creare il vostro primo report interattivo - non è necessaria alcuna esperienza avanzata!

Se volete migliorare in SQL, non c'è niente di meglio che lavorare su progetti reali. La pratica è il luogo in cui le vostre abilità crescono veramente e oggi farete un grande passo avanti.

In questo progetto adatto ai principianti, creerete un semplice rapporto sulla criminalità basato sul set di dati pubblici Chicago Crime, utilizzando BigQuery per scrivere query SQL e Looker Studio per costruire una dashboard interattiva. Nessuna impostazione avanzata, nessuno strumento complicato: solo un percorso chiaro dai dati grezzi ai dati reali.

Utilizzeremo due strumenti potenti (e gratuiti):

• BigQuery per interrogare grandi insiemi di dati pubblici con SQL.
• Looker Studio per trasformare i risultati in visualizzazioni di facile comprensione.

Se dopo questo progetto volete acquisire competenze ancora più solide, date un'occhiata al percorso SQL dalla A alla Z su LearnSQL.it: si tratta di un percorso di apprendimento completo progettato per portarvi da principianti assoluti a utenti sicuri di SQL con la pratica.

Iniziamo!

Passo 1: Configurare l'ambiente

Prima di iniziare a scrivere query SQL, prepariamo tutto. Non preoccupatevi: è veloce, gratuito e adatto ai principianti.

Accesso a BigQuery

Innanzitutto, è necessario un account Google. Andate su Google Cloud Console, create un nuovo progetto e aprite BigQuery. Google offre un livello gratuito che è più che sufficiente per questo progetto: non è necessaria la carta di credito se si utilizzano solo i set di dati pubblici di BigQuery.

All'interno di BigQuery, sarete in grado di eseguire query SQL direttamente nel vostro browser senza installare nulla.

Trovare il dataset pubblico Chicago Crime

Una volta entrati in BigQuery, fate clic su "Add Data" → "Explore Public Datasets". Cercate "chicago crime" e troverete il dataset che vi serve: bigquery-public-data.chicago_crime.crime

Fare clic su di esso per visualizzare le tabelle e i campi disponibili. Siete pronti per iniziare l'interrogazione!

Configurare Looker Studio

Looker Studio (precedentemente noto come Google Data Studio) consente di visualizzare facilmente i risultati di BigQuery, senza bisogno di codifica.

Visitate Looker Studio, accedete con lo stesso account Google e fate clic su "Crea" → "Rapporto".
Quando vi viene chiesto di selezionare un'origine dati, scegliete BigQuery, trovate il vostro progetto e collegatevi al set di dati con cui lavorerete.

Ecco fatto: il vostro ambiente è pronto! Nella fase successiva, inizieremo a esplorare i dati con SQL.

Passo 2: Esplorare il set di dati

Diamo una rapida occhiata ai dati con cui lavoreremo. Il dataset Chicago Crime contiene i dati dettagliati dei crimini denunciati a Chicago, a partire dal 2001.

Ogni riga rappresenta una singola denuncia di reato e include informazioni utili quali:

• date - quando si è verificato il crimine.
• primary_type - la categoria generale (ad esempio, THEFT, ASSAULT).
• description - un'etichetta più specifica.
• location_description - il luogo in cui è avvenuto (ad esempio, STREET, RESIDENCE).
• arrest - se è stato effettuato un arresto (TRUE/FALSE).
• district - il numero del distretto di polizia.
• latitude / longitude - la posizione geografica.

Per visualizzare un'anteprima del set di dati, eseguire questa semplice query in BigQuery:

SELECT *  
FROM `bigquery-public-data.chicago_crime.crime`  
LIMIT 10;

Questa mostra le prime 10 righe e dà un'idea della struttura dei dati. Dedicate un po' di tempo a scorrere i risultati: sapere cosa è disponibile vi aiuterà a scrivere query più intelligenti.

Fase 3: scrivere le query

Ora che avete visto cosa c'è nel dataset, è il momento di scrivere la vostra prima query SQL. Il nostro obiettivo è scoprire quanti crimini sono stati commessi a Chicago nel 2024, raggruppati per tipo.

In un report reale, è probabile che si voglia andare più a fondo, esplorando i tassi di arresto, confrontando le tendenze nel tempo o suddividendo le cose per quartiere. Ma per mantenere questo tutorial adatto ai principianti, ci limiteremo a una domanda chiara: quali sono i tipi di crimini più comuni nel 2024?

Per rispondere a questa domanda, dovremo:

• Filtrare i dati solo per l'anno 2024.
• Raggruppare per primary_type (categoria di reato).
• Contare il numero di reati in ciascuna categoria.

Ecco la query:

SELECT 
  primary_type, 
  COUNT(*) AS num_crimes
FROM `bigquery-public-data.chicago_crime.crime`
WHERE year = 2024
GROUP BY primary_type
ORDER BY num_crimes DESC;

Si ottiene così un elenco classificato dei tipi di reato, dal più frequente al meno frequente. Nella fase successiva, utilizzeremo questo risultato per costruire un semplice grafico a barre in Looker Studio.

Per imparare a scrivere query più complesse per i report, date un'occhiata alla traccia SQL Reporting su LearnSQL.it. Si tratta di una guida pratica per trasformare i dati grezzi in informazioni utili utilizzando tecniche di reporting reali.

È inoltre possibile conservare il SQL per principianti Foglio informativo come riferimento rapido mentre scrivete: è un ottimo compagno per i principianti.

Passo 4: Collegare BigQuery a Looker Studio

Ora che la query è pronta, visualizziamo i risultati utilizzando Looker Studio (ex Google Data Studio). Poiché stiamo utilizzando il livello gratuito di BigQuery, eseguiremo la query utilizzando l'opzione Query personalizzata, senza dover creare o salvare tabelle o viste.

1. Andare a Looker Studio e accedere con lo stesso account Google utilizzato per BigQuery.
2. Fate clic su "Rapporto vuoto" o scegliete "Crea → Rapporto".
3. Quando viene richiesto di selezionare un'origine dati, scegliere "BigQuery".
4. Nella finestra del connettore:
• Selezionare il proprio progetto (quello creato in precedenza in Google Cloud Console).
• Quindi fare clic su "Query personalizzata" in basso a sinistra.
5. Nella casella Query personalizzata, incollare la query SQL creata in precedenza:

   SELECT 
     primary_type, 
     COUNT(*) AS num_crimes
   FROM `bigquery-public-data.chicago_crime.crime`
   WHERE year = 2024
   GROUP BY primary_type
   ORDER BY num_crimes DESC;
   

6. Fare clic su "Aggiungi", quindi confermare facendo clic su "Aggiungi al report".
   

Looker Studio ora utilizzerà direttamente i risultati della query, senza richiedere la memorizzazione dei dati. Siete pronti a creare il vostro primo grafico.

Passo 5: creare un semplice rapporto

Dopo aver collegato l'origine dati, è il momento di creare il primo report. In Looker Studio, iniziate con la creazione di un grafico a barre che mostra il numero di reati per tipo.

Creare il grafico

1. Dal menu in alto, fare clic su "Aggiungi un grafico" e selezionare il grafico a barre.
2. Trascinare per posizionarlo sull'area di disegno del report.

Impostazione dei dati

• Dimensione (asse X): primary_type
• Metrica (asse Y): num_crimes

Verrà visualizzato un grafico a barre in cui ogni barra rappresenta un tipo di reato e l'altezza indica quante volte si è verificato nel 2024.

Looker Studio offre molti altri tipi di grafici che si possono sperimentare, tra cui grafici a linee, a torta, mappe geografiche e altri ancora. Man mano che le query diventano più avanzate, è possibile utilizzarle per mostrare le tendenze nel tempo, confrontare le categorie o visualizzare i dati per località.

Per il momento ci limiteremo a un solo grafico, ma siamo liberi di esplorare e personalizzare il layout man mano che si procede.

Fase 6: ritocchi finali

Ora che il vostro grafico è pronto, prendetevi un minuto per perfezionare il report e renderlo vostro.

• Aggiungete un titolo utilizzando lo strumento casella di testo, ad esempio: "Chicago Crimes by Type - 2024".
• Formattate i numeri per renderli più leggibili: aggiungete virgole, rimuovete i decimali o accorciate i valori più grandi (ad esempio, 12K invece di 12.000).
• Regolate i colori per migliorare il contrasto o evidenziare i punti chiave dei dati: scegliete una tavolozza di colori che si adatti al vostro stile o al vostro marchio.
• Personalizzare lo stile: selezionare i caratteri, le dimensioni dei caratteri e le opzioni di layout che rendono il report facile da leggere e visivamente accattivante.

Looker Studio vi dà il pieno controllo sull'aspetto del vostro report, quindi non esitate a sperimentare. Queste piccole modifiche contribuiscono a rendere il rapporto finale più accattivante e professionale.

Cosa fare dopo? Sviluppare ciò che si è appreso

Avete appena completato un vero progetto SQL, dalla scrittura di una query alla creazione di un report. Si tratta di un ottimo risultato.

Man mano che vi sentite a vostro agio con l'SQL e gli strumenti di reporting, provate a fare un ulteriore passo avanti nei vostri progetti. Ecco alcune idee da esplorare:

• Analizzare i crimini per distretto o quartiere per identificare i punti caldi.
• Controllare i tassi di arresto per tipo di crimine o luogo.
• Confrontare le tendenze nel tempo per vedere come i modelli di criminalità cambiano di mese in mese o di anno in anno.

Questi tipi di informazioni sono esattamente ciò che i professionisti forniscono nei ruoli di reporting reali. Se siete pronti a imparare a strutturare query e report più avanzati, la traccia SQL Reporting su LearnSQL.it è un ottimo passo successivo. È stato progettato per aiutarvi a trasformare i dati grezzi in informazioni utili utilizzando tecniche di reporting reali.

Potete anche esplorare altri corsi pratici su LearnSQL.it per affinare le vostre competenze, sia che siate agli inizi sia che vogliate portare il vostro SQL a un livello superiore.

Iniziate con semplicità, continuate a esplorare: ogni progetto fa crescere le vostre competenze in SQL.