仪表板报告 - 用户笔记

数据与报告
, ,
1

这是用户笔记仪表板报告的 SQL 版本。

:discourse: 此报告需要启用 Discourse 用户笔记 插件。

此仪表板报告列出了在特定日期范围内由员工用户创建的用户笔记。用户笔记是版主或管理员添加到用户个人资料中的注释或评论，通常用于跟踪用户的行为、问题或重要信息。

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

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_by AS moderator_user_id,
    un.created_at::date,
    un.user_note as html$user_note
FROM user_notes un
JOIN users u ON u.id = un.user_id
WHERE un.created_at BETWEEN :start_date AND :end_date
ORDER BY un.created_at ASC

SQL 查询说明

此报告从 plugin_store_rows 表中提取这些笔记，这些笔记由 user_notes 插件以 JSON 格式存储，并以易于理解的格式呈现。

查询分几个步骤进行：

  • 参数：
    • 查询首先定义两个参数 :start_date 和 :end_date 来指定报告的时间段。两个日期参数都接受 YYYY-MM-DD 格式。
  • 通用表表达式 (CTE) - user_notes：查询以一个名为 user_notes 的 CTE 开始，该 CTE 从 plugin_store_rows 表中提取和转换相关数据。此表以键值格式存储各种插件数据，其中用户笔记的键前缀为 notes:，后跟用户 ID。CTE 执行以下操作：
    • 筛选 plugin_name 为 'user_notes' 的行，确保仅选择用户笔记数据。
    • 在 LATERAL 连接中使用 json_array_elements 函数将 value 列中存储的 JSON 数组展开为单个 JSON 对象，每个对象代表一条笔记。
    • 通过删除 notes: 前缀并将其结果转换为整数来从键中提取用户 ID。
    • 从 JSON 对象中提取笔记的创建日期、原始笔记内容以及创建笔记的用户的 ID。
  • 主查询：
    • 将 user_notes CTE 与 users 表连接，以确保仅包含现有用户的笔记。
    • 根据 created_at 日期筛选笔记，仅包含指定日期范围（:start_date 到 :end_date）内的笔记。
    • 选择用户 ID、版主用户 ID（笔记创建者）、笔记创建日期和笔记内容。
    • 按笔记创建日期升序对结果进行排序，以按时间顺序呈现笔记。

示例结果

user moderator_user created_at user_note
user_1 staff_user_2 2024-01-10 example user note with HTML formatting
user_3 staff_user_4 2024-01-14 this is an example note about user_3
… … … …
3 个赞