Este es una versión SQL del Informe del Panel de Reacciones.
Este informe proporciona un recuento diario de todos los “me gusta” y reacciones a las publicaciones en un sitio durante un período de fechas especificado.
Este informe ayuda a proporcionar una instantánea de la participación del usuario dentro de una comunidad de Discourse al medir la frecuencia de varios emojis de reacción y “me gusta” en las publicaciones. Al analizar el uso de diferentes emojis de reacción, los administradores pueden obtener información sobre la frecuencia con la que los usuarios interactúan con el contenido, las respuestas emocionales de los usuarios a las publicaciones e identificar tanto los emojis de reacción populares como los infrautilizados.
Este informe requiere que el plugin Discourse Reactions esté habilitado en su sitio. Las reacciones disponibles en el informe dependerán de qué reacciones específicas se hayan habilitado con la configuración del sitio
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
Explicación de la consulta SQL
Parámetros
- La consulta acepta dos parámetros,
:start_datey:end_date, que definen el rango de fechas para el informe. Ambos parámetros de fecha aceptan el formato de fechaYYYY-MM-DD.
Estructura de la consulta
- Consulta interna (Reacciones): La consulta comienza con una subconsulta que selecciona la fecha (
day), el tipo de reacción (reaction_value) y el recuento de reacciones (reactions_count) de la tabladiscourse_reactions_reactionsunida condiscourse_reactions_reaction_usersen elreaction_id. Esta unión garantiza que estamos teniendo en cuenta las reacciones específicas del usuario. - Agregación por tipos de reacción: Los datos seleccionados se agrupan por
dayyreaction_value, restringiendo el rango a las fechas de inicio y fin seleccionadas. Esto se utiliza para calcular el recuento total de cada tipo de reacción para cada día en el rango de fechas especificado. - Clasificación de totales de reacciones: Para cada tipo de reacción, la consulta utiliza una instrucción
CASEpara agregar el número de veces que se utiliza la reacción específica y convierte el resultado en un entero para un recuento limpio.- Es posible que deba ajustar el
reaction_value = '...'en esta sección según las reacciones que estén habilitadas en su sitio.
- Es posible que deba ajustar el
- Subconsulta de recuento de “me gusta”: Se utiliza una subconsulta separada para calcular el número total de “me gusta” (
likes_count) para cada día utilizando la tablapost_actionsdondepost_action_type_idcorresponde a los “me gusta”. - Combinación de datos: La consulta externa combina el recuento de “me gusta” con los recuentos de reacciones uniéndose en el campo
day. - Selección final: La
SELECTmás externa genera la salida final con elday, el número de “me gusta” (likes_count) y los recuentos de cada tipo de reacción. Si no hay datos de “me gusta” en un día específico, la funciónCOALESCEasegura que se muestre un cero en lugar deNULL. - Ordenación de los resultados: Los resultados se ordenan por fecha (
r.day) para crear una serie temporal de participación en el sitio.
Columnas
day: La fecha en la que se contaron las reacciones y los “me gusta”.likes_count: Número total de “me gusta” para cada día.- Cada tipo de reacción (laughing, cry, exploding_head, etc.): Columnas individuales que muestran el recuento total de cada tipo de reacción por día.
Resultados de ejemplo
| day | 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 |
a 
.