관리자 대시보드 보고서 참고 가이드

[Discourse]

이 가이드는 Admin Dashboard Reports의 작동 방식, 표시되는 데이터, 각 보고서에 해당하는 Data Explorer SQL 쿼리, 그리고 각 보고서의 Ruby 코드를 어디서 찾을 수 있는지를 설명하는 참고 자료입니다.

필요한 사용자 권한: Staff

Discourse에는 커뮤니티에 대한 통계를 탐색하는 데 유용한 여러 내장 Admin Dashboard Reports가 포함되어 있습니다. 이 보고서에 액세스하려면 사이트에서 discourse.example.com/admin/dashboard/reports를 방문하거나(또는 사이드바의 Reports 링크를 클릭)하십시오. 이 보고서에 대한 액세스 권한은 Staff 사용자만 가집니다.

사이트의 모든 사용자 데이터(관리 페이지 방문과 같은 Staff 활동 포함)가 이 보고서에 포함됩니다. 보고서에서 사용자에게 적용되는 유일한 조건은 ‘실제’ 사용자여야 한다는 것이며, 이는 system 사용자를 보고서에서 제외하는 데 사용됩니다.

플러그인은 add_report(name, &block)을 사용하여 대시보드에 보고서를 추가할 수도 있습니다.

대부분의 보고서에 대한 Ruby 모델은 discourse/app/models/concerns/reports/에 위치해 있습니다. 일부 보고서는 discourse/app/models/report.rb도 참조합니다.

dashboard-sql 토픽에는 Admin Dashboard Reports와 동일한 보고서를 생성할 수 있는 모든 해당 SQL 쿼리가 포함되어 있습니다. 이러한 쿼리는 Data Explorer 플러그인 내에서 및 Discourse API를 사용한 Data Explorer 쿼리 실행에 사용할 수 있습니다.

대시보드에서 특정 보고서를 숨기려면 dashboard_hidden_reports 사이트 설정을 사용하십시오.

Accepted solutions

해결책으로 표시된 게시물의 일일 집계.

Ruby 코드: discourse-solved/plugin.rb at main · discourse/discourse-solved · GitHub

SQL 쿼리: Dashboard Report - Accepted Solutions

Admin Logins

위치와 함께된 관리자 로그인 시간 목록.

Ruby 코드: discourse/app/models/concerns/reports/staff_logins.rb

SQL 쿼리: Dashboard Report - Admin Logins

Anonymous

계정에 로그인하지 않은 방문자의 새로운 페이지뷰 수.

Ruby 코드: discourse/app/models/concerns/reports/consolidated_page_views.rb

SQL 쿼리: Dashboard Report - Anonymous

Bookmarks

북마크된 새로운 토픽 및 게시물 수.

Ruby 코드: discourse/app/models/concerns/reports/bookmarks.rb

SQL 쿼리: Dashboard Report - Bookmarks

Consolidated API Requests

날짜별 API 사용 통계로, 일반 API 요청과 사용자 API 요청을 모두 추적합니다.

Ruby 코드: discourse/app/models/concerns/reports/consolidated_api_requests.rb at main · discourse/discourse · GitHub

SQL 쿼리: Dashboard Report - Consolidated API Requests

Consolidated Pageviews

로그인된 사용자,匿名用户 및 크롤러의 페이지뷰. 이 보고서는 Site Traffic 보고서에 의해 대체된 레거시 보고서입니다.

Ruby 코드: discourse/app/models/concerns/reports/consolidated_page_views.rb

SQL 쿼리: Dashboard Report - Consolidated Pageviews

Consolidated Pageviews with Browser Detection (Deprecated)

로그인된 사용자,匿名用户, 알려진 크롤러 및 기타의 페이지뷰. 이 보고서는 비활성화되었으며 이제 Site Traffic 보고서에 위임됩니다.

Ruby 코드: discourse/app/models/concerns/reports/consolidated_page_views_browser_detection.rb

SQL 쿼리: Dashboard Report - Consolidated Pageviews with Browser Detection

DAU/MAU

