仪表盘报告 - 点赞

社区 数据与报告
, ,
4

我的论坛规模不大，大部分点赞都来自“员工”（管理员、版主、TL=4）。我想看看普通用户和“员工”的点赞数量对比，并列出每天的帖子数量，以便更好地了解情况，以及我们需要在哪里集中精力来改进点赞的使用。

我和我的朋友 ChatGPT 想出了这个：

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-12-31

WITH date_range AS (
  SELECT date_trunc('day', series) AS date
  FROM generate_series(
    :start_date::timestamp,
    :end_date::timestamp,
    '1 day'::interval
  ) series
),
staff_users AS (
  SELECT id
  FROM users
  WHERE admin = true OR moderator = true OR trust_level = 4
)

SELECT
  dr.date::date,
  COALESCE(pa_non_staff.regular_likes_count, 0) AS regular_likes_count,
  COALESCE(pa_staff.staff_likes_count, 0) AS staff_likes_count,
  COALESCE(pa_non_staff.regular_likes_count, 0) + COALESCE(pa_staff.staff_likes_count, 0) AS total_likes,
  COALESCE(posts_count.posts_per_day, 0) AS posts_per_day
FROM
  date_range dr
LEFT JOIN (
  SELECT
    date_trunc('day', pa.created_at) AS action_date,
    COUNT(*) AS regular_likes_count
  FROM post_actions pa
  WHERE pa.post_action_type_id = 2
    AND pa.user_id NOT IN (SELECT id FROM staff_users)
    AND pa.created_at >= :start_date
    AND pa.created_at <= (:end_date::date + 1)
  GROUP BY action_date
) pa_non_staff ON dr.date = pa_non_staff.action_date
LEFT JOIN (
  SELECT
    date_trunc('day', pa.created_at) AS action_date,
    COUNT(*) AS staff_likes_count
  FROM post_actions pa
  WHERE pa.post_action_type_id = 2
    AND pa.user_id IN (SELECT id FROM staff_users)
    AND pa.created_at >= :start_date
    AND pa.created_at <= (:end_date::date + 1)
  GROUP BY action_date
) pa_staff ON dr.date = pa_staff.action_date
LEFT JOIN (
  SELECT
    date_trunc('day', p.created_at) AS post_date,
    COUNT(*) AS posts_per_day
  FROM posts p
  WHERE p.created_at >= :start_date
    AND p.created_at <= (:end_date::date + 1)
  GROUP BY post_date
) posts_count ON dr.date = posts_count.post_date
ORDER BY dr.date

对 @SaraDev 的原始查询进行的更改（谢谢你，Sara！）：
SQL 更改摘要

  1. 生成员工组：
    添加了一个 staff_users CTE 来识别 users 表中的员工用户。员工用户被定义为满足以下任一条件的任何人：
    • admin = true
    • moderator = true
    • trust_level = 4
  2. 分离员工点赞：
    添加了一个子查询，通过过滤 post_actions 中 user_id 在 staff_users 组内的点赞来计算员工用户的点赞数（staff_likes_count）。
  3. 重命名非员工点赞列：
    将非员工点赞的输出标签从 likes_count 更改为 regular_likes_count。
  4. 添加总点赞数：
    引入了一个 total_likes 列来汇总 regular_likes_count 和 staff_likes_count。
  5. 添加每日帖子数：
    添加了一个子查询来计算每日帖子数（posts_per_day）并将其连接到日期范围。
    （是的，ChatGPT 也为我列出了这些更改。）

示例结果：

日期 regular_likes_count staff_likes_count posts_per_day
24/1/1 0 6 7
24/1/2 0 5 3
24/1/3 1 0 4
24/1/4 1 2 5
24/1/5 9 9 30
24/1/6 0 1 11
24/1/7 2 4 11
24/1/8 0 5 18
24/1/9 0 0 2
24/1/10 0 0 7
24/1/11 0 4 5
24/1/12 4 0 4
24/1/13 6 0 10
24/1/14 1 7 18
24/1/15 2 4 7
按周报告的相同查询，以便平滑显示 
-- [params]
-- integer :weeks_ago = 52

