@SaraDev
https://meta.discourse.org/u?cards=no&order=post_count에서 제공되는 메트릭에 대한 SQL 쿼리를 공유해 주실 수 있을까요? 아래 이미지를 참고해 주세요.
메트릭
- 받은 좋아요
- 보낸 좋아요
- 본 주제
- 읽은 게시글
- 방문 일수
- 해결책
- 치어(응원)
카테고리 모더레이터를 활용하고 있으므로, 모더레이터 쿼리를 임의의 그룹에 맞게 수정하고 있습니다.
안녕하세요 @srinivas.chilukuri,
/u 사용자 페이지 통계는 directory_items 테이블을 사용하여 Data Explorer를 통해 조회할 수 있습니다.
사용자 디렉터리 페이지 지표
-- [params]
-- int :period
-- Period Options:
-- 1. all
-- 2. yearly
-- 3. monthly
-- 4. weekly
-- 5. daily
-- 6. quarterly
SELECT
di.user_id,
COALESCE(di.likes_received, 0) AS likes_received,
COALESCE(di.likes_given, 0) AS likes_given,
COALESCE(di.topics_entered, 0) AS topics_viewed,
COALESCE(di.topic_count, 0) AS topic_count,
COALESCE(di.post_count, 0) AS post_count,
COALESCE(di.days_visited, 0) AS days_visited,
COALESCE(di.posts_read, 0) AS posts_read,
COALESCE(di.solutions, 0) AS solutions,
COALESCE(di.gamification_score, 0) AS cheers
FROM
directory_items di
WHERE
di.period_type = :period
ORDER BY
di.user_id
일반적인 start_date 및 end_date 파라미터 대신, 이 테이블의 데이터는 디렉터리 페이지에서 사용할 수 있는 다양한 기간에 해당하는 다음 값들을 가진 period_type 필드를 사용하여 필터링할 수 있습니다:
1: 전체 기간 (all time)2: 연도별 (yearly)3: 월별 (monthly)4: 주별 (weekly)5: 일별 (daily)6: 분기별 (quarterly)
이 리포트의 예시 결과는 다음과 같습니다:
| user | likes_received | likes_given | topics_viewed | topic_count | post_count | days_visited | posts_read | solutions | cheers |
|---|---|---|---|---|---|---|---|---|---|
| Username1 | 4 | 17 | 250 | 69 | 116 | 480 | 217 | 10 | 844100 |
| Username2 | 2 | 5 | 47 | 0 | 2 | 43 | 59 | 1 | 112305 |
| Username3 | 0 | 4 | 2 | 0 | 0 | 2 | 7 | 0 | 3100 |
| … | … | .. | … | … | … | … | … | … | … |
@SaraDev
시작 날짜와 종료 날짜가 필요합니다. 주어진 지표들을 시작 날짜와 종료 날짜를 기준으로 가져올 수 있는 우회 방법이 있을까요?
- user
- likes_received
- likes_given
- topics_viewed
- topic_count
- post_count
- days_visited
- posts_read
- solutions
- cheers
참고: 전체 사용자 중 소수의 하위 집합에 대한 지표만 가져오고 있습니다.
사이트의 사용자에 대한 해당 메트릭을 특정 시작 날짜와 종료 날짜로 필터링하여 확인하려면, 각 메트릭에 대한 데이터를 별도의 CTE(Common Table Expression)로 가져오고, 최종 SELECT 문에서 결과를 결합하는 쿼리가 필요합니다.
다음과 같이 작성할 수 있습니다:
사용자 메트릭
-- [params]
-- date :start_date = 2020-01-01
-- date :end_date = 2025-01-01
WITH likes_received AS (
SELECT
ua.user_id AS user_id,
COUNT(*) AS likes_received
FROM
user_actions ua
WHERE
ua.action_type = 2
AND ua.created_at BETWEEN :start_date AND :end_date
GROUP BY
ua.user_id
),
likes_given AS (
SELECT
ua.acting_user_id AS user_id,
COUNT(*) AS likes_given
FROM
user_actions ua
WHERE
ua.action_type = 1
AND ua.created_at BETWEEN :start_date AND :end_date
GROUP BY
ua.acting_user_id
),
user_metrics AS (
SELECT
us.user_id,
SUM(us.topics_entered) AS topics_viewed,
SUM(us.posts_read_count) AS posts_read,
SUM(us.days_visited) AS days_visited
FROM
user_stats us
WHERE
us.first_post_created_at BETWEEN :start_date AND :end_date
GROUP BY
us.user_id
),
solutions AS (
SELECT
ua.acting_user_id AS user_id,
COUNT(*) AS solutions
FROM
user_actions ua
WHERE
ua.action_type = 15
AND ua.created_at BETWEEN :start_date AND :end_date
GROUP BY
ua.acting_user_id
),
cheers AS (
SELECT
gs.user_id,
SUM(gs.score) AS cheers
FROM
gamification_scores gs
WHERE
gs.date BETWEEN :start_date AND :end_date
GROUP BY
gs.user_id
)
SELECT
u.id AS user_id,
COALESCE(lr.likes_received, 0) AS likes_received,
COALESCE(lg.likes_given, 0) AS likes_given,
COALESCE(um.topics_viewed, 0) AS topics_viewed,
COALESCE(um.posts_read, 0) AS posts_read,
COALESCE(um.days_visited, 0) AS days_visited,
COALESCE(sol.solutions, 0) AS solutions,
COALESCE(ch.cheers, 0) AS cheers
FROM
users u
LEFT JOIN
likes_received lr ON u.id = lr.user_id
LEFT JOIN
likes_given lg ON u.id = lg.user_id
LEFT JOIN
user_metrics um ON u.id = um.user_id
LEFT JOIN
solutions sol ON u.id = sol.user_id
LEFT JOIN
cheers ch ON u.id = ch.user_id
ORDER BY
u.id
쿼리 설명:
- 매개변수:
:start_date와:end_date는 조회할 데이터의 날짜 범위를 정의하는 매개변수입니다.
- 공용 테이블 표현식 (CTE):
- likes_received: 지정된 날짜 범위 내에서 각 사용자가 받은 좋아요 수(
action_type = 2)를 계산합니다. - likes_given: 지정된 날짜 범위 내에서 각 사용자가 준 좋아요 수(
action_type = 1)를 계산합니다. - user_metrics: 지정된 날짜 범위 내에서 첫 게시글을 작성한 사용자의 본 토픽 수, 읽은 게시글 수, 방문일 수 등 사용자 통계를 집계합니다.
- solutions: 지정된 날짜 범위 내에서 각 사용자가 제공한 해결책 수(
action_type = 15)를 계산합니다. - cheers: 지정된 날짜 범위 내에서 각 사용자의 게이미피케이션 점수를 합산합니다.
- 최종 선택:
- 메인 쿼리는 받은 좋아요, 준 좋아요, 본 토픽, 읽은 게시글, 방문일 수, 제공한 해결책, 받은 치어 등 각 사용자의 참여 메트릭을 선택합니다.
- 일부 카테고리에서 활동이 없는 사용자도 포함되도록
LEFT JOIN을 사용하며,COALESCE를 사용하여 0으로 채웁니다.
결과 예시
| user_id | likes_received | likes_given | topics_viewed | posts_read | days_visited | solutions | cheers |
|---|---|---|---|---|---|---|---|
| 1 | 10 | 5 | 20 | 100 | 15 | 2 | 30 |
| 2 | 0 | 3 | 5 | 20 | 5 | 0 | 10 |
| — | — | — | — | — | — | — | — |
위 게시물에 제공된 쿼리를 사용했으며, 다음과 같은 질문이 있습니다:
- 이 정보를 위해
user_stats테이블의user_metrics가 올바른 출처인가요?user_stats는 사용자가 Discourse에 가입한 이후의 메트릭을 요약하는 정적 테이블이므로, 특정 시간 범위(예: 시작 날짜에서 종료 날짜까지)의 메트릭을 필터링하는 데는 이상적이지 않을 수 있습니다. - 시계열 비교 (T/S C/O)
주어진 사용자 집단에 대해 사용자 페이지에서 사용할 수 있는 기간별 데이터를 비교해 보았으며, 상당한 불일치를 발견했습니다.
주요 불일치 항목:
topics_enteredposts_read_countdays_visited
시간에 기반한 사용자 메트릭을 가져오는 더 나은 방법이 있는지 설명해 주실 수 있을까요?
PG::UndefinedColumn: ERROR: column uv.topic_id does not exist
LINE 38: COUNT(DISTINCT uv.topic_id) AS topics_viewed, -- Cou...
user_stats 테이블이 사용자가 Discourse에 가입한 이후의 평생 메트릭을 요약하는 정적 테이블이라는 점은 정확합니다.
대신, posts_read_count와 days_visited와 같이 날짜별로 메트릭을 필터링하려면 posts의 경우 user_visits 데이터베이스 테이블을 사용해야 합니다. 또한 topics_entered 메트릭을 날짜별로 필터링하기 위해 topic_views 테이블도 사용해야 합니다.
관찰된 불일치는 user_stats 테이블을 사용하여 해당 통계를 날짜별로 필터링한 것이 아니라, user_visits 및 topic_views와 같은 다른 테이블을 사용하지 않았기 때문에 발생합니다.
이를 해결하기 위해 해당 데이터베이스 테이블을 사용하도록 쿼리를 업데이트할 수 있습니다:
다음은 업데이트된 버전의 쿼리입니다:
사용자 페이지 메트릭
-- [params]
-- date :start_date = 2020-01-01
-- date :end_date = 2026-01-01
WITH likes_received AS (
SELECT
ua.user_id AS user_id,
COUNT(*) AS likes_received
FROM
user_actions ua
WHERE
ua.action_type = 2
AND ua.created_at BETWEEN :start_date AND :end_date
GROUP BY
ua.user_id
),
likes_given AS (
SELECT
ua.acting_user_id AS user_id,
COUNT(*) AS likes_given
FROM
user_actions ua
WHERE
ua.action_type = 1
AND ua.created_at BETWEEN :start_date AND :end_date
GROUP BY
ua.acting_user_id
),
user_metrics AS (
SELECT
tv.user_id,
COUNT(DISTINCT tv.topic_id) AS topics_viewed
FROM
topic_views tv
WHERE
tv.viewed_at BETWEEN :start_date AND :end_date
GROUP BY
tv.user_id
),
days_and_posts AS (
SELECT
uv.user_id,
COUNT(DISTINCT uv.visited_at) AS days_visited,
SUM(uv.posts_read) AS posts_read
FROM
user_visits uv
WHERE
uv.visited_at BETWEEN :start_date AND :end_date
GROUP BY
uv.user_id
),
solutions AS (
SELECT
ua.acting_user_id AS user_id,
COUNT(*) AS solutions
FROM
user_actions ua
WHERE
ua.action_type = 15
AND ua.created_at BETWEEN :start_date AND :end_date
GROUP BY
ua.acting_user_id
),
cheers AS (
SELECT
gs.user_id,
SUM(gs.score) AS cheers
FROM
gamification_scores gs
WHERE
gs.date BETWEEN :start_date AND :end_date
GROUP BY
gs.user_id
)
SELECT
u.id AS user_id,
COALESCE(lr.likes_received, 0) AS likes_received,
COALESCE(lg.likes_given, 0) AS likes_given,
COALESCE(um.topics_viewed, 0) AS topics_viewed,
COALESCE(dp.days_visited, 0) AS days_visited,
COALESCE(dp.posts_read, 0) AS posts_read,
COALESCE(sol.solutions, 0) AS solutions,
COALESCE(ch.cheers, 0) AS cheers
FROM
users u
LEFT JOIN
likes_received lr ON u.id = lr.user_id
LEFT JOIN
likes_given lg ON u.id = lg.user_id
LEFT JOIN
user_metrics um ON u.id = um.user_id
LEFT JOIN
days_and_posts dp ON u.id = dp.user_id
LEFT JOIN
solutions sol ON u.id = sol.user_id
LEFT JOIN
cheers ch ON u.id = ch.user_id
ORDER BY
u.id
이 방법을 사용하면 user_visits 테이블의 posts_read 데이터에는 중요한 차이점이 하나 있습니다. 이는 사용자의 자신의 게시글을 카운트하지 않는 반면, user_stats 테이블의 데이터는 본인이 작성한 게시글을 포함하므로, 이 쿼리와 사용자 페이지 간의 이 두 통계 사이에 여전히 차이가 있을 수 있습니다.