Je voudrais créer un rapport basé sur SQL qui permettrait de comprendre de quoi nos utilisateurs publient.\n\nCatégories, sujets, tags - ce sont les champs que j’ai déterminés qui me fourniront cela, et le nombre de publications serait la métrique (je pense, voulant essentiellement l’utiliser pour comprendre la popularité).\n\nExiste-t-il d’autres champs qui pourraient soutenir cela ? Existe-t-il une requête existante que je peux utiliser car je suppose que d’autres ont demandé cela ?
pas une requête SQL, mais supposez que vous avez remarqué que :
/categories vous donne un certain nombre de nouveaux sujets/mois pour chaque catégorie, ce qui peut être très utile.
/tags vous donne un décompte de sujets par tag
Au cas où vous ne le sauriez pas (mais vous l’auriez peut-être su !)
1 « J'aime »
Je ne suis pas sûr de ce à quoi vous faites référence ici.
S’agit-il des rapports sur la partie administration d’une communauté ?
J’ai vu cela mais je veux essentiellement tout avoir en un seul endroit - pour que vous puissiez voir combien de sujets ou d’étiquettes une catégorie a, etc.
ce sont des chemins d’URL.
Je ne suis pas sûr de pouvoir bien visualiser ce que vous avez en tête pour inclure les catégories, les étiquettes et les sujets, avec un nombre de publications pour chacun, dans le même rapport/requête.
Je pense que vous pourriez probablement le faire en deux parties : une pour les catégories et une pour les étiquettes, avec un décompte des nouveaux sujets et des nouvelles publications dans un certain délai pour chacune. Peut-être même inclure le nombre d’utilisateurs qui publient ?
Cela donnerait donc quelque chose comme ceci comme tableau de résultats :
| catégorie | nouveaux sujets | nouvelles publications | utilisateurs |
|---|---|---|---|
| catégorie a | 9 | 15 | 4 |
| catégorie b | 56 | 167 | 32 |
2 « J'aime »
Ok, j’ai donc saisi Catégories - Discourse Meta comme je suppose que c’est ce que vous vouliez dire.
Essentiellement, je souhaite une sortie de catégories/tags/sujets dans une seule sortie pour les raisons suivantes :
- Identifier les doublons - un sujet, tel que je le comprends, est le mot utilisé pour le début d’une conversation/fil de discussion. Un utilisateur aurait pu ajouter un sujet similaire dans 2 catégories différentes, il serait bon de comprendre cela pour comprendre le comportement des utilisateurs/potentiellement ajuster les catégories pour les rendre plus claires.
- Comprendre quels types de sujets nous obtenons par catégorie - une catégorie pourrait être quelque chose comme « voitures », mais naturellement, cela pourrait abriter de nombreux types de sujets différents, voulant voir de quoi parlent réellement les utilisateurs.
- Tags - dans l’instance que nous avons, je peux voir que les tags ont été utilisés comme une sorte de chevauchement entre différentes catégories, il serait donc bon de comprendre les publications sous cet angle également.
Essentiellement, d’après ce que j’ai pu comprendre, j’ai besoin de joindre les catégories, les tags et les sujets en utilisant SQL et potentiellement les posts, et je me demandais si cela avait déjà été fait pour visualiser le code/s’il existe une sorte de bibliothèque de code/requête ?
|catégorie|sujet|étiquette|messages|utilisateurs|
| — | — | — | — |—|\n|voitures|j’aime les voitures|roues|44|1\n|voitures|je n’aime pas les voitures|roues|32|3\n|voitures|je n’aime pas les voitures|portes|39|4\n|voitures|comment fonctionnent les voitures ?|portes|32|1\n|comment fonctionnent les choses|comment fonctionnent les voitures ?|comment faire|32|3\n\nCeci est un exemple de la vision. Cela permettrait une analyse plus approfondie pour comprendre des choses telles que « quel pourcentage des utilisateurs qui publient dans la catégorie voitures parlent de roues »\n\nJe comprends que ce serait dans l’explorateur de données combinant les messages, les sujets, les étiquettes, les catégories, éventuellement une autre table - j’ai juste posté ici pour comprendre si cela avait déjà été fait/où existent des requêtes sql qui ont été créées précédemment (pas sur notre compte, je veux dire en général)\n\nJ’espère que cela a du sens, je n’ai commencé à enquêter qu’hier
Ah, je vois maintenant. Vous souhaitez une liste de sujets avec des détails supplémentaires plutôt qu’un aperçu général. Je pense que cela devrait être possible. 
Je pense que la seule particularité serait qu’un sujet peut avoir plusieurs étiquettes, mais créons quelque chose et voyons où nous en sommes. 
Mais pour parcourir d’autres requêtes existantes, il y a celles disponibles en standard sur votre tableau de bord (vous pouvez voir les versions SQL ici regroupées sous dashboard-sql), ainsi que quelques rapports standards regroupés avec l’explorateur de données, et il y a une multitude de rapports personnalisés ici sur meta regroupés sous le tag sql-query.
@SStrong - Peut-être quelque chose dans ce sens :
-- [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
2 « J'aime »
Mes excuses, je pensais avoir déjà répondu à cela.
Est-il possible de modifier le code pour qu’il contienne le nom de la catégorie et le nom du sujet, s’il vous plaît ? Je ne sais pas si « nom de publication » est une chose ou si c’est réellement le nom du sujet ?
J’ai essayé de modifier le code moi-même, mais je ne comprends pas encore assez comment les tables interagissent, car le résultat n’a affiché aucun résultat au lieu du même volume d’enregistrements, mais avec des mots et non des ID.
Ce n’est pas un problème. Lorsque visualisés dans l’explorateur de données, category_id et topic_id (et bien d’autres) sont automatiquement convertis en liens internes utilisables, mais si vous exportez pour analyser ailleurs, vous pouvez utiliser les noms de catégories et les titres de sujets à la place :
-- [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
1 « J'aime »