仪表盘报告 - 反应

数据与报告
, , ,
1

这是“反应”仪表板报告的 SQL 版本。

本报告提供指定日期范围内网站上所有帖子点赞和反应的每日计数。

通过衡量帖子中各种反应表情符号和点赞的使用频率，此报告有助于提供 Discourse 社区内用户参与度的快照。通过分析不同反应表情符号的使用情况，管理员可以深入了解用户与内容互动的频率、用户对帖子的情感反应，并识别受欢迎和未被充分利用的反应表情符号。

:information_source: 此报告要求在您的网站上启用 Discourse Reactions 插件。报告中可用的反应将取决于通过 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

SQL 查询说明

参数

  • 该查询接受两个参数 :start_date 和 :end_date，它们定义了报告的日期范围。两个日期参数都接受 YYYY-MM-DD 格式的日期。

查询结构

  • 内部查询（反应）： 查询从一个子查询开始，该子查询从 discourse_reactions_reactions 表中选择日期（day）、反应类型（reaction_value）和反应计数（reactions_count），并与 discourse_reactions_reaction_users 表通过 reaction_id 连接。此连接确保我们计算了用户特定的反应。
  • 按反应类型聚合： 然后将选定的数据按 day 和 reaction_value 分组，并将范围限制在选定的开始和结束日期内。这用于计算指定日期范围内每天每种反应类型的总数。
  • 对反应总数进行排序： 对于每种反应类型，查询使用 CASE 语句聚合特定反应的使用次数，并将结果强制转换为整数以进行清晰计数。
    • 您可能需要根据您网站上启用的反应来调整此部分中的 reaction_value = '...'。
  • 点赞计数子查询： 使用单独的子查询，通过 post_actions 表（其中 post_action_type_id 对应于点赞）计算每天的点赞总数（likes_count）。
  • 合并数据： 然后，外部查询通过按 day 字段连接，将点赞计数与反应计数合并。
  • 最终选择： 最外层的 SELECT 生成最终输出，包含 day、点赞数（likes_count）以及每种反应类型的计数。如果某一天没有点赞数据，COALESCE 函数将显示零而不是 NULL。
  • 排序结果： 结果按日期（r.day）排序，以创建网站参与度的时间序列。

列

  • day：计算反应和点赞的日期。
  • likes_count：每天的点赞总数。
  • 每种反应类型（笑、哭、爆炸头、等）：显示每天每种反应类型总数的单独列。

示例结果

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
1 个赞
2

对于我们这些非开发人员来说，请注意……这些反应是硬编码在那里并且正在使用的。这也意味着，如果将来提供的反应从 :heart: 更改为 :+1:，那么这里显示的就是错误的旧版本。

我感觉这计数是错误的，如果默认值从 :heart: 更改为 :+1: 的话。

3

@JammyDodger 修复了我使用的、考虑到了这一点的查询，我相信：

2 个赞