WITH date_range AS (
  SELECT date_trunc('week', series) AS week_start
  FROM generate_series(
    date_trunc('week', now()) - (:weeks_ago || ' weeks')::interval,
    date_trunc('week', now()),
    '1 week'::interval
  ) series
),
staff_users AS (
  SELECT id
  FROM users
  WHERE admin = true OR moderator = true OR trust_level = 4
)

SELECT
  dr.week_start::date AS week_start,
  COALESCE(pa_non_staff.regular_likes_count, 0) AS regular_likes_count,
  COALESCE(pa_staff.staff_likes_count, 0) AS staff_likes_count,
  COALESCE(pa_non_staff.regular_likes_count, 0) + COALESCE(pa_staff.staff_likes_count, 0) AS total_likes,
  COALESCE(posts_count.posts_per_week, 0) AS posts_per_week
FROM
  date_range dr
LEFT JOIN (
  SELECT
    date_trunc('week', pa.created_at) AS action_week,
    COUNT(*) AS regular_likes_count
  FROM post_actions pa
  WHERE pa.post_action_type_id = 2
    AND pa.user_id NOT IN (SELECT id FROM staff_users)
    AND pa.created_at >= date_trunc('week', now()) - (:weeks_ago || ' weeks')::interval
    AND pa.created_at <= date_trunc('week', now())
  GROUP BY action_week
) pa_non_staff ON dr.week_start = pa_non_staff.action_week
LEFT JOIN (
  SELECT
    date_trunc('week', pa.created_at) AS action_week,
    COUNT(*) AS staff_likes_count
  FROM post_actions pa
  WHERE pa.post_action_type_id = 2
    AND pa.user_id IN (SELECT id FROM staff_users)
    AND pa.created_at >= date_trunc('week', now()) - (:weeks_ago || ' weeks')::interval
    AND pa.created_at <= date_trunc('week', now())
  GROUP BY action_week
) pa_staff ON dr.week_start = pa_staff.action_week
LEFT JOIN (
  SELECT
    date_trunc('week', p.created_at) AS post_week,
    COUNT(*) AS posts_per_week
  FROM posts p
  WHERE p.created_at >= date_trunc('week', now()) - (:weeks_ago || ' weeks')::interval
    AND p.created_at <= date_trunc('week', now())
  GROUP BY post_week
) posts_count ON dr.week_start = posts_count.post_week
ORDER BY dr.week_start
如果感兴趣，这是修改 Sara 查询的最终提示：

我有一个 SQL 查询，用于报告两个日期之间的每日点赞数（likes_count），但我需要进行以下增强，以生成按周汇总并包含附加详细信息的最终输出：

  1. 定义员工组：
    • 从 users 表创建一个 staff_users 组。如果用户满足以下任一条件，则应将其视为员工：
      • admin = true
      • moderator = true
      • trust_level = 4
  2. 按员工和非员工分离点赞：
    • 添加两个单独的列：
      • regular_likes_count：计算非员工用户的点赞数。
      • staff_likes_count：计算员工用户的点赞数。
    • 确保 regular_likes_count 列不包括员工用户产生的点赞。
  3. 添加总点赞数：
    • 包含一个 total_likes 列，该列汇总了 regular_likes_count 和 staff_likes_count。
  4. 添加每周期帖子数：
    • 添加一个 posts_per_week 列，计算每周创建的帖子数量。
  5. 按周汇总：
    • 修改查询以按周间隔对所有数据进行分组，而不是按天分组。
    • 包含一个 week_start 列，表示每周的开始日期。
  6. 按周限制：
    • 引入一个 :weeks_ago 参数，将结果限制为过去 N 周。默认值为 52 周（1 年）。
  7. 排序和最终列：
    • 确保输出按 week_start 排序，并按此顺序包含以下列：
      1. week_start：周的开始日期。
      2. regular_likes_count：非员工用户的点赞计数。
      3. staff_likes_count：员工用户的点赞计数。
      4. total_likes：regular_likes_count 和 staff_likes_count 的总和。
      5. posts_per_week：每周创建的帖子数量。