안녕하세요! 저희는 클라우드 호스팅 버전의 Discourse를 사용 중이며, 플랫폼을 약 1.5년간 운영해 왔습니다. 플랫폼 채택 및 사용 현황에 대한 과거 데이터를 확인하고 싶습니다. 관리자 보고서 탭의 ‘가입자’ 보고서를 이미 확인하고 있지만, 매월 활성 사용자 총 수도 표시할 수 있으면 좋겠습니다. 이 작업을 수행하는 방법에 대해 조언을 주실 수 있는 분이 계신가요? 보고서나 데이터 탐색기에서 적절한 기능을 찾지 못하고 있습니다.
이 데이터 탐색기 쿼리는 월별 고유 사용자 방문 수를 반환합니다. 이것이 원하시는 내용인가요?
--[params]
-- date :start_date = 2021-01-01
-- date :end_date = 2021-12-31
-- string_list :group_name = staff,admins
WITH not_allowed_groups AS (
SELECT gu.user_id FROM group_users gu
INNER JOIN groups g ON (g.id = gu.group_id)
WHERE g.name IN (:group_name)
)
SELECT
to_char(date_trunc('month', uv.visited_at)::DATE,'YYYY-MM') AS "month",
date_trunc('month', uv.visited_at)::DATE AS date_user_visits,
COUNT(distinct uv.user_id) AS qtt_user_visits
FROM user_visits uv
WHERE
uv.user_id NOT IN (SELECT user_id FROM not_allowed_groups)
AND uv.visited_at::DATE BETWEEN :start_date AND :end_date
GROUP BY date_trunc('month', uv.visited_at)::DATE
ORDER BY date_trunc('month', uv.visited_at)::DATE
감사합니다만, 정확히 그렇지는 않습니다… 우리는 매 달력 월 말 기준으로 활성 상태(가입되어 있고 비활성화 또는 삭제되지 않은 상태)인 사용자 수를 알고 싶습니다. 해당 월에 플랫폼을 방문했는지 여부는 중요하지 않습니다.
현재 가입 추이 그래프를 보고 있지만, 특정 월의 가입자 합계는 사용자 기반을 정확히 반영하지 못합니다. 매월 비활성화된 사용자는 자동으로 비활성화되기 때문입니다. 특정 날짜에 대한 과거 사용자 목록 내보내기에 접근하는 것이 유일한 방법인지 궁금합니다. 하지만 우리는 클라우드 호스팅을 사용하므로 해당 기능에 접근할 수 있는지 확실하지 않습니다. Discourse에 요청해야 할까요?
저는 디스커스입니다. 
여기서 질문하시거나 team@discourse.org으로 직접 이메일을 보내주시면 됩니다. SQL에 대해 더 잘 아시는 분이 도와드릴 수 있을 거예요.
이 포럼에 처음 온 터라 몰랐네요. 감사합니다! 클라우드 호스팅 사이트의 과거 백업 파일을 보관하고 계신가요? 요청 시 제공해 주실 수 있을까요?
비즈니스 연속성을 위해 백업을 저장하며, 비상 상황에서 복구할 수 있지만, 그 외의 목적으로는 실제로 사용되지 않습니다.
좋아요, 또 다른 아이디어가 있습니다. 우리 AI 도우미에게 물어봤습니다.
월별 활성 사용자 SQL 쿼리 AI
HAWK:
아직 활성 상태이면서 특정 월 내에 가입한 사용자 계정 목록을 반환하는 데이터 탐색기(Data Explorer) 쿼리를 작성해 주세요
DiscourseHelper:
검색 중
검색 내용: 특정 월 내에 가입한 활성 사용자를 찾는 Discourse 데이터 탐색기 쿼리
읽는 중
읽은 내용: 특정 월의 활성 사용자
아직 활성 상태이면서 특정 월 내에 가입한 사용자 계정 목록을 반환하는 데이터 탐색기 쿼리를 작성하려면 다음 SQL 쿼리를 사용할 수 있습니다. 이 쿼리는 활성 사용자가 주어진 시간 범위 내에 게시글이나 답글을 작성한 사람이라고 가정합니다.
--[params]
-- int :month = 5
-- int :year = 2023
SELECT u.id, u.username, u.created_at, u.last_seen_at
FROM users u
WHERE u.active = TRUE
AND EXTRACT(MONTH FROM u.created_at) = :month
AND EXTRACT(YEAR FROM u.created_at) = :year
AND u.last_seen_at IS NOT NULL -- 활성 상태는 최근 사이트에 접속했다는 의미로 가정
ORDER BY u.created_at
쿼리 설명:
- **
:month와:year**는 특정 월과 연도로 필터링하기 위해 설정할 수 있는 매개변수입니다. u.active = TRUE는 사용자 계정이 아직 활성 상태임을 보장합니다.EXTRACT(MONTH FROM u.created_at) = :month와EXTRACT(YEAR FROM u.created_at) = :year는 가입 날짜를 지정된 월과 연도로 필터링합니다.u.last_seen_at IS NOT NULL은 사용자가 언제든지 로그인한 경우를 활성 상태로 간주합니다.- 결과는 사용자 ID, 사용자 이름, 가입 날짜, 마지막 접속 날짜를 제공합니다.
필요에 따라 매개변수와 필드를 조정해 보세요. 더 도움이 필요하시면 말씀해 주세요!
무엇이 빠져 있는지 확인하려면 추가적인 맥락을 자유롭게 제공해 주세요.
정말 멋진 쿼리 설명 감사합니다! 하지만 unfortunately 이것이 우리가 찾는 것이 아닌 것 같습니다.
예를 들어, 매개변수에 01과 2024를 설정하면 올해 1월 데이터를 보고 있는 것입니다.
출력 결과에는 1월에 가입하여 현재까지도 활성 상태인 모든 사용자가 포함된다는 것을 이해하고 있습니다. 맞습니까?
하지만 우리가 원하는 것은 출시 이후 매월 u.active=TRUE 상태였던 사람의 수(현재 시점이 아닌)입니다. 이렇게 해야 사용자 기반이 월별로 어떻게 변화했는지 차트로 보여줄 수 있습니다. 매월 말 기준으로, 우리에게 이것은 다음과 같습니다:
활성 사용자 = 이전 달의 값 + 신규 가입자 - 신규 삭제 - 신규 비활성화
데이터 탐색기(Data Explorer)에서 비활성화나 활성 변수와 날짜가 연결된 내용을 볼 수 없습니다. 그렇다면 가장 가능성 있는 방법은 직원 작업 로그(staff action logs)를 날짜별로 분석하는 것이겠지만, 내보내기 기능이 우리에게 작동하지 않는 것 같습니다. 직원 작업 로그 결과를 내보내면 이상하게도 직원 작업 테이블이 아닌 다른 게시물의 내용이 포함된 스프레드시트가 나옵니다. 이 문제에 대해 도움을 주실 수 있다면, 그것이 대안이 될 수 있을 것 같습니다! 다른 분들도 동일한 문제를 겪고 있는지 알고 계신가요?
흥미로운 질문이네요.
먼저 여기의 예시를 살펴봤습니다. 하지만 이 방법은 삭제된 사용자를 무시합니다. 해당 시점에 등록되어 현재까지 남아 있는 사용자의 수만 얻을 수 있을 뿐, 그 사이에 삭제된 사용자는 포함되지 않습니다.
따라서 제 아이디어는 해당 월에 마지막으로 등록한 사용자의 ID를 가져오는 것이었습니다. 이는 해당 시점의 최대 가능한 사용자 수입니다. 이후 삭제된 사용자의 수를 이 값에서 빼면 됩니다. 다만, 봇 계정(예: forum-helper)은 ID가 음수이지만 삭제 시에는 계산에 포함됩니다. (하지만 이는 아마도 사소한 차이일 것입니다.) 제 쿼리는 다음과 같습니다:
-- [params]
-- date :start_date
-- date :end_date
WITH month_dates AS (
-- 시작일과 종료일 사이의 월 말 날짜 생성
SELECT DATE_TRUNC('month', generate_series)::date + INTERVAL '1 month' - INTERVAL '1 day' AS month_end
FROM generate_series(:start_date::date, :end_date::date, '1 month'::interval)
),
recent_user AS (
-- 각 월 말 날짜에 대해, 그 날짜 이전에 생성된 가장 최근 사용자 찾기
SELECT md.month_end,
(SELECT id
FROM users u
WHERE u.created_at < md.month_end
ORDER BY u.created_at DESC
LIMIT 1) AS user_max_id
FROM month_dates md
),
cumulative_deletion_count AS (
-- 각 월 말 날짜까지의 누적 삭제 수 계산
SELECT md.month_end,
(SELECT COUNT(*)
FROM user_histories uh
WHERE uh.action = 1 AND uh.updated_at < md.month_end) AS deletions_count
FROM month_dates md
)
SELECT
md.month_end,
ru.user_max_id,
cdc.deletions_count,
ru.user_max_id - cdc.deletions_count AS number_of_users
FROM
month_dates md
LEFT JOIN recent_user ru ON md.month_end = ru.month_end
LEFT JOIN cumulative_deletion_count cdc ON md.month_end = cdc.month_end
ORDER BY md.month_end
하지만 이 쿼리는 user_histories 테이블에도 저장되어 있는 (비)활성화 상태를 고려하지 않습니다. 그래도 시작점으로 도움이 될 수 있을 것입니다.
Hey @marvi,
@Moin의 답변이 도움이 되었는지 궁금해서 여쭤봅니다.