Innanzitutto, buon anno !! 
Vorrei sapere la query SQL da utilizzare per ottenere il numero medio di risposte per argomento fornite dagli utenti (esclusi gli amministratori) al mese.
In alternativa, un modo per avere un rapporto tra il numero totale di post pubblicati dai membri e il numero totale di post pubblicati dallo staff al mese.
Grazie!
Buon Anno (un po’ in ritardo 
)
Per la media, stai cercando argomenti creati sia dallo staff che da non staff, ma il conteggio delle risposte solo dai post non staff? E stai cercando di escludere solo gli amministratori, o amministratori e moderatori?
Ciao Jammy!
Esattamente, la query può visualizzare tutti gli argomenti (creati sia dallo staff che dai non staff), ma il conteggio delle risposte solo dai post dei non staff.
Per ora possiamo solo escludere gli amministratori (dato che quando avvio la mia community, amministratori e moderatori sono la stessa cosa :))
Ma sarebbe anche bello avere facilmente il rapporto tra argomenti dello staff e argomenti dei non staff (esclusi gli amministratori).
Penso che qualcosa del genere ti darebbe i numeri che stai cercando:
-- [params]
-- date :start_date
-- date :end_date
WITH staff_data AS (
SELECT user_id, true as is_staff
FROM group_users
WHERE group_id = 3
),
month_stats AS (
SELECT
date_trunc('month', p.created_at)::date AS month,
COUNT(*) FILTER (WHERE p.post_number = 1) AS total_topics,
COUNT(*) FILTER (WHERE p.post_number <> 1) AS total_posts,
COUNT(DISTINCT p.user_id) FILTER (WHERE p.post_number <> 1 AND is_staff IS NOT TRUE) AS non_staff_users,
COUNT(*) FILTER (WHERE p.post_number <> 1 AND is_staff IS NOT TRUE) AS non_staff_posts,
COUNT(DISTINCT p.user_id) FILTER (WHERE p.post_number <> 1 AND is_staff) AS staff_users,
COUNT(*) FILTER (WHERE p.post_number <> 1 AND is_staff) AS staff_posts
FROM posts p
LEFT JOIN topics t ON t.id = p.topic_id
LEFT JOIN staff_data s ON p.user_id = s.user_id
WHERE p.created_at::date BETWEEN :start_date AND :end_date
AND t.archetype = 'regular'
AND t.deleted_at IS NULL
AND p.deleted_at IS NULL
AND p.post_type = 1
AND p.user_id > 0
GROUP BY month
)
SELECT
ms.month AS "Mese",
ms.total_topics AS "Tutti gli argomenti",
ms.total_posts AS "Tutti i post",
ms.non_staff_posts AS "Post non staff",
ROUND(ms.non_staff_posts * 100.0 / NULLIF(ms.total_posts, 0),1) || '%' AS "Post non staff (% del totale)",
ms.non_staff_posts / NULLIF(ms.total_topics, 0) AS "Post medi non staff per argomento",
ms.non_staff_users AS "Utenti non staff che hanno postato",
ms.staff_posts AS "Post staff",
ROUND(ms.staff_posts * 100.0 / NULLIF(ms.total_posts, 0),1) || '%' AS "Post staff (% del totale)",
ms.staff_posts / NULLIF(ms.total_topics, 0) AS "Post medi staff per argomento",
ms.staff_users AS "Utenti staff che hanno postato"
FROM month_stats ms
ORDER BY "Mese"
Il che ti darebbe qualcosa di simile:
E un po’ di testo aggiuntivo per buona misura:
Questa query è progettata per fornire un riepilogo statistico mensile dell’attività del forum, concentrandosi specificamente sulla distinzione tra i contributi degli utenti “staff” e “non staff” in un dato periodo di tempo. Le metriche calcolate includono il numero totale di argomenti creati, tutti i post effettuati, il numero di utenti non staff unici che hanno postato, il conteggio e la percentuale di post effettuati da non staff, il numero medio di post non staff per argomento, nonché le cifre corrispondenti per i membri dello staff. Le informazioni sono destinate a fornire approfondimenti sull’engagement degli utenti, sulla generazione di contenuti e sul tasso di partecipazione dei membri staff rispetto ai non staff nelle discussioni del forum. La query garantisce l’accuratezza considerando solo argomenti “regolari” (non PM) ed escludendo post o argomenti cancellati, sussurri/piccoli post/azioni del moderatore e post di utenti di sistema, nell’intervallo di date specificato.
Per questo, il criterio “staff” è che facciano parte del gruppo automatico @staff, che include sia amministratori che moderatori - anche se questo può essere modificato per puntare solo agli amministratori, o anche a un gruppo personalizzato di dipendenti che non sono tecnicamente “staff del sito” in sé. Se vuoi escludere definitivamente i moderatori, puoi sostituire quel group_id all’inizio con ‘1’. 
È questo il tipo di cosa che stai cercando?
Per qualche strana ragione so che l’ID dello staff è tre. Ma come si potrebbe trovare quell’ID? All’inizio ero totalmente sicuro che fosse integrato negli URL, come tutti gli altri ID, ma no. Viene utilizzato solo il nome.
So così poco SQL che si può facilmente dire che non posso, ma questo mostra ogni ID di gruppo
select
id,
name
from
groups
Ma sicuramente c’è un modo più comune per trovarlo, non è vero?
Personalmente, vorrei davvero un lookup del parametro group_id proprio come quello per user_id 
- Param dropdown for group_id in data explorer query
Ma finché quel sogno non si avvererà, uso il json della pagina dei gruppi per scoprirlo, ad esempio: https://meta.discourse.org/g.json
Puoi fare un lookup del gruppo all’interno della query stessa, in modo che funzioni con i nomi dei gruppi, il che potrebbe essere un modo più user-friendly per farlo. Quindi qualcosa del tipo:
-- [params]
-- string :group_name
SELECT user_id, true as is_staff
FROM group_users
WHERE group_id = (SELECT id FROM groups WHERE name = LOWER(:group_name))
(o la versione hard-coded se non volessi un parametro:)
SELECT user_id, true as is_staff
FROM group_users
WHERE group_id = (SELECT id FROM groups WHERE name = 'admins')
Grazie mille! Forse mi è sfuggito qualcosa, ma quando clicco su “Execute”, ricevo questo errore:
Ah sì, questa è una stranezza che avrei dovuto menzionare. Se aggiorni la pagina, dovrebbero apparire le caselle di immissione dei parametri.
Scommetto che hai creato quella snippet al volo. Perché non funziona del tutto bene ;)\n\nMostra ogni utente dal gruppo desiderato e dichiara che tutti sono is_staff 
\n\nMa grazie! Ho ottenuto informazioni preziose per gli amministratori di base, su json e su come usare SQL (davvero, ma mi piace comunque vedere come lo vede il report AI…)\n
In quell’esempio, il bit is_staff fa parte della funzione di questa particolare query. Viene aggiunto specificamente qui SELECT user_id, true as is_staff anziché provenire dal database stesso. Imposta chiunque dal gruppo designato come ‘staff’ in modo che possa essere diviso nei due set di risultati (post dello staff rispetto a post non dello staff).
Quindi, se avessi un gruppo per ‘dipendenti’, che tecnicamente non sarebbero staff del sito come impostati dal database, potresti comunque aggiungerli e andrebbero nel bucket ‘staff’ e non nel bucket ‘non-staff’.
OMG, questo è assolutamente ciò di cui avevo bisogno, grazie mille!
Per essere sicuri: “Post” compila argomenti + risposte, o conta solo le risposte?
Grazie ancora!
Per questo, ‘posts’ non include il primo post dell’argomento, quindi include solo le risposte.
Ciao @JammyDodger
Pensi sia possibile avere la stessa cosa ma solo per gli argomenti (=nuova discussione creata) per favore?
Grazie mille!
Intendi un rapporto tra argomenti creati dal personale rispetto a quelli non di personale aggiunti a questa query?
Oh sì, nella stessa query sarebbe fantastico!
Penso che l’aggiunta di queste colonne dovrebbe funzionare:
-- [params]
-- date :start_date
-- date :end_date
WITH staff_data AS (
SELECT user_id, true as is_staff
FROM group_users
WHERE group_id = 3
),
month_stats AS (
SELECT
date_trunc('month', p.created_at)::date AS month,
COUNT(*) FILTER (WHERE p.post_number = 1) AS total_topics,
COUNT(*) FILTER (WHERE p.post_number = 1 AND is_staff IS NOT TRUE) AS non_staff_topics,
COUNT(*) FILTER (WHERE p.post_number = 1 AND is_staff IS TRUE) AS staff_topics,
COUNT(*) FILTER (WHERE p.post_number <> 1) AS total_posts,
COUNT(DISTINCT p.user_id) FILTER (WHERE p.post_number <> 1 AND is_staff IS NOT TRUE) AS non_staff_users,
COUNT(*) FILTER (WHERE p.post_number <> 1 AND is_staff IS NOT TRUE) AS non_staff_posts,
COUNT(DISTINCT p.user_id) FILTER (WHERE p.post_number <> 1 AND is_staff) AS staff_users,
COUNT(*) FILTER (WHERE p.post_number <> 1 AND is_staff) AS staff_posts
FROM posts p
LEFT JOIN topics t ON t.id = p.topic_id
LEFT JOIN staff_data s ON p.user_id = s.user_id
WHERE p.created_at::date BETWEEN :start_date AND :end_date
AND t.archetype = 'regular'
AND t.deleted_at IS NULL
AND p.deleted_at IS NULL
AND p.post_type = 1
AND p.user_id > 0
GROUP BY month
)
SELECT
ms.month AS "Mese",
ms.total_topics AS "Tutti gli argomenti",
ms.non_staff_topics AS "Argomenti non staff",
ROUND(ms.non_staff_topics * 100.0 / NULLIF(ms.total_topics, 0),1) || '%' AS "Argomenti non staff (% del totale)",
ms.staff_topics AS "Argomenti staff",
ROUND(ms.staff_topics * 100.0 / NULLIF(ms.total_topics, 0),1) || '%' AS "Argomenti staff (% del totale)",
ms.total_posts AS "Tutti i post",
ms.non_staff_posts AS "Post non staff",
ROUND(ms.non_staff_posts * 100.0 / NULLIF(ms.total_posts, 0),1) || '%' AS "Post non staff (% del totale)",
ms.non_staff_posts / NULLIF(ms.total_topics, 0) AS "Post medi non staff per argomento",
ms.non_staff_users AS "Utenti non staff che hanno postato",
ms.staff_posts AS "Post staff",
ROUND(ms.staff_posts * 100.0 / NULLIF(ms.total_posts, 0),1) || '%' AS "Post staff (% del totale)",
ms.staff_posts / NULLIF(ms.total_topics, 0) AS "Post medi staff per argomento",
ms.staff_users AS "Utenti staff che hanno postato"
FROM month_stats ms
ORDER BY "Mese"
Grazie mille, è perfetto!
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.
