仪表板报告 - 主题查看统计

数据与报告
, ,
1

这是主题视图统计信息的仪表板报告的 SQL 版本。

本报告收集并汇总了给定日期范围内的主题视图统计信息，包括匿名和登录用户的视图。它还允许按类别进行筛选，并可选择性地包含子类别。

:discourse: 此查询和 topic_view_stats 数据库表已于 2024 年 5 月 添加到 Discourse，不会显示此日期之前的数据。

-- [params]
-- date :start_date
-- date :end_date
-- int :category_id = 0
-- boolean :include_subcategories = false

WITH category_filter AS (
  SELECT
    id
  FROM categories
  WHERE
    (:category_id = 0 OR id = :category_id)
    OR (:include_subcategories AND parent_category_id = :category_id)
)

SELECT
  topic_view_stats.topic_id,
  topics.title AS topic_title,
  SUM(topic_view_stats.anonymous_views) AS total_anonymous_views,
  SUM(topic_view_stats.logged_in_views) AS total_logged_in_views,
  SUM(topic_view_stats.anonymous_views + topic_view_stats.logged_in_views) AS total_views
FROM topic_view_stats
INNER JOIN topics ON topics.id = topic_view_stats.topic_id
WHERE
  topic_view_stats.viewed_at BETWEEN :start_date AND :end_date
  AND (
    :category_id = 0
    OR topics.category_id IN (SELECT id FROM category_filter)
  )
GROUP BY topic_view_stats.topic_id, topics.title
ORDER BY total_views DESC
LIMIT 100

SQL 查询说明

参数

  • :start_date (日期): 分析范围的开始日期（含）。
  • :end_date (日期): 分析范围的结束日期（含）。
  • :category_id (整数, 可选): 按类别筛选主题。使用：
    • 0 包含所有类别。
    • 任何特定的类别 ID 以按该类别进行筛选。
  • :include_subcategories (布尔值, 可选): 选择是否包含指定类别的子类别。
    • false: 仅选定的类别。
    • true: 还包括子类别。

类别筛选
category_filter 公用表表达式 (CTE) 根据 :category_id 参数检索选定的类别 ID。如果参数为 0，则包含所有类别。如果要包含子类别，它会查找 parent_category_id 与指定的 :category_id 匹配的类别。

检索主题统计信息
主题视图统计信息从 topic_view_stats 表中汇总，并与 topics 表连接以检索主题标题。

应用日期和类别筛选
查询筛选 topic_view_stats 数据：

  • 按提供的日期范围（:start_date 和 :end_date）。
  • 按类别，与 category_filter 中的 ID 进行匹配。

计算总视图数
查询汇总了：

  • anonymous_views: 来自匿名用户的视图。
  • logged_in_views: 来自登录用户的视图。
  • 总视图数是两者的总和。

排名和限制：
查询按 total_views 对主题进行排名，并将结果限制为前 100 个主题。

查询结果
查询返回以下列：

列名 描述
topic_id 主题的唯一标识符。
topic_title 主题的标题。
total_anonymous_views 匿名用户的总视图数。
total_logged_in_views 登录用户的总视图数。
total_views 总视图数（匿名+登录）。

示例结果

topic_id topic_title total_anonymous_views total_logged_in_views total_views
12345 “示例主题 1” 500 300 800
67890 “示例主题 2” 450 350 800
3 个赞