Мой форум не очень большой, и большинство наших реакций «лайк» поступают от «сотрудников» (администраторы, модераторы, 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 (спасибо, Сара!):
Сводка изменений SQL
- Генерация группы сотрудников:
Добавлен CTE staff_users для идентификации сотрудников из таблицы users. Сотрудником считается любой пользователь, который:
admin = truemoderator = truetrust_level = 4
- Разделение лайков сотрудников:
Добавлен подзапрос для подсчета количества лайков от сотрудников (staff_likes_count) путем фильтрации post_actions по user_id из группы staff_users.
- Переименование столбца лайков не-сотрудников:
Изменена метрика вывода для лайков не-сотрудников с likes_count на regular_likes_count.
- Добавление общего количества лайков:
Введен столбец total_likes для суммирования regular_likes_count и staff_likes_count.
- Добавление количества постов в день:
Добавлен подзапрос для подсчета количества постов в день (posts_per_day) и его соединение с диапазоном дат.
(Да, этот список изменений тоже составил для меня ChatGPT.)
Пример результатов:
| date |
regular_likes_count |
staff_likes_count |
posts_per_day |
| 1/1/24 |
0 |
6 |
7 |
| 1/2/24 |
0 |
5 |
3 |
| 1/3/24 |
1 |
0 |
4 |
| 1/4/24 |
1 |
2 |
5 |
| 1/5/24 |
9 |
9 |
30 |
| 1/6/24 |
0 |
1 |
11 |
| 1/7/24 |
2 |
4 |
11 |
| 1/8/24 |
0 |
5 |
18 |
| 1/9/24 |
0 |
0 |
2 |
| 1/10/24 |
0 |
0 |
7 |
| 1/11/24 |
0 |
4 |
5 |
| 1/12/24 |
4 |
0 |
4 |
| 1/13/24 |
6 |
0 |
10 |
| 1/14/24 |
1 |
7 |
18 |
| 1/15/24 |
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
На всякий случай, если это интересно, вот финальные промпты, которые модифицировали запрос Сары:
У меня есть SQL-запрос, который отчетливо показывает количество лайков (likes_count) в день между двумя датами, но мне нужно внести следующие улучшения, чтобы получить итоговый вывод, агрегирующий данные по неделям и включающий дополнительные детали:
- Определение группы сотрудников:
- Создать группу
staff_users из таблицы 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: количество постов, созданных за неделю.