Справочник по отчётам на панели администратора

[Discourse]

Это справочное руководство о том, как работают отчёты панели администратора, какие данные они отображают, какие SQL-запросы Data Explorer им соответствуют и где находится Ruby-код для каждого отчёта.

Требуемый уровень пользователя: Staff (персонал)

Discourse содержит несколько встроенных отчётов панели администратора, которые могут быть полезны для изучения статистики сообщества. Чтобы получить доступ к этим отчётам, вы можете перейти по адресу discourse.example.com/admin/dashboard/reports на вашем сайте (или щёлкнуть по ссылке Reports в боковой панели). Обратите внимание, что доступ к этим отчётам имеют только пользователи-персонал.

В эти отчёты включаются данные всех пользователей сайта (включая активность персонала, такую как посещение административных страниц). Единственное условие, налагаемое на пользователей в отчётах, состоит в том, что они должны быть «реальными» пользователями, что используется для исключения пользователя system из отчётов.

Плагины также могут добавлять отчёты на панель с помощью add_report(name, &block).

Ruby-модели для большинства отчётов находятся здесь: discourse/app/models/concerns/reports/. Некоторые отчёты также ссылаются на: discourse/app/models/report.rb

Темы dashboard-sql содержат все соответствующие SQL-запросы, которые можно использовать для генерации отчётов, идентичных отчётам панели администратора. Эти запросы можно использовать в плагине Data Explorer и для запуска запросов Data Explorer через API Discourse

Чтобы скрыть определённые отчёты с панели, используйте настройку сайта dashboard_hidden_reports.

Принятые решения (Accepted solutions)

Отображает ежедневный подсчёт сообщений, отмеченных как решения.

Код Ruby: discourse-solved/plugin.rb at main · discourse/discourse-solved · GitHub

SQL-запрос: Dashboard Report - Accepted Solutions

Входы администраторов (Admin Logins)

Список времени входов администраторов с указанием местоположения.

Код Ruby: discourse/app/models/concerns/reports/staff_logins.rb

SQL-запрос: Dashboard Report - Admin Logins

Анонимные (Anonymous)

Количество новых просмотров страниц посетителями, не вошедшими в аккаунт.

Код Ruby: discourse/app/models/concerns/reports/consolidated_page_views.rb

SQL-запрос: Dashboard Report - Anonymous

Закладки (Bookmarks)

Количество новых тем и сообщений, добавленных в закладки.

Код Ruby: discourse/app/models/concerns/reports/bookmarks.rb

SQL-запрос: Dashboard Report - Bookmarks

Сводные запросы API (Consolidated API Requests)

Статистика использования API по датам, отслеживающая как обычные запросы API, так и запросы пользовательского API.

Код Ruby: discourse/app/models/concerns/reports/consolidated_api_requests.rb at main · discourse/discourse · GitHub

SQL-запрос: Dashboard Report - Consolidated API Requests

Сводные просмотры страниц (Consolidated Pageviews)

Просмотры страниц для пользователей, вошедших в систему, анонимных пользователей и поисковых роботов. Это устаревший отчёт, заменённый отчётом Site Traffic.

Код Ruby: discourse/app/models/concerns/reports/consolidated_page_views.rb

SQL-запрос: Dashboard Report - Consolidated Pageviews

Сводные просмотры страниц с определением браузера (Deprecated) (Consolidated Pageviews with Browser Detection)

Просмотры страниц для пользователей, вошедших в систему, анонимных пользователей, известных поисковых роботов и других. Этот отчёт устарел и теперь перенаправляет в отчёт Site Traffic.

Код Ruby: discourse/app/models/concerns/reports/consolidated_page_views_browser_detection.rb

SQL-запрос: Dashboard Report - Consolidated Pageviews with Browser Detection

DAU/MAU

Количество участников, вошедших в систему за последний день, разделённое на количество участников, вошедших в систему за последний месяц, — возвращает процент, указывающий на «удержание» (stickiness) сообщества. Целевое значение >20%.

Код Ruby: discourse/app/models/concerns/reports/dau_by_mau.rb

SQL-запрос: Dashboard Report - DAU/MAU

Ежедневные активные пользователи (Daily Engaged Users)

Количество пользователей, поставивших лайк или написавших сообщение за последний день.

Код Ruby: discourse/app/models/concerns/reports/daily_engaged_users.rb

