Tutorial Data Explorer - Parte 2 - Nozioni di base di Discourse SQL

Comunità Dati e reportistica
, ,
1

:discourse: Questa guida è un proseguo del Tutorial di Data Explorer - Parte 1 - Scrivere la tua prima query.

Ora che hai visto come funziona Data Explorer in azione, parliamo di alcune basi di SQL che saranno utili da conoscere mentre ti addentrerai nelle tue avventure di esplorazione dei dati.

Istruzioni SQL

Tutte le azioni che dovrai eseguire sul database di Discourse vengono effettuate tramite istruzioni SQL.

:discourse: Le parole chiave SQL NON fanno distinzione tra maiuscole e minuscole: select è equivalente a SELECT, ma in questo tutorial, e per buona pratica, scriveremo tutte le parole chiave SQL in maiuscolo.

Ecco alcune delle istruzioni SQL più comuni che potresti utilizzare. Nota che ne abbiamo usate alcune nella nostra query di esempio precedente:

  • SELECT: Utilizzata per selezionare dati da un database. I dati restituiti sono memorizzati in una tabella dei risultati, chiamata result-set.
SELECT column1, column2, ...
FROM table_name;
  • WHERE: Utilizzata per filtrare i record.
SELECT column1, column2, ...
FROM table_name
WHERE condition;
  • ORDER BY: Utilizzata per ordinare il result-set in ordine crescente o decrescente.
SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;
  • COUNT: Restituisce il numero di righe che corrispondono a un criterio specificato.
SELECT COUNT(column_name)
FROM table_name
WHERE condition;
  • GROUP BY: Spesso utilizzata con funzioni di aggregazione (COUNT, MAX, MIN, SUM, AVG) per raggruppare il result-set per una o più colonne.
SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name;
  • HAVING: Utilizzata per filtrare i risultati di un’operazione GROUP BY. La clausola HAVING viene utilizzata in SQL perché la parola chiave WHERE non può essere usata con funzioni di aggregazione (Es: COUNT(), MAX(), MIN(), SUM(), AVG()).
SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > value;
  • IN: Utilizzata per verificare se un valore è presente in un insieme di valori o restituito da una subquery. È una scorciatoia per condizioni OR multiple, rendendo le tue query SQL più concise e facili da leggere.
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

:discourse: I punti e virgola non sono necessari quando si scrivono query SQL in Data Explorer.

PostgreSQL

Discourse utilizza attualmente PostgreSQL versione 13 per il proprio database, un sistema di gestione di database relazionali (RDBMS) open source che pone l’accento sull’estendibilità e sulla conformità SQL.

Per una comprensione dettagliata di PostgreSQL, potresti voler leggere la Documentazione Ufficiale di PostgreSQL. In particolare, potresti trovare le sezioni su Query, Tipi di Dati e Funzioni particolarmente rilevanti quando scrivi query SQL.

Tabelle del Database

Il database di Discourse è composto da tabelle. Le tabelle sono il modo fondamentale per memorizzare dati in un sistema di gestione di database relazionali.

Ogni tabella in Discourse è identificata da un nome (ad esempio posts o topics), e ogni tabella contiene record (righe) con dati. Le colonne nelle tabelle sono comunemente chiamate campi.

:discourse: Il database di Discourse contiene oltre 240 tabelle!

Data Explorer elenca le 9 tabelle più importanti per prime nel pannello dell’interfaccia di modifica della query, e puoi vedere la struttura e i tipi di tutte le colonne delle tabelle con un clic:

Schema del Database

In PostgreSQL, così come in altri database relazionali, i concetti di Chiave Primaria e Chiave Esterna sono fondamentali. Vengono utilizzati per stabilire relazioni tra tabelle e garantire la coerenza e l’integrità dei dati.

Chiavi Primarie

Una chiave primaria è una colonna o un insieme di colonne in una tabella che identifica in modo univoco ogni riga in quella tabella. Due righe diverse in una tabella non possono avere lo stesso valore di chiave primaria. Non è possibile avere un valore di chiave primaria nullo e il valore deve essere univoco. La chiave primaria viene utilizzata per indicizzare i dati nella tabella, rendendo il recupero dei dati molto più veloce. Nell’esploratore di schemi di Discourse, la chiave primaria di una tabella è sempre elencata per prima.

Esempio: La chiave primaria della tabella posts è il campo id.

Chiavi Esterne

Una chiave esterna è una colonna o un insieme di colonne in una tabella utilizzata per stabilire un collegamento tra i dati di due tabelle. Funziona come un riferimento incrociato tra tabelle perché fa riferimento alla chiave primaria di un’altra tabella, stabilendo così un collegamento tra di esse. La tabella con la chiave esterna è chiamata tabella figlia, mentre la tabella con la chiave primaria è chiamata tabella di riferimento o tabella genitore. Nell’esploratore di schemi di Discourse, una chiave esterna è indicata dal testo fkey seguito dal nome della tabella genitore.

Esempio: Nella tabella posts, user_id è una chiave esterna della tabella users.

Utilizzo di Chiavi Primarie ed Esterne

