Tout d’abord, bonne année !! 
J’aimerais connaître la requête SQL à utiliser pour obtenir le nombre moyen de réponses par sujet créées par les utilisateurs (hors administrateurs) par mois.
Sinon un moyen d’avoir un ratio du nombre total de messages publiés par les membres VS le nombre total de messages publiés par le staff par mois.
Merci !
Bonne année (un peu en retard 
)
Pour la moyenne, cherchez-vous les sujets créés par le personnel et les non-membres du personnel, mais le nombre de réponses uniquement à partir des messages des non-membres du personnel ? Et cherchez-vous à exclure uniquement les administrateurs, ou les administrateurs et les modérateurs ?
Salut Jammy !
Exactement, la requête peut afficher tous les sujets (créés par le personnel et les non-membres), mais le nombre de réponses uniquement à partir des publications des non-membres.
Pour l’instant, nous ne pouvons exclure que les administrateurs (car lorsque je lance ma communauté, les administrateurs et les modérateurs sont les mêmes :))
Mais ce serait aussi bien d’avoir facilement le ratio entre les sujets du personnel et les sujets des non-membres (en excluant les administrateurs).
Je pense que quelque chose comme ceci vous donnerait les chiffres que vous recherchez :
-- [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 "Mois",
ms.total_topics AS "Tous les sujets",
ms.total_posts AS "Tous les messages",
ms.non_staff_posts AS "Messages non-staff",
ROUND(ms.non_staff_posts * 100.0 / NULLIF(ms.total_posts, 0),1) || '%' AS "Messages non-staff (% du total)",
ms.non_staff_posts / NULLIF(ms.total_topics, 0) AS "Messages non-staff moyens par sujet",
ms.non_staff_users AS "Utilisateurs non-staff ayant posté",
ms.staff_posts AS "Messages staff",
ROUND(ms.staff_posts * 100.0 / NULLIF(ms.total_posts, 0),1) || '%' AS "Messages staff (% du total)",
ms.staff_posts / NULLIF(ms.total_topics, 0) AS "Messages staff moyens par sujet",
ms.staff_users AS "Utilisateurs staff ayant posté"
FROM month_stats ms
ORDER BY "Mois"
Ce qui vous donnerait quelque chose comme ceci :
Et un petit texte en prime :
Cette requête est conçue pour fournir un résumé statistique mensuel de l’activité du forum, en se concentrant spécifiquement sur la distinction entre les contributions des utilisateurs « staff » et « non-staff » dans un délai donné. Les métriques calculées comprennent le nombre total de sujets créés, tous les messages postés, le nombre d’utilisateurs uniques non-staff ayant posté, le nombre et le pourcentage de messages postés par des non-staff, le nombre moyen de messages non-staff par sujet, ainsi que les chiffres correspondants pour les membres du staff. Les informations visent à donner un aperçu de l’engagement des utilisateurs, de la génération de contenu et du taux de participation du staff par rapport aux membres non-staff dans les discussions du forum. La requête garantit l’exactitude en ne considérant que les sujets « réguliers » (non-PM) et en excluant les messages ou sujets supprimés, les chuchotements/petits messages/actions de modération, et les messages des utilisateurs système, dans la plage de dates spécifiée.
Pour celui-ci, le critère « staff » est qu’ils font partie du groupe automatique @staff, qui comprend à la fois les administrateurs et les modérateurs - bien que cela puisse être ajusté pour cibler uniquement les administrateurs, ou même un groupe personnalisé d’employés qui ne sont techniquement pas du « staff du site » en tant que tel. Si vous souhaitez définitivement exclure les modérateurs, vous pouvez remplacer cet group_id au début par ‘1’. 
Est-ce le genre de chose que vous recherchez ?
Pour une raison étrange, je sais que l’identifiant du personnel est trois. Mais comment pourrait-on trouver cet identifiant ? Au début, j’étais totalement sûr qu’il était intégré dans les URL, comme tous les autres identifiants, mais non. Seul le nom est utilisé.
Je connais si peu de SQL que l’on peut facilement dire que je ne peux pas, mais cela montre tous les identifiants de groupe
select
id,
name
from
groups
Mais il y a sûrement une façon plus courante de le trouver, n’est-ce pas ?
Personnellement, j’aimerais vraiment avoir une recherche de paramètre group_id tout comme celle de user_id 
- Param dropdown for group_id in data explorer query
Mais en attendant que ce rêve se réalise, j’utilise le JSON de la page des groupes pour le trouver, par exemple : https://meta.discourse.org/g.json
Vous pouvez faire une recherche de groupe dans la requête elle-même afin qu’elle fonctionne avec les noms de groupe, ce qui pourrait être une manière plus conviviale de le faire. Donc quelque chose comme :
-- [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))
(ou la version codée en dur si vous ne vouliez pas de paramètre
SELECT user_id, true as is_staff
FROM group_users
WHERE group_id = (SELECT id FROM groups WHERE name = 'admins')
Merci beaucoup ! J’ai peut-être manqué quelque chose, mais lorsque je clique sur « Exécuter », j’obtiens cette erreur :
Ah oui, c’est une particularité que j’aurais dû mentionner. Si vous actualisez votre page, les champs de saisie des paramètres devraient apparaître.
Je parie que vous avez créé cet extrait à la volée. Parce qu’il ne fonctionne pas très bien 
Il affiche chaque utilisateur du groupe souhaité et prétend que tout le monde est is_staff 
Mais merci ! J’ai obtenu des informations précieuses pour les administrateurs de niveau de base, sur JSON et sur la façon d’utiliser SQL (vraiment, mais j’aime toujours voir comment le rapport IA le perçoit…)
Dans cet exemple, le bit is_staff fait partie de la fonction de cette requête particulière. Il est spécifiquement ajouté ici SELECT user_id, true as is_staff plutôt que de provenir de la base de données elle-même. Il définit toute personne du groupe que vous désignez comme « personnel » afin qu’elle puisse être divisée en deux ensembles de résultats (publications du personnel par rapport aux publications non-personnel).
Donc, si vous aviez un groupe pour les « employés », qui ne seraient techniquement pas du personnel du site tel que défini par la base de données, vous pourriez toujours les ajouter et ils iraient dans le compartiment « personnel » et non dans le compartiment « non-personnel ».
OMG, c’est exactement ce dont j’avais besoin, merci beaucoup !
Pour être sûr : « Posts » compile les sujets + les réponses, ou ne compte-t-il que les réponses ?
Merci encore !
Pour celui-ci, « posts » n’inclut pas le premier message du sujet, il ne s’agit donc que des réponses.
Salut @JammyDodger
Penses-tu qu’il soit possible d’avoir la même chose mais uniquement pour les sujets (= nouveau fil créé) s’il te plaît ?
Merci beaucoup !
Voulez-vous dire un rapport entre les sujets créés par le personnel et ceux ajoutés par des non-membres à cette requête ?
Oh oui, dans la même requête, ce serait génial !
Je pense que l’ajout de ces colonnes devrait suffire :
-- [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 "Mois",
ms.total_topics AS "Tous les sujets",
ms.non_staff_topics AS "Sujets non-staff",
ROUND(ms.non_staff_topics * 100.0 / NULLIF(ms.total_topics, 0),1) || '%' AS "Sujets non-staff (% du total)",
ms.staff_topics AS "Sujets staff",
ROUND(ms.staff_topics * 100.0 / NULLIF(ms.total_topics, 0),1) || '%' AS "Sujets staff (% du total)",
ms.total_posts AS "Tous les messages",
ms.non_staff_posts AS "Messages non-staff",
ROUND(ms.non_staff_posts * 100.0 / NULLIF(ms.total_posts, 0),1) || '%' AS "Messages non-staff (% du total)",
ms.non_staff_posts / NULLIF(ms.total_topics, 0) AS "Messages non-staff moyens par sujet",
ms.non_staff_users AS "Utilisateurs non-staff ayant posté",
ms.staff_posts AS "Messages staff",
ROUND(ms.staff_posts * 100.0 / NULLIF(ms.total_posts, 0),1) || '%' AS "Messages staff (% du total)",
ms.staff_posts / NULLIF(ms.total_topics, 0) AS "Messages staff moyens par sujet",
ms.staff_users AS "Utilisateurs staff ayant posté"
FROM month_stats ms
ORDER BY "Mois"
Merci beaucoup, c’est parfait !
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.
