Подсчет общего количества моментов участия по месяцам с помощью Data Explorer

[Konrad_Sopala]

Привет, волшебники Data Explorer!

Кто-нибудь пробовал создать запрос, который будет показывать общее количество моментов участия по месяцам с помощью запросов Data Explorer?

Имеются в виду общее количество всех сообщений, решений и лайков по месяцам.

Заранее спасибо!

[Rotblut (Bernhard)]

Посмотрите, если вам это интересно.

querys.md

[Konrad_Sopala]

Я не вижу именно того, что ищу

[simon]

@Konrad_Sopala

Привет, волшебники Data Explorer!

Я не эксперт в Data Explorer, но мне нравится писать запросы, когда есть время. Прежде чем я увлекусь этим слишком сильно, я исхожу из того, что вам нужна разбивка статистики по месяцам. Если так, то что-то вроде этого может сработать:

--[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]

Спасибо @simon за то, что поделились! Это действительно полезно и в основном соответствует тому, что я ищу! Могу я спросить вас, можно ли внести одно изменение в запрос? Я не хочу указывать дату начала. Я бы хотел суммировать все эти значения (сообщения, пользователи, решения, лайки) и получить результат запроса в таком виде, по месяцам:

[simon]

@Konrad_Sopala

Мне не нравится указывать дату начала. Я хотел бы суммировать все эти значения (сообщения, пользователи, решения, лайки) и получить результат запроса в таком виде, по месяцам:

Это, возможно, осуществимо. Я попробую. Параметр даты начала был добавлен в запрос в последний момент, когда я обнаружил, что запрос будет истекать по времени, если запустить его на Meta за весь период работы сайта. Возможно, существуют способы повысить эффективность запроса, чтобы устранить проблему с таймаутом. Если нет, то запрос должен позволять устанавливать временной диапазон вместо того, чтобы запрашивать только дату начала. Таким образом, вы сможете извлечь все данные по сайту, запустив запрос несколько раз с разными временными диапазонами.

[Konrad_Sopala]

Конечно! Жду вас, когда у вас будет время внести это изменение.

[michebs (Michelle)]

Привет, Конрад,

Ниже приведен скорректированный запрос.

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]

Это именно то, что я искал! Еще раз спасибо @michebs! Вы думали о создании какой-нибудь коллекции запросов Data Explorer с открытым исходным кодом и о том, чтобы поделиться ею с пользователями Discourse?

[codinghorror (Jeff Atwood)]

Да, это встроенный список запросов, который входит в Data Explorer

[Konrad_Sopala]

Да, я знаю, но можно ли это расширить? Например, на основе вопросов здесь, с форума.

[rishabh]

Мы принимаем PR для добавления запросов, вот пример:

FEATURE: Add a poll results query (#43)

Кроме того, в теме (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]

Отлично! Спасибо, что поделились!