Ceci est une version SQL du rapport de tableau de bord pour les réactions.
Ce rapport fournit un décompte quotidien de tous les likes et réactions aux publications sur un site sur une période spécifiée.
Ce rapport aide à fournir un aperçu de l’engagement des utilisateurs au sein d’une communauté Discourse en mesurant la fréquence des différents emojis de réaction et des likes sur les publications. En analysant l’utilisation des différents emojis de réaction, les administrateurs peuvent obtenir des informations sur la fréquence à laquelle les utilisateurs interagissent avec le contenu, les réponses émotionnelles des utilisateurs aux publications, et identifier les emojis de réaction populaires et sous-utilisés.
Ce rapport nécessite que le plugin Discourse Reactions soit activé sur votre site. Les réactions disponibles dans le rapport dépendront des réactions spécifiques qui ont été activées avec le paramètre de site
discourse_reactions_enabled_reactions.
-- [params]
-- date :start_date = 2023-12-16
-- date :end_date = 2024-01-17
SELECT
r.day,
COALESCE(l.likes_count, 0) as likes_count,
sum(case when reaction_value = 'laughing' then reactions_count else 0 end)::int as laughing,
sum(case when reaction_value = 'cry' then reactions_count else 0 end)::int as cry,
sum(case when reaction_value = 'exploding_head' then reactions_count else 0 end)::int as exploding_head,
sum(case when reaction_value = 'clap' then reactions_count else 0 end)::int as clap,
sum(case when reaction_value = 'confetti_ball' then reactions_count else 0 end)::int as confetti_ball,
sum(case when reaction_value = 'hugs' then reactions_count else 0 end)::int as hugs,
sum(case when reaction_value = 'chefs_kiss' then reactions_count else 0 end)::int as chefs_kiss,
sum(case when reaction_value = '100' then reactions_count else 0 end)::int as one_hundred,
sum(case when reaction_value = '+1' then reactions_count else 0 end)::int as plus_one,
sum(case when reaction_value = 'rocket' then reactions_count else 0 end)::int as rocket,
sum(case when reaction_value = 'star_struck' then reactions_count else 0 end)::int as star_struck,
sum(case when reaction_value = 'eyes' then reactions_count else 0 end)::int as eyes,
sum(case when reaction_value = 'discourse' then reactions_count else 0 end)::int as discourse
FROM (
SELECT
date_trunc('day', drru.created_at)::date as day,
drr.reaction_value,
count(drru.id) as reactions_count
FROM discourse_reactions_reactions as drr
LEFT OUTER JOIN discourse_reactions_reaction_users as drru on drr.id = drru.reaction_id
WHERE drr.reaction_users_count IS NOT NULL
AND drru.created_at::date >= :start_date::date AND drru.created_at::date <= :end_date::date
GROUP BY drr.reaction_value, day
) r
LEFT JOIN (
SELECT
count(pa.id) as likes_count,
date_trunc('day', pa.created_at)::date as day
FROM post_actions as pa
WHERE pa.post_action_type_id = 2
AND pa.created_at::date >= :start_date::date AND pa.created_at::date <= :end_date::date
GROUP BY day
) l ON r.day = l.day
GROUP BY r.day, l.likes_count
ORDER BY r.day
Explication de la requête SQL
Paramètres
- La requête accepte deux paramètres,
:start_dateet:end_date, qui définissent la plage de dates pour le rapport. Les deux paramètres de date acceptent le format de dateAAAA-MM-JJ.
Structure de la requête
- Requête interne (Réactions) : La requête commence par une sous-requête qui sélectionne la date (
day), le type de réaction (reaction_value) et le nombre de réactions (reactions_count) à partir de la tablediscourse_reactions_reactionsjointe avecdiscourse_reactions_reaction_userssur lereaction_id. Cette jointure garantit que nous prenons en compte les réactions spécifiques à l’utilisateur. - Agrégation par types de réactions : Les données sélectionnées sont ensuite regroupées par
dayetreaction_value, en limitant la plage aux dates de début et de fin sélectionnées. Ceci est utilisé pour calculer le nombre total de chaque type de réaction pour chaque jour dans la plage de dates spécifiée. - Tri des totaux de réactions : Pour chaque type de réaction, la requête utilise une instruction
CASEpour agréger le nombre de fois où la réaction spécifique est utilisée et convertit le résultat en entier pour un décompte précis.- Vous devrez peut-être ajuster
reaction_value = '...'dans cette section en fonction des réactions activées sur votre site.
- Vous devrez peut-être ajuster
- Sous-requête pour le nombre de likes : Une sous-requête distincte est utilisée pour calculer le nombre total de likes (
likes_count) pour chaque jour en utilisant la tablepost_actionsoùpost_action_type_idcorrespond aux likes. - Combinaison des données : La requête externe combine ensuite le décompte des likes avec les décomptes de réactions en joignant sur le champ
day. - Sélection finale : La requête
SELECTla plus externe génère la sortie finale avec leday, le nombre de likes (likes_count) et les décomptes de chaque type de réaction. S’il n’y a pas de données pour les likes un jour donné, la fonctionCOALESCEgarantit qu’un zéro est affiché au lieu deNULL. - Tri des résultats : Les résultats sont triés par date (
r.day) pour créer une série chronologique de l’engagement sur le site.
Colonnes
day: La date à laquelle les réactions et les likes ont été comptés.likes_count: Nombre total de likes pour chaque jour.- Chaque type de réaction (rire, pleurer, tête qui explose, etc.) : Colonnes individuelles montrant le nombre total de chaque type de réaction par jour.
Résultats d’exemple
| jour | likes_count | laughing | cry | exploding_head | clap | confetti_ball | hugs | chefs_kiss | one_hundred | plus_one | rocket | star_struck | eyes | discourse |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2023-12-16 | 13 | 0 | 3 | 0 | 3 | 1 | 0 | 0 | 0 | 5 | 2 | 2 | 1 | 0 |
| 2023-12-17 | 17 | 1 | 0 | 2 | 2 | 0 | 0 | 0 | 0 | 4 | 4 | 1 | 2 | 0 |
| 2023-12-18 | 46 | 0 | 1 | 0 | 6 | 0 | 1 | 3 | 0 | 27 | 3 | 4 | 5 | 0 |
à 
par exemple.