Quando scrivi query in Data Explorer, puoi utilizzare queste chiavi primarie ed esterne per JOIN le tabelle insieme e ottenere dati più complessi. Ad esempio, se volessi trovare tutti i post pubblicati da un determinato utente, potresti scrivere una query come questa:

SELECT p.id, p.created_at, p.raw
FROM posts p
JOIN users u ON p.user_id = u.id
WHERE u.username = 'username_here'

In questa query, stiamo utilizzando la chiave esterna user_id nella tabella posts per unirla alla chiave primaria id nella tabella users. Questo ci permette di trovare tutti i post pubblicati da un determinato utente.

SQL JOIN

Come precedentemente menzionato, l’istruzione JOIN viene utilizzata per combinare righe da due o più tabelle, basandosi su una colonna correlata tra di esse.

È importante notare che esistono diversi tipi di istruzioni SQL JOIN, ciascuna con uno scopo diverso:

  1. INNER JOIN: La parola chiave INNER JOIN seleziona i record che hanno valori corrispondenti in entrambe le tabelle. Restituisce le righe da entrambe le tabelle dove c’è una corrispondenza. Usa INNER JOIN quando vuoi restituire solo i record in cui c’è una corrispondenza in entrambe le tabelle. Quando JOIN viene utilizzato senza specificare il tipo di join (come LEFT, RIGHT o FULL), predefinisce INNER JOIN.

  2. LEFT (OUTER) JOIN: La parola chiave LEFT JOIN restituisce tutti i record dalla tabella sinistra (tabella1) e i record corrispondenti dalla tabella destra (tabella2). Il risultato è NULL dal lato destro se non c’è corrispondenza. Usa LEFT JOIN quando vuoi restituire tutti i record dalla tabella sinistra e i record corrispondenti dalla tabella destra. Se non c’è corrispondenza, il risultato è NULL sul lato destro.

  3. RIGHT (OUTER) JOIN: La parola chiave RIGHT JOIN restituisce tutti i record dalla tabella destra (tabella2) e i record corrispondenti dalla tabella sinistra (tabella1). Il risultato è NULL dal lato sinistro quando non c’è corrispondenza. Usa RIGHT JOIN quando vuoi restituire tutti i record dalla tabella destra e i record corrispondenti dalla tabella sinistra. Se non c’è corrispondenza, il risultato è NULL sul lato sinistro.

  4. FULL (OUTER) JOIN: La parola chiave FULL JOIN restituisce tutti i record quando c’è una corrispondenza in una delle tabelle, sinistra (tabella1) o destra (tabella2). Usa FULL JOIN quando vuoi restituire tutti i record quando c’è una corrispondenza in una delle tabelle.

Puoi anche combinare più istruzioni JOIN per unire più tabelle insieme. Ad esempio, possiamo unire la tabella topic_tags con la tabella topics, e poi unire la tabella tags con la tabella topic_tags per accedere ai nomi dei tag associati a un argomento.

SELECT 
    t.id AS topic_id, 
    tg.name as tag_name
FROM 
    topics t
JOIN topic_tags tt ON t.id = tt.topic_id
JOIN tags tg ON tg.id = tt.tag_id

Istruzioni WITH e Subquery

L’istruzione SQL WITH, nota anche come Common Table Expression (CTE), viene utilizzata per creare un result-set temporaneo che può essere referenziato all’interno di un’altra istruzione SELECT. Questo può essere particolarmente utile quando si lavora con query SQL complesse, poiché può aiutare a semplificarle, rendendole più facili da leggere e mantenere.

Ecco un esempio base di un’istruzione WITH:

WITH post_counts AS (
  SELECT user_id, COUNT(*) as post_count
  FROM posts
  GROUP BY user_id
)

SELECT u.username, post_counts.post_count
FROM users u
JOIN post_counts ON u.id = post_counts.user_id
ORDER BY post_counts.post_count DESC;

In questa query, l’istruzione WITH crea una tabella temporanea post_counts che contiene l’ID di ogni utente e il loro totale di post. La query principale unisce quindi questa tabella con la tabella users per ottenere i nomi utente associati a ciascun ID e ordina il risultato per il conteggio dei post in ordine decrescente.

Questa query restituirà il numero di post pubblicati da ciascun utente.

Le subquery sono query annidate all’interno di un’altra query. Una subquery può essere utilizzata all’interno di istruzioni SELECT o all’interno di un’altra subquery. Una subquery può restituire un insieme di record, un singolo record o un singolo valore.

Ecco un esempio di subquery:

SELECT u.username 
FROM users u
WHERE u.id IN (
  SELECT p.user_id 
  FROM posts p
  GROUP BY p.user_id 
  HAVING COUNT(p.id) > 100
)

In questo esempio, la subquery (SELECT p.user_id FROM posts p GROUP BY p.user_id HAVING COUNT(p.id) > 100) seleziona l’user_id dalla tabella posts per gli utenti che hanno pubblicato più di 100 post. La query principale ottiene quindi il username dalla tabella users per quegli user_id.

Questa query restituirà un elenco di nomi utente per gli utenti che hanno pubblicato più di 100 post.

Altri argomenti in questa serie

9 Mi Piace
Discourse Data Explorer
Using Parameters in Data Explorer Queries
Dashboard Report - Consolidated Pageviews