了解用户发布内容的主题

数据与报告
1

我想创建一个基于 SQL 的报告，以了解我们的用户在发布有关什么类型的内容。

类别、主题、标签——这些是我已经确定可以为我提供这些信息的字段，而帖子计数将是指标（我认为，基本上想用它来了解受欢迎程度）。

是否有其他字段可以支持这一点？是否已有可供我使用的查询，因为我假设其他人也曾请求过？

2 个赞
2

这不是 SQL 查询，但您可能已经注意到：

/categories 会为您提供每个类别每月新增主题的数量，这可能非常有用。

/tags 会为您提供按标签计算的主题数量。

以防您不知道（但可能已经知道了！）

1 个赞
3

我不确定您在这里指的是什么。

这些是社区管理部分中的报告吗？

我见过这个，但基本上想将它们全部整合到一个地方——这样您就可以看到一个类别有多少主题或标签等。

4

它们是 URL 路径。

5

我不太确定您设想如何将类别、标签和主题（以及每个的帖子数）包含在同一份报告/查询中。

我认为您可以分两份来完成——一份用于类别，一份用于标签，其中包含在特定时间范围内每个类别/标签下新主题和新帖子的数量。甚至可以包括发帖的用户数？

这样，结果表就会是这样的：

类别 新主题 新帖子 用户
类别 a 9 15 4
类别 b 56 167 32
2 个赞
6

好的，我已经输入了 Categories - Discourse Meta，我猜这正是你想要的意思。

本质上，我希望将类别/标签/主题合并到一个输出中，原因如下：

  • 识别重复项 - 据我所知，主题是用于对话/帖子的开头词。用户可能在 2 个不同的类别中添加了类似的主题，了解这一点有助于理解用户行为/可能调整类别以使其更清晰。
  • 了解每个类别的内容类型 - 类别可能是“汽车”，但自然可以包含很多不同类型的主题，我想了解用户真正谈论的是什么。
  • 标签 - 在我们拥有的实例中，我可以看到标签已被用作跨越多个不同类别的某种方式，因此从这个角度了解帖子也会很有帮助。

本质上，根据我的理解，我需要使用 SQL 连接类别、标签和主题，可能还有帖子，并且我想知道这是否已经完成，以便查看代码/是否有某种代码/查询库？

7

|类别|主题|标签|帖子|用户|
| — | — | — | — |—|\n|汽车|爱汽车|车轮|44|1\n|汽车|恨汽车|车轮|32|3\n|汽车|恨汽车|车门|39|4\n|汽车|汽车如何工作？|车门|32|1\n|事物如何工作|汽车如何工作？|操作方法|32|3\n\n这是愿景的一个例子。这将允许进一步分析，以了解诸如“在汽车类别中发帖的用户中有多少百分比谈论车轮”之类的内容。\n\n我明白这将在数据浏览器中结合帖子、主题、标签、类别，可能还有另一个表——我只是在这里发帖是为了了解这是否已经完成/在哪里可以找到之前创建的 SQL 查询（不是在我们的账户中，我的意思是普遍存在的）。\n\n希望这有道理，昨天才开始调查

8

啊，我明白了。您想要一个包含额外细节的主题列表，而不是一个概要性的概述。我认为这应该是可行的。 :+1:

我认为唯一需要注意的地方是，一个主题可以有多个标签，但让我们先弄一个出来看看效果。 :slight_smile:

但是，要浏览其他现有查询，您可以使用仪表板上提供的标准查询（您可以在此处找到 SQL 版本，并按 dashboard-sql 分组），以及数据浏览器中捆绑的一些标准报告，还有 meta 上的这个链接，其中包含一组自定义查询，并按 sql-query 标签分组。

@SStrong - 也许可以试试这个：

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-12-31


WITH tag_names AS (
   
    SELECT 
        t.id AS topic_id,
        string_agg(tags.name, ', ' ORDER BY tags.name) AS "tags"
    FROM topics t
      JOIN topic_tags tt ON tt.topic_id = t.id
      JOIN tags ON tags.id = tt.tag_id
    WHERE t.created_at BETWEEN :start_date AND :end_date
    GROUP BY t.id
    ),
    
user_count AS (

    SELECT
        p.topic_id,
        COUNT(DISTINCT p.user_id) AS users
    FROM posts p
      JOIN topics t ON t.id = p.topic_id
    WHERE t.created_at BETWEEN :start_date AND :end_date
      AND t.deleted_at IS NULL
      AND p.deleted_at IS NULL
      AND t.archetype = 'regular'
      AND p.post_type = 1
      AND p.user_id > 0
    GROUP BY p.topic_id
        
)

SELECT 
    t.category_id,
    t.id AS topic_id,
    tn.tags,
    t.posts_count,
    uc.users
FROM topics t
  JOIN tag_names tn ON tn.topic_id = t.id
  JOIN user_count uc ON uc.topic_id = t.id
WHERE t.created_at BETWEEN :start_date AND :end_date
  AND t.archetype = 'regular'
  AND t.deleted_at IS NULL
ORDER BY t.category_id, t.title
2 个赞
9

抱歉，以为我已经回复了。

是否可以修改代码以同时保存类别名称和主题名称？我不知道“帖子名称”是否是一个实际的东西，或者它实际上是主题名称？

我尝试自己修改代码，但我目前还不了解表是如何协同工作的，因为我得到的结果是 0 条记录，而不是与之前相同数量的记录，只是包含文字而不是 ID。

10

没问题。 :slight_smile: 在数据浏览器中查看时，category_id 和 topic_id（以及许多其他 ID）会自动转换为可用的站内链接，但如果导出到其他地方进行分析，则可以使用类别名称和主题标题代替：

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-12-31


WITH tag_names AS (

    SELECT
        t.id AS topic_id,
        string_agg(tags.name, ', ' ORDER BY tags.name) AS "tags"
    FROM topics t
      JOIN topic_tags tt ON tt.topic_id = t.id
      JOIN tags ON tags.id = tt.tag_id
    WHERE t.created_at BETWEEN :start_date AND :end_date
    GROUP BY t.id
    ),

user_count AS (

    SELECT
        p.topic_id,
        COUNT(DISTINCT p.user_id) AS users
    FROM posts p
      JOIN topics t ON t.id = p.topic_id
    WHERE t.created_at BETWEEN :start_date AND :end_date
      AND t.deleted_at IS NULL
      AND p.deleted_at IS NULL
      AND t.archetype = 'regular'
      AND p.post_type = 1
      AND p.user_id > 0
    GROUP BY p.topic_id

)

SELECT
    c.name AS category_name,
    t.title,
    tn.tags,
    t.posts_count,
    uc.users
FROM topics t
  JOIN tag_names tn ON tn.topic_id = t.id
  JOIN user_count uc ON uc.topic_id = t.id
  JOIN categories c ON c.id = t.category_id
WHERE t.created_at BETWEEN :start_date AND :end_date
  AND t.archetype = 'regular'
  AND t.deleted_at IS NULL
ORDER BY t.category_id, t.title
1 个赞