社区情绪与有害内容查询

[SaraDev (Sara Devlaeminck)]

如果您想查看 Community Sentiment 和 Toxicity 模块的分类结果数据库，可以使用 Data Explorer 插件中的 classification_results 表。

这对于了解 AI 插件在您的网站上的运行情况以及如何对帖子进行分类非常有用。

AI Sentiment

SELECT target_id as post_id,
model_used,
classification->'negative' as negative,
classification->'neutral' as neutral,
classification->'positive' as positive
from classification_results
WHERE model_used = 'sentiment'
order by id desc

AI Emotion:

SELECT target_id as post_id,
model_used,
classification->'neutral' as neutral,
classification->'sadness' as sadness,
classification->'surprise' as surprise,
classification->'fear' as fear,
classification->'anger' as anger,
classification->'joy' as joy,
classification->'disgust' as disgust
from classification_results
WHERE model_used = 'emotion'
order by id desc

AI Toxicity:

SELECT target_id as post_id,
classification->'toxicity' as toxicity,
classification->'severe_toxicity' as severe_toxicity,
classification->'obscene' as obscene,
classification->'identity_attack' as identity_attack,
classification->'insult' as insult,
classification->'threat' as threat,
classification->'sexual_explicit' as sexual_explicit
From classification_results
WHERE classification_type = 'toxicity'
order by id desc

[Samantha_O]

有没有办法在此查询中添加过滤器以获取特定帖子的帖子？目前，它似乎正在获取整个社区的帖子，这很好，但也很麻烦，难以找到您正在寻找的特定帖子/帖子，更不用说您将遇到此行的限制了。

[Lilly (Lillian Louis)]

你好 @Samantha_O

这是否适用于特定主题？您必须在参数中指定主题 ID。

-- [params]
-- int :topic_id = 

SELECT cr.target_id as post_id,
cr.model_used,
cr.classification->'negative' as negative,
cr.classification->'neutral' as neutral,
cr.classification->'positive' as positive
FROM classification_results cr
JOIN posts p ON p.id = cr.target_id
WHERE cr.model_used = 'sentiment'
AND p.topic_id = :topic_id
ORDER BY cr.id DESC

[JammyDodger]

-- [params]
-- topic_id :topic_id

SELECT 
    cr.target_id as post_id,
    cr.model_used,
    cr.classification->'negative' as negative,
    cr.classification->'neutral' as neutral,
    cr.classification->'positive' as positive
FROM classification_results cr
  JOIN posts p ON p.id = cr.target_id
WHERE cr.model_used = 'sentiment'
  AND p.topic_id = :topic_id
ORDER BY p.id

啊，你抢先一步了。

不过，这里还有一个针对特定帖子的，以免显得回复浪费了：

-- [params]
-- topic_id :topic_id
-- int :post_number

SELECT 
    cr.target_id as post_id,
    cr.model_used,
    cr.classification->'negative' as negative,
    cr.classification->'neutral' as neutral,
    cr.classification->'positive' as positive
FROM classification_results cr
  JOIN posts p ON p.id = cr.target_id
WHERE cr.model_used = 'sentiment'
  AND p.topic_id = :topic_id
  AND p.post_number = :post_number

topic_id 和 post_number 都可以在 URL 中找到，所以非常用户友好。

Topic_id:

Community Sentiment and Toxicity queries

-- [params] -- topic_id :topic_id SELECT cr.target_id as post_id, cr.model_used, cr.classification->'negative' as negative, cr.classification->'neutral' as neutral, cr.classification->'positive' as positive FROM classification_results cr JOIN posts p ON p.id = cr.target_id WHERE cr.model_used = 'sentiment' AND p.topic_id = :topic_id ORDER BY p.id Ahh you pipped me to it. But here’s one for a specific post as well just to not feel like a wasted reply: -…

Post_number:

Community Sentiment and Toxicity queries

-- [params] -- topic_id :topic_id SELECT cr.target_id as post_id, cr.model_used, cr.classification->'negative' as negative, cr.classification->'neutral' as neutral, cr.classification->'positive' as positive FROM classification_results cr JOIN posts p ON p.id = cr.target_id WHERE cr.model_used = 'sentiment' AND p.topic_id = :topic_id ORDER BY p.id Ahh you pipped me to it. But here’s one for a specific post as well just to not feel like a wasted reply: -…