SQL-запрос: Dashboard Report - Daily Engaged Users

Отправленные электронные письма (Emails Sent)

Количество новых отправленных электронных писем.

Код Ruby: discourse/app/models/concerns/reports/emails.rb

SQL-запрос: Dashboard Report - Emails Sent

Флаги (Flags)

Количество новых флагов.

Код Ruby: discourse/app/models/concerns/reports/flags.rb

SQL-запрос: Dashboard Report - Flags

Статус флагов (Flags Status)

Список статусов флагов, включая тип флага, автора, пометившего флагом, и время до разрешения.

Код Ruby: discourse/app/models/concerns/reports/flags_status.rb

SQL-запрос: Dashboard Report - Flags Status

Лайки (Likes)

Количество новых лайков.

Код Ruby: discourse/app/models/concerns/reports/likes.rb

SQL-запрос: Dashboard Report - Likes

Вошедшие в систему (Logged In)

Количество новых просмотров страниц от пользователей, вошедших в систему.

Код Ruby: discourse/app/controllers/admin/reports_controller.rb#L5

SQL-запрос: Dashboard Report - Logged In

Активность модераторов (Moderator Activity)

Список активности модераторов, включая проверенные флаги, время чтения, созданные темы, созданные сообщения, созданные личные сообщения и правки.

SQL-запрос: Dashboard Report - Moderator Activity

Предупреждения модераторов (Moderator Warning)

Количество предупреждений, отправленных личными сообщениями от модераторов.

Код Ruby: discourse/app/models/concerns/reports/moderator_warning_private_messages.rb

SQL-запрос: Dashboard Report - Moderator Warnings

Новые участники (New Contributors)

Количество пользователей, написавших своё первое сообщение за этот период.

Код Ruby: discourse/app/models/concerns/reports/new_contributors.rb

SQL-запрос: Dashboard Report - New Contributors

Уведомление модераторов (Notify Moderators)

Количество случаев, когда модераторы были тайно уведомлены флагом.

Код Ruby: discourse/app/models/concerns/reports/notify_moderators_private_messages.rb

SQL-запрос: Dashboard Report - Notify Moderators

Уведомление пользователя (Notify User)

Количество случаев, когда пользователи были тайно уведомлены флагом.

Код Ruby: discourse/app/models/concerns/reports/notify_user_private_messages.rb

SQL-запрос: Dashboard Report - Notify User

Общий настрой (Overall Sentiment)

Количество сообщений, классифицированных как положительные или отрицательные с помощью ИИ «Sentiment», за указанный период.

Код Ruby: https://github.com/discourse/discourse-ai/blob/main/lib/sentiment/entry_point.rb

SQL-запрос: Dashboard Report - Overall Sentiment

Просмотры страниц (Pageviews)

Количество новых просмотров страниц от всех посетителей. То же самое, что и общее количество для «Сводных просмотров страниц».

Discourse использует следующий запрос для определения общего количества просмотров страниц:

SQL-запрос: Dashboard Report - Consolidated Pageviews

Правки сообщений (Post Edits)

Количество новых правок сообщений.

Код Ruby: discourse/app/models/concerns/reports/post_edits.rb

SQL-запрос: Dashboard Report - Post Edits

Сообщения (Posts)

Новые сообщения, созданные за выбранный период времени

Код Ruby: discourse/app/models/concerns/reports/posts.rb

SQL-запрос: Dashboard Report - Posts

Эмоция сообщения (Post Emotion)

Количество сообщений, классифицированных ИИ с одной из следующих эмоций: Грусть, Удивление, Страх, Гнев, Радость, Отвращение, — сгруппированных по уровню доверия автора, за указанный период.

Код Ruby: https://github.com/discourse/discourse-ai/blob/main/lib/sentiment/entry_point.rb

SQL-запрос: Dashboard Report - Post Emotion

Реакции (Reactions)

Список последних реакций.

Код Ruby: discourse-reactions/plugin.rb at main · discourse/discourse-reactions · GitHub

SQL-запрос: Dashboard Report - Reactions

Регистрации (Signups)

Новые регистрации аккаунтов за этот период.

Код Ruby: discourse/app/models/concerns/reports/signups.rb

SQL-запрос: Dashboard Report - Signups

Трафик сайта (Site Traffic)

