ダッシュボードレポート - 投稿の感情

データとレポート
, , , ,
1

投稿感情のダッシュボードレポートのSQLバージョンです。

:discourse: このレポートには、Discourse AI プラグインと 感情分析 が有効になっている必要があります。

このダッシュボードレポートは、指定された期間内に、投稿者の信頼レベル別にグループ化された、以下のいずれかの感情で分類された投稿数を示します。

  • 悲しみ
  • 驚き
  • 恐怖
  • 怒り
  • 喜び
  • 嫌悪
  • 賞賛
  • 面白さ
  • 苛立ち
  • 同意
  • 思いやり
  • 混乱
  • 好奇心
  • 欲求
  • 落胆
  • 不承認
  • 恥ずかしさ
  • 興奮
  • 感謝
  • 悲嘆
  • 愛
  • 神経質
  • 中立
  • 楽観主義
  • プライド
  • 気づき
  • 安堵
  • 後悔
-- [params]
-- date :start_date = 2024-01-16
-- date :end_date = 2024-02-16
-- double :threshold = 0.30

SELECT
    u.trust_level AS trust_level,
    -- 元のクエリからの基本的な感情
    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,
    
    -- 2番目のクエリからの追加の感情
    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,
    
    -- 閾値を超える感情を持つ投稿の総数
    COUNT(*) AS total_posts,
    
    -- 閾値を超える少なくとも1つの感情を持つ投稿の総数
    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