우선, 새해 복 많이 받으세요!! 
관리자를 제외한 사용자들이 월별로 작성한 주제별 평균 답변 수를 가져오기 위해 사용할 SQL 쿼리가 궁금합니다.
아니면, 월별로 멤버가 게시한 총 게시글 수와 스태프가 게시한 총 게시글 수의 비율을 구하는 방법이 있을까요?
감사합니다!
새해 복 많이 받으세요 (조금 늦었지만요 
)
평균을 구할 때, 스태프와 비스태프가 함께 생성한 토픽을 대상으로 하되, 답변 수는 비스태프 게시글만 기준으로 삼는 것을 원하시나요? 그리고 관리자만 제외할까요, 아니면 관리자와 모더레이터 모두를 제외할까요?
Jammy, 안녕하세요!
네, 정확합니다. 쿼리는 스태프와 비스태프가 모두 생성한 모든 토픽을 조회할 수 있지만, 답글 수는 비스태프 게시글에서만 집계됩니다.
현재로서는 관리자만 제외할 수 있습니다(커뮤니티를 시작하는 단계라 관리자와 모더레이터가 동일하기 때문이죠 :)).
하지만 관리자(관리자 제외)를 기준으로 스태프가 작성한 토픽과 비스태프가 작성한 토픽의 비율을 쉽게 확인할 수 있으면 좋겠습니다.
이런 방식으로 원하는 숫자를 얻을 수 있을 것 같습니다:
-- [params]
-- date :start_date
-- date :end_date
WITH staff_data AS (
SELECT user_id, true as is_staff
FROM group_users
WHERE group_id = 3
),
month_stats AS (
SELECT
date_trunc('month', p.created_at)::date AS month,
COUNT(*) FILTER (WHERE p.post_number = 1) AS total_topics,
COUNT(*) FILTER (WHERE p.post_number <> 1) AS total_posts,
COUNT(DISTINCT p.user_id) FILTER (WHERE p.post_number <> 1 AND is_staff IS NOT TRUE) AS non_staff_users,
COUNT(*) FILTER (WHERE p.post_number <> 1 AND is_staff IS NOT TRUE) AS non_staff_posts,
COUNT(DISTINCT p.user_id) FILTER (WHERE p.post_number <> 1 AND is_staff) AS staff_users,
COUNT(*) FILTER (WHERE p.post_number <> 1 AND is_staff) AS staff_posts
FROM posts p
LEFT JOIN topics t ON t.id = p.topic_id
LEFT JOIN staff_data s ON p.user_id = s.user_id
WHERE p.created_at::date BETWEEN :start_date AND :end_date
AND t.archetype = 'regular'
AND t.deleted_at IS NULL
AND p.deleted_at IS NULL
AND p.post_type = 1
AND p.user_id > 0
GROUP BY month
)
SELECT
ms.month AS "Month",
ms.total_topics AS "All Topics",
ms.total_posts AS "All Posts",
ms.non_staff_posts AS "Non-staff posts",
ROUND(ms.non_staff_posts * 100.0 / NULLIF(ms.total_posts, 0),1) || '%' AS "Non-staff posts (% of total)",
ms.non_staff_posts / NULLIF(ms.total_topics, 0) AS "Average non-staff posts per topic",
ms.non_staff_users AS "Non-staff users who posted",
ms.staff_posts AS "Staff posts",
ROUND(ms.staff_posts * 100.0 / NULLIF(ms.total_posts, 0),1) || '%' AS "Staff posts (% of total)",
ms.staff_posts / NULLIF(ms.total_topics, 0) AS "Average staff posts per topic",
ms.staff_users AS "Staff users who posted"
FROM month_stats ms
ORDER BY "Month"
이렇게 하면 다음과 같은 결과가 나옵니다:
그리고 참고로 설명을 조금 덧붙여 보겠습니다:
이 쿼리는 특정 기간 내 포럼 활동의 월별 통계 요약을 제공하도록 설계되었습니다. 특히 ‘스태프’와 ‘비스태프’ 사용자의 기여도를 구분하는 데 초점을 맞춥니다. 계산되는 지표에는 생성된 전체 토픽 수, 작성된 전체 게시물 수, 게시물을 작성한 고유한 비스태프 사용자 수, 비스태프가 작성한 게시물의 수와 비율, 토픽당 평균 비스태프 게시물 수, 그리고 스태프에 해당하는 동일한 수치들이 포함됩니다. 이 정보는 사용자 참여도, 콘텐츠 생성량, 그리고 포럼 토론에서 스태프와 비스태프 멤버의 참여율을 파악하는 데 유용한 통찰력을 제공합니다. 쿼리는 ‘일반’(비-PM) 토픽만 고려하고, 삭제된 게시물이나 토픽, 속삭임/소형 게시물/관리자 조치, 시스템 사용자의 게시물을 제외함으로써 정확성을 보장합니다. 지정된 날짜 범위 내에서만 데이터를 처리합니다.
이 쿼리에서 ‘스태프’ 기준은 자동 @staff 그룹에 속하는 사용자로, 관리자(Admin)와 모더레이터(Moderator)를 모두 포함합니다 - 단, 관리자만 대상으로 하거나, 엄밀히 '사이트 스태프’가 아닌 임직원으로 구성된 커스텀 그룹을 대상으로하도록 조정할 수도 있습니다. 모더레이터를 확실히 제외하고 싶다면, 시작 부분의 group_id를 '1’로 변경하면 됩니다. 
이런 종류의 것을 찾고 계신가요?
이상한 이유로 그 직원의 ID가 3이라는 것은 알고 있습니다. 그런데 그 ID를 어떻게 찾을 수 있을까요? 처음에는 다른 모든 ID처럼 URL에 직접 포함되어 있을 것이라고 확신했지만, 그렇지 않았습니다. 실제로는 이름만 사용되고 있습니다.
제가 아는 SQL 지식이 너무 부족해서 솔직히 잘 모른다고 해도 될 정도이지만, 아래 쿼리는 모든 그룹 ID를 보여줍니다.
select
id,
name
from
groups
하지만 분명히 더 일반적인 방법으로 찾을 수 있는 방법이 있을 텐데요, 그렇지 않나요?
개인적으로 user_id처럼 group_id 파라미터 조회 기능이 정말 있으면 좋겠습니다. 
- Param dropdown for group_id in data explorer query
하지만 그 꿈이 이루어질 때까지는 그룹 페이지의 json을 활용해서 알아내곤 합니다. 예를 들어 https://meta.discourse.org/g.json 처럼요.
쿼리 내부에서 그룹을 조회할 수 있으므로 그룹 이름으로 동작합니다. 이는 더 사용자 친화적인 방식일 수 있습니다. 예를 들어 다음과 같이 사용할 수 있습니다:
-- [params]
-- string :group_name
SELECT user_id, true as is_staff
FROM group_users
WHERE group_id = (SELECT id FROM groups WHERE name = LOWER(:group_name))
(파라미터를 원하지 않는다면 하드코딩된 버전:)
SELECT user_id, true as is_staff
FROM group_users
WHERE group_id = (SELECT id FROM groups WHERE name = 'admins')
아, 네. 제가 언급했어야 할 특이사항이네요. 페이지를 새로고침하면 매개변수 입력 상자가 표시되어야 합니다.
그 스니펫을 즉석에서 만든 거 맞지? 완벽하게 잘 동작하진 않으니까 
원하는 그룹의 모든 사용자를 표시하면서, 모든 사람을 is_staff로 처리하고 있거든 
그래도 고마워! 기본 수준 관리자를 위한 json과 SQL 사용법에 대해 유용한 정보를 얻었어 (정말 유용했지만, 그래도 AI 리포트가 그걸 어떻게 보는지 보고 싶어…)
그 예에서 is_staff 비트는 이 특정 쿼리의 일부입니다. 데이터베이스 자체에서 가져오는 것이 아니라, 여기 SELECT user_id, true as is_staff에 명시적으로 추가됩니다. 이는 사용자가 '직원’으로 지정된 그룹에 속하도록 설정하여, 두 개의 결과 집합(직원 게시물과 비직원 게시물)으로 나눌 수 있게 합니다.
따라서 데이터베이스에서 설정하는 진정한 사이트 직원은 아니지만 ‘직원’ 그룹이 있다면, 그들을 추가할 수 있으며, 그들은 ‘비직원’ 버킷이 아닌 ‘직원’ 버킷에 들어가게 됩니다.
와, 정말 딱 필요했던 거네요. 정말 감사합니다!
확인차 여쭤볼게요: "Posts"는 주제와 답변을 모두 합산하는 건가요, 아니면 답변만 카운트하는 건가요?
다시 한번 감사합니다!
이 항목의 경우 'posts’에는 토픽의 첫 번째 게시물이 포함되지 않고, 답글만 포함됩니다.
안녕하세요 @JammyDodger
같은 기능을 주제(=새로운 스레드 생성)에만 적용하는 것이 가능할까요?
정말 감사합니다!
이 쿼리에 추가된 스태프가 생성한 주제와 비스태프가 생성한 주제 간의 비율을 말씀하시는 건가요?
오, 네! 같은 쿼리에서 처리할 수 있다면 정말 최고일 것 같아요!
이 컬럼들을 추가하면 될 것 같습니다:
-- [params]
-- date :start_date
-- date :end_date
WITH staff_data AS (
SELECT user_id, true as is_staff
FROM group_users
WHERE group_id = 3
),
month_stats AS (
SELECT
date_trunc('month', p.created_at)::date AS month,
COUNT(*) FILTER (WHERE p.post_number = 1) AS total_topics,
COUNT(*) FILTER (WHERE p.post_number = 1 AND is_staff IS NOT TRUE) AS non_staff_topics,
COUNT(*) FILTER (WHERE p.post_number = 1 AND is_staff IS TRUE) AS staff_topics,
COUNT(*) FILTER (WHERE p.post_number <> 1) AS total_posts,
COUNT(DISTINCT p.user_id) FILTER (WHERE p.post_number <> 1 AND is_staff IS NOT TRUE) AS non_staff_users,
COUNT(*) FILTER (WHERE p.post_number <> 1 AND is_staff IS NOT TRUE) AS non_staff_posts,
COUNT(DISTINCT p.user_id) FILTER (WHERE p.post_number <> 1 AND is_staff) AS staff_users,
COUNT(*) FILTER (WHERE p.post_number <> 1 AND is_staff) AS staff_posts
FROM posts p
LEFT JOIN topics t ON t.id = p.topic_id
LEFT JOIN staff_data s ON p.user_id = s.user_id
WHERE p.created_at::date BETWEEN :start_date AND :end_date
AND t.archetype = 'regular'
AND t.deleted_at IS NULL
AND p.deleted_at IS NULL
AND p.post_type = 1
AND p.user_id > 0
GROUP BY month
)
SELECT
ms.month AS "Month",
ms.total_topics AS "All Topics",
ms.non_staff_topics AS "Non-staff topics",
ROUND(ms.non_staff_topics * 100.0 / NULLIF(ms.total_topics, 0),1) || '%' AS "Non-staff topics (% of total)",
ms.staff_topics AS "Staff topics",
ROUND(ms.staff_topics * 100.0 / NULLIF(ms.total_topics, 0),1) || '%' AS "Staff topics (% of total)",
ms.total_posts AS "All Posts",
ms.non_staff_posts AS "Non-staff posts",
ROUND(ms.non_staff_posts * 100.0 / NULLIF(ms.total_posts, 0),1) || '%' AS "Non-staff posts (% of total)",
ms.non_staff_posts / NULLIF(ms.total_topics, 0) AS "Average non-staff posts per topic",
ms.non_staff_users AS "Non-staff users who posted",
ms.staff_posts AS "Staff posts",
ROUND(ms.staff_posts * 100.0 / NULLIF(ms.total_posts, 0),1) || '%' AS "Staff posts (% of total)",
ms.staff_posts / NULLIF(ms.total_topics, 0) AS "Average staff posts per topic",
ms.staff_users AS "Staff users who posted"
FROM month_stats ms
ORDER BY "Month"
정말 감사합니다. 완벽합니다!