최상위 품질 멤버 쿼리에서 그룹 제외

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

안녕하세요,

이 쿼리에서 특정 그룹을 제거하는 방법에 대해 조언을 구합니다.

SELECT 
    sum(p.score) / count(p) as "average score per post", 
    count(p.id) as post_count, 
    p.user_id
FROM posts p
JOIN users u ON u.id = p.user_id
WHERE p.created_at >= CURRENT_DATE - INTERVAL '3 month'
  AND u.active
GROUP by user_id, u.views
HAVING count(p.id) > 40
ORDER BY sum(p.score) / count(p) DESC
LIMIT 100

미리 감사드립니다!

2

이 부분은 실제로 테스트해 보지 않았습니다.

-- [params]
-- int_list :exclude_groups = 12, 2, 3

SELECT 
    sum(p.score) / count(p) as "average score per post", 
    count(p.id) as post_count, 
    p.user_id
FROM posts p
JOIN users u ON u.id = p.user_id
LEFT JOIN group_users gu on u.id = gu.user_id
WHERE p.created_at >= CURRENT_DATE - INTERVAL '3 month'
  AND u.active
  AND (gu.group_id not IN (:exclude_groups))
GROUP by p.user_id, u.views
HAVING count(p.id) > 40
ORDER BY sum(p.score) / count(p) DESC
LIMIT 100
3

@pfaffman 님, 감사합니다. 정말 친절하시네요.

스크립트를 실행해 보니 제외된 그룹의 멤버들이 결과에 포함되어 있었습니다. 물론 해당 멤버들은 여러 그룹에 속해 있죠. :exclude_groups 목록에 포함된 모든 그룹의 모든 멤버를 완전히 제외할 수 있는 방법이 있을까요?

고려해 주셔서 감사합니다.

4

아, 그렇군요. not in은 처음 생각했던 것과 다르게 작동하네요. 거의 아무 역할도 하지 않는 것 같습니다. 먼저 어떤 그룹에도 속하지 않은 사용자를 조회하는 쿼리를 실행한 다음, 그 결과를 쿼리의 나머지 부분과 조인(join)해야 할 것 같습니다.

죄송합니다.

5 

    -- [params]
    -- int_list :exclude_groups = 1,2

    SELECT 
        sum(p.score) / count(p) as "average score per post", 
        count(p.id) as post_count, 
        p.user_id
    FROM posts p
    JOIN users u ON u.id = p.user_id
    WHERE p.created_at >= CURRENT_DATE - INTERVAL '3 month'
      AND u.active
      AND u.id NOT IN(
        SELECT user_id FROM group_users WHERE group_id IN (:exclude_groups)
      )
    GROUP by user_id, u.views
    HAVING count(p.id) > 40
    ORDER BY sum(p.score) / count(p) DESC
    LIMIT 100
6

@RGJ,

Discourse 생태계에 기여해 주신 점과 고객으로서 저에게 보여주신 놀라운 지원에 진심으로 감사드립니다. 이렇게 훌륭한 호스팅 서비스를 제공해 주셔서 정말 기쁩니다!