Просмотры страниц для браузеров с вошедшими пользователями, анонимных браузеров, поисковых роботов и другого трафика. Это основной отчёт о трафике, заменяющий устаревшие отчёты «Сводные просмотры страниц».

Код Ruby: discourse/app/models/concerns/reports/site_traffic.rb

SQL-запрос: Dashboard Report - Site Traffic

Подозрительные входы (Suspicious Logins)

Детали новых входов, которые подозрительно отличаются от предыдущих входов.

Код Ruby: discourse/app/models/concerns/reports/suspicious_logins.rb

SQL-запрос: Dashboard Report - Suspicious Logins

Система (System)

Количество личных сообщений, отправленных автоматически Системой.

Код Ruby: discourse/app/models/concerns/reports/system_private_messages.rb

SQL-запрос: Dashboard Report - System

Время до первого ответа (Time to first response)

Среднее время (в часах) первого ответа на новые темы.

Код Ruby: discourse/app/models/concerns/reports/time_to_first_response.rb + discourse/discourse/blob/main/app/models/topic.rb#L1799-L1844

SQL-запрос: Dashboard Report - Time to First Response

Топ игнорируемых/заглушённых пользователей (Top Ignored / Muted Users)

Пользователи, которые были заглушены и/или проигнорированы многими другими пользователями.

Код Ruby: discourse/app/models/concerns/reports/top_ignored_users.rb

SQL-запрос: Dashboard Report - Top Ignored / Muted Users

Топ тем по переходам (Top Referred Topics)

Темы, получившие наибольшее количество кликов из внешних источников.

Код Ruby: discourse/app/models/concerns/reports/top_referred_topics.rb

SQL-запрос: Dashboard Report - Top Referred Topics

Топ рефереров (Top Referrers)

Список пользователей, отсортированный по количеству кликов по ссылкам, которые они поделились.

Код Ruby: discourse/app/models/concerns/reports/top_referrers.rb

SQL-запрос: Dashboard Report - Top Referrers

Топ источников трафика (Top Traffic Sources)

Внешние источники, которые чаще всего давали ссылки на этот сайт.

Код Ruby: discourse/app/models/concerns/reports/top_traffic_sources.rb

SQL-запрос: Dashboard Report - Top Traffic Sources

Топ загрузок (Top Uploads)

Список всех загрузок по расширению, размеру файла и автору.

Код Ruby: discourse/app/models/concerns/reports/top_uploads.rb

SQL-запрос: Dashboard Report - Top Uploads

Топ пользователей по полученным лайкам (Top Users by likes received)

Топ-10 пользователей, получивших лайки.

Код Ruby: discourse/app/models/concerns/reports/top_users_by_likes_received.rb

SQL-запрос: Dashboard Report - Top Users by Likes Received

Топ пользователей по лайкам от пользователей с более низким уровнем доверия (Top Users by likes received from a user with a lower trust level)

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

Код Ruby: discourse/app/models/concerns/reports/top_users_by_likes_received_from_inferior_trust_level.rb

SQL-запрос: Dashboard Report - Top Users by Likes Received from a User with a Lower Trust Level

Топ пользователей по лайкам от разнообразных людей (Top Users by likes received from a variety of people)

Топ-10 пользователей, получивших лайки от широкого круга людей.

Код Ruby: discourse/app/models/concerns/reports/top_users_by_likes_received_from_a_variety_of_people.rb

SQL-запрос: Dashboard Report - Top Users by Likes Received From a Variety of People

Темы (Topics)

Новые темы, созданные за этот период.

Код Ruby: discourse/app/models/concerns/reports/topics.rb

SQL-запрос: Dashboard Report - Topics

Темы без ответа (Topics with no response)

Количество новых созданных тем, не получивших ответа.

Код Ruby: discourse/app/models/concerns/reports/topics_with_no_response.rb

SQL-запрос: Dashboard Report - Topics with No Response

Статистика просмотров тем (Topic View Stats)

Топ-100 тем по просмотрам с разбивкой на анонимных и вошедших пользователей, с возможностью фильтрации по категории.

Код Ruby: discourse/app/models/concerns/reports/topic_view_stats.rb

SQL-запрос: Dashboard Report - Topic View Stats

Трендовые поисковые запросы (Trending Search Terms)

Самые популярные поисковые запросы с их коэффициентами кликабельности.

Код Ruby: discourse/app/models/concerns/reports/trending_search.rb

SQL-запрос: Dashboard Report - Trending Search Terms

Рост уровня доверия (Trust Level growth)

Количество пользователей, повысивших свой уровень доверия за этот период.

Отчёт «Рост уровня доверия» извлекает данные из таблицы user_histories в базе данных Discourse. Конкретно, этот отчёт подсчитывает количество случаев, когда в user_histories.action записано повышение уровня доверия пользователя.

Код Ruby: discourse/app/models/concerns/reports/trust_level_growth.rb

SQL-запрос: Dashboard Report - Trust Level Growth

Не принятые политики (Unaccepted policies)

Этот отчёт панели администратора определяет темы с политиками, которые не были приняты определёнными пользователями.

Код Ruby: discourse-policy/plugin.rb at main · discourse/discourse-policy · GitHub

SQL-запрос: Dashboard Report - Unaccepted Policies

Коэффициент флагов пользователей (User Flagging Ratio)

Список пользователей, отсортированный по коэффициенту реакции персонала на их флаги (от несогласия к согласию).

Код Ruby: discourse/app/models/concerns/reports/user_flagging_ratio.rb

SQL-запрос: Dashboard Report - User Flagging Ratio

Заметки о пользователях (User notes)

Список последних заметок о пользователях.

Код Ruby: discourse-user-notes/plugin.rb at main · discourse/discourse-user-notes · GitHub

SQL-запрос: Dashboard Report - User Notes

Просмотры профилей пользователей (User Profile Views)

Общее количество новых просмотров профилей пользователей.

Код Ruby: discourse/app/models/concerns/reports/profile_views.rb

SQL-запрос: Dashboard Report - User Profile Views

Посещения пользователями (User Visits)

Общее количество посещений форума вошедшими пользователями за выбранный период времени (сегодня, вчера, последние 7 дней и т. д.).

Посещение пользователем засчитывается каждый раз, когда уникальный вошедший пользователь посещает сайт, но не чаще одного раза в день. Например, если пользователь посещал сайт каждый день в течение недели, Discourse засчитает это как 7 посещений.

Код Ruby: discourse/app/models/concerns/reports/visits.rb

SQL-запрос: Dashboard Report - User Visits

Посещения пользователями (мобильные) (User Visits (mobile))

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

Код Ruby: discourse/app/models/concerns/reports/mobile_visits.rb

SQL-запрос: Dashboard Report - User Visits

Пользователь к пользователю (без ответов) (User-to-User (excluding replies))

Количество новых инициированных личных сообщений.

Код Ruby: discourse/app/models/concerns/reports/user_to_user_private_messages.rb

SQL-запрос: Dashboard Report - User-to-User

Пользователь к пользователю (с ответами) (User-to-User (with replies))

Количество всех новых личных сообщений и ответов.

Код Ruby: discourse/app/models/concerns/reports/user_to_user_private_messages_with_replies.rb

SQL-запрос: Dashboard Report - User-to-User

Пользователи по уровню доверия (Users per Trust Level)

Количество пользователей, сгруппированных по уровню доверия.

Код Ruby: discourse/app/models/concerns/reports/users_by_trust_level.rb

SQL-запрос: Dashboard Report - Users Per Trust Level

Пользователи по типу (Users per Type)

Количество пользователей, сгруппированных по типу: администратор, модератор, приостановленный и замолчанный.

Код Ruby: discourse/app/models/concerns/reports/users_by_type.rb

SQL-запрос: Dashboard Report - Users Per Type

Просмотры страниц веб-роботами (Web Crawler Pageviews)

Общее количество просмотров страниц веб-роботами со временем.

Код Ruby: discourse/app/models/report.rb

SQL-запрос: Dashboard Report - Web Crawler Pageviews

Пользовательские агенты веб-роботов (Web Crawler User Agents)

Список пользовательских агентов веб-роботов, отсортированный по количеству просмотров страниц.

Код Ruby: discourse/app/models/concerns/reports/web_crawlers.rb

SQL-запрос: Dashboard Report - Web Crawler User Agents

[pfaffman (Jay Pfaffman)]

@Sara Devlaeminck

В Discourse есть несколько встроенных отчётов панели администратора, которые могут быть полезны для анализа статистики сообщества. Чтобы получить к ним доступ, перейдите по адресу /admin/dashboard/reports на вашем сайте.

Я не вижу ссылки на это на странице /admin. Я что-то упускаю? Казалось бы, это должно быть более заметным. Мне казалось, что я знал, что эти отчёты находятся здесь, но искал и не мог их найти.

Хотя мне потребовалось всего несколько минут, чтобы найти их, было бы неплохо добавить что-то вроде:

Плагины могут добавлять отчёты на панель с помощью add_report(name, &block).

[maiki]

@Jay Pfaffman

Я не вижу ссылки на это на странице /admin.

@Jay Pfaffman

Кажется, это должно быть более заметным. Мне казалось, что я знал, где находятся эти отчёты, но искал и не мог их найти.

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

[pfaffman (Jay Pfaffman)]

:лицо_плачущего_кота:

Извините. Я точно думал, что видел это где-то раньше.

Людей просто невозможно заставить читать текст. . . .Разве что я могу прочитать исходный код, чтобы узнать, как это сделать в плагине?

Но, возможно, стоит обновить вышеуказанное так:

@SaraDev

Чтобы получить доступ к этим отчётам, перейдите по адресу /admin/dashboard/reports на вашем сайте или нажмите на ссылку отчёты на панели управления.

Кажется, именно это меня и сбило с толку. (Но нет, у меня нет оправданий.)

[maiki]

@Джей Пфэффман

Но, возможно, обновите вышеизложенное до

Я сделал тему вики-страницей, действуйте!

[hello-smile6 (9pfs)]

@SaraDev

Стремитесь к >30%.

Это не соответствует интерфейсу (в интерфейсе указано 20%). Какое значение правильное?

[JammyDodger]

Отличное замечание. Недавно это было обновлено до 20%. Я внесу изменения в исходный пост.

Is there a difference in target retention rate between a support-first forum and discussion-first forum?

Here’s a PR with that change: Longer term, I think a more nuanced approach could be powerful. I’d love for communities to be able to set their own goals (with some guidance like what you shared above about why they might choose one vs. the other) and use the dashboard or other related features to help them track their results vs. what they are aiming for.

[Yotam_Hagay]

@Sara Devlaeminck

Основные источники трафика

Внешние источники, которые чаще всего ссылались на этот сайт.

Код Ruby: discourse/app/models/concerns/reports/top_traffic_sources.rb

Привет, @SaraDev. Можно ли получить вывод этого отчёта с помощью SQL-запроса? Можете ли вы поделиться им?
Спасибо

[SaraDev (Sara Devlaeminck)]

@Yotam Hagay

Привет @SaraDev, можно ли получить такой отчёт с помощью SQL-запроса? Можешь поделиться им?
Спасибо

Да, для отчёта по основным источникам трафика можно использовать следующий SQL-запрос:

-- [params]
-- date :start_date = 01/05/2023
-- date :end_date = 03/06/2023

WITH count_links AS (
  
SELECT COUNT(*) AS clicks,
       ind.name AS domain
FROM incoming_links il
  INNER JOIN posts p ON p.deleted_at ISNULL AND p.id = il.post_id
  INNER JOIN topics t ON t.deleted_at ISNULL AND t.id = p.topic_id
  INNER JOIN incoming_referers ir ON ir.id = il.incoming_referer_id
  INNER JOIN incoming_domains ind ON ind.id = ir.incoming_domain_id
WHERE t.archetype = 'regular'
  AND il.created_at::date BETWEEN :start_date AND :end_date 
GROUP BY ind.name
ORDER BY clicks DESC
), 

count_topics AS (
  
SELECT COUNT(DISTINCT p.topic_id) AS topics,
       ind.name AS domain
FROM incoming_links il
INNER JOIN posts p ON p.deleted_at ISNULL AND p.id = il.post_id
INNER JOIN topics t ON t.deleted_at ISNULL AND t.id = p.topic_id
INNER JOIN incoming_referers ir ON ir.id = il.incoming_referer_id
INNER JOIN incoming_domains ind ON ind.id = ir.incoming_domain_id
WHERE t.archetype = 'regular'
  AND il.created_at > (CURRENT_TIMESTAMP - INTERVAL '30 DAYS')
GROUP BY ind.name
) 

SELECT cl.domain, 
       cl.clicks AS "Clicks", 
       ct.topics AS "Topics"
FROM count_links cl
JOIN count_topics ct ON cl.domain = ct.domain
LIMIT 10

Обратите внимание, что в этом запросе параметры даты должны быть в формате день/месяц/год.

[Yotam_Hagay]

@Sara Devlaeminck

Да, вы можете использовать следующий SQL-отчёт для «Источников основного трафика»:

Привет @SaraDev, спасибо за предоставление запроса.
У меня более общий вопрос по этому отчёту и таблице incoming_links — она отражает трафик только на страницы постов, а не трафик на все страницы форума, верно?

Контекст: я хочу проанализировать тренды общего трафика форума и надеялся получить данные по общему трафику по источникам из отчёта «Источники основного трафика».
Но при этом общий трафик за последний месяц составляет около 272 тыс. (пользователи и анонимы), а общее количество кликов в отчёте по источникам трафика за тот же период — всего 59 тыс.
Также я заметил, что вы используете INNER JOIN с таблицами topics и posts, что означает: если к клику не привязан post_id, он не учитывается.

Не могли бы вы подтвердить мой вывод и, возможно, немного объяснить логику работы таблицы incoming_links?

[Yotam_Hagay]

@Sara Devlaeminck

Сообщения

Новые сообщения, созданные в этот период

Привет, @SaraDev! Я выполнил этот запрос, и результат не точно совпадает с отчётом «Сообщения» на вкладке «Общее».
Например, за 30 ноября:
Запрос = 112 сообщений
Отчёт = 120 сообщений
Не могли бы вы проверить эту разницу?
Спасибо

[JammyDodger]

Просто к сведению @Yotam_Hagay — даже если Сара является автором темы (OP), руководство — это ответственность всех Не нужно помечать каждого @упоминанием в каждом сообщении.

[Yotam_Hagay]

Спасибо @JammyDodger за уточнение.Есть ли кто-то ещё, кого я могу упомянуть или к кому можно обратиться, чтобы получить ответ?

[Yotam_Hagay]

@Sara Devlaeminck

SQL: Время до первого ответа

--[params]
-- date :date_start
-- date :date_end

WITH first_reply AS (
    SELECT 
        p.topic_id, 
        MIN(post_number) post_number, 
        t.created_at
    FROM posts p
    INNER JOIN topics t ON (p.topic_id = t.id)
    WHERE p.deleted_at IS NULL
        AND p.post_number != 1
        AND p.post_type = 1
        AND p.user_id > 0
        AND t.user_id > 0
        AND t.deleted_at IS NULL
        AND t.archetype = 'regular'
        AND t.created_at::date BETWEEN :date_start AND :date_end
    GROUP BY p.topic_id, t.created_at
    ORDER BY 2 DESC
)

SELECT 
    p.topic_id, 
    fr.created_at::date dt_topic_created,
    (p.created_at - fr.created_at) response_time
FROM posts p
INNER JOIN first_reply fr 
    ON fr.topic_id = p.topic_id 
    AND fr.post_number = p.post_number
    AND p.created_at > fr.created_at
ORDER BY response_time

Результаты этого запроса немного отличаются от отчёта «Время до первого ответа»:
Например, 8 ноября:
Запрос: 93 часа
Отчёт: 116 часов
Может кто-то подсказать?

[JammyDodger]

Я думаю, что на исследование некоторых из этих вопросов может уйти время. Я сам пытаюсь разобраться, чтобы понять, что происходит (хотя разрыв между моими навыками работы с SQL и Ruby довольно велик ).

Но продолжайте присылать результаты — было бы здорово окончательно прояснить всю эту информацию.

[JammyDodger]

@Yotam_Hagay

Привет, @SaraDev, я выполнил этот запрос, и результат точно не совпадает с отчётом «Posts» во вкладке «General».
Например, на 30 ноября:
Запрос = 112 постов
Отчёт = 120 постов
Не могли бы вы проверить это расхождение?
Спасибо

Что касается отчёта по постам, я думаю, что стандартный отчёт также учитывает посты тем, а также посты от системных пользователей, но при этом фильтрует только те, у которых post_type равен 1 (то есть не whisper-сообщения, небольшие сообщения действий или действия модераторов).

Я думаю, что SQL-запрос должен выглядеть примерно так:

--[params]
-- date :start_date
-- date :end_date

SELECT 
    p.created_at::date AS "Day",
    COUNT(p.id) AS "Count"
FROM posts p
INNER JOIN topics t ON t.id = p.topic_id AND t.deleted_at ISNULL
WHERE p.created_at::date BETWEEN :start_date AND :end_date
    AND p.deleted_at ISNULL
    AND t.archetype = 'regular'
    AND p.post_type = 1
GROUP BY p.created_at::date
ORDER BY 1

Не могли бы вы запустить его на вашем сайте и проверить, совпадают ли результаты?

[Yotam_Hagay]

Спасибо, Джамми, проверю!
Сейчас я работаю над анализом времени до первого ответа, поэтому буду признателен, если вы тоже сможете взглянуть на это.

[JammyDodger]

Посмотрев на версию SQL, я думаю, что в ней отсутствует условие AND p.user_id <> t.user_id для исключения ответов от автора оригинального поста (OP). Если я добавлю это условие, то получу точное время между публикацией OP и первым ответом от другого пользователя:

--[params]
-- date :date_start
-- date :date_end

WITH first_reply AS (
    SELECT 
        p.topic_id, 
        MIN(post_number) post_number, 
        t.created_at
    FROM posts p
    INNER JOIN topics t ON (p.topic_id = t.id)
    WHERE p.deleted_at IS NULL
        AND p.user_id <> t.user_id
        AND p.post_number != 1
        AND p.post_type = 1
        AND p.user_id > 0
        AND t.user_id > 0
        AND t.deleted_at IS NULL
        AND t.archetype = 'regular'
        AND t.created_at::date BETWEEN :date_start AND :date_end
    GROUP BY p.topic_id, t.created_at
    ORDER BY 2 DESC
)

SELECT 
    p.topic_id, 
    fr.created_at::date dt_topic_created,
    (p.created_at - fr.created_at) response_time
FROM posts p
INNER JOIN first_reply fr 
    ON fr.topic_id = p.topic_id 
    AND fr.post_number = p.post_number
    AND p.created_at > fr.created_at
ORDER BY response_time

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

---

Небольшое обновление: добавил AVG, чтобы результат был более похож на вывод стандартного отчёта:

--[params]
-- date :date_start
-- date :date_end

WITH first_reply AS (
    SELECT 
        p.topic_id, 
        MIN(post_number) post_number, 
        t.created_at
    FROM posts p
    INNER JOIN topics t ON p.topic_id = t.id
    WHERE p.deleted_at IS NULL
        AND p.user_id <> t.user_id
        AND p.post_type = 1
        AND p.user_id > 0
        AND t.user_id > 0
        AND t.deleted_at IS NULL
        AND t.archetype = 'regular'
        AND t.created_at::date BETWEEN :date_start AND :date_end
    GROUP BY p.topic_id, t.created_at
)

SELECT 
    fr.created_at::date dt_topic_created,
    AVG(p.created_at - fr.created_at) response_time
FROM posts p
INNER JOIN first_reply fr 
    ON fr.topic_id = p.topic_id 
    AND fr.post_number = p.post_number
    AND p.created_at > fr.created_at
GROUP BY fr.created_at::date
ORDER BY response_time

Это, кажется, совпадает со стандартным отчётом, если учитывать, что один формат — десятичный, а другой — ЧЧ:ММ. Я уверен, что есть способ преобразовать response_time из SQL в десятичный формат, но ЧЧ:ММ кажется более интуитивным вариантом. (Кажется, там также есть некоторые дополнительные критерии, которые могут быть не нужны, но также могут служить защитой от нестандартных ситуаций, поэтому я пока оставил их без изменений, пока не смогу точно сказать, нужны они или нет )

Не могли бы вы запустить этот запрос и посмотреть, насколько хорошо результаты совпадают?

[Yotam_Hagay]

Да, теперь сумма совпадает с цифрами в отчёте по запасам, спасибо!
Только один комментарий —
Я обнаружил, что функция AVG ниже возвращает отсутствующие результаты, если время превышает 24 часа (похоже, отсутствует раздел с количеством дней).

AVG(p.created_at - fr.created_at)::time response_time

[JammyDodger]

Ах да, приведение к time было неудачным решением. Если убрать ::time, всё вернётся к более точной (хотя и менее приятной для глаз) версии.

Я также отредактирую то, что выше.