Recientemente escribí algunas consultas de Data Explorer que devuelven datos similares a los que se encuentran en los informes del panel de control de Discourse, pero que permiten agrupar los datos por un período de tiempo. Por ejemplo, mostrar el número de temas creados entre fechas de inicio y fin dadas, pero con los totales sumados en períodos semanales en lugar de diarios.
Los parámetros de las consultas se establecen con las siguientes reglas:
Parámetros de la consulta: query_interval (un intervalo de Postgres, por ejemplo, ‘1 day’, ‘7 days’, ‘1 week’, ‘1 month’), start_date (‘yyyy-mm-dd’), end_date (‘yyyy-mm-dd’), category_ids (una lista de identificadores de categoría separada por comas, por defecto -1), include_subcategories (booleano, por defecto true). Devuelve el número de publicaciones creadas entre las fechas de inicio y fin proporcionadas. Los resultados se agrupan por el intervalo de consulta. Si la lista de category_ids contiene el valor -1, se devolverán resultados para todas las categorías.
Tiempo promedio hasta la primera respuesta para el intervalo
--[params]
-- string :query_interval = 7 days
-- date :start_date
-- date :end_date
-- int_list :category_ids = -1
-- boolean :include_subcategories = true
WITH query_periods AS (
SELECT generate_series(:start_date, :end_date, :query_interval::interval)::date as period_start
),
subcategory_ids AS (
SELECT id FROM categories
WHERE parent_category_id IN (:category_ids)
),
sub_subcategory_ids AS (
SELECT id FROM categories
WHERE parent_category_id IN (SELECT id FROM subcategory_ids)
),
topics_and_replies AS (
SELECT
t.created_at AS topic_created_at,
p.topic_id AS reply_topic_id,
p.created_at AS reply_created_at,
period_start
FROM topics t
JOIN query_periods
ON t.created_at::date >= period_start AND t.created_at::date < period_start + interval :query_interval
JOIN posts p
ON p.topic_id = t.id
WHERE t.posts_count > 1
AND t.archetype = 'regular'
AND t.deleted_at IS NULL
AND CASE
WHEN -1 IN (:category_ids)
THEN true
WHEN :include_subcategories = false
THEN t.category_id IN (:category_ids)
ELSE t.category_id IN (:category_ids) OR t.category_id IN (SELECT id FROM subcategory_ids) OR t.category_id IN (SELECT id FROM sub_subcategory_ids)
END
AND p.post_number > 1
AND p.post_type = 1
AND p.deleted_at IS NULL
)
SELECT period_start, ROUND(AVG(reply_time_hours)::numeric, 2) AS response_time_hours FROM(
SELECT
qp.period_start,
EXTRACT(EPOCH FROM MIN(reply_created_at) - topic_created_at):: float / 3600 AS reply_time_hours
FROM query_periods qp
JOIN topics_and_replies tar
ON tar.period_start = qp.period_start
GROUP BY reply_topic_id, topic_created_at, qp.period_start
) replies_for_period
GROUP BY period_start
ORDER BY period_start
Total de soluciones para el intervalo
--[params]
-- string :query_interval = 7 days
-- date :start_date
-- date :end_date
-- int_list :category_ids = -1
-- boolean :include_subcategories = true
WITH query_periods AS (
SELECT generate_series(:start_date, :end_date, :query_interval::interval)::date as period_start
),
subcategory_ids AS (
SELECT id FROM categories
WHERE parent_category_id IN (:category_ids)
),
sub_subcategory_ids AS (
SELECT id FROM categories
WHERE parent_category_id IN (SELECT id FROM subcategory_ids)
)
SELECT
period_start,
COUNT(1) AS solved_count
FROM user_actions ua
JOIN query_periods
ON ua.created_at::date >= period_start AND ua.created_at::date < period_start + interval :query_interval
JOIN topics t
ON t.id = ua.target_topic_id
JOIN posts p
ON p.id = ua.target_post_id
WHERE ua.action_type = 15
AND t.deleted_at IS NULL
AND p.deleted_at IS NULL
AND CASE
WHEN -1 IN (:category_ids)
THEN true
WHEN :include_subcategories = false
THEN t.category_id IN (:category_ids)
ELSE t.category_id IN (:category_ids) OR t.category_id IN (SELECT id FROM subcategory_ids) OR t.category_id IN (SELECT id FROM sub_subcategory_ids)
END
GROUP BY period_start
ORDER BY period_start
Recuento de temas para el intervalo
--[params]
-- string :query_interval = 7 days
-- date :start_date
-- date :end_date
-- int_list :category_ids = -1
-- boolean :include_subcategories = true
WITH query_periods AS (
SELECT generate_series(:start_date, :end_date, :query_interval::interval)::date as period_start
),
subcategory_ids AS (
SELECT id FROM categories
WHERE parent_category_id IN (:category_ids)
),
sub_subcategory_ids AS (
SELECT id FROM categories
WHERE parent_category_id IN (SELECT id FROM subcategory_ids)
)
SELECT qp.period_start,
COUNT(t.id)
FROM query_periods qp
JOIN topics t
ON t.created_at::date >= qp.period_start AND t.created_at::date < qp.period_start + interval :query_interval
WHERE t.deleted_at IS NULL
AND t.archetype = 'regular'
AND CASE
WHEN -1 IN (:category_ids)
THEN true
WHEN :include_subcategories = false
THEN t.category_id IN (:category_ids)
ELSE t.category_id IN (:category_ids) OR t.category_id IN (SELECT id FROM subcategory_ids) OR t.category_id IN (SELECT id FROM sub_subcategory_ids)
END
GROUP BY qp.period_start
ORDER BY qp.period_start
Recuento de publicaciones para el intervalo
--[params]
-- string :query_interval = 7 days
-- date :start_date
-- date :end_date
-- int_list :category_ids = -1
-- boolean :include_subcategories = true
WITH query_periods AS (
SELECT generate_series(:start_date, :end_date, :query_interval::interval)::date as period_start
),
subcategory_ids AS (
SELECT id FROM categories
WHERE parent_category_id IN (:category_ids)
),
sub_subcategory_ids AS (
SELECT id FROM categories
WHERE parent_category_id IN (SELECT id FROM subcategory_ids)
)
SELECT
period_start,
COUNT(p.id)
FROM query_periods qp
JOIN posts p
ON p.created_at::date >= qp.period_start AND p.created_at::date < qp.period_start + interval :query_interval
JOIN topics t
ON t.id = p.topic_id
WHERE t.archetype = 'regular'
AND t.deleted_at IS NULL
AND CASE
WHEN -1 IN (:category_ids)
THEN true
WHEN :include_subcategories = false
THEN t.category_id IN (:category_ids)
ELSE t.category_id IN (:category_ids) OR t.category_id IN (SELECT id FROM subcategory_ids) OR t.category_id IN (SELECT id FROM sub_subcategory_ids)
END
AND p.deleted_at IS NULL
AND p.post_type = 1
GROUP BY period_start
ORDER BY period_start
