¡Feliz año nuevo! 
Me gustaría saber la consulta SQL para obtener el número promedio de respuestas por tema realizadas por los usuarios (excluyendo a los administradores) por mes.
De lo contrario, una forma de obtener una proporción del número total de publicaciones publicadas por los miembros frente al número total de publicaciones publicadas por el personal por mes.
¡Gracias!
Feliz Año Nuevo (un poco atrasado 
)
Para el promedio, ¿estás buscando temas creados tanto por personal como por no personal, pero el recuento de respuestas solo de publicaciones de no personal? ¿Y quieres excluir solo a los administradores, o a los administradores y moderadores?
¡Hola Jammy!
Exactamente, la consulta puede buscar todos los temas (creados tanto por personal como por no personal), pero el recuento de respuestas solo de las publicaciones de no personal.
Por ahora, solo podemos excluir a los administradores (ya que cuando lanzo mi comunidad, los administradores y moderadores son lo mismo :))
Pero también sería bueno tener fácilmente la proporción entre los temas del personal frente a los temas de no personal (excluyendo a los administradores).
Creo que algo como esto te daría los números que buscas:
-- [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 "Mes",
ms.total_topics AS "Todos los temas",
ms.total_posts AS "Todos los posts",
ms.non_staff_posts AS "Posts no del personal",
ROUND(ms.non_staff_posts * 100.0 / NULLIF(ms.total_posts, 0),1) || '%' AS "Posts no del personal (% del total)",
ms.non_staff_posts / NULLIF(ms.total_topics, 0) AS "Posts promedio de no personal por tema",
ms.non_staff_users AS "Usuarios no del personal que publicaron",
ms.staff_posts AS "Posts del personal",
ROUND(ms.staff_posts * 100.0 / NULLIF(ms.total_posts, 0),1) || '%' AS "Posts del personal (% del total)",
ms.staff_posts / NULLIF(ms.total_topics, 0) AS "Posts promedio del personal por tema",
ms.staff_users AS "Usuarios del personal que publicaron"
FROM month_stats ms
ORDER BY "Mes"
Lo que te daría algo como esto:
Y un poco de texto adicional para completar:
Esta consulta está diseñada para proporcionar un resumen estadístico mensual de la actividad del foro, centrándose específicamente en la distinción entre las contribuciones de los usuarios ‘del personal’ y ‘no del personal’ dentro de un período de tiempo determinado. Las métricas calculadas incluyen el número total de temas creados, todos los posts realizados, el número de usuarios únicos no del personal que publicaron, el recuento y el porcentaje de posts realizados por no personal, el número promedio de posts de no personal por tema, así como las cifras correspondientes para los miembros del personal. La información está destinada a ofrecer información sobre la participación de los usuarios, la generación de contenido y la tasa de participación del personal frente a los miembros no del personal en las discusiones del foro. La consulta garantiza la precisión al considerar solo temas ‘regulares’ (no privados) y excluye cualquier post o tema eliminado, susurros/posts pequeños/acciones de moderador, y posts de usuarios del sistema, dentro del rango de fechas especificado.
Para esto, el criterio de ‘personal’ es que estén en el grupo automático @staff, que incluye tanto a administradores como a moderadores, aunque esto se puede ajustar para dirigirse solo a los administradores, o incluso a un grupo personalizado de empleados que técnicamente no son ‘personal del sitio’ como tal. Si deseas excluir definitivamente a los moderadores, puedes cambiar el group_id al principio por ‘1’. 
¿Es ese el tipo de cosa que buscas?
Por alguna extraña razón sé que el id del personal es tres. Pero, ¿cómo se podría encontrar ese id? Al principio, estaba totalmente seguro de que estaba incrustado en las urls, como todas las demás ids, pero no. Solo se usa el nombre.
Sé tan poco de SQL que podemos decir fácilmente que no puedo, pero esto muestra cada id de grupo
select
id,
name
from
groups
Pero seguramente hay una forma más común de encontrarlo, ¿no?
Personalmente, realmente quiero una búsqueda de parámetros group_id al igual que la de user_id 
- Param dropdown for group_id in data explorer query
Pero hasta que ese sueño se haga realidad, uso el json de la página de grupos para averiguarlo, por ejemplo: https://meta.discourse.org/g.json
Puedes hacer una búsqueda de grupo dentro de la consulta para que funcione con nombres de grupo, lo que puede ser una forma más fácil de usar. Algo como:
-- [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))
(o la versión codificada si no quisieras un parámetro:)
SELECT user_id, true as is_staff
FROM group_users
WHERE group_id = (SELECT id FROM groups WHERE name = 'admins')
¡Muchas gracias! Quizás me perdí algo, pero cuando hago clic en “Ejecutar”, me aparece este error:
Ah sí, esa es una peculiaridad que debería haber mencionado. Si actualizas tu página, deberían aparecer las casillas de entrada de parámetros.
Apuesto a que creaste ese fragmento sobre la marcha. Porque no funciona del todo bien 
Muestra a todos los usuarios del grupo deseado y afirma que todos son is_staff 
¡Pero gracias! Obtuve información valiosa para administradores de nivel básico, sobre json y cómo usar SQL (de verdad, pero aún así me gusta ver cómo lo ve el informe de IA…)
En ese ejemplo, el bit is_staff forma parte de la función de esta consulta en particular. Se añade específicamente aquí SELECT user_id, true as is_staff en lugar de provenir de la base de datos en sí. Establece a cualquiera del grupo que designes como ‘staff’ para que puedan dividirse en los dos conjuntos de resultados (publicaciones de staff frente a publicaciones de no staff).
Por lo tanto, si tuvieras un grupo para ‘empleados’, que técnicamente no serían personal del sitio tal como los establece la base de datos, aún podrías añadirlos y ellos irían al grupo de ‘staff’ y no al grupo de ‘no staff’.
¡OMG, esto es absolutamente lo que necesitaba, ¡muchas gracias!
Para estar seguro: ¿«Publicaciones» compila temas + respuestas, o solo cuenta respuestas?
¡Gracias de nuevo!
Para este, ‘posts’ no incluye la primera publicación del tema, por lo que solo son las respuestas.
Hola @JammyDodger
¿Crees que es posible tener lo mismo pero solo para temas (=nuevo hilo creado), por favor?
¡Muchas gracias!
¿Te refieres a una proporción entre los temas creados por el personal y los no creados por el personal añadidos a esta consulta?
¡Oh sí, en la misma consulta sería genial!
Creo que agregar estas columnas lo hará:
-- [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 "Mes",
ms.total_topics AS "Todos los temas",
ms.non_staff_topics AS "Temas no del personal",
ROUND(ms.non_staff_topics * 100.0 / NULLIF(ms.total_topics, 0),1) || '%' AS "Temas no del personal (% del total)",
ms.staff_topics AS "Temas del personal",
ROUND(ms.staff_topics * 100.0 / NULLIF(ms.total_topics, 0),1) || '%' AS "Temas del personal (% del total)",
ms.total_posts AS "Todas las publicaciones",
ms.non_staff_posts AS "Publicaciones no del personal",
ROUND(ms.non_staff_posts * 100.0 / NULLIF(ms.total_posts, 0),1) || '%' AS "Publicaciones no del personal (% del total)",
ms.non_staff_posts / NULLIF(ms.total_topics, 0) AS "Publicaciones promedio no del personal por tema",
ms.non_staff_users AS "Usuarios no del personal que publicaron",
ms.staff_posts AS "Publicaciones del personal",
ROUND(ms.staff_posts * 100.0 / NULLIF(ms.total_posts, 0),1) || '%' AS "Publicaciones del personal (% del total)",
ms.staff_posts / NULLIF(ms.total_topics, 0) AS "Publicaciones promedio del personal por tema",
ms.staff_users AS "Usuarios del personal que publicaron"
FROM month_stats ms
ORDER BY "Mes"
¡Muchas gracias, esto es perfecto!
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.
