数据看板报告：帖子情绪

数据与报告
, , , ,
1

这是关于“帖子情感”的仪表板报告的 SQL 版本。

:discourse: 此报告需要启用 Discourse AI 插件和 Sentiment Analysis 。

此仪表板报告显示在指定日期范围内，按发帖人信任级别分组的、被归类为以下某种情感的帖子数量：

  • 悲伤
  • 惊讶
  • 恐惧
  • 愤怒
  • 喜悦
  • 厌恶
  • 钦佩
  • 娱乐
  • 恼怒
  • 同意
  • 关心
  • 困惑
  • 好奇
  • 渴望
  • 失望
  • 不赞成
  • 尴尬
  • 兴奋
  • 感激
  • 悲伤
  • 爱
  • 紧张
  • 中性
  • 乐观
  • 自豪
  • 顿悟
  • 如释重负
  • 懊悔
-- [params]
-- date :start_date = 2024-01-16
-- date :end_date = 2024-02-16
-- double :threshold = 0.30

SELECT
    u.trust_level AS trust_level,
    -- Basic emotions from original query
    COUNT(CASE WHEN (classification->>'sadness')::float > :threshold THEN 1 ELSE NULL END) AS sadness,
    COUNT(CASE WHEN (classification->>'surprise')::float > :threshold THEN 1 ELSE NULL END) AS surprise,
    COUNT(CASE WHEN (classification->>'fear')::float > :threshold THEN 1 ELSE NULL END) AS fear,
    COUNT(CASE WHEN (classification->>'anger')::float > :threshold THEN 1 ELSE NULL END) AS anger,
    COUNT(CASE WHEN (classification->>'joy')::float > :threshold THEN 1 ELSE NULL END) AS joy,
    COUNT(CASE WHEN (classification->>'disgust')::float > :threshold THEN 1 ELSE NULL END) AS disgust,
    
    -- Additional emotions from the second query
    COUNT(CASE WHEN (classification->>'admiration')::float > :threshold THEN 1 ELSE NULL END) AS admiration,
    COUNT(CASE WHEN (classification->>'amusement')::float > :threshold THEN 1 ELSE NULL END) AS amusement,
    COUNT(CASE WHEN (classification->>'annoyance')::float > :threshold THEN 1 ELSE NULL END) AS annoyance,
    COUNT(CASE WHEN (classification->>'approval')::float > :threshold THEN 1 ELSE NULL END) AS approval,
    COUNT(CASE WHEN (classification->>'caring')::float > :threshold THEN 1 ELSE NULL END) AS caring,
    COUNT(CASE WHEN (classification->>'confusion')::float > :threshold THEN 1 ELSE NULL END) AS confusion,
    COUNT(CASE WHEN (classification->>'curiosity')::float > :threshold THEN 1 ELSE NULL END) AS curiosity,
    COUNT(CASE WHEN (classification->>'desire')::float > :threshold THEN 1 ELSE NULL END) AS desire,
    COUNT(CASE WHEN (classification->>'disappointment')::float > :threshold THEN 1 ELSE NULL END) AS disappointment,
    COUNT(CASE WHEN (classification->>'disapproval')::float > :threshold THEN 1 ELSE NULL END) AS disapproval,
    COUNT(CASE WHEN (classification->>'embarrassment')::float > :threshold THEN 1 ELSE NULL END) AS embarrassment,
    COUNT(CASE WHEN (classification->>'excitement')::float > :threshold THEN 1 ELSE NULL END) AS excitement,
    COUNT(CASE WHEN (classification->>'gratitude')::float > :threshold THEN 1 ELSE NULL END) AS gratitude,
    COUNT(CASE WHEN (classification->>'grief')::float > :threshold THEN 1 ELSE NULL END) AS grief,
    COUNT(CASE WHEN (classification->>'love')::float > :threshold THEN 1 ELSE NULL END) AS love,
    COUNT(CASE WHEN (classification->>'nervousness')::float > :threshold THEN 1 ELSE NULL END) AS nervousness,
    COUNT(CASE WHEN (classification->>'neutral')::float > :threshold THEN 1 ELSE NULL END) AS neutral,
    COUNT(CASE WHEN (classification->>'optimism')::float > :threshold THEN 1 ELSE NULL END) AS optimism,
    COUNT(CASE WHEN (classification->>'pride')::float > :threshold THEN 1 ELSE NULL END) AS pride,
    COUNT(CASE WHEN (classification->>'realization')::float > :threshold THEN 1 ELSE NULL END) AS realization,
    COUNT(CASE WHEN (classification->>'relief')::float > :threshold THEN 1 ELSE NULL END) AS relief,
    COUNT(CASE WHEN (classification->>'remorse')::float > :threshold THEN 1 ELSE NULL END) AS remorse,
    
    -- Total count of posts with any emotion above threshold
    COUNT(*) AS total_posts,
    
    -- Total count of posts with at least one emotion above threshold
    COUNT(CASE WHEN 
        (classification->>'sadness')::float > :threshold OR
        (classification->>'surprise')::float > :threshold OR
        (classification->>'fear')::float > :threshold OR
        (classification->>'anger')::float > :threshold OR
        (classification->>'joy')::float > :threshold OR
        (classification->>'disgust')::float > :threshold OR
        (classification->>'admiration')::float > :threshold OR
        (classification->>'amusement')::float > :threshold OR
        (classification->>'annoyance')::float > :threshold OR
        (classification->>'approval')::float > :threshold OR
        (classification->>'caring')::float > :threshold OR
        (classification->>'confusion')::float > :threshold OR
        (classification->>'curiosity')::float > :threshold OR
        (classification->>'desire')::float > :threshold OR
        (classification->>'disappointment')::float > :threshold OR
        (classification->>'disapproval')::float > :threshold OR
        (classification->>'embarrassment')::float > :threshold OR
        (classification->>'excitement')::float > :threshold OR
        (classification->>'gratitude')::float > :threshold OR
        (classification->>'grief')::float > :threshold OR
        (classification->>'love')::float > :threshold OR
        (classification->>'nervousness')::float > :threshold OR
        (classification->>'neutral')::float > :threshold OR
        (classification->>'optimism')::float > :threshold OR
        (classification->>'pride')::float > :threshold OR
        (classification->>'realization')::float > :threshold OR
        (classification->>'relief')::float > :threshold OR
        (classification->>'remorse')::float > :threshold
    THEN 1 ELSE NULL END) AS emotional_posts
