分析版主和举报活动报告

SaraDev · 2025 年2 月 13 日 23:08

维护一个健康且包容的社区需要有效的管理，这可能包括审查被标记的帖子、分析管理员表现以及管理用户内容。

本指南包含多种用于 Discourse 的 SQL 报告，旨在帮助分析与管理工作相关的活动。

在本主题中，您将找到详细的 Data Explorer 查询，用于：

标记解决统计。
待审项目解决百分比。
特定管理员的性能指标。
用户标记活动的洞察。
所有被标记的用户、帖子和主题操作的全面数据

按类型划分的帖子标记解决百分比

SQL 查询解释

此查询计算被标记帖子的解决情况（同意、不同意、暂缓、删除）的百分比，并按标记类型分组。

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

WITH period_actions AS (
    SELECT pa.id,
           pa.post_action_type_id,
           pa.created_at,
           pa.agreed_at,
           pa.disagreed_at,
           pa.deferred_at,
           pa.agreed_by_id,
           pa.disagreed_by_id,
           pa.deferred_by_id,
           pa.deleted_at,
           pa.post_id,
           pa.user_id,
           COALESCE(pa.disagreed_at, pa.agreed_at, pa.deferred_at) AS responded_at,
           EXTRACT(EPOCH FROM (COALESCE(pa.disagreed_at, pa.agreed_at, pa.deferred_at) - pa.created_at)) / 60 AS time_to_resolution_minutes -- 解决时间（分钟）
    FROM post_actions pa
    WHERE pa.post_action_type_id IN (3,4,6,7,8)
      AND pa.created_at >= :start_date
      AND pa.created_at <= :end_date
),
flag_types AS (
    SELECT pat.id,
           CASE 
               WHEN pat.id = 3 THEN 'off_topic'
               WHEN pat.id = 4 THEN 'inappropriate'
               WHEN pat.id = 6 THEN 'notify_user'
               WHEN pat.id = 7 THEN 'notify_moderators'
               WHEN pat.id = 8 THEN 'spam'
           END AS flag_type
    FROM post_action_types pat
),
flag_resolutions AS (
    SELECT
        pa.post_action_type_id,
        CASE 
            WHEN pa.agreed_at IS NOT NULL THEN 'agreed'
            WHEN pa.disagreed_at IS NOT NULL THEN 'disagreed'
            WHEN pa.deferred_at IS NOT NULL THEN 'deferred'
            WHEN pa.deleted_at IS NOT NULL THEN 'deleted'
        END AS resolution,
        COUNT(*) AS resolution_count
    FROM period_actions pa
    GROUP BY pa.post_action_type_id, resolution
),
flag_totals AS (
    SELECT
        pa.post_action_type_id,
        COUNT(*) AS total_flags
    FROM period_actions pa
    GROUP BY pa.post_action_type_id
),
resolution_percentages AS (
    SELECT
        fty.flag_type,
        fr.resolution,
        fr.resolution_count,
        ft.total_flags,
        ROUND((fr.resolution_count::decimal / ft.total_flags) * 100, 2) AS resolution_percentage
    FROM flag_resolutions fr
    JOIN flag_totals ft ON ft.post_action_type_id = fr.post_action_type_id
    JOIN flag_types fty ON fty.id = fr.post_action_type_id
),
pivoted_data AS (
    SELECT
        flag_type,
        MAX(CASE WHEN resolution = 'agreed' THEN resolution_percentage ELSE 0 END) AS agreed_percentage,
        MAX(CASE WHEN resolution = 'disagreed' THEN resolution_percentage ELSE 0 END) AS disagreed_percentage,
        MAX(CASE WHEN resolution = 'deferred' THEN resolution_percentage ELSE 0 END) AS deferred_percentage,
        MAX(CASE WHEN resolution = 'deleted' THEN resolution_percentage ELSE 0 END) AS deleted_percentage,
        MAX(CASE WHEN resolution = 'agreed' THEN resolution_count ELSE 0 END) AS agreed_count,
        MAX(CASE WHEN resolution = 'disagreed' THEN resolution_count ELSE 0 END) AS disagreed_count,
        MAX(CASE WHEN resolution = 'deferred' THEN resolution_count ELSE 0 END) AS deferred_count,
        MAX(CASE WHEN resolution = 'deleted' THEN resolution_count ELSE 0 END) AS deleted_count,
        MAX(total_flags) AS total_flags
    FROM resolution_percentages
    GROUP BY flag_type
)
SELECT
    flag_type,
    agreed_percentage,
    agreed_count,
    disagreed_percentage,
    disagreed_count,
    deferred_percentage,
    deferred_count,
    deleted_percentage,
    deleted_count,
    total_flags
FROM pivoted_data
ORDER BY flag_type

使用的参数

:start_date: 用于筛选被标记帖子的起始日期。
:end_date: 用于筛选被标记帖子的结束日期。

CTE 解释

period_actions: 筛选指定日期范围内的被标记帖子，并计算每个标记的解决时间。
flag_types: 将标记类型 ID 映射为人类可读的名称（例如：离题、不当、垃圾信息）。
flag_resolutions: 按类型和解决情况（同意、不同意、暂缓、删除）对标记进行分组，并统计每种解决情况的发生次数。
flag_totals: 计算每种标记类型的标记总数。
resolution_percentages: 结合解决计数和标记总数，计算每种标记类型的每种解决情况的百分比。
pivoted_data: 将数据透视，以便在单独的列中显示每种解决情况的百分比和计数。

结果解释

最终结果是一个表格，显示：

标记类型（例如：离题、垃圾信息）。
每种解决情况（同意、不同意、暂缓、删除）的百分比和计数。
每种标记类型的标记总数。

示例结果

标记类型	同意 %	同意计数	不同意 %	不同意计数	暂缓 %	暂缓计数	删除 %	删除计数	总标记数
off_topic	50.00	25	30.00	15	10.00	5	10.00	5	50
spam	70.00	35	20.00	10	5.00	2	5.00	3	50

待审项目解决百分比

SQL 查询解释

此查询分析给定日期范围内待审项目（例如被标记的帖子）的解决状态。它计算每种标记类型的每种解决状态的百分比和计数。

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

WITH flag_data AS (
    SELECT
        r.type AS flag_type,
        r.status AS resolution_status,
        COUNT(*) AS flag_count
    FROM
        reviewables r
    WHERE
        r.created_at BETWEEN :start_date AND :end_date
    GROUP BY
        r.type,
        r.status
),
flag_totals AS (
    SELECT
        flag_type,
        SUM(flag_count) AS total_flags
    FROM
        flag_data
    GROUP BY
        flag_type
),
flag_percentages AS (
    SELECT
        fd.flag_type,
        fd.resolution_status,
        fd.flag_count,
        ROUND((fd.flag_count::decimal / ft.total_flags) * 100, 2) AS percentage
    FROM
        flag_data fd
    JOIN
        flag_totals ft
    ON
        fd.flag_type = ft.flag_type
)
SELECT
    fp.flag_type,
    -- 百分比
    COALESCE(MAX(CASE WHEN fp.resolution_status = 1 THEN fp.percentage END), 0) AS pending_percentage,
    COALESCE(MAX(CASE WHEN fp.resolution_status = 2 THEN fp.percentage END), 0) AS approved_percentage,
    COALESCE(MAX(CASE WHEN fp.resolution_status = 3 THEN fp.percentage END), 0) AS rejected_percentage,
    COALESCE(MAX(CASE WHEN fp.resolution_status = 4 THEN fp.percentage END), 0) AS ignored_percentage,
    COALESCE(MAX(CASE WHEN fp.resolution_status = 5 THEN fp.percentage END), 0) AS deleted_percentage,
    -- 计数
    COALESCE(MAX(CASE WHEN fp.resolution_status = 1 THEN fp.flag_count END), 0) AS pending_count,
    COALESCE(MAX(CASE WHEN fp.resolution_status = 2 THEN fp.flag_count END), 0) AS approved_count,
    COALESCE(MAX(CASE WHEN fp.resolution_status = 3 THEN fp.flag_count END), 0) AS rejected_count,
    COALESCE(MAX(CASE WHEN fp.resolution_status = 4 THEN fp.flag_count END), 0) AS ignored_count,
    COALESCE(MAX(CASE WHEN fp.resolution_status = 5 THEN fp.flag_count END), 0) AS deleted_count
