仪表板报告 - 被忽略/静音用户排行榜

数据与报告
, ,
1

此为“被忽略/静音用户排行榜”的 SQL 版本仪表板报告。

此仪表板报告为管理员提供了平台成员在指定开始和结束日期之间忽略或静音了哪些用户的相关见解。被忽略的用户是指被某成员选择性屏蔽，使其帖子对该成员不可见的用户，而静音用户是指某成员选择不接收其通知的用户。

此报告可以识别在社区中引起摩擦的用户，或其行为导致他人不愿与其互动的用户。该报告有助于做出审核决策，例如联系经常被忽略或静音的用户以解决其行为问题，或更广泛地了解社区中需要关注的问题。

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

WITH ignored_users AS (
    SELECT
        ignored_user_id AS user_id,
        COUNT(*) AS ignores_count
    FROM ignored_users
    WHERE created_at >= :start_date
      AND created_at <= :end_date
    GROUP BY ignored_user_id
    ORDER BY COUNT(*) DESC
),
muted_users AS (
    SELECT
        muted_user_id AS user_id,
        COUNT(*) AS mutes_count
    FROM muted_users
    WHERE created_at >= :start_date
      AND created_at <= :end_date
    GROUP BY muted_user_id
    ORDER BY COUNT(*) DESC
)

SELECT
    u.id AS user_id,
    u.username AS username,
    ig.ignores_count AS ignores_count,
    COALESCE(mu.mutes_count, 0) AS mutes_count,
    ig.ignores_count + COALESCE(mu.mutes_count, 0) AS total
FROM users AS u
JOIN ignored_users AS ig ON ig.user_id = u.id
LEFT OUTER JOIN muted_users AS mu ON mu.user_id = u.id
ORDER BY total DESC

SQL 查询说明

此 SQL 查询分几个步骤进行：

  • 日期参数：
    • 查询接受两个参数：:start_date 和 :end_date，它们定义了报告的日期范围。两个日期参数均接受 YYYY-MM-DD 格式的日期。
  • 公共表表达式 (CTE)：
    • ignored_users CTE 选择 ignored_user_id 并计算该 ID 在 ignored_users 表中出现的次数（表示该用户被忽略的次数），并将结果筛选在 :start_date 和 :end_date 之间。
    • muted_users CTE 类似，但从 muted_users 表中选择 muted_user_id，并在日期过滤器内计算实例数。
  • 主 SELECT 语句：从 users 表中选择用户详细信息，以及来自 ignored_users 和 muted_users CTE 的计数。
    • ignores_count 直接来自 ignored_users CTE。
    • mutes_count 来自 muted_users CTE，但如果为 NULL（表示用户未被静音），则使用 COALESCE 函数将其替换为 0。
    • 通过将忽略数和静音数相加来计算总数。
  • JOIN：主查询利用 JOIN 连接 ignored_users 以包含至少被忽略一次的所有用户，并利用 LEFT OUTER JOIN 连接 muted_users 以包含可能未被静音的用户。
  • ORDER BY：报告按总忽略数和静音数降序排序，将“问题”用户排在报告顶部。

示例结果

user username ignores_count mutes_count total
user1 user1 4 1 5
user2 user2 3 0 3
user3 user3 1 2 3
3 个赞