Прежде всего, с Новым годом! 
Подскажите, какой SQL-запрос использовать, чтобы получить среднее количество ответов на темы, сделанных пользователями (за исключением администраторов), в месяц.
Или же способ получить соотношение общего числа постов, опубликованных участниками, к общему числу постов, опубликованных сотрудниками, в месяц.
Спасибо!
С Новым годом (немного с опозданием 
)
Когда речь идет о среднем значении, вы имеете в виду темы, созданные как сотрудниками, так и не сотрудниками, но подсчет ответов только по сообщениям от не сотрудников? И вы хотите исключить только администраторов или и администраторов, и модераторов?
Привет, Jammy!
Точно, запрос может охватывать все темы (созданные как сотрудниками, так и не сотрудниками), но подсчёт ответов будет вестись только по постам от не сотрудников.
На данный момент мы можем исключать только администраторов (поскольку я только запускаю своё сообщество, администраторы и модераторы — это одни и те же лица :)).
Однако было бы здорово также легко получать соотношение тем от сотрудников к темам от не сотрудников (исключая администраторов).
По-моему, что-то вроде этого даст вам нужные цифры:
-- [params]
-- date :start_date
-- date :end_date
WITH staff_data AS (
SELECT user_id, true as is_staff
FROM group_users
WHERE group_id = 3
),
month_stats AS (
SELECT
date_trunc('month', p.created_at)::date AS month,
COUNT(*) FILTER (WHERE p.post_number = 1) AS total_topics,
COUNT(*) FILTER (WHERE p.post_number <> 1) AS total_posts,
COUNT(DISTINCT p.user_id) FILTER (WHERE p.post_number <> 1 AND is_staff IS NOT TRUE) AS non_staff_users,
COUNT(*) FILTER (WHERE p.post_number <> 1 AND is_staff IS NOT TRUE) AS non_staff_posts,
COUNT(DISTINCT p.user_id) FILTER (WHERE p.post_number <> 1 AND is_staff) AS staff_users,
COUNT(*) FILTER (WHERE p.post_number <> 1 AND is_staff) AS staff_posts
FROM posts p
LEFT JOIN topics t ON t.id = p.topic_id
LEFT JOIN staff_data s ON p.user_id = s.user_id
WHERE p.created_at::date BETWEEN :start_date AND :end_date
AND t.archetype = 'regular'
AND t.deleted_at IS NULL
AND p.deleted_at IS NULL
AND p.post_type = 1
AND p.user_id > 0
GROUP BY month
)
SELECT
ms.month AS "Месяц",
ms.total_topics AS "Все темы",
ms.total_posts AS "Все сообщения",
ms.non_staff_posts AS "Сообщения от не-сотрудников",
ROUND(ms.non_staff_posts * 100.0 / NULLIF(ms.total_posts, 0),1) || '%' AS "Сообщения от не-сотрудников (% от общего числа)",
ms.non_staff_posts / NULLIF(ms.total_topics, 0) AS "Среднее число сообщений от не-сотрудников на тему",
ms.non_staff_users AS "Не-сотрудники, публиковавшие сообщения",
ms.staff_posts AS "Сообщения от сотрудников",
ROUND(ms.staff_posts * 100.0 / NULLIF(ms.total_posts, 0),1) || '%' AS "Сообщения от сотрудников (% от общего числа)",
ms.staff_posts / NULLIF(ms.total_topics, 0) AS "Среднее число сообщений от сотрудников на тему",
ms.staff_users AS "Сотрудники, публиковавшие сообщения"
FROM month_stats ms
ORDER BY "Месяц"
Это должно дать вам что-то вроде этого:
И немного пояснительного текста для полноты картины:
Этот запрос предназначен для предоставления ежемесячного статистического сводного отчёта о активности на форуме, с особым акцентом на различие между вкладами «сотрудников» и «не-сотрудников» в заданный период времени. Рассчитываемые метрики включают общее количество созданных тем, все опубликованные сообщения, число уникальных не-сотрудников, публиковавших сообщения, количество и процент сообщений от не-сотрудников, среднее число сообщений от не-сотрудников на тему, а также аналогичные показатели для сотрудников. Информация призвана дать представление об вовлечённости пользователей, генерации контента и уровне участия сотрудников по сравнению с не-сотрудниками в обсуждениях на форуме. Запрос обеспечивает точность, учитывая только «обычные» (не личные сообщения) темы и исключая любые удалённые сообщения или темы, шёпоты/малые сообщения/действия модераторов, а также сообщения от системных пользователей в указанном диапазоне дат.
В данном случае критерием «сотрудника» является его наличие в автоматической группе @staff, которая включает как администраторов, так и модераторов. Однако это можно настроить так, чтобы нацеливаться только на администраторов или даже на пользовательскую группу сотрудников, которые формально не считаются «сотрудниками сайта». Если вы точно хотите исключить модераторов, можно заменить group_id в начале запроса на ‘1’. 
Вот то, что вы искали?
По какой-то странной причине я знаю, что id сотрудника равен трём. Но как можно найти этот id? Сначала я был абсолютно уверен, что он «вшит» в URL, как и все остальные id, но нет. Используется только имя.
Я знаю так мало SQL, что можно смело сказать «не получится», но вот этот запрос показывает все group-id:
select
id,
name
from
groups
Но surely должен быть более распространённый способ его найти, не так ли?
Лично я очень хочу получить возможность поиска по параметру group_id, как это уже есть для user_id 
- Param dropdown for group_id in data explorer query
Но пока эта мечта не сбылась, я использую JSON-страницу групп, чтобы найти нужный идентификатор, например: https://meta.discourse.org/g.json
Вы можете выполнять поиск по группе непосредственно внутри запроса, что позволяет использовать имена групп — это может быть более удобным для пользователя способом. Например, что-то вроде:
-- [params]
-- string :group_name
SELECT user_id, true as is_staff
FROM group_users
WHERE group_id = (SELECT id FROM groups WHERE name = LOWER(:group_name))
(или вариант с жёстко заданным значением, если вы не хотите использовать параметр:)
SELECT user_id, true as is_staff
FROM group_users
WHERE group_id = (SELECT id FROM groups WHERE name = 'admins')
Ах да, это особенность, о которой я должен был упомянуть. Если вы обновите страницу, поля ввода параметров должны появиться.
Спорю, что ты создал этот фрагмент на ходу. Потому что он работает не совсем корректно 
Он показывает всех пользователей из целевой группы и утверждает, что у всех is_staff 
Но спасибо! Я получил ценную информацию для администраторов базового уровня о JSON и о том, как использовать SQL (на самом деле, но мне всё ещё нравится смотреть, как ИИ-отчёт воспринимает это…)
В этом примере бит is_staff является частью функции данного конкретного запроса. Он специально добавлен здесь SELECT user_id, true as is_staff, а не берётся из базы данных. Это позволяет пометить как «персонал» любого пользователя из указанной вами группы, чтобы затем разделить результаты на два набора (посты персонала и посты не из персонала).
Таким образом, если у вас есть группа «сотрудники», которые технически не являются персоналом сайта согласно настройкам базы данных, вы всё равно можете добавить их, и они попадут в категорию «персонал», а не «не персонал».
Ого, это именно то, что мне нужно, большое спасибо!
Для уверенности: «Посты» включают темы и ответы, или считается только количество ответов?
Ещё раз спасибо!
В данном случае «сообщения» не включают первое сообщение темы, поэтому речь идет только о ответах.
Привет, @JammyDodger!
Как ты думаешь, возможно ли сделать то же самое, но только для тем (=создание новой ветки), пожалуйста?
Спасибо большое!
Вы имеете в виду соотношение тем, созданных сотрудниками, к темам, добавленным не сотрудниками, в этом запросе?
О да, в том же запросе это было бы здорово!
Думаю, добавление этих столбцов должно помочь:
-- [params]
-- date :start_date
-- date :end_date
WITH staff_data AS (
SELECT user_id, true as is_staff
FROM group_users
WHERE group_id = 3
),
month_stats AS (
SELECT
date_trunc('month', p.created_at)::date AS month,
COUNT(*) FILTER (WHERE p.post_number = 1) AS total_topics,
COUNT(*) FILTER (WHERE p.post_number = 1 AND is_staff IS NOT TRUE) AS non_staff_topics,
COUNT(*) FILTER (WHERE p.post_number = 1 AND is_staff IS TRUE) AS staff_topics,
COUNT(*) FILTER (WHERE p.post_number <> 1) AS total_posts,
COUNT(DISTINCT p.user_id) FILTER (WHERE p.post_number <> 1 AND is_staff IS NOT TRUE) AS non_staff_users,
COUNT(*) FILTER (WHERE p.post_number <> 1 AND is_staff IS NOT TRUE) AS non_staff_posts,
COUNT(DISTINCT p.user_id) FILTER (WHERE p.post_number <> 1 AND is_staff) AS staff_users,
COUNT(*) FILTER (WHERE p.post_number <> 1 AND is_staff) AS staff_posts
FROM posts p
LEFT JOIN topics t ON t.id = p.topic_id
LEFT JOIN staff_data s ON p.user_id = s.user_id
WHERE p.created_at::date BETWEEN :start_date AND :end_date
AND t.archetype = 'regular'
AND t.deleted_at IS NULL
AND p.deleted_at IS NULL
AND p.post_type = 1
AND p.user_id > 0
GROUP BY month
)
SELECT
ms.month AS "Месяц",
ms.total_topics AS "Все темы",
ms.non_staff_topics AS "Темы, созданные не сотрудниками",
ROUND(ms.non_staff_topics * 100.0 / NULLIF(ms.total_topics, 0),1) || '%' AS "Темы, созданные не сотрудниками (% от общего числа)",
ms.staff_topics AS "Темы, созданные сотрудниками",
ROUND(ms.staff_topics * 100.0 / NULLIF(ms.total_topics, 0),1) || '%' AS "Темы, созданные сотрудниками (% от общего числа)",
ms.total_posts AS "Все сообщения",
ms.non_staff_posts AS "Сообщения от не сотрудников",
ROUND(ms.non_staff_posts * 100.0 / NULLIF(ms.total_posts, 0),1) || '%' AS "Сообщения от не сотрудников (% от общего числа)",
ms.non_staff_posts / NULLIF(ms.total_topics, 0) AS "Среднее число сообщений от не сотрудников на тему",
ms.non_staff_users AS "Пользователи (не сотрудники), оставившие сообщения",
ms.staff_posts AS "Сообщения от сотрудников",
ROUND(ms.staff_posts * 100.0 / NULLIF(ms.total_posts, 0),1) || '%' AS "Сообщения от сотрудников (% от общего числа)",
ms.staff_posts / NULLIF(ms.total_topics, 0) AS "Среднее число сообщений от сотрудников на тему",
ms.staff_users AS "Пользователи (сотрудники), оставившие сообщения"
FROM month_stats ms
ORDER BY "Месяц"
Огромное спасибо, это идеально!
