Data Explorer の年別平均値は？

hhlp · 2020 年 5 月 5 日午前 10:55

Data-Explorer SQL クエリの作成が必要です。

年ごとのリクエストを行いたいと考えています。例えば、2019-01-01 から 2019-12-31 までです。

カテゴリ別（トピックと投稿）のリプライ数
1 年間のトップタグ
月あたりの新規トピックの平均数（これは管理パネルの管理レポートで見つけました）
月あたりのリプライの平均数
月あたりの新規ユーザーの平均数

どなたかお手伝いいただけませんか？あるいは方向性を示していただけませんか？

よろしくお願いいたします。

HAWK · 2020 年 5 月 7 日午前 3:01

@michebs さんがおそらくお手伝いできるはずです。

michebs · 2020 年 5 月 7 日午前 10:26

こんにちは、

他のクエリを設定する前に、これがご要望に合っているか確認させてください。

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

hhlp · 2020 年 5 月 7 日午後 3:00

@michebs.. はい、その通りです。非常に早くご理解くださいました。

もし差し支えなければ、以下のような形になります。

年月 avg_topic

2020 01 数値
2020 02 数値
2020 03 数値
など…

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

よろしくお願いいたします。

michebs · 2020 年 5 月 7 日午後 4:08

問題ありません。お探しの情報はこれだと考えられます：

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

このパターンに基づいて、他のクエリも設定いたします。

michebs · 2020 年 5 月 16 日午後 8:55

こんにちは、

ご返信が遅くなり申し訳ございません。ご依頼いただいたクエリは、一部が通常よりも複雑であったため、作成に時間を要しました。ご不明な点がございましたら、いつでもお気軽にご質問ください。

ミシェル

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

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

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

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

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

hhlp · 2020 年 5 月 17 日午前 8:58

本当に素晴らしいです…

おかげで私の日々が救われました…

よろしくお願いいたします…

トピック		返信	表示
Get topic count per month for a certain category Data & reporting sql-query	1	895	2022 年 3 月 27 日
Calculate total participation moments by month using Data Explorer Data & reporting sql-query	13	1368	2021 年 3 月 27 日
Top 50 active topics (and their associated categories) Data & reporting sql-query	0	158	2018 年 2 月 12 日
Aggregate dashboard report data by time interval Data & reporting sql-query	4	1165	2022 年 9 月 24 日
Help finding some monthly site stats about topics and user trust levels Data & reporting sql-query	22	5568	2021 年 11 月 12 日

Data Explorer の年別平均値は？

年 月 avg_topic

関連トピック

年月 avg_topic