Средние значения по годам в Data Explorer?

Создание сообщества Данные и отчетность
1

Мне нужно создать этот SQL-запрос для Data-Explorer:

Мне нужен запрос по годам. На вход, например, даты с 2019-01-01 по 2019-12-31.

Количество ответов по категориям (ТЕМЫ и СООБЩЕНИЯ)
Самые популярные теги за год
Среднее количество новых тем в месяц (я нашел это в панели отчётов администратора)
Среднее количество ответов в месяц
Среднее количество новых пользователей в месяц

Кто-нибудь может помочь с этим? Или хотя бы подсказать направление?

С уважением.

2

@michebs, вероятно, сможет вам помочь.

5

Здравствуйте,

Давайте уточним, правильно ли я понял вашу задачу, прежде чем настраивать остальные запросы:

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
6

@michebs.. Да, именно так, вы очень быстро сообразили.

Если это не слишком большая просьба, должно быть что-то вроде этого:

год месяц avg_topic

2020 01 число
2020 02 число
2020 03 число
и так далее…

И спасибо заранее за это.

С уважением.

7

Без проблем, полагаю, это та информация, которую вы искали:

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

Я подготовлю другие запросы, основываясь на этой логике. :wink:

8

Привет,

Извините за задержку с ответом. Ниже приведены запрошенные данные; некоторые из них оказались сложнее обычного. Если у вас возникнут вопросы, я на связи.

Мишель

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
год id категория кол-во
2020 13 Общее 14
2020 16 База знаний 3
2020 15 Персонал 3
2020 1 Без категории 2
2020 17 Идеи 1
2019 18 Сборки 10
2019 1 Без категории 8
2019 11 CS001x: Введение в информатику 7
2019 13 Общее 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
год ранг название_тега количество
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
год месяц среднее_тем_в_день
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
год месяц среднее_ответов_в_день
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
год месяц среднее_новых_пользователей_в_день
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
9

работает как по волшебству…

ты спасаешь мои дни…

С уважением…