Vorrei creare un report basato su SQL che possa fornire una comprensione di ciò di cui parlano i nostri utenti.
Categorie, argomenti, tag: questi sono i campi che ho accertato che mi forniranno questo, e postcount sarebbe la metrica (penso, essenzialmente volendo usarlo per capire la popolarità)
Ci sono altri campi che potrebbero supportare questo? Esiste una query esistente che posso utilizzare, dato che presumo che altri abbiano richiesto questo?
non è una query SQL, ma presumo tu abbia notato che:
/categories ti fornisce un numero di nuovi argomenti/mese per ciascuna categoria, il che può essere molto utile.
/tags ti fornisce un conteggio di argomenti per tag.
Nel caso non lo sapessi (ma potresti averlo saputo!)
Non sono sicuro a cosa ti riferisci qui.
Questi sono i report nella parte amministrativa di una community?
Ho visto questo ma voglio essenzialmente averli tutti in uno, in modo da poter vedere quanti argomenti o tag ha una categoria, ecc.
sono percorsi URL.
Non sono sicuro di riuscire a immaginare cosa hai in mente per includere categorie, tag e argomenti, con un conteggio di post per ciascuno, nello stesso report/query.
Penso che potresti farlo in due, uno per le categorie e uno per i tag, con un conteggio di nuovi argomenti e nuovi post entro un certo periodo di tempo per ciascuno. Forse includere anche il numero di utenti che pubblicano?
Quindi darebbe qualcosa di simile come tabella dei risultati:
| categoria | nuovi argomenti | nuovi post | utenti |
|---|---|---|---|
| categoria a | 9 | 15 | 4 |
| categoria b | 56 | 167 | 32 |
Ok, quindi ho inserito Categories - Discourse Meta come presumo che intendessi.
In sostanza, voglio un output di categorie/tag/argomenti in un unico output per questi motivi:
| categoria | argomento | etichetta | post | utenti |
|---|---|---|---|---|
| auto | amore per le auto | ruote | 44 | 1 |
| auto | odio per le auto | ruote | 32 | 3 |
| auto | odio per le auto | porte | 39 | 4 |
| auto | come funzionano le auto? | porte | 32 | 1 |
| come funzionano le cose | come funzionano le auto? | guide | 32 | 3 |
Questo è un esempio della visione. Ciò consentirebbe ulteriori analisi per comprendere cose come “quale % di utenti che pubblicano nella categoria auto parla di ruote”
Capisco che sarebbe nell’esploratore dati combinando post, argomenti, etichette, categorie, possibilmente un’altra tabella - ho appena pubblicato qui per capire se questo era già stato fatto/dove esistono query sql che sono state create in precedenza (non sul nostro account, intendo in generale)
Spero abbia senso, ho iniziato a indagare ieri
Capisco, ora. Desideri un elenco di argomenti con dettagli aggiuntivi anziché una panoramica generale. Penso che sia possibile. 
Penso che l’unica particolarità sarebbe che un argomento può avere più tag, ma prepariamo qualcosa e vediamo dove arriviamo. 
Ma per la navigazione di altre query esistenti, ci sono quelle disponibili standard dalla tua dashboard (puoi vedere le versioni SQL qui raggruppate sotto dashboard-sql), così come alcuni report standard inclusi con l’esploratore di dati, e c’è un’intera serie di quelle personalizzate qui su meta raggruppate sotto il tag sql-query.
@SStrong - Forse qualcosa del genere:
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-12-31
WITH tag_names AS (
SELECT
t.id AS topic_id,
string_agg(tags.name, ', ' ORDER BY tags.name) AS "tags"
FROM topics t
JOIN topic_tags tt ON tt.topic_id = t.id
JOIN tags ON tags.id = tt.tag_id
WHERE t.created_at BETWEEN :start_date AND :end_date
GROUP BY t.id
),
user_count AS (
SELECT
p.topic_id,
COUNT(DISTINCT p.user_id) AS users
FROM posts p
JOIN topics t ON t.id = p.topic_id
WHERE t.created_at BETWEEN :start_date AND :end_date
AND t.deleted_at IS NULL
AND p.deleted_at IS NULL
AND t.archetype = 'regular'
AND p.post_type = 1
AND p.user_id > 0
GROUP BY p.topic_id
)
SELECT
t.category_id,
t.id AS topic_id,
tn.tags,
t.posts_count,
uc.users
FROM topics t
JOIN tag_names tn ON tn.topic_id = t.id
JOIN user_count uc ON uc.topic_id = t.id
WHERE t.created_at BETWEEN :start_date AND :end_date
AND t.archetype = 'regular'
AND t.deleted_at IS NULL
ORDER BY t.category_id, t.title
Mi scuso, pensavo di aver già risposto a questo.
È possibile modificare il codice per contenere il nome della categoria e il nome dell’argomento? Non so se “nome del post” sia una cosa o se sia effettivamente il nome dell’argomento?
Ho tentato di modificare il codice da solo ma non capisco ancora abbastanza come si comportano le tabelle insieme, poiché è uscito con 0 risultati invece che solo lo stesso volume di record ma con parole e non ID.
Nessun problema. Se visualizzati nell’esploratore dati, category_id e topic_id (e molti altri) vengono automaticamente convertiti in link utilizzabili sul sito, ma se si esporta per analizzare altrove è possibile utilizzare invece i nomi delle categorie e i titoli degli argomenti:
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-12-31
WITH tag_names AS (
SELECT
t.id AS topic_id,
string_agg(tags.name, ', ' ORDER BY tags.name) AS "tags"
FROM topics t
JOIN topic_tags tt ON tt.topic_id = t.id
JOIN tags ON tags.id = tt.tag_id
WHERE t.created_at BETWEEN :start_date AND :end_date
GROUP BY t.id
),
user_count AS (
SELECT
p.topic_id,
COUNT(DISTINCT p.user_id) AS users
FROM posts p
JOIN topics t ON t.id = p.topic_id
WHERE t.created_at BETWEEN :start_date AND :end_date
AND t.deleted_at IS NULL
AND p.deleted_at IS NULL
AND t.archetype = 'regular'
AND p.post_type = 1
AND p.user_id > 0
GROUP BY p.topic_id
)
SELECT
c.name AS category_name,
t.title,
tn.tags,
t.posts_count,
uc.users
FROM topics t
JOIN tag_names tn ON tn.topic_id = t.id
JOIN user_count uc ON uc.topic_id = t.id
JOIN categories c ON c.id = t.category_id
WHERE t.created_at BETWEEN :start_date AND :end_date
AND t.archetype = 'regular'
AND t.deleted_at IS NULL
ORDER BY t.category_id, t.title