Data Explorer로 월별 총 참여 모멘트 계산하기

[Konrad_Sopala]

데이터 탐색 마스터 여러분 안녕하세요!

데이터 탐색 쿼리를 이용해 월별 총 참여 횟수를 계산하는 쿼리를 작성해 본 분이 계신가요?

즉, 월별 전체 게시물, 해결책, 좋아요 수를 합산하는 것입니다.

미리 감사드립니다!

[Rotblut (Bernhard)]

이것을 좋아하신다면 보세요.

querys.md

[Konrad_Sopala]

찾고 있는 정확한 항목을 찾을 수 없습니다.

[simon]

@Konrad_Sopala

안녕하세요, 데이터 탐색 마법사 여러분!

저는 데이터 탐색 전문가가 아니지만, 시간이 나면 쿼리를 작성하는 것을 좋아합니다. 너무 깊게 들어가기 전에, 여러분이 월별 통계 분석을 원하시는 것으로 가정하고 있습니다. 그렇다면 다음과 같은 쿼리가 도움이 될 수 있습니다:

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

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 님, 다시 한번 감사드립니다! 디스코urs 사용자들과 공유할 수 있는 Data Explorer 쿼리의 오픈소스 컬렉션을 만들어보는 건 어떨지 생각해 본 적 있으신가요?

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

좋아요! 알려주셔서 감사합니다!