我的论坛规模不大，大部分点赞都来自“员工”（管理员、版主、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 更改摘要
- 生成员工组：
添加了一个 staff_users CTE 来识别 users 表中的员工用户。员工用户被定义为满足以下任一条件的任何人：
admin = truemoderator = truetrust_level = 4
- 分离员工点赞：
添加了一个子查询，通过过滤 post_actions 中 user_id 在 staff_users 组内的点赞来计算员工用户的点赞数（staff_likes_count）。
- 重命名非员工点赞列：
将非员工点赞的输出标签从 likes_count 更改为 regular_likes_count。
- 添加总点赞数：
引入了一个 total_likes 列来汇总 regular_likes_count 和 staff_likes_count。
- 添加每日帖子数：
添加了一个子查询来计算每日帖子数（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），但我需要进行以下增强，以生成按周汇总并包含附加详细信息的最终输出：
- 定义员工组：
- 从
users 表创建一个 staff_users 组。如果用户满足以下任一条件，则应将其视为员工：
admin = truemoderator = truetrust_level = 4
- 按员工和非员工分离点赞：
- 添加两个单独的列：
regular_likes_count：计算非员工用户的点赞数。staff_likes_count：计算员工用户的点赞数。
- 确保
regular_likes_count 列不包括员工用户产生的点赞。
- 添加总点赞数：
- 包含一个
total_likes 列，该列汇总了 regular_likes_count 和 staff_likes_count。
- 添加每周期帖子数：
- 添加一个
posts_per_week 列，计算每周创建的帖子数量。
- 按周汇总：
- 修改查询以按周间隔对所有数据进行分组，而不是按天分组。
- 包含一个
week_start 列，表示每周的开始日期。
- 按周限制：
- 引入一个
:weeks_ago 参数，将结果限制为过去 N 周。默认值为 52 周（1 年）。
- 排序和最终列：
- 确保输出按
week_start 排序，并按此顺序包含以下列：
week_start：周的开始日期。regular_likes_count：非员工用户的点赞计数。staff_likes_count：员工用户的点赞计数。total_likes：regular_likes_count 和 staff_likes_count 的总和。posts_per_week：每周创建的帖子数量。