# Data Explorer の年別平均値は？
**URL:** https://meta.discourse.org/t/data-explorer-averages-by-year/150424
**Category:** Data & reporting
**Tags:** sql-query
**Created:** [2020 年 5 月 5 日午前 10:55 UTC](https://meta.discourse.org/t/data-explorer-averages-by-year/150424 "2020-05-05T10:55:26Z")
**Posts on this page:** 7
**Page:** 1
### Author:  [@hhlp](https://meta.discourse.org/u/hhlp)
#### Post date: [2020 年 5 月 5 日午前 10:55 UTC](https://meta.discourse.org/t/data-explorer-averages-by-year/150424/1 "2020-05-05T10:55:27Z")
Data-Explorer SQL クエリの作成が必要です。
年ごとのリクエストを行いたいと考えています。例えば、2019-01-01 から 2019-12-31 までです。
- カテゴリ別（トピックと投稿）のリプライ数
- 1 年間のトップタグ
- 月あたりの新規トピックの平均数（これは管理パネルの管理レポートで見つけました）
- 月あたりのリプライの平均数
- 月あたりの新規ユーザーの平均数
どなたかお手伝いいただけませんか？あるいは方向性を示していただけませんか？
よろしくお願いいたします。
---
### Author:  [@HAWK](https://meta.discourse.org/u/HAWK)
#### Post date: [2020 年 5 月 7 日午前 3:01 UTC](https://meta.discourse.org/t/data-explorer-averages-by-year/150424/2 "2020-05-07T03:01:33Z")
@michebs さんがおそらくお手伝いできるはずです。
---
### Author:  [@michebs](https://meta.discourse.org/u/michebs)
#### Post date: [2020 年 5 月 7 日午前 10:26 UTC](https://meta.discourse.org/t/data-explorer-averages-by-year/150424/5 "2020-05-07T10:26:59Z")
こんにちは、
他のクエリを設定する前に、これがご要望に合っているか確認させてください。
> [@hhlp](#):
>
> 月あたりの新規トピックの平均数
```
WITH data AS (SELECT
id,
EXTRACT(MONTH FROM created_at) AS month,
EXTRACT(YEAR FROM created_at) AS year
FROM topics)
SELECT
ROUND(AVG(qt_topic_month)) AS avg_topic,
year
FROM
(SELECT COUNT(id) AS qt_topic_month,
month,
year
FROM data
GROUP BY month, year) AS top_m
GROUP BY year
ORDER BY year DESC
```
| avg\_topic | year |
| --- | --- |
| 694 | 2020 |
| 1011 | 2019 |
| 284 | 2018 |
| 79 | 2017 |
---
### Author:  [@hhlp](https://meta.discourse.org/u/hhlp)
#### Post date: [2020 年 5 月 7 日午後 3:00 UTC](https://meta.discourse.org/t/data-explorer-averages-by-year/150424/6 "2020-05-07T15:00:08Z")
@michebs.. はい、その通りです。非常に早くご理解くださいました。
もし差し支えなければ、以下のような形になります。
## 年 月 avg\_topic
2020 01 数値
2020 02 数値
2020 03 数値
など…
事前にありがとうございます。
よろしくお願いいたします。
---
### Author:  [@michebs](https://meta.discourse.org/u/michebs)
#### Post date: [2020 年 5 月 7 日午後 4:08 UTC](https://meta.discourse.org/t/data-explorer-averages-by-year/150424/7 "2020-05-07T16:08:01Z")
問題ありません。お探しの情報はこれだと考えられます：
```
WITH data AS (SELECT
id,
EXTRACT(DAY FROM created_at) AS day,
EXTRACT(MONTH FROM created_at) AS month,
EXTRACT(YEAR FROM created_at) AS year
FROM topics)
SELECT
year,
month,
ROUND(AVG(qt_topic_day)) AS avg_topic
FROM
(SELECT COUNT(id) AS qt_topic_day,
day,
month,
year
FROM data
GROUP BY day, month, year) AS top_day
GROUP BY month, year
ORDER BY year DESC, month ASC
```
| year | month | avg\_topic |
| --- | --- | --- |
| 2020 | 1 | 23 |
| 2020 | 2 | 26 |
| 2020 | 3 | 24 |
| 2020 | 4 | 35 |
| 2020 | 5 | 31 |
このパターンに基づいて、他のクエリも設定いたします。😉
---
### Author:  [@michebs](https://meta.discourse.org/u/michebs)
#### Post date: [2020 年 5 月 16 日午後 8:55 UTC](https://meta.discourse.org/t/data-explorer-averages-by-year/150424/8 "2020-05-16T20:55:02Z")
こんにちは、
ご返信が遅くなり申し訳ございません。ご依頼いただいたクエリは、一部が通常よりも複雑であったため、作成に時間を要しました。ご不明な点がございましたら、いつでもお気軽にご質問ください。
ミシェル
> [@hhlp](#):
>
> `カテゴリ別返信数（トピックと投稿）`
```
WITH post AS (SELECT
id AS post_id,
topic_id,
EXTRACT(YEAR FROM created_at) AS year
FROM posts
WHERE post_type = 1
AND deleted_at ISNULL
AND post_number != 1)
SELECT
p.year,
t.category_id AS id,
c.name category,
COUNT(p.post_id) AS qt
FROM post p
INNER JOIN topics t ON t.id = p.topic_id
LEFT JOIN categories c ON c.id = t.category_id
WHERE t.deleted_at ISNULL
-- AND t.category_id NOT NULL --> ***カテゴリなしの投稿を削除できるようにする***
GROUP BY t.category_id, c.name, p.year
ORDER BY p.year DESC, qt DESC
```
| year | id | category | qt |
| --- | --- | --- | --- |
| 2020 | 13 | General | 14 |
| 2020 | 16 | Knowledge Base | 3 |
| 2020 | 15 | Staff | 3 |
| 2020 | 1 | Uncategorized | 2 |
| 2020 | 17 | Ideas | 1 |
| 2019 | 18 | Builds | 10 |
| 2019 | 1 | Uncategorized | 8 |
| 2019 | 11 | CS001x: Intro to Computer Science | 7 |
| 2019 | 13 | General | 5 |
* * *
> [@hhlp](#):
>
> `年別トップタグ`
```
WITH data AS (SELECT
tag_id,
EXTRACT(YEAR FROM created_at) AS year
FROM topic_tags)
SELECT year, rank, name, qt FROM (
SELECT
tag_id,
COUNT(tag_id) AS qt,
year,
rank() OVER (PARTITION BY year ORDER BY COUNT(tag_id) DESC) AS rank
FROM
data
GROUP BY year, tag_id) as rnk
INNER JOIN tags ON tags.id = rnk.tag_id
WHERE rank <= 5 -- ***ランキングの上限を選択***
ORDER BY year DESC, qt DESC
```
| year | rank | tag\_name | quantity |
| --- | --- | --- | --- |
| 2020 | 1 | featured | 7 |
| 2020 | 2 | human-resources | 3 |
| 2020 | 3 | demo | 1 |
| 2019 | 1 | demo | 12 |
| 2019 | 2 | human-resources | 4 |
| 2019 | 3 | featured | 3 |
| 2019 | 3 | customer | 3 |
| 2019 | 3 | milestones-2019 | 3 |
* * *
> [@hhlp](#):
>
> `月別新規トピック数の平均`
```
WITH data AS (SELECT
id,
EXTRACT(DAY FROM created_at) AS day,
EXTRACT(MONTH FROM created_at) AS month,
EXTRACT(YEAR FROM created_at) AS year
FROM topics
WHERE deleted_at ISNULL)
SELECT
year,
month,
ROUND(AVG(qt_topic_day)) AS avg_topic_by_day
FROM
(SELECT COUNT(id) AS qt_topic_day,
day,
month,
year
FROM data
GROUP BY day, month, year) AS top_day
GROUP BY month, year
ORDER BY year DESC, month ASC
```
| year | month | avg\_topic\_by\_day |
| --- | --- | --- |
| 2020 | 1 | 1 |
| 2020 | 2 | 1 |
| 2020 | 3 | 2 |
| 2020 | 4 | 3 |
| 2020 | 5 | 2 |
| 2019 | 4 | 9 |
| 2019 | 5 | 4 |
| 2019 | 6 | 4 |
| 2019 | 7 | 1 |
| 2019 | 8 | 2 |
| 2019 | 9 | 3 |
| 2019 | 10 | 1 |
* * *
> [@hhlp](#):
>
> `月別返信数の平均`
```
WITH data AS (SELECT
id,
EXTRACT(DAY FROM created_at) AS day,
EXTRACT(MONTH FROM created_at) AS month,
EXTRACT(YEAR FROM created_at) AS year
FROM posts
WHERE post_type = 1
AND deleted_at ISNULL
AND post_number != 1)
SELECT
year,
month,
ROUND(AVG(qt_reply_day)) AS avg_reply_by_day
FROM
(SELECT COUNT(id) AS qt_reply_day,
day,
month,
year
FROM data
GROUP BY day, month, year) AS top_reply
GROUP BY month, year
ORDER BY year DESC, month ASC
```
| year | month | avg\_reply\_by\_day |
| --- | --- | --- |
| 2020 | 1 | 7 |
| 2020 | 3 | 2 |
| 2020 | 4 | 5 |
| 2020 | 5 | 6 |
| 2019 | 4 | 3 |
| 2019 | 5 | 2 |
| 2019 | 6 | 4 |
| 2019 | 7 | 2 |
| 2019 | 8 | 15 |
| 2019 | 9 | 3 |
| 2019 | 10 | 5 |
| 2019 | 12 | 2 |
* * *
> [@hhlp](#):
>
> `月別新規ユーザー数の平均`
```
WITH data AS (SELECT
id,
EXTRACT(DAY FROM created_at) AS day,
EXTRACT(MONTH FROM created_at) AS month,
EXTRACT(YEAR FROM created_at) AS year
FROM users)
SELECT
year,
month,
ROUND(AVG(qt_new_user)) AS avg_new_user_by_day
FROM
(SELECT COUNT(id) AS qt_new_user,
day,
month,
year
FROM data
GROUP BY day, month, year) AS top_new_user
GROUP BY month, year
ORDER BY year DESC, month ASC
```
| year | month | avg\_new\_user\_by\_day |
| --- | --- | --- |
| 2020 | 1 | 1 |
| 2020 | 2 | 1 |
| 2020 | 3 | 1 |
| 2020 | 4 | 3 |
| 2020 | 5 | 1 |
| 2019 | 4 | 4 |
| 2019 | 5 | 2 |
| 2019 | 6 | 2 |
| 2019 | 7 | 1 |
* * *
---
### Author:  [@hhlp](https://meta.discourse.org/u/hhlp)
#### Post date: [2020 年 5 月 17 日午前 8:58 UTC](https://meta.discourse.org/t/data-explorer-averages-by-year/150424/9 "2020-05-17T08:58:46Z")
本当に素晴らしいです…
おかげで私の日々が救われました…
よろしくお願いいたします…