Я хочу предоставить нашей команде статистику по количеству текущих активных пользователей, используя следующее определение «Активный пользователь»:
Пользователи, которые либо читали, будучи авторизованными, либо публиковали сообщения на нашем форуме за последний год.
У нас много пользователей в режиме подписки на рассылку, которые отвечают по электронной почте, поэтому мы упустим многих, если просто соберём количество читателей согласно этому запросу:
Я также могу получить количество тех, кто опубликовал сообщения здесь:
Мне просто не хватает ума, чтобы объединить их с помощью оператора OR, чтобы охватить обе группы. Можете помочь?
Это должно вернуть всех пользователей, которые либо читали, будучи авторизованными, либо публиковали сообщения за последний год. Data Explorer также покажет их количество.
SELECT p.user_id
FROM posts p
LEFT JOIN topics t ON t.id = p.topic_id
WHERE p.created_at::date > CURRENT_TIMESTAMP - INTERVAL '365 days'
AND t.deleted_at IS NULL
AND t.visible = TRUE
AND t.closed = FALSE
AND t.archived = FALSE
AND t.archetype = 'regular'
AND p.deleted_at IS NULL
UNION
SELECT u.user_id
FROM user_visits u
WHERE u.posts_read > 0
AND u.visited_at > CURRENT_TIMESTAMP - INTERVAL '365 days'
ORDER BY user_id
Возможно, существует более эффективный способ сделать это, но данный вариант работает . Если вам нужен конкретный период, вы можете заменить обе части > CURRENT_TIMESTAMP - INTERVAL '365 days' на что-то вроде этого: BETWEEN '20200101'::date AND '20210101'::date.
Вы предоставили отличную информацию и ссылки в своём вопросе! Мне осталось только выбрать нужное и объединить это.
В итоге я сделал кое-что немного иное, так как хотел получить больше информации, а UNION оказался немного ограничивающим. Кроме того, я хотел разбить данные по группам. Мне удалось добиться успеха (надеюсь), объединив включённый запрос «Активные наблюдатели» с другим моим запросом для извлечения информации, специфичной для групп:
-- [params]
-- int :number_of_days = 365
-- string :group_name = trust_level_0
With included_users AS (
SELECT
gu.user_id
FROM group_users gu
JOIN groups g
ON g.id = gu.group_id
WHERE g.name = :group_name
),
posts_by_user AS (
SELECT COUNT(*) AS posts, p.user_id
FROM posts p
LEFT JOIN topics t ON t.id = p.topic_id
WHERE p.created_at::date > CURRENT_TIMESTAMP - INTERVAL ':number_of_days' day
AND t.deleted_at IS NULL
AND t.visible = TRUE
AND t.closed = FALSE
AND t.archived = FALSE
AND t.archetype = 'regular'
AND p.deleted_at IS NULL
GROUP BY p.user_id
),
posts_read_by_user AS (
SELECT SUM(posts_read) AS posts_read, uv.user_id
FROM user_visits uv
WHERE uv.posts_read > 0
AND uv.visited_at > CURRENT_TIMESTAMP - INTERVAL ':number_of_days' day
GROUP BY uv.user_id
)
SELECT
u.id AS "user_id",
u.username_lower AS "username",
u.last_seen_at,
COALESCE(pbu.posts, 0) AS "posts_created",
COALESCE(prbu.posts_read, 0) AS "posts_read"
FROM users u
LEFT JOIN posts_by_user pbu ON pbu.user_id = u.id
LEFT JOIN posts_read_by_user prbu ON prbu.user_id = u.id
WHERE u.active = true
AND u.id > 0
AND u.id IN (SELECT user_id FROM included_users)
AND (COALESCE(pbu.posts, 0) > 0 OR COALESCE(prbu.posts_read, 0) > 0)
ORDER BY u.id
Запросы на получение значков довольно запутанные — для возврата данных достаточно указать только user_id и granted_at. Так что вам придётся немного поэкспериментировать.
Рекомендую внимательно изучить темы, посвящённые запросам значков, и попробовать сделать это самостоятельно. Также запускайте такой запрос не чаще одного раза в день, иначе нагрузка может оказаться слишком высокой.
. Если вам нужен конкретный период, вы можете заменить обе части