Data Explorer を使用して月ごとの総参加モーメントを計算する

Konrad_Sopala · 2021 年 2 月 16 日午後 2:14

こんにちは、データエクスプローラーの魔法使いの皆さん！

誰か、データエクスプローラーのクエリを使って、月ごとの参加回数（投稿数、解決数、いいね数の合計）を取得するクエリを作成したことがありますか？

事前にありがとうございます！

Rotblut · 2021 年 2 月 16 日午後 3:30

もしよろしければご覧ください。

github.com/SidVal/discourse-data-explorer

querys.md

queries

---
This plugin allows admins to run SQL queries against the live Discourse database, 
including parameterized queries and formatting for several common column types.

More info: https://meta.discourse.org/t/data-explorer-plugin/32566
---

# List of Queries

* Fetch top 10 posts by likes received in the last month - [SQL](https://github.com/SidVal/discourse-data-explorer/blob/queries/queries/top-posts-by-likes.sql)
* Users Last Seen - [SQL](https://github.com/SidVal/discourse-data-explorer/blob/queries/queries/users-last-seen.sql)
  + Since N Weeks Ago
  + Since N Days Ago
* Banner Stats - [SQL](https://github.com/SidVal/discourse-data-explorer/blob/queries/queries/banner-stats.sql)
* Top Quality Users - [SQL](https://github.com/SidVal/discourse-data-explorer/blob/queries/queries/top-quality-users.sql)
* Posts Read (Daily) - [SQL](https://github.com/SidVal/discourse-data-explorer/blob/queries/queries/posts-read-daily.sql)
* Posts Read Percentiles - [SQL](https://github.com/SidVal/discourse-data-explorer/blob/queries/queries/posts-read-percentiles.sql)
* Active Readers - [SQL](https://github.com/SidVal/discourse-data-explorer/blob/queries/queries/active-readers.sql)
  + Active Readers (Past Month)
  + Active Readers (Since N Days Ago)

This file has been truncated. show original

Konrad_Sopala · 2021 年 2 月 17 日午後 3:53

探しているものが見つかりません。

simon · 2021 年 2 月 18 日午前 12:51

私はデータエクスプローラーの専門家ではありませんが、時間があればクエリを書くのが好きです。あまり先走ってしまわないように、あなたが求めているのは統計データの月別内訳だと仮定しています。もしそうであれば、以下のようなクエリが役立つかもしれません：

--[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 列は、その月にサイトに「ログインした」一意のユーザー数を返すものであり、これらのユーザーがサイトに何らかのアクション（例：投稿への「いいね」や投稿の作成など）を実行したかどうかは確認していません。クエリでそのようにすることも可能かもしれませんが、タイムアウトの問題が懸念されます。

もしこれが求めているデータの種類でなければ、お知らせください。基本的な考え方が合っていれば、クエリに追加で必要なデータがあるか、または結果に何か不自然な点があるかをお知らせください。

Konrad_Sopala · 2021 年 2 月 18 日午後 12:05

@simon さん、共有ありがとうございます。とても役立ちますし、ほぼ私が探していたものです！クエリに対して1つ変更を加えることは可能でしょうか？開始日を指定したくないのです。これらの値（投稿、ユーザー、解決策、いいね）をすべて合計し、月別に次のようなクエリ結果にしたいのです。

simon · 2021 年 2 月 18 日午後 5:18

これは可能かもしれません。試してみます。開始日パラメータは、サイトがオンラインになっている期間全体でメタサイト上でクエリを実行するとタイムアウトが発生することが判明した直前に、クエリに追加されました。クエリの効率を向上させてタイムアウトの問題を解消する方法があるかもしれません。もしそれが不可能な場合、開始日のみを指定するのではなく、時間枠を設定できるようにクエリを変更すべきです。そうすれば、異なる時間枠でクエリを数回実行することで、サイトのすべてのデータを取得できるようになります。

Konrad_Sopala · 2021 年 2 月 19 日午前 8:41

もちろん！その調整ができたら、いつでもお待ちしています。

michebs · 2021 年 2 月 19 日午後 7:40

こんにちは、Konrad さん、

以下に調整済みのクエリを示します。


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 詳細

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

Konrad_Sopala · 2021 年 2 月 23 日午前 10:35

まさに探していたものです！@michebs さん、ありがとうございます！Data Explorer クエリのオープンソースコレクションを作成して、Discourse ユーザーと共有することを検討されたことはありますか？

codinghorror · 2021 年 2 月 23 日午後 10:01

はい、それはデータエクスプローラーに組み込まれているクエリの一覧です

Konrad_Sopala · 2021 年 2 月 24 日午前 11:45

はい、それは承知しています。ただし、例えばここフォーラムの質問に基づいて、それを拡張することは可能でしょうか？

rishabh · 2021 年 2 月 25 日午前 7:38

クエリの追加については PR を受け付けています。例を以下に示します：

その他にも、(Superseded) What cool data explorer queries have you come up with? や @SidV のリスト discourse-data-explorer/querys.md at queries · SidVal/discourse-data-explorer · GitHub には、非常に多くの有用なクエリが掲載されています！

Konrad_Sopala · 2021 年 2 月 25 日午前 8:51

素晴らしい！共有ありがとうございます！

system · 2021 年 3 月 27 日午前 8:51

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.

トピック		返信	表示
Retrospectively pulling number of users each calendar month Data & reporting	9	237	2024 年 11 月 12 日
Help finding some monthly site stats about topics and user trust levels Data & reporting sql-query	22	5565	2021 年 11 月 12 日
Get member count overall by month using Data Explorer Data & reporting sql-query	6	644	2021 年 3 月 12 日
Data Explorer averages by year? Data & reporting sql-query	6	1363	2020 年 5 月 17 日
Aggregate dashboard report data by time interval Data & reporting sql-query	4	1165	2022 年 9 月 24 日

Data Explorer を使用して月ごとの総参加モーメントを計算する

画像と完全に一致するようにすべての列を追加する必要がある場合は、以下のクエリを使用してください：

関連トピック