[Lilly (Lillian Louis)]

我认为这应该针对类别 ID：

-- [params]
-- int :category_id = 

SELECT cr.target_id as post_id,
cr.model_used,
cr.classification->'negative' as negative,
cr.classification->'neutral' as neutral,
cr.classification->'positive' as positive
FROM classification_results cr
JOIN posts p ON p.id = cr.target_id
JOIN topics t ON t.id = p.topic_id
WHERE cr.model_used = 'sentiment'
AND t.category_id = :category_id
ORDER BY cr.id DESC

[Samantha_O]

感谢你们两位！运行这些时，它找不到旧的主题/主题 ID。我想知道——情感分析是否只发生在新的帖子中？

编辑以求清晰

[Lilly (Lillian Louis)]

@JammyDodger 会更清楚，但是的，我认为情感分析适用于新帖子。我认为要让它使用旧帖子，可能需要一些 rails 命令来通过情感模型处理它们，并将它们放入 classification_results 表中（新帖子的分析结果就在那里）。

[JammyDodger]

启用后，它在新帖子中生效。 我不确定它是否有回填选项？

[Falco]

从现在开始只发布新帖子。我们可以在开始报告数据并进行验证之后再考虑回填这些信息。

[Samantha_O]

您（或其他才华横溢的查询专家）能否帮助我对此进行一些微调？我想为日期设置参数，这些日期将对应于帖子日期。最终目标是能够随着时间的推移查看更改，同时等待查看仪表板将来是否可以做到这一点

[SaraDev (Sara Devlaeminck)]

你好 Samantha，

如果你想在查询中加入开始和结束日期参数，可以使用如下查询：

-- [params]
-- date :start_date = 2023-01-01
-- date :end_date = 2024-01-01

SELECT 
p.created_at,
cr.target_id as post_id,
cr.model_used,
cr.classification->'negative' as negative,
cr.classification->'neutral' as neutral,
cr.classification->'positive' as positive
FROM classification_results cr
JOIN posts p ON p.id = cr.target_id
WHERE cr.model_used = 'sentiment'
AND p.created_at BETWEEN :start_date AND :end_date
ORDER BY cr.id DESC

示例结果：

created_at	post	model_used	negative	neutral	positive
2023-11-08T21:21:23.913Z	post_id	sentiment	58	38	2

这里添加的重要部分是将 posts 表与 classification_results 表连接起来，以便我们可以将以下内容添加到 WHERE 语句中：

AND p.created_at BETWEEN :start_date AND :end_date

以根据 created_at 日期过滤帖子。

由于 emotion 和 toxicity 分类查询类似，你也可以修改这些查询以包含开始和结束日期参数。

[Lilly (Lillian Louis)]

Dashboard Report - Overall Sentiment

This is an SQL version of the Dashboard Report for Overall Sentiment. This report requires the Discourse AI plugin and Sentiment Analysis to be enabled. This dashboard report compares the number of posts classified either positive or negative with the “Sentiment” AI, over a specified period. By leveraging sentiment analysis, the report categorizes posts as either positive or negative based on a defined threshold, which can be adjusted. This categorization is then used to calcula…

[SaraDev (Sara Devlaeminck)]

在此处添加此项：

Dashboard Report - Post Emotion

This is an SQL version of the Dashboard Report for Post Emotion. This report requires the Discourse AI plugin and Sentiment Analysis to be enabled. This dashboard report shows the number of posts classified with one of following emotions, group by poster trust level, within a specified date range: Sadness Surprise Fear Anger Joy Disgust Admiration Amusement Annoyance Approval Caring Confusion Curiosity Desire Disappointment Disapproval Embarrassment Excitement Gratitude Grief L…

[sonpage (Page D)]

我尝试使用此查询（我们的情绪设置已配置好），但它没有返回任何结果。有什么可以尝试排查的吗？

[Falco]

我已经更新了指南 Dashboard Report - Overall Sentiment 和 https://meta.discourse.org/t/dashboard-report-post-emotion/295553，你能用更新后的查询再试一次吗？