지난 1일 동안 로그인한 멤버 수를 지난 1개월 동안 로그인한 멤버 수로 나눈 값 – 커뮤니티의 '중속성(stickiness)'을 나타내는 %를 반환합니다. 20% 이상을 목표로 하십시오.

Ruby 코드: discourse/app/models/concerns/reports/dau_by_mau.rb

SQL 쿼리: Dashboard Report - DAU/MAU

Daily Engaged Users

지난 1일 동안 '좋아요’를 누르거나 게시물을 작성한 사용자 수.

Ruby 코드: discourse/app/models/concerns/reports/daily_engaged_users.rb

SQL 쿼리: Dashboard Report - Daily Engaged Users

Emails Sent

보낸 새로운 이메일 수.

Ruby 코드: discourse/app/models/concerns/reports/emails.rb

SQL 쿼리: Dashboard Report - Emails Sent

Flags

새로운 플래그 수.

Ruby 코드: discourse/app/models/concerns/reports/flags.rb

SQL 쿼리: Dashboard Report - Flags

Flags Status

플래그 유형, 게시자, 플래그를 등록한 사람, 해결까지 걸린 시간을 포함한 플래그 상태 목록.

Ruby 코드: discourse/app/models/concerns/reports/flags_status.rb

SQL 쿼리: Dashboard Report - Flags Status

Likes

새로운 ‘좋아요’ 수.

Ruby 코드: discourse/app/models/concerns/reports/likes.rb

SQL 쿼리: Dashboard Report - Likes

Logged In

로그인된 사용자의 새로운 페이지뷰 수.

Ruby 코드: discourse/app/controllers/admin/reports_controller.rb#L5

SQL 쿼리: Dashboard Report - Logged In

Moderator Activity

검토된 플래그, 읽기 시간, 생성된 토픽, 생성된 게시물, 생성된 개인 메시지, 수정 사항을 포함한 모더레이터 활동 목록.

SQL 쿼리: Dashboard Report - Moderator Activity

Moderator Warning

모더레이터가 개인 메시지로 보낸 경고 수.

Ruby 코드: discourse/app/models/concerns/reports/moderator_warning_private_messages.rb

SQL 쿼리: Dashboard Report - Moderator Warnings

New Contributors

이 기간 동안 첫 번째 게시물을 작성한 사용자 수.

Ruby 코드: discourse/app/models/concerns/reports/new_contributors.rb

SQL 쿼리: Dashboard Report - New Contributors

Notify Moderators

플래그로 인해 모더레이터가 비공개로 통지를 받은 횟수.

Ruby 코드: discourse/app/models/concerns/reports/notify_moderators_private_messages.rb

SQL 쿼리: Dashboard Report - Notify Moderators

Notify User

플래그로 인해 사용자가 비공개로 통지를 받은 횟수.

Ruby 코드: discourse/app/models/concerns/reports/notify_user_private_messages.rb

SQL 쿼리: Dashboard Report - Notify User

Overall Sentiment

지정된 기간 동안 “Sentiment” AI로 긍정적 또는 부정적으로 분류된 게시물 수.

Ruby 코드: https://github.com/discourse/discourse-ai/blob/main/lib/sentiment/entry_point.rb

SQL 쿼리: Dashboard Report - Overall Sentiment

Pageviews

모든 방문자의 새로운 페이지뷰 수. Consolidated Pageviews의 합계와 동일합니다.

Discourse는 다음 쿼리를 사용하여 총 페이지뷰를 결정합니다:

SQL 쿼리: Dashboard Report - Consolidated Pageviews

Post Edits

새로운 게시물 편집 수.

Ruby 코드: discourse/app/models/concerns/reports/post_edits.rb

SQL 쿼리: Dashboard Report - Post Edits

Posts

선택된 시간 기간 동안 생성된 새로운 게시물

Ruby 코드: discourse/app/models/concerns/reports/posts.rb

SQL 쿼리: Dashboard Report - Posts

Post Emotion

다음 중 하나의 감정으로 AI에 의해 분류된 게시물 수: 슬픔, 놀람, 두려움, 분노, 기쁨, 혐오 - 지정된 기간 동안 게시자의 신뢰 수준별로 그룹화.

Ruby 코드: https://github.com/discourse/discourse-ai/blob/main/lib/sentiment/entry_point.rb

SQL 쿼리: Dashboard Report - Post Emotion

Reactions

최근 반응 목록.

Ruby 코드: discourse-reactions/plugin.rb at main · discourse/discourse-reactions · GitHub

SQL 쿼리: Dashboard Report - Reactions

Signups

이 기간의 새로운 계정 등록.

Ruby 코드: discourse/app/models/concerns/reports/signups.rb

SQL 쿼리: Dashboard Report - Signups

Site Traffic

로그인된 브라우저,匿名用户 브라우저, 크롤러 및 기타 트래픽의 페이지뷰. 이것은 레거시 Consolidated Pageviews 보고서를 대체하는 주요 트래픽 보고서입니다.

Ruby 코드: discourse/app/models/concerns/reports/site_traffic.rb

SQL 쿼리: Dashboard Report - Site Traffic

Suspicious Logins

이전 로그인과 의심스럽게 다른 새로운 로그인에 대한 세부 정보.

Ruby 코드: discourse/app/models/concerns/reports/suspicious_logins.rb

SQL 쿼리: Dashboard Report - Suspicious Logins

System

System이 자동으로 보낸 개인 메시지 수.

Ruby 코드: discourse/app/models/concerns/reports/system_private_messages.rb

SQL 쿼리: Dashboard Report - System

Time to first response

새로운 토픽에 대한 첫 번째 응답의 평균 시간(시간 단위).

Ruby 코드: discourse/app/models/concerns/reports/time_to_first_response.rb + discourse/discourse/blob/main/app/models/topic.rb#L1799-L1844

SQL 쿼리: Dashboard Report - Time to First Response

Top Ignored / Muted Users

많은 다른 사용자들에게 음소거되고/또는 무시된 사용자.

Ruby 코드: discourse/app/models/concerns/reports/top_ignored_users.rb

SQL 쿼리: Dashboard Report - Top Ignored / Muted Users

Top Referred Topics

외부 출처에서 가장 많은 클릭을 받은 토픽.

Ruby 코드: discourse/app/models/concerns/reports/top_referred_topics.rb

SQL 쿼리: Dashboard Report - Top Referred Topics

Top Referrers

공유한 링크에 대한 클릭 수로 나열된 사용자.

Ruby 코드: discourse/app/models/concerns/reports/top_referrers.rb

SQL 쿼리: Dashboard Report - Top Referrers

Top Traffic Sources

이 사이트에 가장 많이 링크한 외부 출처.

Ruby 코드: discourse/app/models/concerns/reports/top_traffic_sources.rb

SQL 쿼리: Dashboard Report - Top Traffic Sources

Top Uploads

확장자, 파일 크기 및 작성자로 모든 업로드를 나열.

Ruby 코드: discourse/app/models/concerns/reports/top_uploads.rb

SQL 쿼리: Dashboard Report - Top Uploads

Top Users by likes received

'좋아요’를 가장 많이 받은 상위 10명의 사용자.

Ruby 코드: discourse/app/models/concerns/reports/top_users_by_likes_received.rb

SQL 쿼리: Dashboard Report - Top Users by Likes Received

Top Users by likes received from a user with a lower trust level

더 높은 신뢰 수준의 상위 10명의 사용자에게 더 낮은 신뢰 수준의 사람들이 '좋아요’를 누른 경우.

Ruby 코드: discourse/app/models/concerns/reports/top_users_by_likes_received_from_inferior_trust_level.rb

SQL 쿼리: Dashboard Report - Top Users by Likes Received from a User with a Lower Trust Level

Top Users by likes received from a variety of people

다양한 사람들로부터 '좋아요’를 받은 상위 10명의 사용자.

Ruby 코드: discourse/app/models/concerns/reports/top_users_by_likes_received_from_a_variety_of_people.rb

SQL 쿼리: Dashboard Report - Top Users by Likes Received From a Variety of People

Topics

이 기간 동안 생성된 새로운 토픽.

Ruby 코드: discourse/app/models/concerns/reports/topics.rb

SQL 쿼리: Dashboard Report - Topics

Topics with no response

응답을 받지 못한 새로운 토픽 생성 수.

Ruby 코드: discourse/app/models/concerns/reports/topics_with_no_response.rb

SQL 쿼리: Dashboard Report - Topics with No Response

Topic View Stats

匿名用户 및 로그인된 사용자 분해와 함께 뷰 수 기준 상위 100개 토픽, 카테고리별로 필터링 가능.

Ruby 코드: discourse/app/models/concerns/reports/topic_view_stats.rb

SQL 쿼리: Dashboard Report - Topic View Stats

Trending Search Terms

클릭률과 함께 가장 인기 있는 검색어.

Ruby 코드: discourse/app/models/concerns/reports/trending_search.rb

SQL 쿼리: Dashboard Report - Trending Search Terms

Trust Level growth

이 기간 동안 신뢰 수준을 높인 사용자 수.

Trust Level Growth 보고서는 Discourse 데이터베이스의 user_histories 테이블에서 데이터를 가져옵니다. 구체적으로, 이 보고서는 사용자 신뢰 수준 증가에 대해 user_histories.action이 기록된 횟수를 계산합니다.

Ruby 코드: discourse/app/models/concerns/reports/trust_level_growth.rb

SQL 쿼리: Dashboard Report - Trust Level Growth

Unaccepted policies

이 대시보드 보고서는 특정 사용자에 의해 승인되지 않은 정책을 가진 토픽을 식별합니다.

Ruby 코드: discourse-policy/plugin.rb at main · discourse/discourse-policy · GitHub

SQL 쿼리: Dashboard Report - Unaccepted Policies

User Flagging Ratio

플래그에 대한 Staff 응답 비율(불일치에서 일치로)로 정렬된 사용자 목록.

Ruby 코드: discourse/app/models/concerns/reports/user_flagging_ratio.rb

SQL 쿼리: Dashboard Report - User Flagging Ratio

User notes

최근 사용자 메모 목록.

Ruby 코드: discourse-user-notes/plugin.rb at main · discourse/discourse-user-notes · GitHub

SQL 쿼리: Dashboard Report - User Notes

User Profile Views

사용자 프로필의 총 새로운 뷰.

Ruby 코드: discourse/app/models/concerns/reports/profile_views.rb

SQL 쿼리: Dashboard Report - User Profile Views

User Visits

선택된 시간 기간(오늘, 어제, 지난 7일 등) 동안 포럼의 로그인된 사용자 방문 총 수.

User Visit은 고유한 로그인된 사용자가 사이트를 방문할 때마다 최대 하루 1회까지 계산됩니다. 예를 들어, 사용자가 한 주 동안 매일 사이트를 방문했다면 Discourse는 이를 7개의 사용자 방문으로 계산합니다.

Ruby 코드: discourse/app/models/concerns/reports/visits.rb

SQL 쿼리: Dashboard Report - User Visits

User Visits (mobile)

모바일 장치를 사용하여 방문한 고유한 로그인된 사용자 수.

Ruby 코드: discourse/app/models/concerns/reports/mobile_visits.rb

SQL 쿼리: Dashboard Report - User Visits

User-to-User (excluding replies)

새로 시작된 개인 메시지 수.

Ruby 코드: discourse/app/models/concerns/reports/user_to_user_private_messages.rb

SQL 쿼리: Dashboard Report - User-to-User

User-to-User (with replies)

모든 새로운 개인 메시지와 응답의 수.

Ruby 코드: discourse/app/models/concerns/reports/user_to_user_private_messages_with_replies.rb

SQL 쿼리: Dashboard Report - User-to-User

Users per Trust Level

신뢰 수준별로 그룹화된 사용자 수.

Ruby 코드: discourse/app/models/concerns/reports/users_by_trust_level.rb

SQL 쿼리: Dashboard Report - Users Per Trust Level

Users per Type

관리자, 모더레이터, 정지, 음소거별로 그룹화된 사용자 수.

Ruby 코드: discourse/app/models/concerns/reports/users_by_type.rb

SQL 쿼리: Dashboard Report - Users Per Type

Web Crawler Pageviews

시간 경과에 따른 웹 크롤러의 총 페이지뷰.

Ruby 코드: discourse/app/models/report.rb

SQL 쿼리: Dashboard Report - Web Crawler Pageviews

Web Crawler User Agents

페이지뷰로 정렬된 웹 크롤러 사용자 에이전트 목록.

Ruby 코드: discourse/app/models/concerns/reports/web_crawlers.rb

SQL 쿼리: Dashboard Report - Web Crawler User Agents

[pfaffman (Jay Pfaffman)]

@SaraDev

Discourse에는 커뮤니티에 대한 통계를 탐색하는 데 유용한 여러 내장 관리자 대시보드 보고서가 포함되어 있습니다. 이러한 보고서에 액세스하려면 사이트에서 /admin/dashboard/reports 경로를 방문할 수 있습니다.

/admin에서 이 링크를 찾을 수 없습니다. 제가 읽기를 잘못하고 있는 건가요? 이 링크가 더 쉽게 발견될 수 있도록 해야 할 것 같습니다. 이 보고서가 여기 있다는 것은 알고 있었지만, 찾아보았음에도 불구하고 찾지 못했습니다.

찾는 데 몇 분밖에 걸리지 않았지만, 다음과 같은 내용을 추가하면 좋겠습니다.

플러그인은 add_report(name, &block)를 사용하여 대시보드에 보고서를 추가할 수 있습니다.

[maiki]

@pfaffman

/admin에서 이 페이지로 가는 링크를 찾을 수 없습니다.

@pfaffman

이 기능은 더 쉽게 발견할 수 있어야 할 것 같습니다. 이 보고서들이 여기에 있다는 것은 알고 있었지만, 찾아봐도 찾을 수 없었습니다.

네, 사이트가 처음 생성될 때 스태프에게 개인 메시지를 보내서 이 기능에 대해 언급해 주면 좋을 것 같습니다.

[pfaffman (Jay Pfaffman)]

미안해요. 분명 어딘가에서 본 적이 있었던 것 같은데.

사람들이 글을 제대로 읽지 않는다는 말이지요… 그런데 플러그인에서 어떻게 하는지 알아보기 위해 소스 코드를 읽을 수는 있었어요?

하지만 위의 내용을 이렇게 업데이트하는 건 어떨까요:

@SaraDev

. 이 보고서를 보려면 사이트에서 /admin/dashboard/reports로 이동하거나 대시보드에서 reports 링크를 클릭하세요.

이 부분이 진짜로 나를 혼란스럽게 만든 것 같아요. (하지만 아니요, 변명은 없어요.)

[maiki]

@pfaffman

하지만 위 내용을 다음과 같이 업데이트하는 것이 좋겠습니다.

주제를 위키로 만들었습니다, 마음껏 수정하세요!

[hello-smile6 (9pfs)]

@SaraDev

30% 이상을 목표로 하세요.

이것은 UI(20% 사용)와 일치하지 않습니다. 어느 쪽이 맞아야 하나요?

[JammyDodger]

좋은 지적입니다. 최근 20%로 업데이트되었습니다. 원문(OP)에 수정하겠습니다.

지원 우선 포럼과 토론 우선 포럼의 목표 체류 시간 사이에 차이가 있나요?

Here’s a PR with that change: https://github.com/discourse/discourse/pull/20908 Longer term, I think a more nuanced approach could be powerful. I’d love for communities to be able to set their own goals (with some guidance like what you shared above about why they might choose one vs. the other) and use the dashboard or other related features to help them track their results vs. what they are aiming for.

[Yotam_Hagay]

@SaraDev

주요 트래픽 출처

이 사이트로 가장 많이 링크된 외부 출처입니다.

Ruby 코드: discourse/app/models/concerns/reports/top_traffic_sources.rb

@SaraDev 이 보고서의 출력을 SQL 쿼리로 얻을 수 있을까요? 공유해 주실 수 있을까요?
감사합니다

[SaraDev (Sara Devlaeminck)]

@Yotam_Hagay

안녕하세요 @SaraDev 이 리포트 출력을 SQL 쿼리로 얻을 수 있나요? 공유해 주실 수 있을까요?
감사합니다

네, 상위 트래픽 소스(Top Traffic Sources)에 대해 다음 SQL 리포트를 사용할 수 있습니다:

-- [params]
-- date :start_date = 01/05/2023
-- date :end_date = 03/06/2023

WITH count_links AS (
  
SELECT COUNT(*) AS clicks,
       ind.name AS domain
FROM incoming_links il
  INNER JOIN posts p ON p.deleted_at ISNULL AND p.id = il.post_id
  INNER JOIN topics t ON t.deleted_at ISNULL AND t.id = p.topic_id
  INNER JOIN incoming_referers ir ON ir.id = il.incoming_referer_id
  INNER JOIN incoming_domains ind ON ind.id = ir.incoming_domain_id
WHERE t.archetype = 'regular'
  AND il.created_at::date BETWEEN :start_date AND :end_date 
GROUP BY ind.name
ORDER BY clicks DESC
), 

count_topics AS (
  
SELECT COUNT(DISTINCT p.topic_id) AS topics,
       ind.name AS domain
FROM incoming_links il
INNER JOIN posts p ON p.deleted_at ISNULL AND p.id = il.post_id
INNER JOIN topics t ON t.deleted_at ISNULL AND t.id = p.topic_id
INNER JOIN incoming_referers ir ON ir.id = il.incoming_referer_id
INNER JOIN incoming_domains ind ON ind.id = ir.incoming_domain_id
WHERE t.archetype = 'regular'
  AND il.created_at > (CURRENT_TIMESTAMP - INTERVAL '30 DAYS')
GROUP BY ind.name
) 

SELECT cl.domain, 
       cl.clicks AS "Clicks", 
       ct.topics AS "Topics"
FROM count_links cl
JOIN count_topics ct ON cl.domain = ct.domain
LIMIT 10

이 쿼리를 사용할 때 날짜 파라미터는 일/월/년 형식의 날짜를 받아들이는 것에 유의하세요.

[Yotam_Hagay]

@SaraDev

네, 상위 트래픽 소스에 대해 다음 SQL 보고서를 사용할 수 있습니다:

@SaraDev 님, 쿼리를 공유해 주셔서 감사합니다.
이 보고서, 그리고 실제로 incoming_links 테이블에 대해 더 일반적인 질문을 하나 드리고 싶습니다. 이 테이블은 포스트 페이지의 트래픽만 나타내고, 포럼 전체 페이지의 트래픽은 포함하지 않는 것이 맞나요?

배경 설명: 저는 포럼 전체 트래픽의 추세를 분석하려 하고 있으며, 상위 트래픽 소스 보고서를 통해 소스별 전체 트래픽을 확인하기를 바랐습니다.
그러나 지난 달 전체 트래픽(유저 및 비로그인 사용자)이 약 272K인 반면, 같은 기간 트래픽 소스 보고서의 총 클릭 수는 단 59K에 불과합니다.
또한, topics 테이블과 posts 테이블을 inner join으로 사용하셨는데, 이는 클릭에 post_id가 연결되어 있지 않으면 해당 클릭을 카운트하지 않는다는 의미인 것 같습니다.

제 결론을 확인해 주실 수 있을까요? 또한 incoming_links 테이블의 로직에 대해 조금 설명해 주시면 감사하겠습니다.

[Yotam_Hagay]

@SaraDev

게시물

해당 기간 동안 생성된 새로운 게시물

@SaraDev 안녕하세요. 이 쿼리를 실행해 보았는데, 일반 탭의 게시물 보고서와 결과가 정확히 일치하지 않습니다.
예를 들어 11월 30일 기준:
쿼리 = 112개
보고서 = 120개
차이가 나는 부분을 확인해 주실 수 있을까요?
감사합니다.

[JammyDodger]

참고로 @Yotam_Hagay - 사라가 원포스터(OP)이지만, 가이드는 모두의 책임입니다 각 게시물마다 @멘션을 달 필요는 없어요.

[Yotam_Hagay]

@JammyDodger 님, 설명해 주셔서 감사합니다.
답변을 받을 수 있도록 태그할 수 있는 다른 사람이 있거나, 도움을 요청할 수 있는 사람이 있을까요?

[Yotam_Hagay]

@SaraDev

SQL 최초 응답까지 소요 시간

--[params]
-- date :date_start
-- date :date_end

WITH first_reply AS (
    SELECT 
        p.topic_id, 
        MIN(post_number) post_number, 
        t.created_at
    FROM posts p
    INNER JOIN topics t ON (p.topic_id = t.id)
    WHERE p.deleted_at IS NULL
        AND p.post_number != 1
        AND p.post_type = 1
        AND p.user_id > 0
        AND t.user_id > 0
        AND t.deleted_at IS NULL
        AND t.archetype = 'regular'
        AND t.created_at::date BETWEEN :date_start AND :date_end
    GROUP BY p.topic_id, t.created_at
    ORDER BY 2 DESC
)

SELECT 
    p.topic_id, 
    fr.created_at::date dt_topic_created,
    (p.created_at - fr.created_at) response_time
FROM posts p
INNER JOIN first_reply fr 
    ON fr.topic_id = p.topic_id 
    AND fr.post_number = p.post_number
    AND p.created_at > fr.created_at
ORDER BY response_time

이 쿼리의 결과는 최초 응답까지 소요 시간(Time to first response) 보고서와 약간 다릅니다.
예를 들어 11월 8일 기준:
쿼리: 93시간
보고서: 116시간
도움을 주실 수 있는 분 있으신가요?

[JammyDodger]

이 중 일부는 조사하는 데 시간이 좀 걸릴 것 같습니다. 저도 직접 살펴보고 어떤 일이 일어나고 있는지 확인해 보려 합니다(다만, 제 SQL 실력과 Ruby 실력 사이의 격차가 꽤 큽니다 ).

하지만 이 모든 정보를 더 명확하게 정리할 수 있다면 좋으니, 조사 결과가 나오면 계속 공유해 주세요.

[JammyDodger]

@Yotam_Hagay

안녕하세요 @SaraDev 이 쿼리를 실행해 보았는데, 일반 탭의 게시물 보고서와 결과가 정확히 일치하지 않습니다.
예를 들어 11월 30일의 경우:
쿼리 = 112개
보고서 = 120개
차이를 확인해 주실 수 있을까요?
감사합니다

게시물 보고서는 기존 보고서가 주제 게시물과 시스템 사용자의 게시물도 함께 집계하지만, post_type이 1인 게시물(즉, 속삭임, 작은 행동 게시물, 또는 관리자의 행동이 아닌 게시물)만 대상으로 한다는 점을 고려해야 합니다.

SQL은 다음과 같이 작성하는 것이 더 적절할 것 같습니다:

--[params]
-- date :start_date
-- date :end_date

SELECT 
    p.created_at::date AS "Day",
    COUNT(p.id) AS "Count"
FROM posts p
INNER JOIN topics t ON t.id = p.topic_id AND t.deleted_at ISNULL
WHERE p.created_at::date BETWEEN :start_date AND :end_date
    AND p.deleted_at ISNULL
    AND t.archetype = 'regular'
    AND p.post_type = 1
GROUP BY p.created_at::date
ORDER BY 1

사이트에서 이 쿼리를 실행해 보시고 결과가 일치하는지 확인해 주시겠어요?

[Yotam_Hagay]

Jammy, 감사합니다! 확인해 보겠습니다.
현재 첫 응답 시간(Time to first response) 분석을 진행 중인데, 이 부분도 한번 봐 주시면 감사하겠습니다.

[JammyDodger]

SQL 버전을 살펴보니, OP(첫 번째 게시자)의 답변을 제외하기 위해 AND p.user_id <> t.user_id 조건이 빠져 있는 것 같습니다. 이 조건을 추가하면 OP와 다른 사람으로부터의 첫 번째 응답 사이의 정확한 시간 간격을 계산할 수 있습니다:

--[params]
-- date :date_start
-- date :date_end

WITH first_reply AS (
    SELECT 
        p.topic_id, 
        MIN(post_number) post_number, 
        t.created_at
    FROM posts p
    INNER JOIN topics t ON (p.topic_id = t.id)
    WHERE p.deleted_at IS NULL
        AND p.user_id <> t.user_id
        AND p.post_number != 1
        AND p.post_type = 1
        AND p.user_id > 0
        AND t.user_id > 0
        AND t.deleted_at IS NULL
        AND t.archetype = 'regular'
        AND t.created_at::date BETWEEN :date_start AND :date_end
    GROUP BY p.topic_id, t.created_at
    ORDER BY 2 DESC
)

SELECT 
    p.topic_id, 
    fr.created_at::date dt_topic_created,
    (p.created_at - fr.created_at) response_time
FROM posts p
INNER JOIN first_reply fr 
    ON fr.topic_id = p.topic_id 
    AND fr.post_number = p.post_number
    AND p.created_at > fr.created_at
ORDER BY response_time

또한 내장된 리포트는 시간과 분(HH:MM) 형식이 아니라 소수점(십진수) 형식인 것 같습니다. 일치하도록 다시 시도해 보겠습니다.

---

내장된 리포트의 출력과 더 유사하도록 AVG를 포함하도록 작은 업데이트를 했습니다:

--[params]
-- date :date_start
-- date :date_end

WITH first_reply AS (
    SELECT 
        p.topic_id, 
        MIN(post_number) post_number, 
        t.created_at
    FROM posts p
    INNER JOIN topics t ON p.topic_id = t.id
    WHERE p.deleted_at IS NULL
        AND p.user_id <> t.user_id
        AND p.post_type = 1
        AND p.user_id > 0
        AND t.user_id > 0
        AND t.deleted_at IS NULL
        AND t.archetype = 'regular'
        AND t.created_at::date BETWEEN :date_start AND :date_end
    GROUP BY p.topic_id, t.created_at
)

SELECT 
    fr.created_at::date dt_topic_created,
    AVG(p.created_at - fr.created_at) response_time
FROM posts p
INNER JOIN first_reply fr 
    ON fr.topic_id = p.topic_id 
    AND fr.post_number = p.post_number
    AND p.created_at > fr.created_at
GROUP BY fr.created_at::date
ORDER BY response_time

하나는 십진수이고 다른 하나는 HH:MM 형식임을 고려하면, 이 쿼리는 내장된 리포트와 일치하는 것 같습니다. SQL의 response_time을 십진수로 변환하는 방법이 분명히 있겠지만, HH:MM 형식이 더 직관적인 방법이라고 생각합니다. (필요하지 않을 수 있는 몇 가지 추가 조건이 포함된 것 같지만, 이는 비정상적인 상황을 방지하는 안전장치일 수도 있으므로, 확신이 들 때까지는 해당 부분들을 그대로 두었습니다. )

이 쿼리를 실행해서 결과가 어떻게 일치하는지 확인해 주시겠습니까?

[Yotam_Hagay]

네, 이제 주식 보고서에 표시된 수치와 일치합니다. 감사합니다!
한 가지 의견만 드리자면,
아래의 Avg 함수가 시간이 24시간을 초과하는 경우(# of days 섹션이 누락된 것으로 보입니다) 결측치를 반환하는 것을 발견했습니다.

AVG(p.created_at - fr.created_at)::time response_time

[JammyDodger]

아 맞다, time으로 캐스팅하는 것은 좋은 선택이 아니었네요. ::time을 제거하면 더 정확하지만(눈에는 덜 편한) 원래 버전으로 돌아갑니다.

위쪽 내용도 수정하겠습니다.