현재 활성 사용자 수가 몇 명인지 팀에 공유하기 위해, 다음과 같은 ‘활성 사용자’ 정의를 적용한 통계를 찾고 있습니다:
지난 1년 동안 로그인 상태에서 글을 읽거나 포럼에 게시글을 작성한 사용자
우리는 메일링 리스트 모드로 참여하여 이메일로 응답하는 사용자가 많기 때문에, 다음 쿼리처럼 단순히 독자의 수만 집계하면 많은 사용자를 놓치게 됩니다:
여기서 게시글을 작성한 사용자의 수도 구할 수 있습니다:
두 그룹을 모두 포함하도록 OR 조건으로 합치는 방법을 모르겠습니다. 도와줄 수 있을까요?
이 쿼리는 지난 1년 동안 로그인한 상태에서 글을 읽었거나 게시물을 작성한 모든 사용자를 반환합니다. 데이터 탐색기(Data Explorer)는 해당 사용자의 수를 출력하기도 합니다.
SELECT p.user_id
FROM posts p
LEFT JOIN topics t ON t.id = p.topic_id
WHERE p.created_at::date > CURRENT_TIMESTAMP - INTERVAL '365 days'
AND t.deleted_at IS NULL
AND t.visible = TRUE
AND t.closed = FALSE
AND t.archived = FALSE
AND t.archetype = 'regular'
AND p.deleted_at IS NULL
UNION
SELECT u.user_id
FROM user_visits u
WHERE u.posts_read > 0
AND u.visited_at > CURRENT_TIMESTAMP - INTERVAL '365 days'
ORDER BY user_id
더 효율적인 방법이 있을 수도 있지만, 일단 잘 동작합니다 
. 특정 기간을 지정하려면 > CURRENT_TIMESTAMP - INTERVAL '365 days' (두 곳 모두)를 다음과 같이 변경할 수 있습니다: BETWEEN '20200101'::date AND '20210101'::date .
질문에 훌륭한 정보와 참고 자료를 제공해 주셨네요! 저는 적절한 부분만 골라 조합하는 것만 하면 되었습니다.
감사합니다! 가능할 때는 다른 사람들이 도움을 주기 쉽게 만드는 것이 좋다고 생각합니다.
UNION - 제 SQL 지식에 없던 부분이었습니다. 이제 해결되었습니다. 감사합니다! 더 다듬어지면 최종 쿼리를 여기에 올리겠습니다.
실제로는 더 많은 정보를 얻고 싶었고 UNION이 다소 제한적이라 약간 다른 방식으로 처리했습니다. 또한 그룹별로 나누어 보고 싶었습니다. 포함된 Active Lurkers 쿼리를 그룹별 정보를 추출하기 위해 제가 가지고 있는 다른 쿼리와 결합하는 방식으로 성공(이라고 생각합니다)을 거두었습니다:
-- [params]
-- int :number_of_days = 365
-- string :group_name = trust_level_0
With included_users AS (
SELECT
gu.user_id
FROM group_users gu
JOIN groups g
ON g.id = gu.group_id
WHERE g.name = :group_name
),
posts_by_user AS (
SELECT COUNT(*) AS posts, p.user_id
FROM posts p
LEFT JOIN topics t ON t.id = p.topic_id
WHERE p.created_at::date > CURRENT_TIMESTAMP - INTERVAL ':number_of_days' day
AND t.deleted_at IS NULL
AND t.visible = TRUE
AND t.closed = FALSE
AND t.archived = FALSE
AND t.archetype = 'regular'
AND p.deleted_at IS NULL
GROUP BY p.user_id
),
posts_read_by_user AS (
SELECT SUM(posts_read) AS posts_read, uv.user_id
FROM user_visits uv
WHERE uv.posts_read > 0
AND uv.visited_at > CURRENT_TIMESTAMP - INTERVAL ':number_of_days' day
GROUP BY uv.user_id
)
SELECT
u.id AS "user_id",
u.username_lower AS "username",
u.last_seen_at,
COALESCE(pbu.posts, 0) AS "posts_created",
COALESCE(prbu.posts_read, 0) AS "posts_read"
FROM users u
LEFT JOIN posts_by_user pbu ON pbu.user_id = u.id
LEFT JOIN posts_read_by_user prbu ON prbu.user_id = u.id
WHERE u.active = true
AND u.id > 0
AND u.id IN (SELECT user_id FROM included_users)
AND (COALESCE(pbu.posts, 0) > 0 OR COALESCE(prbu.posts_read, 0) > 0)
ORDER BY u.id
공유해 주셔서 감사합니다. 이 코드를 사용해 보았고, 배지 쿼리와 매우 유사한 코드도 시도해 보았지만 다음 오류가 발생합니다:
Contract violation:
Query does not return a 'granted_at' column
배지 시스템에서 이 쿼리를 사용하려면 코드에서 무언가 빠뜨린 것 같습니다. 제 사용 사례는 ‘lurkers’ 그룹을 자동화하는 것입니다
교묘하네요!
배지 쿼리는 꽤 복잡한 구조를 가지고 있습니다 - 반환하려면 단순히 'user_id’와 'granted_at’만 있으면 됩니다. 그래서 이 부분을 조금 손봐야 할 것입니다.
배지 쿼리에 관한 주제들을 꼼꼼히 읽어보고 직접 시도해 보시길 추천드립니다. 또한 다른 경우보다 부하가 클 수 있으므로 하루에 한 번만 실행하는 것이 좋습니다.
진행 상황을 알려주세요!