Questa è una versione SQL del report della dashboard per argomenti senza risposta.
Il report della dashboard è progettato per contare il numero di argomenti creati entro un intervallo di date specificato che non hanno ricevuto alcuna risposta da altri utenti. Questo report può essere filtrato per una categoria specifica e può includere facoltativamente sottocategorie.
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-02-01
-- null int :category_id
-- boolean :include_subcategories = false
WITH no_response_total AS (
SELECT *
FROM (
SELECT t.id, t.created_at, MIN(p.post_number) first_reply
FROM topics t
LEFT JOIN posts p ON p.topic_id = t.id AND p.user_id != t.user_id AND p.deleted_at IS NULL AND p.post_type = 1
WHERE t.archetype <> 'private_message'
AND t.deleted_at ISNULL
AND (
:category_id IS NULL
OR t.category_id = :category_id
OR (
:include_subcategories
AND t.category_id IN (
SELECT id FROM categories WHERE parent_category_id = :category_id
)
)
)
GROUP BY t.id
) tt
WHERE tt.first_reply IS NULL OR tt.first_reply < 2
)
SELECT
DATE(nrt.created_at) AS date,
COUNT(nrt.id) AS topics_without_response
FROM no_response_total nrt
WHERE nrt.created_at::date BETWEEN :start_date AND :end_date
GROUP BY date
ORDER BY date ASC
Parametri
Parametri Data:
La query accetta due parametri, :start_date e :end_date, che definiscono l’intervallo di date per il report. Entrambi i parametri data accettano il formato data AAAA-MM-GG.
Parametri Categoria:
:category_id: un parametro intero che può essere impostato sull’ID di una categoria specifica per restringere l’analisi ai post all’interno di tale categoria. Se impostato su null o non fornito, vengono considerati gli argomenti di tutte le categorie.
:include_subcategories: un parametro booleano che controlla se includere i post dalle sottocategorie del :category_id specificato. Se impostato su true, il report includerà collegamenti ai post sia nella categoria specificata che nelle sue sottocategorie; se false, verrà considerata solo la categoria specificata.
Spiegazione della Query SQL
La query inizia con una Common Table Expression (CTE) denominata no_response_total. Questa CTE esegue i seguenti passaggi:
Selezione degli Argomenti: Seleziona tutti gli argomenti (t.id) e le loro date di creazione (t.created_at) dalla tabella topics.
Left Join con i Post: Esegue un left join con la tabella posts per trovare la prima risposta a ciascun argomento. Le condizioni di join assicurano che il post non sia del creatore dell’argomento (p.user_id != t.user_id), che il post non sia stato eliminato (p.deleted_at IS NULL) e che il post sia di tipo 1, che in genere rappresenta una risposta standard.
Filtraggio degli Argomenti: La query filtra gli argomenti che sono messaggi privati (t.archetype <> 'private_message') e gli argomenti che sono stati eliminati (t.deleted_at ISNULL).
Filtraggio per Categoria: Se viene fornito un :category_id, la query filtrerà gli argomenti per includere solo quelli nella categoria specificata. Se :include_subcategories è true, includerà anche gli argomenti dalle sottocategorie della categoria specificata.
Raggruppamento e Numero Minimo di Post: Gli argomenti vengono raggruppati per ID e viene calcolato il numero minimo di post (MIN(p.post_number)) per trovare la prima risposta.
Filtraggio per Nessuna Risposta: La sottoquery tt filtra gli argomenti che hanno una prima risposta con un numero di post maggiore o uguale a 2, lasciando solo gli argomenti senza risposte (tt.first_reply IS NULL) o solo il post originale (tt.first_reply < 2).
Dopo che la CTE no_response_total è definita, la query principale esegue le seguenti operazioni:
Filtro per Intervallo di Date: Filtra gli argomenti dalla CTE in base alle date di inizio e fine fornite (:start_date e :end_date).
Conteggio degli Argomenti Senza Risposta: Conta il numero di argomenti senza risposta per ogni data all’interno dell’intervallo specificato.
Raggruppamento per Data: I risultati vengono raggruppati per data di creazione dell’argomento (DATE(nrt.created_at)).
Ordinamento: I risultati vengono ordinati per data in ordine crescente.
Riesci a creare una versione che non contenga parametri? Voglio creare una versione che guardi indietro di 7 giorni per poi inviarla via email alle persone e sto lottando per usare questo codice poiché ci sono parametri impostati al suo interno
Sì, ecco una versione aggiornata della query che guarda indietro di 7 giorni dalla data corrente senza utilizzare parametri.
Questa versione non include alcun filtro per categorie o sottocategorie.
WITH no_response_total AS (
SELECT *
FROM (
SELECT
t.id,
t.created_at,
MIN(p.post_number) AS first_reply
FROM
topics t
LEFT JOIN
posts p
ON
p.topic_id = t.id
AND p.user_id != t.user_id
AND p.deleted_at IS NULL
AND p.post_type = 1
WHERE
t.archetype <> 'private_message'
AND t.deleted_at IS NULL
AND (
t.category_id = :category_id
OR t.category_id IN (
SELECT id FROM categories WHERE parent_category_id = :category_id
)
)
GROUP BY
t.id
) tt
WHERE
tt.first_reply IS NULL
OR tt.first_reply < 2
)
SELECT
DATE(nrt.created_at) AS date,
COUNT(nrt.id) AS topics_without_response
FROM
no_response_total nrt
WHERE
nrt.created_at::date BETWEEN CURRENT_DATE - INTERVAL '7 days' AND CURRENT_DATE
GROUP BY
date
ORDER BY
date ASC
Se volessi regolare quanto indietro guarda la query, dovresti solo cambiare questa riga nella query:
nrt.created_at::date BETWEEN CURRENT_DATE - INTERVAL '7 days' AND
Grazie per la risposta, tornerò su questo quando ne avrò bisogno la prossima volta poiché l’attenzione si è ora spostata su qualcos’altro, quindi non ho avuto il tempo di tornare su questo.
Sto davvero faticando a trasformare questo in modo che non sia basato sulla data, ma su mese e anno.
Ho fatto diverse cose per cercare di farlo funzionare, ma continua a dirmi che la colonna non esiste (quando esiste, dato che l’ho appena creata all’interno di un’istruzione with e poi ci faccio riferimento successivamente).
Come si potrebbe modificare questo codice in modo che, invece di analizzare gli argomenti senza risposta giorno per giorno, si possa visualizzare anno per anno, mese per mese, ecc.?
Per modificare la query in modo che possa aggregare gli argomenti senza risposte per anno, mese o altri intervalli di tempo, potresti aggiungere un parametro per specificare l’intervallo desiderato alla funzione date_trunc per ottenerlo.
Ad esempio:
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01
-- null int :category_id
-- boolean :include_subcategories = false
-- text :interval = day -- Opzioni: day, week, month, year
WITH no_response_total AS (
SELECT *
FROM (
SELECT t.id, t.created_at, MIN(p.post_number) first_reply
FROM topics t
LEFT JOIN posts p ON p.topic_id = t.id AND p.user_id != t.user_id AND p.deleted_at IS NULL AND p.post_type = 1
WHERE t.archetype <> 'private_message'
AND t.deleted_at ISNULL
AND (
:category_id IS NULL
OR t.category_id = :category_id
OR (
:include_subcategories
AND t.category_id IN (
SELECT id FROM categories WHERE parent_category_id = :category_id
)
)
)
GROUP BY t.id
) tt
WHERE tt.first_reply IS NULL OR tt.first_reply < 2
)
SELECT
date_trunc(:interval, nrt.created_at)::date AS period,
COUNT(nrt.id) AS topics_without_response
FROM no_response_total nrt
WHERE nrt.created_at::date BETWEEN :start_date AND :end_date
GROUP BY period
ORDER BY period ASC
Se volessi rimuovere i parametri, potresti in alternativa usare una query come questa:
WITH no_response_total AS (
SELECT *
FROM (
SELECT t.id, t.created_at, MIN(p.post_number) first_reply
FROM topics t
LEFT JOIN posts p ON p.topic_id = t.id AND p.user_id != t.user_id AND p.deleted_at IS NULL AND p.post_type = 1
WHERE t.archetype <> 'private_message'
AND t.deleted_at ISNULL
GROUP BY t.id
) tt
WHERE tt.first_reply IS NULL OR tt.first_reply < 2
)
SELECT
date_trunc('year', nrt.created_at)::date AS period,
COUNT(nrt.id) AS topics_without_response
FROM no_response_total nrt
WHERE nrt.created_at::date BETWEEN '2024-01-01' AND '2025-01-01'
GROUP BY period
ORDER BY period ASC
Grazie, questo ha risolto il problema di come ottenere l’anno.
Ora sono di nuovo bloccato poiché le date in postgre sembrano comportarsi diversamente
to_char(t.created_at, ‘MM-YY’) as Yearmonth,
Questo mi dà 10-22 che rappresenta “Ott-22”
Come posso cambiare 10-22 in Ott-22? Ho provato a cercare indicazioni all’interno di discourse ma non sono riuscito a trovarle, a meno che non sia sicuro di dove cercare?
Per cambiare il formato della data da 10-22 a Oct-22 in PostgreSQL, puoi usare la funzione TO_CHAR. Questa funzione ti permette di formattare le date in vari modi, ad esempio:
SELECT
TO_CHAR(TO_DATE('10-22', 'MM-YY'), 'Mon-YY') AS formatted_date
In questa istruzione SQL:
TO_DATE('10-22', 'MM-YY') converte la stringa 10-22 in un tipo data usando il formato MM-YY.
TO_CHAR(..., 'Mon-YY') formatta quindi questa data per visualizzare il nome abbreviato del mese seguito dall’anno, risultando in Oct-22.
Ecco un altro esempio basato sulla versione della query Topics with No Response con il parametro interval condiviso sopra:
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01
-- null int :category_id
-- boolean :include_subcategories = false
-- text :interval = month
WITH no_response_total AS (
SELECT *
FROM (
SELECT t.id, t.created_at, MIN(p.post_number) first_reply
FROM topics t
LEFT JOIN posts p ON p.topic_id = t.id AND p.user_id != t.user_id AND p.deleted_at IS NULL AND p.post_type = 1
WHERE t.archetype <> 'private_message'
AND t.deleted_at ISNULL
AND (
:category_id IS NULL
OR t.category_id = :category_id
OR (
:include_subcategories
AND t.category_id IN (
SELECT id FROM categories WHERE parent_category_id = :category_id
)
)
)
GROUP BY t.id
) tt
WHERE tt.first_reply IS NULL OR tt.first_reply < 2
)
SELECT
TO_CHAR(date_trunc(:interval, nrt.created_at)::date, 'Mon-YY') AS period,
COUNT(nrt.id) AS topics_without_response
FROM no_response_total nrt
WHERE nrt.created_at::date BETWEEN :start_date AND :end_date
GROUP BY period, date_trunc(:interval, nrt.created_at)::date
ORDER BY date_trunc(:interval, nrt.created_at)::date ASC
Come riferimento, i risultati di questa query sarebbero simili a:
