사용자들이 어떤 내용을 게시하는지 이해하기

커뮤니티 구축 데이터 및 보고
1

SQL 기반 리포트를 작성하여 사용자가 어떤 종류의 주제에 대해 게시물을 올리는지 파악하고 싶습니다.

카테고리, 주제, 태그는 이 목적을 위해 사용할 수 있는 필드로 확인했고, postcount를 지표로 사용할 것 같습니다(기본적으로 이를 통해 인기도를 파악하고 싶습니다).

이 목적을 지원하는 다른 필드가 있을까요? 다른 사용자들도 이런 요청을 했을 것 같으니, 참고할 수 있는 기존 쿼리가 있을까요?

2

SQL 쿼리는 아니지만, 아마도 이미 알고 계셨을 텐데요:

/categories는 각 카테고리별로 월별 신규 주제 수를 표시하며, 꽤 유용할 수 있습니다.

/tags는 태그별 주제 수를 표시합니다.

혹시 몰라서 말씀드립니다 (이미 알고 계셨을 수도 있지만요!)

3

여기서 무엇을 말씀하시는 건지 정확히 이해하지 못하겠습니다.

커뮤니티의 관리자 영역에 대한 보고서들을 말씀하시는 건가요?

해당 내용을 확인해 보았지만, 기본적으로 모든 것을 한 곳에 통합하고 싶습니다. 예를 들어, 각 카테고리마다 얼마나 많은 주제나 태그가 있는지 한눈에 볼 수 있도록요.

4

URL 경로입니다.

5

카테고리, 태그, 그리고 주제(토픽)를 각각 게시글 수와 함께 동일한 보고서/쿼리에 포함하는 방법에 대해 정확히 어떤 것을 염두에 두고 계신지 잘 상상이 되지 않습니다.

두 개의 쿼리로 처리할 수 있을 것 같습니다. 하나는 카테고리를 위해, 다른 하나는 태그를 위해 각각 특정 기간 내의 신규 주제와 신규 게시글 수를 집계하는 방식입니다. 게시를 한 사용자 수도 포함할 수 있을 것입니다.

그러면 결과 테이블은 다음과 같은 형태가 될 것입니다:

카테고리 신규 주제 신규 게시글 사용자
카테고리 a 9 15 4
카테고리 b 56 167 32
6

좋아요, 제가 말씀하신 것이 Categories - Discourse Meta라고 가정하고 해당 주소를 입력했습니다.

결국 다음 이유들로 인해 카테고리/태그/토픽을 하나의 출력으로 확인하고 싶습니다:

  • 중복 항목 식별 - 제가 이해한 바로는 토픽은 대화/스레드의 시작을 가리키는 용어입니다. 사용자가 유사한 토픽을 서로 다른 두 개의 카테고리에 추가했을 수 있으므로, 이를 파악하면 사용자 행동 패턴을 이해하거나 카테고리를 더 명확하게 조정하는 데 도움이 될 것입니다.
  • 카테고리별 토픽 유형 파악 - 카테고리가 "자동차"와 같은 것일 수 있지만, 당연히 그 안에는 다양한 유형의 토픽이 포함될 수 있습니다. 사용자가 실제로 어떤 주제에 대해 이야기하고 있는지 보고 싶습니다.
  • 태그 - 현재 인스턴스에서 태그가 여러 다른 카테고리를 가로지르는 어떤 형태의 교차점으로 사용되고 있음을 확인할 수 있습니다. 따라서 이 관점에서도 게시물을 이해하는 것이 좋겠습니다.

제가 파악한 바로는, SQL을 사용하여 카테고리, 태그, 토픽을 결합하고 경우에 따라 게시물까지 연결해야 할 것 같습니다. 이미 이러한 작업이 완료된 적이 있는지, 또는 관련 코드/쿼리 라이브러리가 있는지 궁금합니다.

7
category topic tag posts users
cars love cars wheels 44 1
cars hate cars wheels 32 3
cars hate cars doors 39 4
cars how do cars work? doors 32 1
how do things work how do cars work? how tos 32 3

이것은 비전(구상)의 예시입니다. 이를 통해 "cars 카테고리에 게시글을 올리는 사용자 중 wheels에 대해 이야기하는 비율이 몇 %인가"와 같은 사항을 파악하기 위한 추가 분석이 가능해집니다.

데이터 탐색기에서 게시글, 주제, 태그, 카테고리, 그리고 아마도 다른 테이블을 결합하여 구현할 수 있을 것 같습니다. 여기에서 질문을 올린 이유는, 이것이 이미 수행되었는지, 혹은 이전에 작성된 SQL 쿼리가 어디에 있는지(우리 계정이 아닌 일반적인 의미에서) 이해하기 위해서입니다.

