仪表板报告 - 用户标记比率

数据与报告
, ,
1

这是用户标记比率的仪表板报告的 SQL 版本。

此仪表板报告会生成一个用户列表，按工作人员对其标记（同意和不同意）的响应比率排序。报告包含有关每个用户标记活动的信息，特别关注由版主同意、不同意或忽略的标记数量，以及代表用户标记绩效的计算分数。

该报告对于管理员识别准确标记不当内容的用户的身份、评估用户在社区版主方面的参与度、提供有关标记实践的反馈以及评估用户标记绩效以担任版主角色非常有用。

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-02-01

SELECT
  u.id AS user_id,
  u.username,
  CASE
    WHEN u.silenced_till IS NOT NULL THEN 't'
    ELSE 'f'
  END AS silenced,
  SUM(CASE WHEN rs.status = 2 THEN 1 ELSE 0 END)::numeric AS disagreed_flags,
  SUM(CASE WHEN rs.status = 1 THEN 1 ELSE 0 END)::numeric AS agreed_flags,
  SUM(CASE WHEN rs.status = 3 THEN 1 ELSE 0 END)::numeric AS ignored_flags,
  (
    CASE
      WHEN SUM(CASE WHEN rs.status = 2 THEN 1 ELSE 0 END)::numeric = 0 THEN
        SUM(CASE WHEN rs.status = 1 THEN 1 ELSE 0 END)::numeric * SUM(CASE WHEN rs.status = 1 THEN 1 ELSE 0 END)::numeric
      ELSE
        ROUND(
          (1 - (SUM(CASE WHEN rs.status = 1 THEN 1 ELSE 0 END)::numeric / SUM(CASE WHEN rs.status = 2 THEN 1 ELSE 0 END))) *
          (SUM(CASE WHEN rs.status = 2 THEN 1 ELSE 0 END) - SUM(CASE WHEN rs.status = 1 THEN 1 ELSE 0 END)::numeric)
        )
    END
  ) AS score
FROM
  users AS u
  INNER JOIN reviewable_scores AS rs ON rs.user_id = u.id
WHERE
  u.id > 0
  AND rs.created_at >= :start_date
  AND rs.created_at <= :end_date
GROUP BY
  u.id,
  u.username,
  u.uploaded_avatar_id,
  u.silenced_till
ORDER BY
  score DESC
LIMIT 100

SQL 查询说明

以下是查询的细分：

  • 参数：查询接受两个参数，:start_date 和 :end_date，允许用户指定报告的日期范围。两个日期参数都接受 YYYY-MM-DD 格式的日期。
  • SELECT 子句：查询选择以下列：
    • user_id：用户的唯一标识符。
    • username：用户的用户名。
    • silenced：一个布尔值，指示用户当前是否被静默。
    • disagreed_flags：版主不同意的用户标记的总数。
    • agreed_flags：版主同意的用户标记的总数。
    • ignored_flags：版主忽略的用户标记的总数。
    • score：表示用户标记绩效的计算分数。
  • FROM 子句：查询在 user_id 上将 users 表与 reviewable_scores 表连接起来，以获取与每个用户相关的标记信息。
  • WHERE 子句：查询筛选数据，仅包括 id 大于 0 的用户的记录，以及 reviewable_scores 的 created_at 日期在指定日期范围内的记录。
  • GROUP BY 子句：查询按 user_id、username、uploaded_avatar_id 和 silenced_till 对结果进行分组，以汇总每个用户的标记数据。
  • ORDER BY 子句：查询按计算出的 score 按降序对结果进行排序，以首先显示标记绩效最高的用户。
  • LIMIT 子句：查询根据计算出的分数将结果限制为排名前 100 的用户。

示例结果

user username silenced disagreed_flags agreed_flags ignored_flags score
user_1_id user_1 f 0.0 10.0 3.0 100.0
user_2_id user_2 f 0.0 6.0 3.0 36.0
user_3_id user_3 f 0.0 4.0 0.0 16.0
… … … … … … …
1 个赞