최근 7일 동안 이메일 도메인별 게시글 수를 집계하는 쿼리입니다. 자동 그룹(봇, 시스템)은 제외합니다.
SELECT
split_part(ue.email, '@', 2) AS email_domain, -- 이메일의 도메인 부분을 추출합니다
COUNT(p.id) AS post_count
FROM
posts p
JOIN users u ON p.user_id = u.id
JOIN user_emails ue ON u.id = ue.user_id
AND ue.primary = TRUE -- 기본 이메일을 사용하도록 보장합니다
LEFT JOIN group_users gu ON gu.user_id = u.id
LEFT JOIN GROUPS g ON gu.group_id = g.id
AND g.automatic = TRUE
WHERE
p.created_at >= CURRENT_DATE - INTERVAL '7 days' -- 지난 7일간의 게시글을 필터링합니다
AND g.id IS NULL -- 사용자가 자동 그룹에 속하지 않도록 보장합니다
GROUP BY
email_domain
ORDER BY
post_count DESC -- 게시글 수 기준으로 내림차순 정렬합니다
이렇게 하는 것이 맞지 않는 것 같습니다. 의도가 무엇인가요?
지난 1주일 동안의 게시물을 이메일 도메인별로 그룹화하여 집계하되, 시스템 사용자는 제외하는 것인가요?
삭제된 게시물이나 삭제된 주제의 게시물을 집계해야 하는지, 작은 액션 게시물을 제외해야 하는지, 아니면 개인 메시지(PM)에 있는 게시물을 제외해야 하는지 확인이 필요합니다.
이것은 도메인별 게시글 수를 확인하기 위해 SQL Helper 봇을 간단히 테스트해 본 것입니다. 이 사용 사례는 포럼 내 하위 그룹 중 어떤 그룹이 가장 활발하게 활동하는지 추적하기를 원하는 제휴사나 멤버십 포럼에 유용할 수 있습니다.
삭제된 게시글이나 소규모 액션 게시글, 사설(PM) 등을 제외하는 등 개선의 여지가 분명히 있지만, 혹시라도 영감을 받을 수 있을지 몰라 공유해 보았습니다.
그룹 가입 때문에 숫자가 부풀려지는 것 같아 (또한 모든 사용자가 신뢰 수준에 따라 자동 그룹에 속해 있어서 약간 일관성이 떨어지기도 해). 이 부분을 제거하고, 주제, 게시물, 개인 메시지에 초점을 맞출 수 있도록 몇 가지 조정을 추가할 수 있을 것 같아.
한번 시도해 볼게.
이런 식으로 하면 어떨까:
-- [params]
-- string :interval = 7 days
SELECT
split_part(ue.email, '@', 2) AS email_domain, -- 이메일의 도메인 부분을 추출
COUNT(p.id) AS "Total Posts",
COUNT(p.id) FILTER (WHERE p.post_number = 1 AND t.archetype = 'regular') AS "Topics",
COUNT(p.id) FILTER (WHERE p.post_number > 1 AND t.archetype = 'regular') AS "Posts",
COUNT(p.id) FILTER (WHERE t.archetype = 'private_message') AS "Personal Message posts"
FROM posts p
JOIN user_emails ue ON p.user_id = ue.user_id AND ue.primary = TRUE -- 기본 이메일을 사용하도록 보장
JOIN topics t ON t.id = p.topic_id
WHERE p.created_at >= CURRENT_DATE - INTERVAL :interval -- 매개변수에 따라 지난 x 기간 동안의 게시물을 필터링
AND p.user_id > 0 -- 시스템 사용자(시스템 및 봇) 제외
AND p.deleted_at ISNULL
AND t.deleted_at ISNULL
AND p.post_type <> 3
GROUP BY email_domain
ORDER BY "Total Posts" DESC -- 게시물 수 기준 내림차순 정렬