이해가 되길 바랍니다. 어제야 막 조사를 시작했거든요.

8

아, 이제 이해했습니다. 요약 개요가 아니라 추가 세부 정보가 포함된 주제 목록을 원하시는 거죠. 가능할 것 같습니다. :+1:

유일한 특이한 점은 한 주제가 여러 태그를 가질 수 있다는 것뿐일 텐데, 일단 무언가를 만들어서 결과를 한번 확인해 보겠습니다. :slight_smile:

다만 다른 기존 쿼리를 탐색하려면, 대시보드에서 표준으로 제공되는 쿼리(여기에서 dashboard-sql 아래에 그룹화되어 있는 SQL 버전을 볼 수 있습니다)와 데이터 탐색기에 번들로 포함된 표준 보고서, 그리고 메타에서 sql-query 태그 아래에 그룹화되어 있는 수많은 사용자 정의 쿼리가 있습니다.

@SStrong - 이런 방향으로 어떨까요:

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-12-31


WITH tag_names AS (
   
    SELECT 
        t.id AS topic_id,
        string_agg(tags.name, ', ' ORDER BY tags.name) AS "tags"
    FROM topics t
      JOIN topic_tags tt ON tt.topic_id = t.id
      JOIN tags ON tags.id = tt.tag_id
    WHERE t.created_at BETWEEN :start_date AND :end_date
    GROUP BY t.id
    ),
    
user_count AS (

    SELECT
        p.topic_id,
        COUNT(DISTINCT p.user_id) AS users
    FROM posts p
      JOIN topics t ON t.id = p.topic_id
    WHERE t.created_at BETWEEN :start_date AND :end_date
      AND t.deleted_at IS NULL
      AND p.deleted_at IS NULL
      AND t.archetype = 'regular'
      AND p.post_type = 1
      AND p.user_id > 0
    GROUP BY p.topic_id
        
)

SELECT 
    t.category_id,
    t.id AS topic_id,
    tn.tags,
    t.posts_count,
    uc.users
FROM topics t
  JOIN tag_names tn ON tn.topic_id = t.id
  JOIN user_count uc ON uc.topic_id = t.id
WHERE t.created_at BETWEEN :start_date AND :end_date
  AND t.archetype = 'regular'
  AND t.deleted_at IS NULL
ORDER BY t.category_id, t.title
9

죄송합니다. 이미 답장을 보낸 줄 알았네요.

코드를 수정해서 카테고리 이름과 토픽 이름을 포함하도록 할 수 있을까요? "게시물 이름"이라는 것이 실제로 존재하는지, 아니면 그것이 사실은 토픽 이름을 가리키는 것인지 잘 모르겠습니다.

직접 코드를 수정해 보았지만, 현재 테이블들이 어떻게 함께 동작하는지 충분히 이해하지 못해서, 결과적으로 레코드 수가 그대로 유지되되 ID 대신 텍스트가 표시되기를 원했는데 0건만 반환되었습니다.

10

문제가 아닙니다. :slight_smile: 데이터 탐색기에서 열람할 경우 category_id와 topic_id(그리고 기타 여러 항목)가 자동으로 사이트 내 링크로 변환되지만, 다른 곳에서 분석을 위해 내보내는 경우에는 대신 카테고리 이름과 주제 제목을 사용할 수 있습니다:

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-12-31


WITH tag_names AS (
   
    SELECT 
        t.id AS topic_id,
        string_agg(tags.name, ', ' ORDER BY tags.name) AS "tags"
    FROM topics t
      JOIN topic_tags tt ON tt.topic_id = t.id
      JOIN tags ON tags.id = tt.tag_id
    WHERE t.created_at BETWEEN :start_date AND :end_date
    GROUP BY t.id
    ),
    
user_count AS (

    SELECT
        p.topic_id,
        COUNT(DISTINCT p.user_id) AS users
    FROM posts p
      JOIN topics t ON t.id = p.topic_id
    WHERE t.created_at BETWEEN :start_date AND :end_date
      AND t.deleted_at IS NULL
      AND p.deleted_at IS NULL
      AND t.archetype = 'regular'
      AND p.post_type = 1
      AND p.user_id > 0
    GROUP BY p.topic_id
        
)

SELECT 
    c.name AS category_name,
    t.title,
    tn.tags,
    t.posts_count,
    uc.users
FROM topics t
  JOIN tag_names tn ON tn.topic_id = t.id
  JOIN user_count uc ON uc.topic_id = t.id
  JOIN categories c ON c.id = t.category_id
WHERE t.created_at BETWEEN :start_date AND :end_date
  AND t.archetype = 'regular'
  AND t.deleted_at IS NULL
ORDER BY t.category_id, t.title