# 시간 간격별 대시보드 리포트 데이터 집계

**URL:** https://meta.discourse.org/t/aggregate-dashboard-report-data-by-time-interval/154929
**Category:** Data & reporting
**Tags:** sql-query
**Created:** [6월 15, 2020, 8:22오후 UTC](https://meta.discourse.org/t/aggregate-dashboard-report-data-by-time-interval/154929 "2020-06-15T20:22:44Z")
**Posts on this page:** 5
**Page:** 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: [6월 15, 2020, 8:22오후 UTC](https://meta.discourse.org/t/aggregate-dashboard-report-data-by-time-interval/154929/1 "2020-06-15T20:22:45Z")

</div>

최근 저는 Discourse 대시보드 보고서에서 볼 수 있는 것과 유사한 데이터를 반환하지만, 시간 기간별로 데이터를 집계할 수 있도록 하는 [Data Explorer](https://meta.discourse.org/t/32566?silent=true) 쿼리를 작성했습니다. 예를 들어, 주어진 시작일과 종료일 사이에 생성된 주제의 수를 표시하되, 일별이 아닌 주별 기간으로 합계를 계산하는 것입니다.

쿼리 매개변수는 다음 규칙에 따라 설정됩니다:

쿼리 매개변수: `query_interval` (Postgres interval, 예: ‘1 day’, ‘7 days’, ‘1 week’, ‘1 month’), `start_date` (‘yyyy-mm-dd’), `end_date` (‘yyyy-mm-dd’), `category_ids` (쉼표로 구분된 카테고리 ID 목록, 기본값 -1), `include_subcategories` (boolean, 기본값 true). 제공된 시작일과 종료일 사이에 생성된 게시물의 수를 반환합니다. 결과는 쿼리 간격별로 그룹화됩니다. category\_ids 목록에 -1 값이 포함되어 있으면 모든 카테고리에 대한 결과가 반환됩니다.

### 기간별 첫 응답까지의 평균 소요 시간

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

```

### 기간별 총 해결된 수

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

```

### 기간별 주제 수

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

```

### 기간별 게시물 수

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

```

---

<div class="post-metadata">

### Author: ![BenLeong](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/benleong/32/60951_2.png) [@BenLeong](https://meta.discourse.org/u/BenLeong)
#### Post date: [6월 16, 2020, 4:21오전 UTC](https://meta.discourse.org/t/aggregate-dashboard-report-data-by-time-interval/154929/2 "2020-06-16T04:21:51Z")

</div>

@simon님 감사합니다 - 정말 훌륭합니다!

처음에는 기간(interval)을 선택할 때 `start_date`와 `end_date` 매개변수가 여전히 필수인 것이, 그리고 그 반대도 마찬가지인 것이 혼란스러웠습니다. 이제 보니 날짜 범위 내에서 X 기간 단위로 결과를 반환하는 방식이군요. 한 해 동안의 월별 변화를 빠르게 확인하거나 비슷한 시나리오에서 정말 유용합니다.

카테고리와 하위 카테고리 포함 기능도 좋습니다. 커뮤니티의 다양한 부분에서 활동을 추적하고 있기 때문에, 특정 카테고리 전체와 하위 카테고리의 성과를 빠르게 확인할 수 있어 매우 유용합니다.

이 쿼리를 수정하여 하위 카테고리 결과를 쉼표로 구분된 목록으로 표시할 수 있는 간단한 방법이 있을까요?

예: 기간 동안 카테고리 1에 작성된 게시물 (10개), 2 (20개) & 3 (30개).

쿼리에 `1,2,3`이라는 category\_ids를 추가하면 합계(60개)가 반환됩니다. `10,20,30`을 반환하는 방법을 찾고 싶습니다. 그러면 카테고리 간에 나란히 비교할 수 있을 것입니다.

---

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

</div>

> [@BenLeong](#):
>
> 이 쿼리들을 수정하여 하위 카테고리 결과를 쉼표로 구분된 목록으로 표시하는 간단한 방법이 있을까요?

가능합니다. 더 쉬운 접근법은 쿼리를 수정하여 각 카테고리에 대해 한 줄씩 반환하도록 하는 것입니다. 이는 마지막 `GROUP BY` 절에 카테고리 ID를 포함하도록 변경하면 됩니다. 제가 게시한 모든 예제에서 이를 시도해 보지는 않았지만, 아래는 이를 수행하는 “구간별 게시글 수” 쿼리의 수정 예시입니다:

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

```

제 개발 사이트에서 결과가 어떻게 보이는지 아래에 첨부했습니다:

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

---

<div class="post-metadata">

### Author: ![BenLeong](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/benleong/32/60951_2.png) [@BenLeong](https://meta.discourse.org/u/BenLeong)
#### Post date: [6월 18, 2020, 4:04오전 UTC](https://meta.discourse.org/t/aggregate-dashboard-report-data-by-time-interval/154929/4 "2020-06-18T04:04:52Z")

</div>

정말 좋아요 - 다시 한번 감사드립니다! 제가 원하는 대로 작동할 것 같아요 🙂

---

<div class="post-metadata">

### Author: ![Nesha](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/nesha/32/268340_2.png) [@Nesha](https://meta.discourse.org/u/Nesha)
#### Post date: [9월 24, 2022, 9:18오후 UTC](https://meta.discourse.org/t/aggregate-dashboard-report-data-by-time-interval/154929/5 "2022-09-24T21:18:41Z")

</div>

이것은 정말 좋습니다. @simon 감사합니다.

간단한 질문을 해서 죄송하지만, 다음이 가능한가요?

1. 대시보드의 ‘보고서(Reports)’ 섹션에 직접 작성한 보고서를 포함할 수 있으며, 어떻게 해야 하나요?
2. DataExplorer에서 실행한 쿼리의 결과에 따라 특정 작업을 트리거할 수 있나요? 예를 들어 관리자에게 메시지를 보내는 것 같은 경우요.

감사합니다