FROM
    flag_percentages fp
GROUP BY
    fp.flag_type
ORDER BY
    fp.flag_type

使用的参数

:start_date: 用于筛选待审项目的起始日期。
:end_date: 用于筛选待审项目的结束日期。

CTE 解释

flag_data: 按标记类型和解决状态对待审项目进行分组，统计每种组合的发生次数。
flag_totals: 计算每种标记类型的标记总数。
flag_percentages: 结合标记计数和总数，计算每种标记类型的每种解决状态的百分比。

结果解释

最终结果是一个表格，显示：

标记类型。
每种解决状态（待处理、批准、拒绝、忽略、删除）的百分比和计数。

示例结果

标记类型	待处理 %	待处理计数	批准 %	批准计数	拒绝 %	拒绝计数	忽略 %	忽略计数	删除 %	删除计数
off_topic	20.00	10	50.00	25	10.00	5	10.00	5	10.00	5
spam	10.00	5	70.00	35	10.00	5	5.00	2	5.00	3

管理员标记解决情况

SQL 查询解释

此查询通过显示哪些管理员解决了被标记的帖子、他们处理的标记类型以及他们应用的解决情况，提供对管理员活动的洞察。它计算每种解决类型的百分比和计数。

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01
-- boolean :only_staff = false

WITH period_actions AS (
    SELECT 
        pa.id,
        pa.post_action_type_id,
        pa.created_at,
        pa.agreed_at,
        pa.disagreed_at,
        pa.deferred_at,
        pa.agreed_by_id,
        pa.disagreed_by_id,
        pa.deferred_by_id,
        pa.deleted_at,
        pa.post_id,
        pa.user_id,
        COALESCE(pa.disagreed_at, pa.agreed_at, pa.deferred_at) AS responded_at,
        EXTRACT(EPOCH FROM (COALESCE(pa.disagreed_at, pa.agreed_at, pa.deferred_at) - pa.created_at)) / 60 AS time_to_resolution_minutes -- 解决时间（分钟）
    FROM post_actions pa
    WHERE pa.post_action_type_id IN (3,4,6,7,8)
      AND pa.created_at >= :start_date
      AND pa.created_at <= :end_date
),
flag_types AS (
    SELECT 
        pat.id,
        CASE 
            WHEN pat.id = 3 THEN 'off_topic'
            WHEN pat.id = 4 THEN 'inappropriate'
            WHEN pat.id = 6 THEN 'notify_user'
            WHEN pat.id = 7 THEN 'notify_moderators'
            WHEN pat.id = 8 THEN 'spam'
        END AS flag_type
    FROM post_action_types pat
),
flag_resolutions AS (
    SELECT 
        pa.user_id,
        pa.post_action_type_id,
        CASE 
            WHEN pa.agreed_at IS NOT NULL THEN 'agreed'
            WHEN pa.disagreed_at IS NOT NULL THEN 'disagreed'
            WHEN pa.deferred_at IS NOT NULL THEN 'deferred'
            WHEN pa.deleted_at IS NOT NULL THEN 'deleted'
        END AS resolution,
        COUNT(*) AS resolution_count
    FROM period_actions pa
    GROUP BY pa.user_id, pa.post_action_type_id, resolution
),
flag_totals AS (
    SELECT 
        pa.user_id,
        pa.post_action_type_id,
        COUNT(*) AS total_flags
    FROM period_actions pa
    GROUP BY pa.user_id, pa.post_action_type_id
),
resolution_percentages AS (
    SELECT 
        fr.user_id,
        fty.flag_type,
        fr.resolution,
        fr.resolution_count,
        ft.total_flags,
        ROUND((fr.resolution_count::decimal / ft.total_flags) * 100, 2) AS resolution_percentage
    FROM flag_resolutions fr
    JOIN flag_totals ft ON ft.user_id = fr.user_id AND ft.post_action_type_id = fr.post_action_type_id
    JOIN flag_types fty ON fty.id = fr.post_action_type_id
),
pivoted_data AS (
    SELECT 
        rp.user_id,
        rp.flag_type,
        MAX(CASE WHEN rp.resolution = 'agreed' THEN rp.resolution_percentage ELSE 0 END) AS agreed_percentage,
        MAX(CASE WHEN rp.resolution = 'disagreed' THEN rp.resolution_percentage ELSE 0 END) AS disagreed_percentage,
        MAX(CASE WHEN rp.resolution = 'deferred' THEN rp.resolution_percentage ELSE 0 END) AS deferred_percentage,
        MAX(CASE WHEN rp.resolution = 'deleted' THEN rp.resolution_percentage ELSE 0 END) AS deleted_percentage,
        MAX(CASE WHEN rp.resolution = 'agreed' THEN rp.resolution_count ELSE 0 END) AS agreed_count,
        MAX(CASE WHEN rp.resolution = 'disagreed' THEN rp.resolution_count ELSE 0 END) AS disagreed_count,
        MAX(CASE WHEN rp.resolution = 'deferred' THEN rp.resolution_count ELSE 0 END) AS deferred_count,
        MAX(CASE WHEN rp.resolution = 'deleted' THEN rp.resolution_count ELSE 0 END) AS deleted_count,
        MAX(rp.total_flags) AS total_flags
    FROM resolution_percentages rp
    GROUP BY rp.user_id, rp.flag_type
)
SELECT 
    u.id AS user_id,
    u.username,
    p.flag_type,
    p.agreed_percentage,
    p.agreed_count,
    p.disagreed_percentage,
    p.disagreed_count,
    p.deferred_percentage,
    p.deferred_count,
    p.deleted_percentage,
    p.deleted_count,
    p.total_flags
FROM pivoted_data p
JOIN users u ON u.id = p.user_id
WHERE (:only_staff = false OR (u.admin = true OR u.moderator = true))
ORDER BY u.username, p.flag_type, p.total_flags

使用的参数

:start_date: 用于筛选被标记帖子的起始日期。
:end_date: 用于筛选被标记帖子的结束日期。
:only_staff: 一个布尔参数，用于筛选结果以仅包含工作人员。

CTE 解释

period_actions: 筛选指定日期范围内的被标记帖子，并计算每个标记的解决时间。
flag_types: 将标记类型 ID 映射为人类可读的名称。
flag_resolutions: 按用户、标记类型和解决情况对标记进行分组，统计每种组合的发生次数。
flag_totals: 计算每个用户和每种标记类型的标记总数。
resolution_percentages: 结合解决计数和总数，计算每种解决类型的百分比。
pivoted_data: 将数据透视，以便在单独的列中显示每种解决情况的百分比和计数。

