In questo tutorial, esploreremo l’uso della funzione COALESCE nelle query SQL di Data Explorer.
COALESCE ti permette di gestire i valori NULL nei risultati delle tue query. Se i tuoi dati contengono valori NULL, puoi utilizzare COALESCE per fornire un valore predefinito (come 0) al loro posto.
COALESCE è particolarmente utile quando potresti eseguire calcoli successivi o analisi dei dati sui risultati della tua query, dove i valori NULL potrebbero causare problemi o interpretazioni errate.
Sintassi
La funzione COALESCE accetta due o più argomenti e restituisce il primo valore non NULL che incontra da sinistra a destra nell’elenco. Se tutti gli argomenti sono NULL, COALESCE restituisce NULL.
La sintassi di base per COALESCE è la seguente:
COALESCE(valore1, valore2, ..., valoreN)
Ad esempio, COALESCE(NULL, 1, 2) restituirà 1 perché 1 è il primo argomento non NULL.
Query di Esempio
Esaminiamo alcune query di esempio per comprendere come viene utilizzata COALESCE nelle query di Data Explorer.
Post Creati, Mi Piace Ricevuti e Segnalibri Ricevuti
Livello di Complessità: Principiante
Questa query ottiene il numero totale di post creati, mi piace ricevuti e segnalibri ricevuti sui post per ogni utente di un sito. Se un utente non ha post, mi piace o segnalibri ricevuti, la funzione COALESCE restituirà 0 invece di NULL.
SELECT
users.id AS user_id,
users.username,
COALESCE(COUNT(posts.id), 0) AS post_count,
COALESCE(SUM(posts.like_count), 0) AS likes_received,
COALESCE(SUM(posts.bookmark_count), 0) AS bookmarks_received
FROM
users
LEFT JOIN
posts ON users.id = posts.user_id
GROUP BY
users.id, users.username
ORDER BY
post_count DESC, likes_received DESC, bookmarks_received DESC
Risultati di Esempio:
| user | username | post_count | likes_received | bookmarks_received |
|---|---|---|---|---|
| 1 | alice | 345 | 6 | 9 |
| 2 | bella | 278 | 5 | 6 |
| 3 | charlie | 37 | 3 | 3 |
| 4 | dave | 0 | 0 | 0 |
In questa query, uniamo la tabella users con la tabella posts sul campo user_id. Successivamente, utilizziamo la funzione COALESCE per garantire che, se un utente non ha post, mi piace ricevuti o segnalibri ricevuti, restituiamo 0 invece di NULL. I risultati sono raggruppati per ID utente e nome utente, e ordinati per numero di post, mi piace e segnalibri in ordine decrescente.
Argomenti e Risposte per Utente
Livello di Complessità: Intermedio
Questa query ottiene il conteggio degli argomenti e delle risposte pubblicati da ogni utente tra due date. Se un utente non ha argomenti o risposte, COALESCE restituirà 0 invece di NULL.
-- [params]
-- date :start_date
-- date :end_date
-- string NULL:username
WITH qtt_topics AS (
SELECT
t.user_id,
COUNT(*) AS topics
FROM topics t
WHERE
t.user_id > 0
AND t.deleted_at ISNULL
AND t.archetype = 'regular'
AND t.created_at::date BETWEEN :start_date AND :end_date
GROUP BY t.user_id
),
qtt_replies AS (
SELECT
p.user_id,
COUNT(*) AS replies
FROM posts p
WHERE
p.user_id > 0
AND p.deleted_at ISNULL
AND p.post_number != 1
AND p.created_at::date BETWEEN :start_date AND :end_date
GROUP BY p.user_id
),
total AS (
SELECT
COALESCE(qr.user_id, qt.user_id) user_id,
COALESCE(topics,0) qtt_topics,
COALESCE(replies,0) qtt_replies
FROM qtt_topics qt
FULL JOIN qtt_replies qr ON qt.user_id = qr.user_id
ORDER BY user_id)
SELECT
username,
qtt_topics,
qtt_replies
FROM total
INNER JOIN users u ON u.id = user_id
WHERE :username ISNULL OR LOWER(username) LIKE '%'||LOWER(:username)||'%'
Risultati di Esempio:
| username | qtt_topics | qtt_replies |
|---|---|---|
| Alice | 10 | 50 |
| Bella | 15 | 45 |
| Charlie | 12 | 30 |
In questa query, COALESCE viene utilizzata nell’espressione di tabella comune (CTE) total. Garantisce che, se l’user_id è NULL in qtt_topics o qtt_replies, venga utilizzato l’altro valore. Questo è importante perché viene utilizzato un FULL JOIN per combinare qtt_topics e qtt_replies, e se un utente ha solo argomenti ma nessuna risposta (o viceversa), il suo user_id sarebbe NULL in una delle tabelle. COALESCE previene questo problema.
Spiegazione Dettagliata con Commenti Inline
-- [params]
-- date :start_date
-- date :end_date
-- string NULL:username
-- Definisci una CTE (Common Table Expression) per contare gli argomenti per utente
WITH qtt_topics AS (
SELECT
t.user_id, -- id utente
COUNT(*) AS topics -- conteggio degli argomenti
FROM topics t -- dalla tabella topics
WHERE
t.user_id > 0 -- considera solo gli id utente diversi da zero
AND t.deleted_at ISNULL -- considera solo gli argomenti non eliminati
AND t.archetype = 'regular' -- considera solo gli argomenti regolari
AND t.created_at::date BETWEEN :start_date AND :end_date -- considera solo gli argomenti creati tra start_date e end_date
GROUP BY t.user_id -- raggruppa per id utente per ottenere il conteggio degli argomenti per utente
),
-- Definisci una CTE per contare le risposte per utente
qtt_replies AS (
SELECT
p.user_id, -- id utente
COUNT(*) AS replies -- conteggio delle risposte
FROM posts p -- dalla tabella posts
WHERE
p.user_id > 0 -- considera solo gli id utente diversi da zero
AND p.deleted_at ISNULL -- considera solo i post non eliminati
AND p.post_number != 1 -- considera solo i post che non sono il primo post in un argomento (cioè le risposte)
AND p.created_at::date BETWEEN :start_date AND :end_date -- considera solo i post creati tra start_date e end_date
GROUP BY p.user_id -- raggruppa per id utente per ottenere il conteggio delle risposte per utente
),
-- Definisci una CTE per combinare i conteggi di argomenti e risposte per utente
total AS (
SELECT
COALESCE(qr.user_id, qt.user_id) user_id, -- id utente (da qtt_replies o qtt_topics)
COALESCE(topics,0) qtt_topics, -- conteggio degli argomenti (se NULL, restituisce 0)
COALESCE(replies,0) qtt_replies -- conteggio delle risposte (se NULL, restituisce 0)
FROM qtt_topics qt -- dalla CTE qtt_topics
FULL JOIN qtt_replies qr ON qt.user_id = qr.user_id -- unisci con la CTE qtt_replies sull'id utente
ORDER BY user_id -- ordina per id utente
)
-- Query principale per ottenere il set di risultati finale
SELECT
username, -- nome utente
qtt_topics, -- conteggio degli argomenti
qtt_replies -- conteggio delle risposte
FROM total -- dalla CTE total
INNER JOIN users u ON u.id = user_id -- unisci con la tabella users sull'id utente
WHERE :username ISNULL OR LOWER(username) LIKE '%'||LOWER(:username)||'%' -- filtra per nome utente (se fornito)
Dati sulle Domande Risolte
Livello di Complessità: Avanzato / Richiede il Plugin Discourse Solved
Questa query viene utilizzata per ottenere dettagli sugli argomenti, inclusi se sono risolti o meno, il tempo impiegato per la prima risposta, il tempo impiegato per la soluzione e altre statistiche correlate.
Questa query presuppone che tutti gli argomenti su un sito possano essere risolti.
--[params]
-- date :start_date = 2022-01-01
-- date :end_date = 2024-01-01
-- text :tag_name = all
WITH valid_topics AS (
SELECT
t.id,
t.user_id,
t.title,
t.views,
posts_count-1 AS "posts_count",
t.created_at,
(CURRENT_DATE::date-t.created_at::date) AS "total_days",
string_agg(tags.name, ', ') AS tag_names
FROM topics t
LEFT JOIN topic_tags tt ON tt.topic_id = t.id
LEFT JOIN tags ON tags.id = tt.tag_id
LEFT JOIN tag_group_memberships tgm ON tgm.tag_id = tt.tag_id
LEFT JOIN (SELECT topic_id, MIN(created_at) AS "created_at"
FROM posts
WHERE deleted_at ISNULL
AND post_type = 1
AND post_number > 1
GROUP BY topic_id) date_first_reply ON date_first_reply.topic_id = t.id
WHERE t.deleted_at ISNULL
AND t.created_at::date BETWEEN :start_date AND :end_date
AND t.archetype = 'regular'
GROUP BY t.id, t.user_id, t.title, t.views, posts_count, t.created_at
),
solved_topics AS (
SELECT
vt.id,
tcf.created_at
FROM topic_custom_fields tcf
INNER JOIN valid_topics vt ON vt.id = tcf.topic_id
WHERE tcf.name = 'accepted_answer_post_id'
),
last_reply AS (
SELECT p.topic_id, p.user_id FROM posts p
INNER JOIN (SELECT topic_id, MAX(id) AS post FROM posts p
WHERE deleted_at ISNULL
AND post_type = 1
GROUP BY topic_id) x ON x.post = p.id
),
first_reply AS (
SELECT p.topic_id, p.user_id, p.created_at FROM posts p
INNER JOIN (SELECT topic_id, MIN(id) AS post FROM posts p
WHERE deleted_at ISNULL
AND post_type = 1
AND post_number > 1
GROUP BY topic_id) x ON x.post = p.id
)
SELECT
CASE
WHEN st.id IS NOT NULL THEN 'solved'
ELSE 'unsolved'
END AS status,
vt.tag_names,
vt.id AS topic_id,
vt.user_id topic_user_id,
ue.email,
vt.title,
vt.views,
lr.user_id AS last_reply_user_id,
ue2.email AS last_reply_user_email,
vt.created_at::date topic_create,
COALESCE(TO_CHAR(fr.created_at,'YYYY-MM-DD'),'') AS first_reply_create,
COALESCE(TO_CHAR(st.created_at,'YYYY-MM-DD'),'') AS solution_create,
COALESCE(fr.created_at::date - vt.created_at::date,0) AS "time_first_reply(days)",
COALESCE(CEIL(extract(epoch from (fr.created_at - vt.created_at))/3600.00),0) as "time_first_reply(hours)",
COALESCE(st.created_at::date - vt.created_at::date,0) AS "time_solution(days)",
COALESCE(CEIL(extract(epoch from (st.created_at - vt.created_at))/3600.00),0) as "time_solution(hours)",
posts_count AS number_of_replies,
total_days AS total_days_without_solution
FROM valid_topics vt
LEFT JOIN last_reply lr ON lr.topic_id = vt.id
LEFT JOIN first_reply fr ON fr.topic_id = vt.id
LEFT JOIN solved_topics st ON st.id = vt.id
INNER JOIN user_emails ue ON vt.user_id = ue.user_id AND ue."primary" = true
LEFT JOIN user_emails ue2 ON lr.user_id = ue2.user_id AND ue2."primary" = true
WHERE (:tag_name = 'all' OR LOWER(vt.tag_names) LIKE '%' || LOWER(:tag_name) || '%')
ORDER BY tag_names, total_days DESC
Risultati di Esempio:
| status | tag_names | topic | topic_user | title | views | last_reply_user | last_reply_user_email | topic_create | first_reply_create | solution_create | time_first_reply(days) | time_first_reply(hours) | time_solution(days) | time_solution(hours) | number_of_replies | total_days_without_solution | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| solved | a, c, b | A Topic Title (7) | alice | alice@example.com | A Topic Title | 58 | bella | bella@example.com | 2023-08-25 | 2023-08-25 | 2023-08-29 | 0 | 1 | 1 | 24 | 9 | 4 |
| unsolved | tag1 | Welcome to the Lounge (3) | system | no_email | Welcome to the Lounge | 3 | system | no_email | 2023-05-01 | 0 | 0 | 0 | 0 | 2 | 134 |
In questa query, COALESCE viene utilizzata nelle seguenti righe:
COALESCE(TO_CHAR(fr.created_at,'YYYY-MM-DD'),'') AS first_reply_create: Questa riga converte la datacreated_atdella prima risposta in una stringa. Se la prima risposta non esiste (cioèfr.created_atè null), restituirà una stringa vuota (‘’).COALESCE(TO_CHAR(st.created_at,'YYYY-MM-DD'),'') AS solution_create: Simile alla precedente, questa riga converte la datacreated_atdella soluzione in una stringa. Se la soluzione non esiste (cioèst.created_atè null), restituirà una stringa vuota (‘’).COALESCE(fr.created_at::date - vt.created_at::date,0) AS "time_first_reply(days)": Questa riga calcola la differenza di tempo in giorni tra la creazione dell’argomento e la prima risposta. Se la prima risposta non esiste (cioèfr.created_atè null), restituirà 0.COALESCE(CEIL(extract(epoch from (fr.created_at - vt.created_at))/3600.00),0) as "time_first_reply(hours)": Questa riga calcola la differenza di tempo in ore tra la creazione dell’argomento e la prima risposta. Se la prima risposta non esiste (cioèfr.created_atè null), restituirà 0.COALESCE(st.created_at::date - vt.created_at::date,0) AS "time_solution(days)": Questa riga calcola la differenza di tempo in giorni tra la creazione dell’argomento e la soluzione. Se la soluzione non esiste (cioèst.created_atè null), restituirà 0.COALESCE(CEIL(extract(epoch from (st.created_at - vt.created_at))/3600.00),0) as "time_solution(hours)": Questa riga calcola la differenza di tempo in ore tra la creazione dell’argomento e la soluzione. Se la soluzione non esiste (cioèst.created_atè null), restituirà 0.
In tutti questi casi, COALESCE viene utilizzata per evitare che i valori NULL appaiano nei risultati finali, migliorando la leggibilità della query risultante e potendo essere utile per successivi processi di elaborazione o analisi dei dati.
Spiegazione Dettagliata con Commenti Inline
--[params]
-- date :start_date = 2022-01-01
-- date :end_date = 2024-01-01
-- text :tag_name = all
-- Definisci una CTE per gli argomenti validi
WITH valid_topics AS (
-- Seleziona i campi necessari
SELECT
t.id, -- id argomento
t.user_id, -- id utente
t.title, -- titolo argomento
t.views, -- numero di visualizzazioni
posts_count-1 AS "posts_count", -- numero di post nell'argomento
t.created_at, -- data di creazione dell'argomento
(CURRENT_DATE::date-t.created_at::date) AS "total_days", -- giorni totali dalla creazione dell'argomento
string_agg(tags.name, ', ') AS tag_names -- aggrega tutti i tag associati all'argomento
FROM topics t -- dalla tabella topics
-- Unisci le tabelle necessarie per ottenere i nomi dei tag
LEFT JOIN topic_tags tt ON tt.topic_id = t.id
LEFT JOIN tags ON tags.id = tt.tag_id
LEFT JOIN tag_group_memberships tgm ON tgm.tag_id = tt.tag_id
-- Sottoprogramma per ottenere la data della prima risposta per ogni argomento
LEFT JOIN (SELECT topic_id, MIN(created_at) AS "created_at"
FROM posts
WHERE deleted_at ISNULL
AND post_type = 1
AND post_number > 1
GROUP BY topic_id) date_first_reply ON date_first_reply.topic_id = t.id
WHERE t.deleted_at ISNULL -- considera solo gli argomenti non eliminati
AND t.created_at::date BETWEEN :start_date AND :end_date -- considera solo gli argomenti creati tra start_date e end_date
AND t.archetype = 'regular' -- considera solo gli argomenti regolari
GROUP BY t.id, t.user_id, t.title, t.views, posts_count, t.created_at -- raggruppa per i campi necessari per ottenere il conteggio corretto
),
-- Definisci una CTE per gli argomenti risolti
solved_topics AS (
-- Seleziona l'id dell'argomento e la data di creazione della soluzione
SELECT
vt.id,
tcf.created_at
FROM topic_custom_fields tcf -- dalla tabella topic_custom_fields
INNER JOIN valid_topics vt ON vt.id = tcf.topic_id -- unisci con la CTE valid_topics
WHERE tcf.name = 'accepted_answer_post_id' -- considera solo gli argomenti con una risposta accettata
),
-- Definisci una CTE per l'ultima risposta di ogni argomento
last_reply AS (
-- Seleziona l'id dell'argomento e l'id utente dell'ultima risposta
SELECT p.topic_id, p.user_id FROM posts p
INNER JOIN (SELECT topic_id, MAX(id) AS post FROM posts p -- sottoprogramma per ottenere l'id dell'ultimo post per ogni argomento
WHERE deleted_at ISNULL
AND post_type = 1
GROUP BY topic_id) x ON x.post = p.id -- unisci con la tabella posts per ottenere l'id utente dell'ultima risposta
),
-- Definisci una CTE per la prima risposta di ogni argomento
first_reply AS (
-- Seleziona l'id dell'argomento, l'id utente e la data di creazione della prima risposta
SELECT p.topic_id, p.user_id, p.created_at FROM posts p
INNER JOIN (SELECT topic_id, MIN(id) AS post FROM posts p -- sottoprogramma per ottenere l'id della prima risposta per ogni argomento
WHERE deleted_at ISNULL
AND post_type = 1
AND post_number > 1
GROUP BY topic_id) x ON x.post = p.id -- unisci con la tabella posts per ottenere l'id utente e la data di creazione della prima risposta
)
-- Query principale per ottenere il set di risultati finale
SELECT
CASE
WHEN st.id IS NOT NULL THEN 'solved' -- se l'id dell'argomento è nella CTE solved_topics, lo stato è 'risolto'
ELSE 'unsolved' -- altrimenti, lo stato è 'non risolto'
END AS status,
vt.tag_names, -- nomi dei tag
vt.id AS topic_id, -- id argomento
vt.user_id topic_user_id, -- id utente
ue.email, -- email utente
vt.title, -- titolo argomento
vt.views, -- numero di visualizzazioni
lr.user_id AS last_reply_user_id, -- id utente dell'ultima risposta
ue2.email AS last_reply_user_email, -- email dell'utente che ha fatto l'ultima risposta
vt.created_at::date topic_create, -- data di creazione dell'argomento
COALESCE(TO_CHAR(fr.created_at,'YYYY-MM-DD'),'') AS first_reply_create, -- data di creazione della prima risposta, se esiste, altrimenti stringa vuota
COALESCE(TO_CHAR(st.created_at,'YYYY-MM-DD'),'') AS solution_create, -- data di creazione della soluzione, se esiste, altrimenti stringa vuota
COALESCE(fr.created_at::date - vt.created_at::date,0) AS "time_first_reply(days)", -- tempo impiegato per la prima risposta in giorni
COALESCE(CEIL(extract(epoch from (fr.created_at - vt.created_at))/3600.00),0) as "time_first_reply(hours)", -- tempo impiegato per la prima risposta in ore
COALESCE(st.created_at::date - vt.created_at::date,0) AS "time_solution(days)", -- tempo impiegato per la soluzione in giorni
COALESCE(CEIL(extract(epoch from (st.created_at - vt.created_at))/3600.00),0) as "time_solution(hours)", -- tempo impiegato per la soluzione in ore
posts_count AS number_of_replies, -- numero di risposte
total_days AS total_days_without_solution -- giorni totali senza soluzione
FROM valid_topics vt -- dalla CTE valid_topics
LEFT JOIN last_reply lr ON lr.topic_id = vt.id -- unisci con la CTE last_reply
LEFT JOIN first_reply fr ON fr.topic_id = vt.id -- unisci con la CTE first_reply
LEFT JOIN solved_topics st ON st.id = vt.id -- unisci con la CTE solved_topics
INNER JOIN user_emails ue ON vt.user_id = ue.user_id AND ue."primary" = true -- unisci con la tabella user_emails per ottenere l'email dell'utente
LEFT JOIN user_emails ue2 ON lr.user_id = ue2.user_id AND ue2."primary" = true -- unisci con la tabella user_emails per ottenere l'email dell'utente che ha fatto l'ultima risposta
WHERE (:tag_name = 'all' OR LOWER(vt.tag_names) LIKE '%' || LOWER(:tag_name) || '%') -- filtra per nome del tag
ORDER BY tag_names, total_days DESC -- ordina per nomi dei tag e giorni totali in ordine decrescente
Se hai domande o esempi su come hai utilizzato COALESCE nelle tue query di Data Explorer, sentiti libero di condividerli qui sotto. 