J’ai récemment écrit des requêtes Data Explorer qui retournent des données similaires à celles des rapports du tableau de bord Discourse, mais qui permettent d’agréger les données par période de temps. Par exemple, afficher le nombre de sujets créés entre des dates de début et de fin données, mais avec des totaux sommés par semaine plutôt que par jour.
Les paramètres des requêtes sont définis selon les règles suivantes :
Paramètres de la requête : query_interval (un intervalle Postgres, par exemple ‘1 day’, ‘7 days’, ‘1 week’, ‘1 month’), start_date (‘yyyy-mm-dd’), end_date (‘yyyy-mm-dd’), category_ids (une liste de numéros de catégorie séparée par des virgules, par défaut -1), include_subcategories (booléen, par défaut true). Retourne le nombre de publications créées entre les dates de début et de fin fournies. Les résultats sont regroupés selon l’intervalle de la requête. Si la liste category_ids contient la valeur -1, les résultats sont retournés pour toutes les catégories.
Temps moyen jusqu’à la première réponse pour l’intervalle
--[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 des solutions pour l’intervalle
--[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
Nombre de sujets pour l’intervalle
--[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
Nombre de publications pour l’intervalle
--[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
