数据探索器按年求平均值？

hhlp · 2020 年5 月 5 日 10:55

我需要创建这个 Data-Explorer SQL 查询：

我需要按年份进行请求。例如输入 2019-01-01 至 2019-12-31。

按类别（主题和帖子）统计回复数量
年度热门标签
每月新主题的平均数量（我在管理面板的管理报告中找到了这个数据）
每月回复的平均数量
每月新用户的平均数量

有人能帮帮我吗？或者给我一些指导？

此致，

HAWK · 2020 年5 月 7 日 03: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 日 15:00

@michebs.. 是的，完全正确，你理解得很快

如果不麻烦的话，格式可以像这样：

年月平均主题

2020 01 数值
2020 02 数值
2020 03 数值
等等……

提前感谢！

此致，

michebs · 2020 年5 月 7 日 16: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 日 20:55

你好，

抱歉回复晚了。以下是您要求的查询结果，由于部分查询比平时更复杂，如果您有任何疑问，我随时可以提供帮助。

Michelle

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 日 08:58

效果出奇地好……

你帮了我大忙……

致敬……

话题		回复	浏览量
Get topic count per month for a certain category Data & reporting sql-query	1	898	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	159	2018 年2 月 12 日
Aggregate dashboard report data by time interval Data & reporting sql-query	4	1169	2022 年9 月 24 日
Help finding some monthly site stats about topics and user trust levels Data & reporting sql-query	22	5572	2021 年11 月 12 日

数据探索器按年求平均值？

年 月 平均主题

相关话题

年月平均主题