# Aggrega i dati del report della dashboard per intervallo di tempo

**URL:** <https://meta.discourse.org/t/aggregate-dashboard-report-data-by-time-interval/154929>\
**Category:** Data & reporting\
**Tags:** sql-query\
**Created:** [15 Giugno 2020, 8:22pm UTC](https://meta.discourse.org/t/aggregate-dashboard-report-data-by-time-interval/154929 "2020-06-15T20:22:44Z")\
**Posts on this page:** 1\
**Showing post:** 1

<div class="post-metadata">

**Author:** ![simon](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/simon/32/339122_2.png) [@simon](https://meta.discourse.org/u/simon)\
**Post date:** [15 Giugno 2020, 8:22pm UTC](https://meta.discourse.org/t/aggregate-dashboard-report-data-by-time-interval/154929/1 "2020-06-15T20:22:45Z")

</div>

Ho recentemente scritto alcune query per [Data Explorer](https://meta.discourse.org/t/32566?silent=true) che restituiscono dati simili a quelli presenti nei report della dashboard di Discourse, ma consentono di aggregare i dati per un periodo temporale. Ad esempio, mostrare il numero di topic creati tra date di inizio e fine specificate, ma con i totali sommati per intervalli settimanali invece che giornalieri.

I parametri per le query sono impostati secondo le seguenti regole:

Parametri della query: `query_interval` (un intervallo Postgres, ad esempio ‘1 day’, ‘7 days’, ‘1 week’, ‘1 month’), `start_date` (‘yyyy-mm-dd’), `end_date` (‘yyyy-mm-dd’), `category_ids` (una lista di ID categoria separati da virgola, di default -1), `include_subcategories` (booleano, di default true). Restituisce il numero di post creati tra le date di inizio e fine fornite. I risultati sono raggruppati in base all’intervallo della query. Se l’elenco `category_ids` contiene il valore -1, i risultati verranno restituiti per tutte le categorie.

### Tempo medio per la prima risposta per intervallo

```sql
--[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

```

### Totale soluzioni per intervallo

```sql
--[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

```

### Conteggio topic per intervallo

```sql
--[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

```

### Conteggio post per intervallo

```sql
--[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

```

---

_[View the full topic](https://meta.discourse.org/t/aggregate-dashboard-report-data-by-time-interval/154929)._