结果解释

最终结果是一个表格，显示：

管理员用户名。
每种解决情况（同意、不同意、暂缓、删除）的百分比和计数。
每位管理员处理的标记总数。

示例结果

管理员	标记类型	同意 %	同意计数	不同意 %	不同意计数	暂缓 %	暂缓计数	删除 %	删除计数	总标记数
mod1	off_topic	60.00	30	20.00	10	10.00	5	10.00	5	50
mod2	spam	70.00	35	20.00	10	5.00	2	5.00	3	50

谁在标记帖子

SQL 查询解释

此查询识别在指定日期范围内标记帖子的用户，并计算每个用户提交的标记总数。

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01
-- boolean :only_staff = false

SELECT 
    u.id AS user_id,
    u.username,
    COUNT(pa.id) AS flag_count
FROM post_actions pa
JOIN users u ON u.id = pa.user_id
WHERE pa.post_action_type_id IN (3, 4, 6, 7, 8) -- 标记类型
  AND pa.created_at >= :start_date
  AND pa.created_at <= :end_date
  AND (:only_staff = false OR (u.admin = true OR u.moderator = true))
GROUP BY u.id, u.username
ORDER BY flag_count DESC, u.username
LIMIT 10

使用的参数

:start_date: 用于筛选被标记帖子的起始日期。
:end_date: 用于筛选被标记帖子的结束日期。
:only_staff: 一个布尔参数，用于筛选结果以仅包含工作人员。

结果解释

最终结果是一个按标记计数排名的用户列表。

示例结果

用户 ID	用户名	标记计数
1	user1	50
2	user2	30
3	user3	20

用户备注

SQL 查询解释

此查询从 plugin_store_rows 表中检索用户备注。它提取详细信息，如用户 ID、创建日期、备注内容和创建者 ID。

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

WITH user_notes AS (

    SELECT 
        REPLACE(key, 'notes:', '')::int AS user_id,
        notes.value->>'created_at' AS created_at,
        notes.value->>'raw' AS user_note,
        notes.value->>'created_by' AS created_by
    FROM plugin_store_rows,
    LATERAL json_array_elements(value::json) notes
    WHERE plugin_name = 'user_notes'
    ORDER BY 2 DESC 
)

SELECT 
    un.user_id,
    un.created_at::date,
    un.user_note,
    un.created_by AS created_by_user_id
FROM user_notes un
JOIN users u ON u.id = un.user_id
WHERE un.created_at::date BETWEEN :start_date AND :end_date
ORDER BY created_at DESC

使用的参数

:start_date: 用于筛选用户备注的起始日期。
:end_date: 用于筛选用户备注的结束日期。

结果解释

最终结果是一个包含相关元数据的详细用户备注列表。

示例结果

用户 ID	创建日期	用户备注	创建者用户 ID
1	2025-01-01	这个用户很乐于助人。	2
2	2025-02-01	这个用户与另外两个用户账户有关联。	3

管理员 KPI - 标记和平均标记解决时间

SQL 查询解释

此查询通过计算每位管理员处理的标记数量和平均解决时间（分钟）来评估管理员表现。

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

WITH period_actions AS (
    SELECT pa.id,
           pa.post_action_type_id,
           pa.created_at,
           pa.agreed_at,
           pa.disagreed_at,
           pa.deferred_at,
           pa.agreed_by_id,
           pa.disagreed_by_id,
           pa.deferred_by_id,
           pa.post_id,
           pa.user_id,
           COALESCE(pa.disagreed_at, pa.agreed_at, pa.deferred_at) AS responded_at,
           EXTRACT(EPOCH FROM (COALESCE(pa.disagreed_at, pa.agreed_at, pa.deferred_at) - pa.created_at)) / 60 AS time_to_resolution_minutes -- 解决时间（分钟）
    FROM post_actions pa
    WHERE pa.post_action_type_id IN (3,4,6,7,8) -- 标记类型
      AND pa.created_at >= :start_date
      AND pa.created_at <= :end_date
),
moderator_actions AS (
    SELECT pa.id,
           pa.post_id,
           pa.created_at,
           pa.responded_at,
           pa.time_to_resolution_minutes,
           COALESCE(pa.agreed_by_id, pa.disagreed_by_id, pa.deferred_by_id) AS moderator_id
    FROM period_actions pa
    WHERE COALESCE(pa.agreed_by_id, pa.disagreed_by_id, pa.deferred_by_id) IS NOT NULL
),
moderator_stats AS (
    SELECT
        m.moderator_id,
        u.username AS moderator_username,
        COUNT(m.id) AS handled_flags,
        AVG(m.time_to_resolution_minutes) AS avg_resolution_time_minutes
    FROM moderator_actions m
    JOIN users u ON u.id = m.moderator_id
    GROUP BY m.moderator_id, u.username
)
SELECT
    ms.moderator_username,
    ms.handled_flags,
    ROUND(ms.avg_resolution_time_minutes::numeric, 2) AS avg_resolution_time_minutes
FROM moderator_stats ms
ORDER BY ms.handled_flags DESC, ms.avg_resolution_time_minutes ASC

使用的参数

:start_date: 用于筛选被标记帖子的起始日期。
:end_date: 用于筛选被标记帖子的结束日期。

CTE 解释

period_actions: 筛选指定日期范围内的被标记帖子，并计算每个标记的解决时间。
moderator_actions: 识别由管理员解决的标记，并计算每个标记的解决时间。
moderator_stats: 按管理员对标记进行分组，并计算处理的标记总数和平均解决时间。

结果解释

最终结果是一个按处理标记计数和平均解决时间排名的管理员列表。

示例结果

管理员用户名	处理标记数	平均解决时间（分钟）
mod1	50	15.00
mod2	30	20.00

所有标记数据

SQL 查询解释

此查询提供指定日期范围内所有被标记的用户、帖子和主题数据的全面数据集。它结合多个表的数据，包括标记类型、被标记项目、标记原因、标记来源、解决决策和相关消息等详细信息。

-- [params]
-- date :start_date = 2020-01-01
-- date :end_date = 2026-01-01

