# 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:** 3

<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:** [17 Giugno 2020, 5:57pm UTC](https://meta.discourse.org/t/aggregate-dashboard-report-data-by-time-interval/154929/3 "2020-06-17T17:57:32Z")

</div>

> [@BenLeong](#):
>
> Esiste un modo semplice per modificare queste query in modo da visualizzare i risultati delle sottocategorie in un elenco separato da virgole?

Potrebbe essere possibile. Un approccio più semplice consiste nel modificare le query in modo che restituiscano una riga per ciascuna categoria. Questo può essere ottenuto modificando la clausola finale `GROUP BY` per includere l’ID della categoria. Non ho provato questa soluzione con tutti gli esempi che ho pubblicato, ma ecco una modifica della query “Conteggi dei post per intervallo” che lo fa:

```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,
t.category_id,
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, t.category_id
ORDER BY period_start

```

Ecco come appaiono i risultati sul mio sito di sviluppo:

 ![image](https://global.discourse-cdn.com/meta/original/3X/1/3/135fa7f4a1b0603c3e5d750e19b4ab7a0b39215f.png)

---

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