Я хочу создать SQL-отчет, который позволит понять, о чем именно пишут наши пользователи.
Категории, темы, теги — это поля, которые, как я выяснил, помогут мне это сделать, а количество постов (postcount) будет метрикой (я думаю, что это нужно, чтобы понять популярность).
Есть ли какие-либо другие поля, которые могли бы помочь? Существует ли уже готовый запрос, который я могу использовать, учитывая, что, вероятно, другие тоже запрашивали это?
Это не SQL-запрос, но, полагаю, вы заметили:
/categories предоставляет количество новых тем в месяц для каждой категории, что может быть весьма полезно.
/tags предоставляет подсчет тем по тегам.
На случай, если вы этого не знали (но, возможно, уже знали!)
Не совсем уверен, что вы имеете в виду.
Речь идет о отчетах в административной части сообщества?
Я видел это, но хотел бы объединить всё в одном месте, чтобы можно было видеть, сколько тем или тегов есть в категории и так далее.
Это пути URL-адресов.
Я не совсем уверен, что вы имеете в виду, когда говорите об объединении категорий, тегов и тем с количеством постов для каждого в одном отчёте или запросе.
Кажется, это можно сделать в двух отдельных запросах: один для категорий, другой для тегов, с подсчётом новых тем и новых постов за определённый период для каждого. Возможно, даже стоит добавить количество пользователей, которые публиковали посты?
Тогда результат мог бы выглядеть примерно так:
| категория | новые темы | новые посты | пользователи |
|---|---|---|---|
| категория A | 9 | 15 | 4 |
| категория B | 56 | 167 | 32 |
Хорошо, я ввёл Categories - Discourse Meta, как я предполагаю, это то, что вы имели в виду.
По сути, мне нужен вывод категорий/тегов/тем в одном результате по следующим причинам:
По сути, судя по тому, что я смог выяснить, мне нужно объединить категории, теги и темы с помощью SQL, возможно, с привлечением постов. Неужели это уже было сделано? Есть ли готовый код или библиотека запросов для этого?
| категория | тема | тег | сообщения | пользователи |
|---|---|---|---|---|
| автомобили | люблю автомобили | колеса | 44 | 1 |
| автомобили | ненавижу автомобили | колеса | 32 | 3 |
| автомобили | ненавижу автомобили | двери | 39 | 4 |
| автомобили | как работают автомобили? | двери | 32 | 1 |
| как работают вещи | как работают автомобили? | инструкции | 32 | 3 |
Это пример видения. Это позволит провести дальнейший анализ для понимания таких вещей, как «какой процент пользователей, публикующих сообщения в категории «автомобили», говорят о колесах».
Я понимаю, что это можно сделать в «Исследователе данных», объединив сообщения, темы, теги, категории и, возможно, ещё одну таблицу. Я просто опубликовал это здесь, чтобы понять, было ли это уже сделано и существуют ли SQL-запросы, которые были созданы ранее (не в нашем аккаунте, а в целом).
Надеюсь, это понятно, я начал расследование только вчера.
Ах, теперь я понял. Вы хотите список тем с дополнительными деталями, а не обзорное резюме. Думаю, это возможно. 
Единственная особенность, пожалуй, в том, что у темы может быть несколько тегов, но давайте попробуем что-то создать и посмотрим, к чему это приведёт. 
Однако для просмотра других существующих запросов доступны стандартные варианты из вашей панели управления (версии на SQL можно увидеть здесь, сгруппированные под dashboard-sql), а также некоторые стандартные отчёты, включённые в состав обозревателя данных. Кроме того, здесь, на мета-форуме, есть множество пользовательских запросов, сгруппированных под тегом sql-query.
@SSstrong — Возможно, что-то в таком духе:
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-12-31
WITH tag_names AS (
SELECT
t.id AS topic_id,
string_agg(tags.name, ', ' ORDER BY tags.name) AS "tags"
FROM topics t
JOIN topic_tags tt ON tt.topic_id = t.id
JOIN tags ON tags.id = tt.tag_id
WHERE t.created_at BETWEEN :start_date AND :end_date
GROUP BY t.id
),
user_count AS (
SELECT
p.topic_id,
COUNT(DISTINCT p.user_id) AS users
FROM posts p
JOIN topics t ON t.id = p.topic_id
WHERE t.created_at BETWEEN :start_date AND :end_date
AND t.deleted_at IS NULL
AND p.deleted_at IS NULL
AND t.archetype = 'regular'
AND p.post_type = 1
AND p.user_id > 0
GROUP BY p.topic_id
)
SELECT
t.category_id,
t.id AS topic_id,
tn.tags,
t.posts_count,
uc.users
FROM topics t
JOIN tag_names tn ON tn.topic_id = t.id
JOIN user_count uc ON uc.topic_id = t.id
WHERE t.created_at BETWEEN :start_date AND :end_date
AND t.archetype = 'regular'
AND t.deleted_at IS NULL
ORDER BY t.category_id, t.title
Извините, я подумал, что уже ответил на это.
Можно ли изменить код так, чтобы он сохранял название категории и название темы? Я не уверен, существует ли термин «название поста» или на самом деле имеется в виду название темы?
Я попытался изменить код самостоятельно, но пока недостаточно хорошо понимаю, как взаимодействуют таблицы, поэтому в результате получилось 0 записей вместо того же количества записей, но с текстом вместо ID.
Это не проблема. При просмотре в исследовании данных category_id и topic_id (и многие другие) автоматически преобразуются в удобные ссылки на сайте, но при экспорте для анализа в другом месте можно использовать названия категорий и заголовки тем вместо них:
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-12-31
WITH tag_names AS (
SELECT
t.id AS topic_id,
string_agg(tags.name, ', ' ORDER BY tags.name) AS "tags"
FROM topics t
JOIN topic_tags tt ON tt.topic_id = t.id
JOIN tags ON tags.id = tt.tag_id
WHERE t.created_at BETWEEN :start_date AND :end_date
GROUP BY t.id
),
user_count AS (
SELECT
p.topic_id,
COUNT(DISTINCT p.user_id) AS users
FROM posts p
JOIN topics t ON t.id = p.topic_id
WHERE t.created_at BETWEEN :start_date AND :end_date
AND t.deleted_at IS NULL
AND p.deleted_at IS NULL
AND t.archetype = 'regular'
AND p.post_type = 1
AND p.user_id > 0
GROUP BY p.topic_id
)
SELECT
c.name AS category_name,
t.title,
tn.tags,
t.posts_count,
uc.users
FROM topics t
JOIN tag_names tn ON tn.topic_id = t.id
JOIN user_count uc ON uc.topic_id = t.id
JOIN categories c ON c.id = t.category_id
WHERE t.created_at BETWEEN :start_date AND :end_date
AND t.archetype = 'regular'
AND t.deleted_at IS NULL
ORDER BY t.category_id, t.title