Me gustaría crear un informe basado en SQL que proporcione una comprensión sobre de qué tipo de cosas publican nuestros usuarios.
Categorías, temas, etiquetas: estos son los campos que he determinado que me proporcionarán esto, y postcount sería la métrica (creo, esencialmente quiero usar esto para comprender la popularidad).
¿Hay algún otro campo que pueda apoyar esto? ¿Existe una consulta existente que pueda usar, ya que supongo que otros han solicitado esto?
no es una consulta SQL, pero presume que has notado que:
/categories te da un número de nuevos Temas/mes para cada Categoría, lo cual puede ser muy útil.
/tags te da un recuento de Temas por etiqueta
Por si no lo sabías (¡pero quizás sí lo sabías!)
No estoy seguro a qué te refieres aquí.
¿Son estos los informes de la parte de administración de una comunidad?
He visto esto pero quiero tenerlos esencialmente todos en uno, para que puedas ver cuántos temas o etiquetas tiene una categoría, etc.
son rutas de URL.
No estoy seguro de poder visualizar completamente lo que tienes en mente para incluir categorías, etiquetas y temas, con un recuento de publicaciones para cada uno, en el mismo informe/consulta.
Creo que podrías hacerlo en dos partes: una para categorías y otra para etiquetas, con un recuento de nuevos temas y nuevas publicaciones dentro de un período de tiempo determinado para cada una. Quizás incluso incluir el número de usuarios que publican.
Así que daría algo como esto como tabla de resultados:
| categoría | nuevos temas | nuevas publicaciones | usuarios |
|---|---|---|---|
| categoría a | 9 | 15 | 4 |
| categoría b | 56 | 167 | 32 |
Ok, así que he introducido Categorías - Discourse Meta ya que supongo que eso es lo que querías.
Esencialmente, quiero una salida de categorías/etiquetas/temas en una sola salida por estas razones:
| categoría | tema | etiqueta | publicaciones | usuarios |
|---|---|---|---|---|
| coches | amor por los coches | ruedas | 44 | 1 |
| coches | odio por los coches | ruedas | 32 | 3 |
| coches | odio por los coches | puertas | 39 | 4 |
| coches | ¿cómo funcionan los coches? | puertas | 32 | 1 |
| cómo funcionan las cosas | ¿cómo funcionan los coches? | cómo hacer | 32 | 3 |
Este es un ejemplo de la visión. Esto permitiría un análisis adicional para comprender cosas como “qué % de usuarios que publican en la categoría de coches hablan de ruedas”
Entiendo que estaría en el explorador de datos combinando publicaciones, temas, etiquetas, categorías, posiblemente otra tabla - Acabo de publicar aquí para entender si esto ya se había hecho/dónde existen consultas SQL que se hayan creado previamente (no en nuestra cuenta, me refiero en general)
Espero que esto tenga sentido, solo comencé a investigar ayer
Ah, ya veo. Quieres una lista de temas con detalles adicionales en lugar de una descripción general. Creo que eso debería ser posible. 
Creo que la única peculiaridad sería que un tema puede tener varias etiquetas, pero prepararemos algo y veremos cómo resulta. 
Pero para buscar otras consultas existentes, están las disponibles de forma estándar en tu panel (puedes ver las versiones SQL aquí agrupadas bajo dashboard-sql), así como algunos informes estándar incluidos con el explorador de datos, y hay una gran cantidad de informes personalizados aquí en meta agrupados bajo la etiqueta sql-query.
@SStrong - Quizás algo como esto:
-- [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
Disculpas, pensé que ya había respondido a esto.
¿Es posible modificar el código para que contenga el nombre de la categoría y el nombre del tema? No sé si “nombre de la publicación” es algo real o si en realidad es el nombre del tema.
Intenté modificar el código yo mismo, pero actualmente no entiendo cómo se comportan las tablas juntas, ya que obtuve 0 resultados en lugar del mismo volumen de registros, pero con palabras en lugar de IDs.
No hay problema. Al ver los datos en el explorador de datos, category_id y topic_id (y muchos otros) se convierten automáticamente en enlaces utilizables dentro del sitio, pero si exportas para analizar en otro lugar, puedes usar los nombres de las categorías y los títulos de los temas en su lugar:
-- [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