### Author:  [@Konrad\_Sopala](https://meta.discourse.org/u/Konrad_Sopala)
#### Post date: [2021 年 2 月 16 日午後 2:14 UTC](https://meta.discourse.org/t/calculate-total-participation-moments-by-month-using-data-explorer/179885/1 "2021-02-16T14:14:38Z")
こんにちは、データエクスプローラーの魔法使いの皆さん！
誰か、データエクスプローラーのクエリを使って、月ごとの参加回数（投稿数、解決数、いいね数の合計）を取得するクエリを作成したことがありますか？
事前にありがとうございます！
---
### Author:  [@Rotblut](https://meta.discourse.org/u/Rotblut)
#### Post date: [2021 年 2 月 16 日午後 3:30 UTC](https://meta.discourse.org/t/calculate-total-participation-moments-by-month-using-data-explorer/179885/2 "2021-02-16T15:30:06Z")
もしよろしければご覧ください。
>
---
### Author:  [@Konrad\_Sopala](https://meta.discourse.org/u/Konrad_Sopala)
#### Post date: [2021 年 2 月 17 日午後 3:53 UTC](https://meta.discourse.org/t/calculate-total-participation-moments-by-month-using-data-explorer/179885/3 "2021-02-17T15:53:34Z")
探しているものが見つかりません。
---
### Author:  [@simon](https://meta.discourse.org/u/simon)
#### Post date: [2021 年 2 月 18 日午前 12:51 UTC](https://meta.discourse.org/t/calculate-total-participation-moments-by-month-using-data-explorer/179885/4 "2021-02-18T00:51:17Z")
> [@Konrad\_Sopala](#):
>
> こんにちは、データエクスプローラーの達人の皆様！
私はデータエクスプローラーの専門家ではありませんが、時間があればクエリを書くのが好きです。あまり先走ってしまわないように、あなたが求めているのは統計データの月別内訳だと仮定しています。もしそうであれば、以下のようなクエリが役立つかもしれません：
```sql
--[params]
-- date :start_date
WITH month_starts AS (
SELECT generate_series(date_trunc('month', :start_date::date), CURRENT_DATE, interval '1 month')::date AS month_start
),
monthly_posts AS (
SELECT
month_start,
COUNT(1) AS posts_count
FROM posts p
JOIN month_starts
ON p.created_at::date >= month_start AND p.created_at::date <= month_start + interval '1 month - 1 day'
WHERE p.deleted_at IS NULL
AND p.post_type = 1
AND p.created_at >= :start_date
GROUP BY month_start
),
monthly_total_users AS (
SELECT
month_start,
COUNT(1) AS total_users_count
FROM users u
JOIN month_starts
ON u.created_at::date <= month_start + interval '1 month - 1 day'
WHERE u.id > 0
GROUP BY month_start
),
monthly_active_users AS (
SELECT
month_start,
COUNT(DISTINCT user_id) AS active_users_count
fROM user_visits uv
JOIN month_starts
ON uv.visited_at >= month_start AND uv.visited_at <= month_start + interval '1 month - 1 day'
WHERE uv.visited_at >= :start_date
GROUP BY month_start
),
monthly_solutions AS (
SELECT
month_start,
COUNT(1) AS solutions_count
FROM user_actions ua
JOIN month_starts ms
ON ua.created_at::date >= month_start AND ua.created_at::date <= month_start + interval '1 month - 1 day'
WHERE ua.action_type = 15
AND ua.created_at >= :start_date
GROUP BY month_start
),
monthly_likes AS (
SELECT
month_start,
COUNT(1) AS likes_count
FROM user_actions ua
JOIN month_starts ms
ON ua.created_at::date >= month_start AND ua.created_at::date <= month_start + interval '1 month - 1 day'
WHERE ua.action_type = 2
AND ua.created_at >= :start_date
GROUP BY month_start
)
SELECT
ms.month_start,
COALESCE(posts_count, 0) AS posts_count,
COALESCE(total_users_count, 0) AS total_users_count,
COALESCE(active_users_count, 0) AS active_users_count,
COALESCE(solutions_count, 0) AS solutions_count,
COALESCE(likes_count, 0) AS likes_count
FROM month_starts ms
LEFT JOIN monthly_posts mp ON mp.month_start = ms.month_start
LEFT JOIN monthly_total_users mtu ON mtu.month_start = ms.month_start
LEFT JOIN monthly_active_users mau ON mau.month_start = ms.month_start
LEFT JOIN monthly_solutions mts ON mts.month_start = ms.month_start
LEFT JOIN monthly_likes ml ON ml.month_start = ms.month_start
ORDER BY month_start DESC
```
クエリを実行する前に、`start_date` パラメータに値を指定する必要があります。形式は `yyyy-mm-dd` です。ただし、このクエリはその日付から「月」の部分のみを抽出します。開始日パラメータが必要なのは、サイトがオンラインになった全期間に対してクエリを実行すると、大規模なサイトではタイムアウトしてしまうためです。開始日パラメータを使用することで、Meta に対して数年分の期間でクエリを実行してもタイムアウトせずに済みます。
このクエリに関する注意点として、`active_users_count` 列は、その月にサイトに「ログインした」一意のユーザー数を返すものであり、これらのユーザーがサイトに何らかのアクション（例：投稿への「いいね」や投稿の作成など）を実行したかどうかは確認していません。クエリでそのようにすることも可能かもしれませんが、タイムアウトの問題が懸念されます。
もしこれが求めているデータの種類でなければ、お知らせください。基本的な考え方が合っていれば、クエリに追加で必要なデータがあるか、または結果に何か不自然な点があるかをお知らせください。
---
### Author:  [@Konrad\_Sopala](https://meta.discourse.org/u/Konrad_Sopala)
#### Post date: [2021 年 2 月 18 日午後 12:05 UTC](https://meta.discourse.org/t/calculate-total-participation-moments-by-month-using-data-explorer/179885/6 "2021-02-18T12:05:29Z")
@simon さん、共有ありがとうございます。とても役立ちますし、ほぼ私が探していたものです！クエリに対して1つ変更を加えることは可能でしょうか？開始日を指定したくないのです。これらの値（投稿、ユーザー、解決策、いいね）をすべて合計し、月別に次のようなクエリ結果にしたいのです。
---
### Author:  [@simon](https://meta.discourse.org/u/simon)
#### Post date: [2021 年 2 月 18 日午後 5:18 UTC](https://meta.discourse.org/t/calculate-total-participation-moments-by-month-using-data-explorer/179885/8 "2021-02-18T17:18:26Z")
> [@Konrad\_Sopala](#):
>
> 開始日を指定するのは好きではありません。これらの値（投稿、ユーザー、解決済み、いいね）をすべて合計し、月ごとのクエリ結果を次のように取得したいと考えています：
これは可能かもしれません。試してみます。開始日パラメータは、サイトがオンラインになっている期間全体でメタサイト上でクエリを実行するとタイムアウトが発生することが判明した直前に、クエリに追加されました。クエリの効率を向上させてタイムアウトの問題を解消する方法があるかもしれません。もしそれが不可能な場合、開始日のみを指定するのではなく、時間枠を設定できるようにクエリを変更すべきです。そうすれば、異なる時間枠でクエリを数回実行することで、サイトのすべてのデータを取得できるようになります。
---
### Author:  [@Konrad\_Sopala](https://meta.discourse.org/u/Konrad_Sopala)
#### Post date: [2021 年 2 月 19 日午前 8:41 UTC](https://meta.discourse.org/t/calculate-total-participation-moments-by-month-using-data-explorer/179885/9 "2021-02-19T08:41:24Z")
もちろん！その調整ができたら、いつでもお待ちしています。
---
### Author:  [@michebs](https://meta.discourse.org/u/michebs)
#### Post date: [2021 年 2 月 19 日午後 7:40 UTC](https://meta.discourse.org/t/calculate-total-participation-moments-by-month-using-data-explorer/179885/10 "2021-02-19T19:40:10Z")
こんにちは、Konrad さん、
以下に調整済みのクエリを示します。
```sql
WITH monthly_users AS (
SELECT
date_part('year', created_at) AS year,
date_part('month', created_at) AS month,
COUNT(*) AS "new_users_month"
FROM users
WHERE id > 0
GROUP BY date_part('year', created_at), date_part('month', created_at)
ORDER BY date_part('year', created_at) ASC, date_part('month', created_at)
),
monthly_posts AS (
SELECT
date_part('year', created_at) AS year,
date_part('month', created_at) AS month,
COUNT(*) AS "posts_count"
FROM posts p
WHERE p.deleted_at IS NULL
AND p.post_type = 1
GROUP BY date_part('year', created_at), date_part('month', created_at)
ORDER BY date_part('year', created_at) ASC, date_part('month', created_at)
),
monthly_active_users AS (
SELECT
date_part('year', visited_at) AS year,
date_part('month', visited_at) AS month,
COUNT(DISTINCT user_id) AS "active_users_count"
FROM user_visits uv
GROUP BY date_part('year', visited_at), date_part('month', visited_at)
ORDER BY date_part('year', visited_at) ASC, date_part('month', visited_at)
),
monthly_solutions AS (
SELECT
date_part('year', created_at) AS year,
date_part('month', created_at) AS month,
COUNT(*) AS "solutions_count"
FROM user_actions ua
WHERE ua.action_type = 15
GROUP BY date_part('year', created_at), date_part('month', created_at)
ORDER BY date_part('year', created_at) ASC, date_part('month', created_at)
),
monthly_likes AS (
SELECT
date_part('year', created_at) AS year,
date_part('month', created_at) AS month,
COUNT(*) AS "likes_count"
FROM user_actions ua
WHERE ua.action_type = 2
GROUP BY date_part('year', created_at), date_part('month', created_at)
ORDER BY date_part('year', created_at) ASC, date_part('month', created_at)
)
SELECT
mu.year,
mu.month,
SUM(new_users_month) over (ORDER BY mu.year, mu.month rows between unbounded preceding AND current row) AS total_users,
posts_count,
COALESCE(active_users_count, 0) AS active_users_count,
COALESCE(solutions_count, 0) AS solutions_count,
COALESCE(likes_count, 0) AS solutions_count
FROM monthly_users mu
LEFT JOIN monthly_posts mp ON mp.year = mu.year AND mp.month = mu.month
LEFT JOIN monthly_active_users mau ON mau.year = mu.year AND mau.month = mu.month
LEFT JOIN monthly_solutions ms ON ms.year = mu.year AND ms.month = mu.month
LEFT JOIN monthly_likes ml ON ml.year = mu.year AND ml.month = mu.month
ORDER BY mu.year, mu.month
```
#### 画像と完全に一致するようにすべての列を追加する必要がある場合は、以下のクエリを使用してください：
> **SQL 詳細**
>
> ```sql
> WITH monthly_users AS (
> SELECT
> date_part('year', created_at) AS year,
> date_part('month', created_at) AS month,
> COUNT(*) AS new_users_month
> FROM users
> WHERE id > 0
> GROUP BY date_part('year', created_at), date_part('month', created_at)
> ORDER BY date_part('year', created_at) ASC, date_part('month', created_at)
> ),
>
> monthly_posts AS (
> SELECT
> date_part('year', created_at) AS year,
> date_part('month', created_at) AS month,
> COUNT(*) AS posts_count
> FROM posts p
> WHERE p.deleted_at IS NULL
> AND p.post_type = 1
> GROUP BY date_part('year', created_at), date_part('month', created_at)
> ORDER BY date_part('year', created_at) ASC, date_part('month', created_at)
> ),
>
> monthly_active_users AS (
> SELECT
> date_part('year', visited_at) AS year,
> date_part('month', visited_at) AS month,
> COUNT(DISTINCT user_id) AS active_users_count
> FROM user_visits uv
> GROUP BY date_part('year', visited_at), date_part('month', visited_at)
> ORDER BY date_part('year', visited_at) ASC, date_part('month', visited_at)
> ),
>
> monthly_solutions AS (
> SELECT
> date_part('year', created_at) AS year,
> date_part('month', created_at) AS month,
> COUNT(*) AS solutions_count
> FROM user_actions ua
> WHERE ua.action_type = 15
> GROUP BY date_part('year', created_at), date_part('month', created_at)
> ORDER BY date_part('year', created_at) ASC, date_part('month', created_at)
> ),
>
> monthly_likes AS (
> SELECT
> date_part('year', created_at) AS year,
> date_part('month', created_at) AS month,
> COUNT(*) AS likes_count
> FROM user_actions ua
> WHERE ua.action_type = 2
> GROUP BY date_part('year', created_at), date_part('month', created_at)
> ORDER BY date_part('year', created_at) ASC, date_part('month', created_at)
> )
>
> SELECT
> mu.year,
> mu.month,
> SUM(new_users_month + COALESCE(posts_count,0) +
> COALESCE(active_users_count, 0) +
> COALESCE(solutions_count, 0) +
> COALESCE(likes_count, 0))
> over (ORDER BY mu.year, mu.month rows between unbounded preceding AND current row) AS sum_total
> FROM monthly_users mu
> LEFT JOIN monthly_posts mp ON mp.year = mu.year AND mp.month = mu.month
> LEFT JOIN monthly_active_users mau ON mau.year = mu.year AND mau.month = mu.month
> LEFT JOIN monthly_solutions ms ON ms.year = mu.year AND ms.month = mu.month
> LEFT JOIN monthly_likes ml ON ml.year = mu.year AND ml.month = mu.month
> ORDER BY mu.year, mu.month
>
> ```
---
### Author:  [@Konrad\_Sopala](https://meta.discourse.org/u/Konrad_Sopala)
#### Post date: [2021 年 2 月 23 日午前 10:35 UTC](https://meta.discourse.org/t/calculate-total-participation-moments-by-month-using-data-explorer/179885/11 "2021-02-23T10:35:47Z")
まさに探していたものです！@michebs さん、ありがとうございます！[Data Explorer](https://meta.discourse.org/t/32566?silent=true) クエリのオープンソースコレクションを作成して、Discourse ユーザーと共有することを検討されたことはありますか？
---
### Author:  [@codinghorror](https://meta.discourse.org/u/codinghorror)
#### Post date: [2021 年 2 月 23 日午後 10:01 UTC](https://meta.discourse.org/t/calculate-total-participation-moments-by-month-using-data-explorer/179885/12 "2021-02-23T22:01:26Z")
はい、それはデータエクスプローラーに組み込まれているクエリの一覧です 😉
---
### Author:  [@Konrad\_Sopala](https://meta.discourse.org/u/Konrad_Sopala)
#### Post date: [2021 年 2 月 24 日午前 11:45 UTC](https://meta.discourse.org/t/calculate-total-participation-moments-by-month-using-data-explorer/179885/13 "2021-02-24T11:45:48Z")
はい、それは承知しています。ただし、例えばここフォーラムの質問に基づいて、それを拡張することは可能でしょうか？
---
### Author:  [@rishabh](https://meta.discourse.org/u/rishabh)
#### Post date: [2021 年 2 月 25 日午前 7:38 UTC](https://meta.discourse.org/t/calculate-total-participation-moments-by-month-using-data-explorer/179885/14 "2021-02-25T07:38:26Z")
クエリの追加については PR を受け付けています。例を以下に示します：
>
その他にも、[(Superseded) What cool data explorer queries have you come up with?](https://meta.discourse.org/t/what-cool-data-explorer-queries-have-you-come-up-with/43516) や @SidV のリスト [discourse-data-explorer/querys.md at queries · SidVal/discourse-data-explorer · GitHub](https://github.com/SidVal/discourse-data-explorer/blob/queries/querys.md) には、非常に多くの有用なクエリが掲載されています！
---
### Author:  [@Konrad\_Sopala](https://meta.discourse.org/u/Konrad_Sopala)
#### Post date: [2021 年 2 月 25 日午前 8:51 UTC](https://meta.discourse.org/t/calculate-total-participation-moments-by-month-using-data-explorer/179885/15 "2021-02-25T08:51:04Z")
素晴らしい！共有ありがとうございます！
---
### Author:  [@system](https://meta.discourse.org/u/system)
#### Post date: [2021 年 3 月 27 日午前 8:51 UTC](https://meta.discourse.org/t/calculate-total-participation-moments-by-month-using-data-explorer/179885/16 "2021-03-27T08:51:51Z")
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.