仪表盘报告 - 标志

数据与报告
, ,
1

这是“标记报告”的 SQL 版本

本报告提供了一个可选的特定类别（以及可选的子类别）中标记帖子的计数，涵盖给定的日期范围。此信息可帮助社区经理和版主了解论坛特定部分提出的标记数量，从而有助于识别趋势和可能需要额外关注或审核的领域。

-- [params]
-- date :start_date = 2023-01-01
-- date :end_date = 2024-01-01
-- null int :category_id = 0
-- boolean :include_subcategories = false

WITH RECURSIVE subcategories AS (
    SELECT id FROM categories WHERE id = :category_id
    UNION ALL
    SELECT c.id FROM categories c
    INNER JOIN subcategories sc ON c.parent_category_id = sc.id
)

SELECT
    DATE(r.created_at) AS flag_date,
    COUNT(r.id) AS flags_count
FROM reviewables r
LEFT JOIN categories c ON r.category_id = c.id
WHERE r.type = 'ReviewableFlaggedPost'
    AND r.created_at::DATE BETWEEN :start_date AND :end_date
    AND (:category_id = 0 OR (
        :include_subcategories
        AND r.category_id IN (SELECT id FROM subcategories)
        OR NOT :include_subcategories
        AND r.category_id = :category_id
    ))
GROUP BY flag_date
ORDER BY flag_date

SQL 查询说明

该 SQL 查询旨在计算指定日期范围和类别内每天创建的标记数量（在 reviewables 表中表示为 ReviewableFlaggedPost 记录）。它使用递归公共表表达式 (CTE) 来选择性地包含所选类别的子类别。

参数

  • :start_date：用于计算标记的日期范围的开始日期。
  • :end_date：用于计算标记的日期范围的结束日期。
  • :category_id：用于计算标记的类别的 ID。如果设置为 0，则不应用类别筛选。
  • :include_subcategories：一个布尔值，用于确定是否在计数中包含指定类别的子类别。

查询分解

  1. 递归 CTE (subcategories)：
  • 此 CTE 首先选择由 :category_id 参数提供的 ID 的类别。
  • 然后，它通过在 parent_category_id 字段上连接 categories 表和 subcategories CTE 来递归选择该类别的所有子类别。
  • 结果是所有子类别（包括初始类别）的列表，将用于筛选。
  1. 主查询：
  • 查询选择两列：标记创建日期 (flag_date) 和该日期的标记计数 (flags_count)。
  • 它将 reviewables 表与 categories 表连接起来，将每个标记与其类别相关联。
  1. 筛选器：
  • 查询筛选 reviewables 记录，仅包括类型为 ReviewableFlaggedPost 的记录，这代表被标记的帖子。
  • 它确保标记的 created_at 日期在指定的日期范围（:start_date 到 :end_date）内。
  • 它根据 :category_id 和 :include_subcategories 参数应用条件筛选：
    • 如果 :category_id 为 0，则不应用类别筛选。
    • 如果 :include_subcategories 为 true，则查询包括来自初始类别及其所有子类别的标记。
    • 如果 :include_subcategories 为 false，则查询仅包括来自初始类别的标记。
  1. 分组和排序：
  • 结果按标记创建日期分组，以提供每日计数。
  • 结果按日期排序，以按时间顺序呈现数据。

示例结果

flag_date flags_count
2023-11-19 2
2023-11-22 1
2023-11-23 1
2023-11-24 2
2023-11-26 1
… …
1 个赞