WITH flag_data AS (
    SELECT
        r.id AS flag_id,
        p.id AS post_id,
        p.topic_id,
        p.raw AS flagged_item_text,
        p.user_id AS post_author_id,
        fu.username AS flagged_by_username,
        r.created_at AS flagged_date,
        r.type AS flag_type,
        r.reviewable_by_moderator AS flag_source,
        r.payload AS flag_reason,
        r.status AS review_status,
        r.potentially_illegal AS potentially_illegal, 
        rs.reviewed_by_id,
        rs.reviewed_at,
        rs.score AS review_score,
        ru.username AS reviewed_by_username,
        p.deleted_at AS post_deleted_at,
        p.hidden_at AS post_hidden_at,
        u.silenced_till AS user_silenced_till,
        u.suspended_till AS user_suspended_till
    FROM
        reviewables r
    LEFT JOIN posts p ON r.target_id = p.id AND r.target_type = 'Post'
    LEFT JOIN users fu ON r.created_by_id = fu.id
    LEFT JOIN reviewable_scores rs ON rs.reviewable_id = r.id
    LEFT JOIN users ru ON rs.reviewed_by_id = ru.id
    LEFT JOIN users u ON p.user_id = u.id
    WHERE
        r.created_at BETWEEN :start_date AND :end_date
        --AND r.status = 1 -- 仅包含同意并采取行动的标记
),
review_decisions AS (
    SELECT
        0 AS status_code, 'pending' AS decision_name
    UNION ALL
    SELECT
        1 AS status_code, 'agreed' AS decision_name
    UNION ALL
    SELECT
        2 AS status_code, 'disagreed' AS decision_name
    UNION ALL
    SELECT
        3 AS status_code, 'ignored' AS decision_name
),
flag_types AS (
    SELECT
        3 AS post_action_type_id, 'off_topic' AS flag_type_name
    UNION ALL
    SELECT
        4 AS post_action_type_id, 'inappropriate' AS flag_type_name
    UNION ALL
    SELECT
        6 AS post_action_type_id, 'notify_user' AS flag_type_name
    UNION ALL
    SELECT
        7 AS post_action_type_id, 'notify_moderators' AS flag_type_name
    UNION ALL
    SELECT
        8 AS post_action_type_id, 'spam' AS flag_type_name
    UNION ALL
    SELECT
        10 AS post_action_type_id, 'illegal' AS flag_type_name
)
SELECT
    fd.flag_id,
    fd.post_id AS flagged_item,
    fd.flagged_by_username,
    fd.flagged_date,
    fd.flag_type,
    ft.flag_type_name AS flag_type_name,
    fd.flag_source AS reviewable_by_moderator,
    fd.flag_reason,
    fd.flagged_item_text,
    pa.related_post_id AS related_message_id_post_id,
    regexp_replace(rp.raw, '(https?://[^\s]+)', '', 'g') AS related_message_text, -- 仅移除 URL
    fd.reviewed_at,
    fd.reviewed_by_username AS reviewed_by,
    rd.decision_name AS review_decision,
    CASE 
        WHEN fd.user_silenced_till IS NOT NULL THEN 'User silenced'
        WHEN fd.user_suspended_till IS NOT NULL THEN 'User suspended'
        WHEN fd.post_deleted_at IS NOT NULL THEN 'Post deleted'
        WHEN fd.post_hidden_at IS NOT NULL THEN 'Post hidden'
        ELSE 'No action taken'
    END AS action_taken,
    CASE 
        WHEN fd.reviewed_at IS NOT NULL THEN ROUND(EXTRACT(EPOCH FROM (fd.reviewed_at - fd.flagged_date)) / 60, 2)
        ELSE NULL
    END AS review_time_minutes -- 时间差（分钟）
FROM
    flag_data fd
LEFT JOIN post_actions pa 
    ON pa.post_id = fd.post_id
LEFT JOIN posts rp 
    ON pa.related_post_id = rp.id
LEFT JOIN flag_types ft 
    ON pa.post_action_type_id = ft.post_action_type_id
LEFT JOIN review_decisions rd 
    ON fd.review_status = rd.status_code
ORDER BY
    fd.flagged_date DESC

使用的参数

:start_date: 用于筛选被标记帖子的起始日期。
:end_date: 用于筛选被标记帖子的结束日期。

CTE 解释

flag_data: 检索有关被标记帖子的详细信息，包括标记 ID、被标记项目、标记类型、标记原因、标记来源和审查详情。
review_decisions: 将审查状态代码映射为人类可读的决策名称（例如：待处理、同意、不同意、忽略）。
flag_types: 将帖子操作类型 ID 映射为人类可读的标记类型名称（例如：离题、不当、垃圾信息）。

结果解释

标记 ID: 标记的唯一标识符。
被标记项目: 被标记帖子的 ID。
标记者用户名: 标记该帖子的用户的用户名。
标记日期: 创建标记的日期。
标记类型: 标记的数字类型。
标记类型名称: 标记类型的人类可读名称（例如：离题、垃圾信息）。
管理员可审查: 指示标记是由用户还是系统提出的。
标记原因: 提供标记的原因。
被标记项目文本: 被标记帖子的内容。
相关消息 ID: 任何相关消息的 ID（如果适用）。
相关消息文本: 相关消息的内容，为清晰起见移除了 URL。
审查者用户名: 审查标记的管理员的用户名。
审查决策: 审查者做出的决策（例如：同意、不同意、忽略、删除）。
采取的行动: 因标记而采取的行动，例如禁言或暂停用户、删除或隐藏帖子，或未采取行动。
审查时间（分钟）: 审查标记所花费的时间，计算为标记创建时间和审查时间之间的差值，以分钟为单位。

示例结果（匿名化）

标记 ID	被标记项目	标记者用户名	标记日期	标记类型	标记类型名称	管理员可审查	标记原因	被标记项目文本	相关消息 ID	相关消息文本	审查者用户名	审查决策	采取的行动	审查时间（分钟）
12345	67890	user123	2025-04-01 12:00	8	Spam	true	垃圾内容	“立即在 spam.com 购买”	98765	“看看这个！”	mod456	Agreed	帖子已删除	15.25
12346	67891	user124	2025-04-02 14:30	4	Inappropriate	false	冒犯性	“这不合适！”	NULL	NULL	mod457	Disagreed	未采取行动	30.50

提交的标记报告

此报告概述了指定日期范围内提交的标记。它按类型（例如：垃圾信息、不当）对标记进行分类，并区分用户报告的标记和系统生成的标记。该报告包括每种类型的标记总数，有助于识别平台上最常见的被标记问题。

-- [params]
-- date :start_date = 2020-01-01
-- date :end_date = 2026-01-01

WITH flag_data AS (
    SELECT
        r.id AS flag_id,
        p.id AS post_id,
        p.topic_id,
        p.raw AS flagged_item_text,
        p.user_id AS post_author_id,
        fu.username AS flagged_by_username,
        r.created_at AS flagged_date,
        r.type AS flag_type,
        r.reviewable_by_moderator AS flag_source,
        r.payload AS flag_reason,
        r.status AS review_status,
        rs.reviewed_by_id,
        rs.reviewed_at,
        rs.score AS review_score,
        ru.username AS reviewed_by_username
    FROM
        reviewables r
    LEFT JOIN posts p ON r.target_id = p.id AND r.target_type = 'Post'
    LEFT JOIN users fu ON r.created_by_id = fu.id
    LEFT JOIN reviewable_scores rs ON rs.reviewable_id = r.id
    LEFT JOIN users ru ON rs.reviewed_by_id = ru.id
    WHERE
        r.created_at BETWEEN :start_date AND :end_date
),
flag_types AS (
    SELECT
        3 AS post_action_type_id, 'Off-topic' AS flag_type_name
    UNION ALL
    SELECT
        4 AS post_action_type_id, 'Inappropriate' AS flag_type_name
    UNION ALL
    SELECT
        6 AS post_action_type_id, 'Notify_user' AS flag_type_name
    UNION ALL
    SELECT
        7 AS post_action_type_id, 'Notify_moderators' AS flag_type_name
    UNION ALL
    SELECT
        8 AS post_action_type_id, 'Spam' AS flag_type_name
    UNION ALL
    SELECT
        10 AS post_action_type_id, 'Illegal' AS flag_type_name
    UNION ALL
    SELECT
        NULL AS post_action_type_id, 'Something else' AS flag_type_name
)
SELECT
    ft.flag_type_name AS Type,
    COUNT(CASE WHEN fd.flagged_by_username NOT IN ('spam_scanner_bot', 'system') THEN 1 END) AS Reported,
    COUNT(CASE WHEN fd.flagged_by_username IN ('spam_scanner_bot', 'system') THEN 1 END) AS Automated,
    COUNT(*) AS Total
