Советы по работе с данными Common Discourse

Создание сообщества Данные и отчетность
1

Это хороший пример нескольких особенностей данных Discourse, которые помогут вам получать более точные результаты при построении запросов.

Поскольку большинство постов и тем «мягко удаляются» при их удалении, они по-прежнему остаются в базе данных и могут включаться в отчеты по пользовательской статистике. Поэтому полезно исключать их из любых результатов, которые вы хотите извлечь, например, для статистики пользователей или категорий. Аналогично, вы также можете захотеть исключить из статистики личные сообщения, а также системных пользователей.

Вот несколько советов, как это сделать:

Этот SQL-запрос используется для получения топ-10 пользователей (за исключением системных), сделавших наибольшее количество публичных постов, с исключением любых удалённых постов или постов из удалённых тем.

-- Выбор user_id из таблицы posts и подсчёт количества постов каждого пользователя
SELECT 
    p.user_id,
    COUNT(p.id) AS "Post Count"
FROM posts p
  -- Левое соединение с таблицей topics по topic_id
  LEFT JOIN topics t ON t.id = p.topic_id
WHERE 
  -- Исключаем темы личных сообщений
  t.archetype <> 'private_message'
  -- Исключаем удалённые темы
  AND t.deleted_at ISNULL
  -- Исключаем удалённые посты
  AND p.deleted_at ISNULL
  -- Исключаем whispers, посты с малым действием и посты модераторов
  AND p.post_type = 1
  -- Исключаем посты, сделанные системным пользователем и discobot (user_id 0 и -1)
  AND p.user_id > 0
-- Группировка результатов по user_id
GROUP BY 1
-- Сортировка результатов по количеству постов в порядке убывания
ORDER BY 2 DESC
-- Ограничение результатов топ-10 пользователей с наибольшим количеством постов
LIMIT 10

А вот этот же запрос без встроенных комментариев:

SELECT 
    p.user_id,
    COUNT(p.id) AS "Post Count"
FROM posts p
  LEFT JOIN topics t ON t.id = p.topic_id
WHERE t.archetype <> 'private_message'
  AND t.deleted_at ISNULL
  AND p.deleted_at ISNULL
  AND p.post_type = 1
  AND p.user_id > 0
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10

Это лишь простой запрос для демонстрации примеров, но, надеемся, он поможет вам начать работу с Data Explorer. :+1: Если у вас возникнут вопросы, пожалуйста, задайте их ниже. :slight_smile:

2

Вы имели в виду сообщения здесь?

любые сообщения или сообщения из[…]

звучит для меня странно.

3

Я так не думаю? Это должно означать, что посты в удалённых темах не включаются. Использование слова «сообщения» создаёт впечатление, что речь идёт о личных сообщениях. Но я ещё раз посмотрю, как описать это более понятно. :+1:

Ещё одна мысль —

Добавление AND p.post_type = 1 может быть хорошим решением и для этого случая. :thinking:

4

Я подумал, что это может быть связано с тем, что система также фильтрует личные сообщения и исключает любые сообщения, удалённые посты и посты из удалённых тем.

Для меня это звучит логичнее, чем

исключение любых постов или постов из тем, которые были удалены.

Повторно перечитав это сейчас, я вижу, что имелся в виду оригинальный смысл, так что всё на месте, просто не так ясно, как я ожидал, полагаю. :person_shrugging: