Ich habe kürzlich einige Data Explorer-Abfragen geschrieben, die ähnliche Daten zurückgeben wie die Berichte im Discourse-Dashboard, jedoch mit der Möglichkeit, die Daten nach einem Zeitintervall zu aggregieren. Beispielsweise die Anzahl der erstellten Themen zwischen einem angegebenen Start- und Enddatum anzeigen, wobei die Summen jedoch wöchentlich statt täglich berechnet werden.
Die Parameter für die Abfragen werden nach folgenden Regeln festgelegt:
Abfrageparameter: query_interval (ein Postgres-Intervall, z. B. ‘1 day’, ‘7 days’, ‘1 week’, ‘1 month’), start_date (‘yyyy-mm-dd’), end_date (‘yyyy-mm-dd’), category_ids (eine durch Kommas getrennte Liste von Kategorie-IDs, Standardwert ist -1), include_subcategories (Boolean, Standardwert ist true). Gibt die Anzahl der zwischen dem angegebenen Start- und Enddatum erstellten Beiträge zurück. Die Ergebnisse werden nach dem Abfrageintervall gruppiert. Wenn die Liste der category_ids den Wert -1 enthält, werden Ergebnisse für alle Kategorien zurückgegeben.
Durchschnittliche Zeit bis zur ersten Antwort für ein Intervall
--[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
Gesamtzahl der Lösungen für ein Intervall
--[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
Themenanzahl für ein Intervall
--[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
Beitragsanzahl für ein Intervall
--[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