FROM
    flag_data fd
LEFT JOIN post_actions pa 
    ON pa.post_id = fd.post_id
LEFT JOIN flag_types ft 
    ON pa.post_action_type_id = ft.post_action_type_id
GROUP BY
    ft.flag_type_name
ORDER BY
    Total DESC

使用的参数

:start_date: 用于筛选标记帖子的开始日期。
:end_date: 用于筛选标记帖子的结束日期。

CTE 说明

flag_data：
此 CTE 检索有关标记帖子的详细信息，包括：

标记的唯一 ID (flag_id)、被标记帖子的 ID (post_id) 以及其所属的主题 (topic_id)。
标记本身的信息，例如标记该帖子的用户 (flagged_by_username)、标记日期 (flagged_date)、标记类型 (flag_type) 以及标记原因 (flag_reason)。
审核过程的详细信息，包括审核标记的管理员 (reviewed_by_username)、审核决定 (review_status) 以及审核时间 (reviewed_at)。

flag_types：
此 CTE 将数字帖子操作类型 ID 映射为人类可读的标记类型名称：

3: 离题
4: 不当
6: 通知用户
7: 通知管理员
8: 垃圾信息
10: 非法
NULL: 其他

结果说明

最终查询按标记类型聚合标记数据，并提供以下指标：

类型：标记类型的人类可读名称（例如，离题、垃圾信息）。
用户报告：由用户提交的标记数量（不包括系统生成的标记）。
自动：由系统或机器人生成的标记数量（例如，spam_scanner_bot、system）。
总计：每种类型的标记总数。

结果按标记总数降序排列。

示例结果

类型	用户报告	自动	总计
垃圾信息	120	80	200
不当	90	10	100
离题	60	5	65
通知管理员	30	0	30
非法	10	2	12
其他	5	0	5

封禁和暂停：

此报告列出了在指定日期范围内被暂停或禁言的用户。其中包括暂停或禁言日期、操作持续时间以及用户账户创建和最后活动日期等详细信息。此报告有助于监控管理操作，并识别导致封禁或禁言的用户行为模式。

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2026-01-01
SELECT 
    u.id AS user_id,
    u.username,
    u.name,
    u.suspended_at,
    u.suspended_till,
    u.silenced_till,
    u.created_at AS account_created_at,
    u.last_seen_at,
    u.flag_level,
    u.admin,
    u.moderator
FROM 
    users u
WHERE 
    (
        u.suspended_at BETWEEN :start_date AND :end_date
        OR u.silenced_till BETWEEN :start_date AND :end_date
    )
ORDER BY 
    u.suspended_at DESC NULLS LAST,
    u.silenced_till DESC NULLS LAST

使用的参数

:start_date: 用于筛选被暂停或禁言用户的开始日期。
:end_date: 用于筛选被暂停或禁言用户的结束日期。

结果说明

此查询检索在指定日期范围内被暂停或禁言的用户列表。关键列包括：

用户 ID：用户的唯一标识符。
用户名：用户的用户名。
姓名：用户的全名（如果可用）。
暂停时间：用户被暂停的日期。
暂停至：用户被暂停的截止日期。
禁言至：用户被禁言的截止日期。
账户创建时间：用户账户创建的日期。
最后在线时间：用户在平台上最后活跃的时间。
标记等级：用户当前的标记等级。
管理员：用户是否为管理员（真/假）。
版主：用户是否为版主（真/假）。

结果按暂停日期 (suspended_at) 和禁言日期 (silenced_till) 降序排列，空值排在最后。

示例结果

用户 ID	用户名	姓名	暂停时间	暂停至	禁言至	账户创建时间	最后在线时间	标记等级	管理员	版主
101	user123	John Doe	2025-03-15 10:00	2025-04-15 10:00	NULL	2020-01-01 12:00	2025-03-14 18:00	2	false	false
102	user456	Jane Smith	NULL	NULL	2025-03-20 18:00	2021-06-10 15:00	2025-03-19 20:00	1	false	false
103	mod789	Moderator1	2025-02-01 08:00	2025-03-01 08:00	NULL	2019-05-05 10:00	2025-01-31 22:00	3	false	true
104	admin001	AdminUser	NULL	NULL	2025-03-25 12:00	2018-12-25 09:00	2025-03-24 16:00	0	true	false

已同意的标记操作

此报告侧重于版主同意并采取了行动的标记。它按类型对标记进行分类，并提供诸如标记总数、处理标记的中位时间以及结果（例如，用户被禁言、帖子被删除）等指标。

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

WITH flag_data AS (
    SELECT
        r.id AS flag_id,
        p.id AS post_id,
        p.topic_id,
        p.raw AS flagged_item_text,
        p.user_id AS post_author_id,
        fu.username AS flagged_by_username,
        r.created_at AS flagged_date,
        r.type AS flag_type,
        r.reviewable_by_moderator AS flag_source,
        r.payload AS flag_reason,
        r.status AS review_status,
        rs.reviewed_by_id,
        rs.reviewed_at,
        rs.score AS review_score,
        ru.username AS reviewed_by_username,
        p.deleted_at AS post_deleted_at,
        u.silenced_till AS user_silenced_till,
        u.suspended_till AS user_suspended_till,
        p.hidden_at AS post_hidden_at,
        pa.post_action_type_id
    FROM
        reviewables r
    LEFT JOIN posts p ON r.target_id = p.id AND r.target_type = 'Post'
    LEFT JOIN users fu ON r.created_by_id = fu.id
    LEFT JOIN reviewable_scores rs ON rs.reviewable_id = r.id
    LEFT JOIN users ru ON rs.reviewed_by_id = ru.id
    LEFT JOIN users u ON p.user_id = u.id
    LEFT JOIN post_actions pa ON pa.post_id = p.id
    WHERE
        r.created_at BETWEEN :start_date AND :end_date
        AND r.status = 1 -- 仅包括已同意并采取行动的标记
),
flag_types AS (
    SELECT
        3 AS post_action_type_id, 'Off-topic' AS flag_type_name
    UNION ALL
    SELECT
        4 AS post_action_type_id, 'Inappropriate' AS flag_type_name
    UNION ALL
    SELECT
        6 AS post_action_type_id, 'Notify_user' AS flag_type_name
    UNION ALL
    SELECT
        7 AS post_action_type_id, 'Notify_moderators' AS flag_type_name
    UNION ALL
    SELECT
        8 AS post_action_type_id, 'Spam' AS flag_type_name
    UNION ALL
    SELECT
        10 AS post_action_type_id, 'Illegal' AS flag_type_name
),
median_time_to_act AS (
    SELECT
        COALESCE(ft.flag_type_name, fd.flag_type) AS flag_type,
        ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY EXTRACT(EPOCH FROM (fd.reviewed_at - fd.flagged_date))) / 60) AS median_time_minutes
    FROM
        flag_data fd
    LEFT JOIN flag_types ft 
        ON fd.post_action_type_id = ft.post_action_type_id
    WHERE
        fd.reviewed_at IS NOT NULL
        AND (
            ft.flag_type_name IS NOT NULL -- 包括映射的标记类型
            OR fd.flag_type IN (
                'ReviewableAkismetPost',
                'ReviewableUser',
                'ReviewableFlaggedPost',
                'ReviewableChatMessage',
                'ReviewablePost',
                'ReviewableQueuedPost'
            ) -- 包括 NULL 结果的特定标记类型
        )
    GROUP BY
        COALESCE(ft.flag_type_name, fd.flag_type)
)
SELECT
    COALESCE(ft.flag_type_name, fd.flag_type) AS Type,
    COUNT(CASE WHEN fd.flagged_by_username NOT IN ('spam_scanner_bot', 'system') THEN 1 END) AS Reported,
    COUNT(CASE WHEN fd.flagged_by_username IN ('spam_scanner_bot', 'system') THEN 1 END) AS Automated,
    COUNT(*) AS Total,
    COALESCE(mta.median_time_minutes, 0) AS "Median time to act (minutes)",
    COUNT(CASE WHEN fd.user_silenced_till IS NOT NULL THEN 1 END) AS "User silenced",
    COUNT(CASE WHEN fd.user_suspended_till IS NOT NULL THEN 1 END) AS "User deleted",
    COUNT(CASE WHEN fd.post_deleted_at IS NOT NULL THEN 1 END) AS "Post deleted",
    COUNT(CASE WHEN fd.post_hidden_at IS NOT NULL THEN 1 END) AS "Post hidden"

