内容审计：报告提取类别、主题和回复

数据与报告
3

您好 @RachFeverBee，

使用数据探索器查询是查找您所需信息的最佳方式。

根据您分享的主题以及您的报告需求描述，以下是一个数据探索器查询，您可以将其作为起点：

分类主题和回复

-- [params]
-- date :start_date = 2023-01-01
-- date :end_date = 2025-01-28

WITH filtered_topics AS (
    SELECT
        t.id AS topic_id,
        t.title AS topic_title,
        t.created_at AS topic_created_at,
        t.user_id AS topic_user_id,
        t.category_id AS topic_category_id
    FROM
        topics t
    WHERE
        t.created_at BETWEEN :start_date AND :end_date
),
filtered_posts AS (
    SELECT
        p.id AS post_id,
        p.topic_id,
        p.user_id AS post_user_id,
        p.created_at AS post_created_at,
        p.raw AS post_content,
        p.post_number
    FROM
        posts p
    WHERE
        p.created_at BETWEEN :start_date AND :end_date
),
categories_with_topics AS (
    SELECT
        c.id AS category_id,
        c.name AS category_name,
        ft.topic_id,
        ft.topic_title,
        ft.topic_created_at,
        ft.topic_user_id
    FROM
        categories c
    JOIN
        filtered_topics ft ON c.id = ft.topic_category_id
),
final_data AS (
    SELECT
        cwt.category_name,
        cwt.topic_id,
        cwt.topic_title,
        cwt.topic_created_at,
        cwt.topic_user_id,
        fp.post_id,
        fp.post_content,
        fp.post_created_at,
        fp.post_user_id,
        fp.post_number
    FROM
        categories_with_topics cwt
    LEFT JOIN
        filtered_posts fp ON cwt.topic_id = fp.topic_id
)
SELECT
    fd.category_name AS "分类",
    fd.topic_id AS "主题 ID",
    fd.topic_title AS "主题标题",
    fd.topic_created_at AS "主题创建时间",
    fd.topic_user_id AS "主题发帖人",
    fd.post_id AS "帖子 ID",
    fd.post_content AS "帖子内容",
    fd.post_created_at AS "帖子创建时间",
    fd.post_user_id AS "帖子发帖人",
    fd.post_number AS "帖子编号"
FROM
    final_data fd
ORDER BY
    fd.topic_created_at ASC,
    fd.post_created_at ASC

此报告将生成以下结果：

  • 分类名称: 分类的名称。
  • 主题 ID: 可链接的主题 ID。
  • 主题标题: 主题的标题。
  • 主题创建时间: 主题的创建日期。
  • 主题发帖人: 创建主题的用户。
  • 帖子 ID: 可链接的帖子 ID。
  • 帖子内容: 帖子的全部文本。
  • 帖子创建时间: 帖子的创建日期。
  • 帖子发帖人: 创建帖子的用户。
  • 帖子编号: 主题内的帖子编号。

示例结果

分类 主题 ID 主题标题 主题创建时间 主题发帖人 帖子 ID 帖子内容 帖子创建时间 帖子发帖人 帖子编号
General Discussion 101 Welcome to the Forum! 2023-01-02 10:00:00 UTC 1 201 Hello everyone, welcome! 2023-01-02 10:05:00 UTC 2 1
General Discussion 101 Welcome to the Forum! 2023-01-02 10:00:00 UTC 1 202 Thanks for the warm welcome! 2023-01-02 10:10:00 UTC 3 2
Tech Support 102 How to reset my device? 2023-02-15 14:30:00 UTC 4 203 Can someone help me reset this? 2023-02-15 14:35:00 UTC 4 1
Tech Support 102 How to reset my device? 2023-02-15 14:30:00 UTC 4 204 Sure, here are the steps… 2023-02-15 14:40:00 UTC 5 2
Announcements 103 New Features Released! 2023-03-01 09:00:00 UTC 6 205 Check out our new features! 2023-03-01 09:05:00 UTC 6 1

您可能需要根据您希望查看结果的方式，调整查询末尾 ORDER BY 语句的排序方式，并且请注意，如果您的网站有大量主题和帖子，您可能需要注意数据探索器插件的 结果限制。

8 个赞