FROM
    classification_results AS cr
    INNER JOIN posts p ON p.id = cr.target_id AND cr.target_type = 'Post'
    INNER JOIN users u ON p.user_id = u.id
    INNER JOIN topics t ON t.id = p.topic_id
WHERE
    t.archetype = 'regular' AND
    p.user_id > 0 AND
    cr.model_used = 'SamLowe/roberta-base-go_emotions' AND
    (p.created_at > :start_date AND p.created_at < :end_date)
GROUP BY
    u.trust_level
ORDER BY
    u.trust_level

SQL 查询说明

SQL 查询通过执行以下步骤来运行：

  • 参数定义：
    • :start_date 和 :end_date 用于指定分析的日期范围。
    • :threshold 用于设置对帖子情感进行分类的最低分数。:threshold 的默认值设置为 0.30，以匹配仪表板报告。
  • 数据选择和连接：
    • 查询从 classification_results 表中选择数据，该表包含应用于帖子的情感分类模型的结果。
    • 它将 classification_results 表与 posts 表连接起来，以仅筛选属于帖子的分类（cr.target_type = 'Post'）。
    • 它进一步与 users 和 topics 表连接，以访问用户信任级别并确保帖子属于常规主题（而不是私信或其他特殊类型）。
  • 过滤：
    • 查询会筛选在指定日期范围内创建的帖子（p.created_at > :start_date AND p.created_at < :end_date）。
    • 它确保帖子来自常规主题（t.archetype = 'regular'），由注册用户发布（p.user_id > 0），并且专门针对情感分类（cr.model_used = 'emotion'）。
  • 分类计数：
    • 对于每种情感（悲伤、惊讶、恐惧、愤怒、喜悦、厌恶），查询会计算分类强度大于指定阈值（:threshold）的帖子数量。
  • 分组：结果按用户信任级别（u.trust_level）进行分组，从而按用户信任级别细分情感内容。

示例结果

trust_level sadness surprise fear anger joy disgust admiration amusement … emotional_posts total_posts
0 12 8 5 15 20 3 18 25 … 78 120
1 35 42 18 29 64 12 57 82 … 245 310
2 67 85 32 48 112 23 124 156 … 487 520
3 45 63 24 37 95 18 102 124 … 326 380
4 21 36 14 18 53 9 67 72 … 175 210
3 个赞