FROM
    flag_data fd
LEFT JOIN flag_types ft 
    ON fd.post_action_type_id = ft.post_action_type_id
LEFT JOIN median_time_to_act mta 
    ON COALESCE(ft.flag_type_name, fd.flag_type) = mta.flag_type
WHERE
    ft.flag_type_name IS NOT NULL -- 包括映射的标记类型
    OR fd.flag_type IN (
        'ReviewableAkismetPost',
        'ReviewableUser',
        'ReviewableFlaggedPost',
        'ReviewableChatMessage',
        'ReviewablePost',
        'ReviewableQueuedPost'
    ) -- 包括 NULL 结果的特定标记类型
GROUP BY
    COALESCE(ft.flag_type_name, fd.flag_type), mta.median_time_minutes
ORDER BY
    Total DESC

使用的参数

:start_date: 用于筛选已同意标记的开始日期。
:end_date: 用于筛选已同意标记的结束日期。

CTE 说明

flag_data：
此 CTE 检索有关已同意并采取行动的标记的详细信息。它包括：

标记的唯一 ID (flag_id)、被标记帖子的 ID (post_id) 以及其所属的主题 (topic_id)。
标记本身的信息，例如标记该帖子的用户 (flagged_by_username)、标记日期 (flagged_date)、标记类型 (flag_type) 以及标记原因 (flag_reason)。
审核过程的详细信息，包括审核标记的管理员 (reviewed_by_username)、审核决定 (review_status) 以及审核时间 (reviewed_at)。
有关被标记帖子的其他信息，例如是否被删除、隐藏，或者作者是否被禁言或暂停。

flag_types：
此 CTE 将数字帖子操作类型 ID 映射为人类可读的标记类型名称：

3: 离题
4: 不当
6: 通知用户
7: 通知管理员
8: 垃圾信息
10: 非法

median_time_to_act：
此 CTE 计算处理每种标记类型所需的中位时间（以分钟为单位）。时间计算为标记创建时间 (flagged_date) 与审核时间 (reviewed_at) 之间的差值。

结果说明

最终查询按标记类型聚合已同意的标记数据，并提供以下指标：

类型：标记类型的人类可读名称（例如，离题、垃圾信息）。
用户报告：由用户提交的标记数量（不包括系统生成的标记）。
自动：由系统或机器人生成的标记数量（例如，spam_scanner_bot、system）。
总计：每种类型的标记总数。
处理中位时间（分钟）：处理此类标记所需的中位时间，以分钟为单位。
用户被禁言：导致用户被禁言的标记数量。
用户被删除：导致用户被暂停的标记数量。
帖子被删除：导致帖子被删除的标记数量。
帖子被隐藏：导致帖子被隐藏的标记数量。

结果按标记总数降序排列。

示例结果

类型	用户报告	自动	总计	处理中位时间（分钟）	用户被禁言	用户被删除	帖子被删除	帖子被隐藏
垃圾信息	100	50	150	30	20	10	50	30
不当	80	5	85	45	15	5	30	20
离题	40	2	42	25	5	0	10	15
通知管理员	20	0	20	60	0	0	5	10
非法	5	1	6	120	1	1	3	2

采取的管理操作

此报告提供了指定日期范围内采取的管理操作的摘要。它聚合了各种类型的已同意操作，包括由用户或自动化系统标记的内容、被删除或隐藏的帖子、发出的警告、被删除或暂停的账户，以及被长期禁言的用户。每个类别都展示了案例总数，提供了管理活动的高层概览。

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

WITH flag_data AS (
    SELECT
        r.id AS flag_id,
        p.id AS post_id,
        p.topic_id,
        p.raw AS flagged_item_text,
        p.user_id AS post_author_id,
        fu.username AS flagged_by_username,
        r.created_at AS flagged_date,
        r.type AS flag_type,
        r.reviewable_by_moderator AS flag_source,
        r.payload AS flag_reason,
        r.status AS review_status,
        rs.reviewed_by_id,
        rs.reviewed_at,
        rs.score AS review_score,
        ru.username AS reviewed_by_username,
        p.deleted_at AS post_deleted_at,
        u.silenced_till AS user_silenced_till,
        u.suspended_till AS user_suspended_till,
        p.hidden_at AS post_hidden_at,
        pa.post_action_type_id
    FROM
        reviewables r
    LEFT JOIN posts p ON r.target_id = p.id AND r.target_type = 'Post'
    LEFT JOIN users fu ON r.created_by_id = fu.id
    LEFT JOIN reviewable_scores rs ON rs.reviewable_id = r.id
    LEFT JOIN users ru ON rs.reviewed_by_id = ru.id
    LEFT JOIN users u ON p.user_id = u.id
    LEFT JOIN post_actions pa ON pa.post_id = p.id
    WHERE
        r.created_at BETWEEN :start_date AND :end_date
        AND r.status = 1 -- 仅包括已同意并采取行动的标记
),
flag_types AS (
    SELECT
        3 AS post_action_type_id, 'Off-topic' AS flag_type_name
    UNION ALL
    SELECT
        4 AS post_action_type_id, 'Inappropriate' AS flag_type_name
    UNION ALL
    SELECT
        6 AS post_action_type_id, 'Notify_user' AS flag_type_name
    UNION ALL
    SELECT
        7 AS post_action_type_id, 'Notify_moderators' AS flag_type_name
    UNION ALL
    SELECT
        8 AS post_action_type_id, 'Spam' AS flag_type_name
    UNION ALL
    SELECT
        10 AS post_action_type_id, 'Illegal' AS flag_type_name
),
flagged_content AS (
    SELECT
        COUNT(CASE WHEN fd.flagged_by_username NOT IN ('spam_scanner_bot', 'system') THEN 1 END) AS user_flagged,
        COUNT(CASE WHEN fd.flagged_by_username IN ('spam_scanner_bot', 'system') THEN 1 END) AS automation_flagged
    FROM
        flag_data fd
    LEFT JOIN flag_types ft 
        ON fd.post_action_type_id = ft.post_action_type_id
    WHERE
        ft.flag_type_name IS NOT NULL -- 包括映射的标记类型
        OR fd.flag_type IN (
            'ReviewableAkismetPost',
            'ReviewableUser',
            'ReviewableFlaggedPost',
            'ReviewableChatMessage',
            'ReviewablePost',
            'ReviewableQueuedPost'
        ) -- 包括 NULL 结果的特定标记类型
),
warnings_issued AS (
    SELECT
        COUNT(*) AS warnings_count
    FROM
        user_warnings
    WHERE
        created_at BETWEEN :start_date AND :end_date
),
violations_and_suspensions AS (
    SELECT
        COUNT(CASE 
            WHEN uh.action = 1 
                AND (
                    LOWER(uh.context) LIKE '%deleted via review queue%' OR
                    LOWER(uh.context) LIKE '%to be a spammer%' OR
                    LOWER(uh.context) LIKE '%review%' OR
                    LOWER(uh.context) LIKE '%reviewable user rejected%'
                ) 
            THEN 1 
        END) AS accounts_deleted,
        COUNT(CASE WHEN uh.action = 10 THEN 1 END) AS accounts_suspended
    FROM
        user_histories uh
    WHERE
        uh.created_at BETWEEN :start_date AND :end_date
),
posts_deleted_and_hidden AS (
    SELECT
        COUNT(CASE WHEN fd.post_deleted_at IS NOT NULL THEN 1 END) AS posts_deleted,
        COUNT(CASE WHEN fd.post_hidden_at IS NOT NULL THEN 1 END) AS posts_hidden
    FROM
        flag_data fd
),
silences_issued AS (
    SELECT
        COUNT(*) AS silences_count
    FROM
        user_histories uh
    WHERE
        uh.action = 30 -- silence_user
        AND uh.created_at BETWEEN :start_date AND :end_date
        AND EXISTS (
            SELECT 1
            FROM users u
            WHERE u.id = uh.target_user_id 
              AND u.silenced_till > (CAST(:start_date AS TIMESTAMP) + INTERVAL '10 years')
        )
)
SELECT
    'Content flagged by users' AS category,
    fc.user_flagged AS "Number of Cases"
