这是Moderator Activity的仪表板报告的SQL版本。
本报告提供了在指定时间范围内，在Discourse站点上版主执行的活动的全面概述。报告结合了版主活动的多个方面：在平台上的花费时间、审查的标记数量、创建的帖子、创建的个人消息（PM）、创建的主题以及进行的帖子修订。
此仪表板报告是管理员衡量其版主团队的有效性和参与度的宝贵工具，提供了对其活动和贡献的详细了解。报告提供的见解可以为版主培训、认可和招聘的决策提供信息，并确保版主团队均衡且有效地维护社区标准。
--[params]
-- date :start_date = 2023-12-01
-- date :end_date = 2024-12-01
-- boolean :include_admins = false
WITH mods AS (
SELECT
id AS user_id,
username_lower AS username,
uploaded_avatar_id
FROM users u
WHERE ((u.moderator = true) OR (:include_admins AND u.admin = true))
AND u.id > 0
),
time_read AS (
SELECT SUM(uv.time_read) AS time_read,
uv.user_id
FROM mods m
JOIN user_visits uv ON m.user_id = uv.user_id
WHERE uv.visited_at >= :start_date
AND uv.visited_at <= :end_date
GROUP BY uv.user_id
),
flag_count AS (
WITH period_actions AS (
SELECT agreed_by_id,
disagreed_by_id
FROM post_actions
WHERE post_action_type_id IN (3,4,8,6,7)
AND created_at >= :start_date
AND created_at <= :end_date
),
agreed_flags AS (
SELECT pa.agreed_by_id AS user_id,
COUNT(*) AS flag_count
FROM mods m
JOIN period_actions pa
ON pa.agreed_by_id = m.user_id
GROUP BY agreed_by_id
),
disagreed_flags AS (
SELECT pa.disagreed_by_id AS user_id,
COUNT(*) AS flag_count
FROM mods m
JOIN period_actions pa
ON pa.disagreed_by_id = m.user_id
GROUP BY disagreed_by_id
)
SELECT
COALESCE(af.user_id, df.user_id) AS user_id,
COALESCE(af.flag_count, 0) + COALESCE(df.flag_count, 0) AS flag_count
FROM agreed_flags af
FULL OUTER JOIN disagreed_flags df
ON df.user_id = af.user_id
),
revision_count AS (
SELECT pr.user_id,
COUNT(*) AS revision_count
FROM mods m
JOIN post_revisions pr
ON pr.user_id = m.user_id
JOIN posts p
ON p.id = pr.post_id
WHERE pr.created_at >= :start_date
AND pr.created_at <= :end_date
AND p.user_id <> pr.user_id
GROUP BY pr.user_id
),
topic_count AS (
SELECT t.user_id,
COUNT(*) AS topic_count
FROM mods m
JOIN topics t ON t.user_id = m.user_id
WHERE t.archetype = 'regular'
AND t.created_at >= :start_date
AND t.created_at <= :end_date
GROUP BY t.user_id
),
post_count AS (
SELECT p.user_id,
COUNT(*) AS post_count
FROM mods m
JOIN posts p ON p.user_id = m.user_id
JOIN topics t ON t.id = p.topic_id
WHERE t.archetype = 'regular'
AND p.created_at >= :start_date
AND p.created_at <= :end_date
GROUP BY p.user_id
),
pm_count AS (
SELECT p.user_id,
COUNT(*) AS pm_count
FROM mods m
JOIN posts p ON p.user_id = m.user_id
JOIN topics t ON t.id = p.topic_id
WHERE t.archetype = 'private_message'
AND p.created_at >= :start_date
AND p.created_at <= :end_date
GROUP BY p.user_id
)
SELECT
m.user_id,
m.username,
fc.flag_count as flags_reviewed,
ROUND(((tr.time_read) / 3600.00),2) as time_reading_hours,
tc.topic_count as topics_created,
pmc.pm_count as PMs_created,
pc.post_count as posts_created,
rc.revision_count as revisions
FROM mods m
LEFT JOIN time_read tr ON tr.user_id = m.user_id
LEFT JOIN flag_count fc ON fc.user_id = m.user_id
LEFT JOIN revision_count rc ON rc.user_id = m.user_id
LEFT JOIN topic_count tc ON tc.user_id = m.user_id
LEFT JOIN post_count pc ON pc.user_id = m.user_id
LEFT JOIN pm_count pmc ON pmc.user_id = m.user_id
ORDER BY m.username ASC
参数
:start_date和:end_date- 这些参数定义了报告的日期范围。两个日期参数都接受YYYY-MM-DD格式的日期。:include_admins- 此参数决定是否在报告中包含管理员以及版主。
SQL 查询说明
报告使用通用表表达式（CTE）进行结构化，将数据处理划分为可管理和逻辑的部分。以下是每个CTE的功能：
- mods：识别所有具有版主身份或管理员身份（如果通过
:include_admins参数包含）的用户。它仅选择相关的用户列以供进一步查询。 - time_read：计算在提供的开始和结束日期之间，每位版主在平台上阅读内容所花费的总时间（以秒为单位）。
- flag_count：计算在指定期间内版主同意或不同意的标记数量。它考虑了由各自的帖子操作类型ID表示的多个标记类型。
- revision_count：计算在给定时间范围内，版主对其他用户帖子进行的修订数量。
- topic_count：计算版主创建的常规主题的数量。
- post_count：计算版主在常规主题中创建的帖子的数量。
- pm_count：计算版主发起的私人消息的数量。
在CTE中收集数据后，主查询根据用户ID将它们连接起来，并编译最终报告，显示每位版主的用户名、花费的阅读时间总数（转换为小时）、审查的标记数量、创建的主题、创建的个人消息、创建的帖子以及进行的修订。结果按版主的用户名字母顺序排序。
示例结果
| user | username | flags_reviewed | time_reading_hours | topics_created | pms_created | posts_created | revisions |
|---|---|---|---|---|---|---|---|
| 1 | moderator1 | NULL | 36.11 | NULL | 344 | 8 | 15 |
| 2 | moderator2 | 46 | 104.52 | 2 | 271 | 466 | 363 |
| 3 | moderator3 | NULL | 72.15 | NULL | 418 | 64 | 16 |