FROM flagged_content fc

UNION ALL

SELECT
    'Content flagged by automation' AS category,
    fc.automation_flagged AS "Number of Cases"
FROM flagged_content fc

UNION ALL

SELECT
    'Posts deleted for violating terms' AS category,
    pdh.posts_deleted AS "Number of Cases"
FROM posts_deleted_and_hidden pdh

UNION ALL

SELECT
    'Posts hidden' AS category,
    pdh.posts_hidden AS "Number of Cases"
FROM posts_deleted_and_hidden pdh

UNION ALL

SELECT
    'Warnings Issued' AS category,
    wi.warnings_count AS "Number of Cases"
FROM warnings_issued wi

UNION ALL

SELECT
    'Accounts deleted' AS category,
    vs.accounts_deleted AS "Number of Cases"
FROM violations_and_suspensions vs

UNION ALL

SELECT
    'Accounts suspended' AS category,
    vs.accounts_suspended AS "Number of Cases"
FROM violations_and_suspensions vs

UNION ALL

SELECT
    'Users silenced for 10+ years' AS category,
    si.silences_count AS "Number of Cases"
FROM silences_issued si

使用的参数

:start_date: 用于筛选管理操作的开始日期。
:end_date: 用于筛选管理操作的结束日期。

结果说明

查询将管理操作聚合为类别，并提供每个类别的案例总数。类别包括：

用户标记的内容：由普通用户标记的帖子数量（不包括系统生成的标记）。
自动化标记的内容：由自动化系统或机器人标记的帖子数量（例如，spam_scanner_bot、system）。
因违反条款而被删除的帖子：因违反社区准则或服务条款而被删除的帖子数量。
被隐藏的帖子：因各种原因被隐藏（但未删除）的帖子数量。
发出的警告：因不当行为或内容向用户发出的警告数量。
被删除的账户：因违规（例如被标记为垃圾信息发送者或在审核队列中被拒绝）而被删除的用户账户数量。
被暂停的账户：因违规而被暂停特定时间的用户账户数量。
被禁言 10 年以上的用户：被无限期或长期（10 年以上）禁言的用户数量。

示例结果

类别	案例数
用户标记的内容	150
自动化标记的内容	100
因违反条款而被删除的帖子	50
被隐藏的帖子	30
发出的警告	20
被删除的账户	10
被暂停的账户	15
被禁言 10 年以上的用户	5

采取的个人管理操作

此报告提供了指定日期范围内采取的个人管理操作的详细日志。其中包括执行操作的用户、目标用户、操作日期、操作类别（例如，标记内容、删除帖子、发出警告）以及操作的背景或原因等信息。此报告有助于审计特定的管理决定，并了解每个操作背后的背景。

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

WITH flag_data AS (
    SELECT
        r.id AS flag_id,
        p.id AS post_id,
        p.topic_id,
        p.raw AS flagged_item_text,
        p.user_id AS post_author_id,
        fu.username AS flagged_by_username,
        r.created_at AS flagged_date,
        r.type AS flag_type,
        r.reviewable_by_moderator AS flag_source,
        r.payload AS flag_reason,
        r.status AS review_status,
        rs.reviewed_by_id,
        rs.reviewed_at,
        rs.score AS review_score,
        ru.username AS reviewed_by_username,
        p.deleted_at AS post_deleted_at,
        u.silenced_till AS user_silenced_till,
        u.suspended_till AS user_suspended_till,
        p.hidden_at AS post_hidden_at,
        pa.post_action_type_id
    FROM
        reviewables r
    LEFT JOIN posts p ON r.target_id = p.id AND r.target_type = 'Post'
    LEFT JOIN users fu ON r.created_by_id = fu.id
    LEFT JOIN reviewable_scores rs ON rs.reviewable_id = r.id
    LEFT JOIN users ru ON rs.reviewed_by_id = ru.id
    LEFT JOIN users u ON p.user_id = u.id
    LEFT JOIN post_actions pa ON pa.post_id = p.id
    WHERE
        r.created_at BETWEEN :start_date AND :end_date
        AND r.status = 1 -- 仅包括已同意并采取行动的标记
),
flag_types AS (
    SELECT
        3 AS post_action_type_id, 'Off-topic' AS flag_type_name
    UNION ALL
    SELECT
        4 AS post_action_type_id, 'Inappropriate' AS flag_type_name
    UNION ALL
    SELECT
        6 AS post_action_type_id, 'Notify_user' AS flag_type_name
    UNION ALL
    SELECT
        7 AS post_action_type_id, 'Notify_moderators' AS flag_type_name
    UNION ALL
    SELECT
        8 AS post_action_type_id, 'Spam' AS flag_type_name
    UNION ALL
    SELECT
        10 AS post_action_type_id, 'Illegal' AS flag_type_name
),
flagged_content AS (
    SELECT
        fd.flagged_by_username AS acting_user,
        CAST(fd.post_author_id AS TEXT) AS target_user,
        fd.flagged_date AS action_date,
        'Content flagged by users' AS category,
        fd.flagged_item_text AS context
    FROM
        flag_data fd
    LEFT JOIN flag_types ft 
        ON fd.post_action_type_id = ft.post_action_type_id
    WHERE
        ft.flag_type_name IS NOT NULL
        AND fd.flagged_by_username NOT IN ('spam_scanner_bot', 'system')
    
    UNION ALL

    SELECT
        fd.flagged_by_username AS acting_user,
        CAST(fd.post_author_id AS TEXT) AS target_user,
        fd.flagged_date AS action_date,
        'Content flagged by automation' AS category,
        fd.flagged_item_text AS context
    FROM
        flag_data fd
    LEFT JOIN flag_types ft 
        ON fd.post_action_type_id = ft.post_action_type_id
    WHERE
        ft.flag_type_name IS NOT NULL
        AND fd.flagged_by_username IN ('spam_scanner_bot', 'system')
),
posts_deleted_and_hidden AS (
    SELECT
        fd.reviewed_by_username AS acting_user,
        CAST(fd.post_author_id AS TEXT) AS target_user,
        fd.post_deleted_at AS action_date,
        'Posts deleted for violating terms' AS category,
        fd.flagged_item_text AS context
    FROM
        flag_data fd
    WHERE
        fd.post_deleted_at IS NOT NULL
    
    UNION ALL

    SELECT
        fd.reviewed_by_username AS acting_user,
        CAST(fd.post_author_id AS TEXT) AS target_user,
        fd.post_hidden_at AS action_date,
        'Posts hidden' AS category,
        fd.flagged_item_text AS context
    FROM
        flag_data fd
    WHERE
        fd.post_hidden_at IS NOT NULL
),
warnings_issued AS (
    SELECT
        CAST(uw.created_by_id AS TEXT) AS acting_user,
        CAST(uw.user_id AS TEXT) AS target_user,
        uw.created_at AS action_date,
        'Warnings Issued' AS category,
        NULL AS context
    FROM
        user_warnings uw
    WHERE
        uw.created_at BETWEEN :start_date AND :end_date
),
violations_and_suspensions AS (
    SELECT
        CAST(uh.acting_user_id AS TEXT) AS acting_user,
        CAST(uh.target_user_id AS TEXT) AS target_user,
        uh.created_at AS action_date,
        'Accounts deleted' AS category,
        uh.context AS context
    FROM
        user_histories uh
    WHERE
        uh.created_at BETWEEN :start_date AND :end_date
        AND uh.action = 1
        AND (
            LOWER(uh.context) LIKE '%deleted via review queue%' OR
            LOWER(uh.context) LIKE '%to be a spammer%' OR
            LOWER(uh.context) LIKE '%review%' OR
            LOWER(uh.context) LIKE '%reviewable user rejected%'
        )
    
    UNION ALL

    SELECT
        CAST(uh.acting_user_id AS TEXT) AS acting_user,
        CAST(uh.target_user_id AS TEXT) AS target_user,
        uh.created_at AS action_date,
        'Accounts suspended' AS category,
        uh.context AS context
    FROM
        user_histories uh
    WHERE
        uh.created_at BETWEEN :start_date AND :end_date
        AND uh.action = 10
),
silences_issued AS (
    SELECT
        CAST(uh.acting_user_id AS TEXT) AS acting_user,
        CAST(uh.target_user_id AS TEXT) AS target_user,
        uh.created_at AS action_date,
        'Users silenced for 10+ years' AS category,
        uh.context AS context
    FROM
        user_histories uh
    WHERE
        uh.action = 30 -- silence_user
        AND uh.created_at BETWEEN :start_date AND :end_date
        AND EXISTS (
            SELECT 1
            FROM users u
            WHERE u.id = uh.target_user_id 
              AND u.silenced_till > (CAST(:start_date AS TIMESTAMP) + INTERVAL '10 years')
        )
)
SELECT
    acting_user,
    target_user,
    action_date,
    category,
    context
FROM flagged_content

UNION ALL

SELECT
    acting_user,
    target_user,
    action_date,
    category,
    context
FROM posts_deleted_and_hidden

UNION ALL

SELECT
    acting_user,
    target_user,
    action_date,
    category,
    context
FROM warnings_issued

UNION ALL

SELECT
    acting_user,
    target_user,
    action_date,
    category,
    context
FROM violations_and_suspensions

UNION ALL

SELECT
    acting_user,
    target_user,
    action_date,
    category,
    context
FROM silences_issued

使用的参数

:start_date: 用于筛选个人管理操作的开始日期。
:end_date: 用于筛选个人管理操作的结束日期。

结果说明

查询提供了个人管理操作的详细日志，包括：

执行用户：执行操作的管理员、系统或用户的用户名。
目标用户：作为操作对象的用户（例如，被标记帖子的作者或收到警告的接收者）的用户名或 ID。
操作日期：操作发生的日期和时间。
类别：管理操作的类型，例如：

用户标记的内容
自动化标记的内容
因违反条款而被删除的帖子
被隐藏的帖子
发出的警告
被删除的账户
被暂停的账户
被禁言 10 年以上的用户

背景：与操作相关的其他信息或内容，例如被标记帖子的文本或暂停的原因。

示例结果

执行用户	目标用户	操作日期	类别	背景
user123	user456	2024-02-01 10:00	用户标记的内容	“此帖子包含垃圾信息内容。”
spam_scanner	user789	2024-02-02 12:00	自动化标记的内容	“被系统检测为垃圾信息。”
mod001	user456	2024-02-03 14:00	因违反条款而被删除的帖子	“示例帖子内容
mod002	user123	2024-02-04 16:00	被隐藏的帖子	“帖子被视为不当。”
admin001	user789	2024-02-05 18:00	发出的警告	NULL
admin002	user456	2024-02-06 20:00	被删除的账户	“账户通过审核队列被删除。”
mod003	user123	2024-02-07 22:00	被暂停的账户	“用户因重复发送垃圾信息被暂停。”
admin003	user789	2024-02-08 08:00	被禁言 10 年以上的用户	“用户因严重违规被禁言。”

话题		回复	浏览量
Dashboard Report - Flags Status Data & reporting sql-query , dashboard-reports , dashboard-sql	0	430	2023 年12 月 22 日
Dashboard Report - User Flagging Ratio Data & reporting sql-query , dashboard-reports , dashboard-sql	0	358	2024 年2 月 6 日
Dashboard Report - Moderator Activity Data & reporting sql-query , dashboard-reports , dashboard-sql	5	791	2024 年10 月 30 日
Statistics on spam flags reported by AI triage automation Data & reporting ai , sql-query , spam	3	146	2025 年5 月 31 日
Show what action was taken and by who for Flags Feature	2	914	2014 年4 月 12 日

分析版主和举报活动报告

按类型划分的帖子标记解决百分比

SQL 查询解释

使用的参数

CTE 解释

结果解释

示例结果

待审项目解决百分比

SQL 查询解释

使用的参数

CTE 解释

结果解释

示例结果

管理员标记解决情况

SQL 查询解释

使用的参数

CTE 解释

结果解释

示例结果

谁在标记帖子

SQL 查询解释

使用的参数

结果解释

示例结果

用户备注

SQL 查询解释

使用的参数

结果解释

示例结果

管理员 KPI - 标记和平均标记解决时间

SQL 查询解释

使用的参数

CTE 解释

结果解释

示例结果

所有标记数据

SQL 查询解释

使用的参数

CTE 解释

结果解释

示例结果（匿名化）

示例结果（匿名化）

提交的标记报告

使用的参数

CTE 说明

结果说明

示例结果

封禁和暂停：

使用的参数

结果说明

示例结果

已同意的标记操作

使用的参数

CTE 说明

结果说明

示例结果

采取的管理操作

使用的参数

结果说明

示例结果

采取的个人管理操作

使用的参数

结果说明

示例结果

相关话题