[MySQL]공동 1위 찾기

현재 진행중인 Todo List 프로젝트의 조회 과정에서 어려움에 봉착했다.

시나리오

"n월의 리포트" (월간 리포트 : n월 동안의 Todo List 좋아요 현황, 달성 현황 등...) 페이지 에서 "n월 중 좋아요를 가장 많이 누른 사용자" 찾기

요구사항

n월 중 좋아요를 가장 많이 누른 사용자를 찾아라.
(좋아요를 가장 많이 누른 사용자가 중복되어 결과가 2개 이상 나올 가능성 있음)

문제점

처음에는 좋아요를 가장 많이 누른 사용자 한명만 찾아내면 될 것이라 생각하여 이런식으로 쿼리를 작성했다.

SELECT m.member_nickname, COUNT(*) AS likes_count 
FROM likes l
INNER JOIN todo_list tl
ON l.todo_list_id = tl.todo_list_id
INNER JOIN member m
ON l.member_id = m.member_id
WHERE tl.member_id = 3 AND tl.todo_list_due_date LIKE '2022-09%'
GROUP BY l.member_id
ORDER BY likes_count DESC
LIMIT 1;

likes 테이블과 todo_list, member 테이블을 join 하여 찾고자 하는 해당 member_id와 Todo List의 기간(월, todo_list_due_date)의 조건에 부합하는 데이터를 todo_list_id로 그룹화, 내림차순 정렬하였고

최종적으로 첫 데이터(좋아요를 가장 많이 누른 사용자)만 반환하도록 처리 하였다.

그러나 요구사항 조건 중 이러한 내용이 있었다.

좋아요를 가장 많이 누른 사용자가 중복되어 결과가 2개 이상 나올 가능성 있음

이러한 가정이 있다면 likes_count가 같은 데이터가 있을 때(2개 이상) 모두 반환 해주어야 하는데 이러한 접근의 쿼리로는 원하는 값을 얻어 낼 수가 없었다.

해결 과정

반나절을 구글링하고 시도를 해보았지만 진전이 보이질 않았다.
팀원들과 함께 머리를 싸매고 힘을 합쳐 해결하고자 노력했지만 마찬가지였다.

동점의 데이터가 있어도 대표로 하나만 보여줄까? 보여주되 조회 시 동점자를 랜덤으로 보여줄까?

# 대표 1명만 보여주기 (동점자를 조회 시 랜덤으로 보여주기)
SELECT m.member_nickname, COUNT(*) AS likes_count 
FROM likes l
INNER JOIN todo_list tl
ON l.todo_list_id = tl.todo_list_id
INNER JOIN member m
ON l.member_id = m.member_id
WHERE tl.member_id = 3 AND tl.todo_list_due_date LIKE '2022-09%'
GROUP BY l.member_id, m.member_nickname
ORDER BY likes_count DESC, RAND()
LIMIT 1;

타협의 고비를 마주하기도 했다.

또 HAVING 절에 sub query를 사용하여 하드코딩으로 구현을 해내긴 했다.

# 노가다ㅎ
SELECT m.member_nickname, COUNT(*) AS likes_count 
FROM likes l
INNER JOIN todo_list tl
ON l.todo_list_id = tl.todo_list_id
INNER JOIN member m
ON l.member_id = m.member_id
WHERE tl.member_id = 3 AND tl.todo_list_due_date LIKE '2022-09%'
GROUP BY l.member_id
HAVING likes_count = (
     SELECT COUNT(*) AS likes_count 
     FROM likes l
     INNER JOIN todo_list tl
     ON l.todo_list_id = tl.todo_list_id
     INNER JOIN member m
     ON l.member_id = m.member_id
     WHERE tl.member_id = 3 AND tl.todo_list_due_date LIKE '2022-09%'
     GROUP BY l.member_id
     ORDER BY likes_count DESC
     LIMIT 1
);

이건 좀 아니다.
HAVING 절에서 단순히 비교 값을 얻어내기 위해 중복 쿼리를 사용하고 있다.

분명 나같은 상황이 있을 법도 한데 내 눈에는 코빼기도 보이질 않았다.
한참을 방황하던 중, RANK()라는 키워드가 눈에 띄었다.

RANK 함수

동일한 값이면 중복 순위 부여, 이후 순위는 해당 개수만큼 건너뛰고 반환함

DENSE_RANK 함수

동일한 값이면 중복 순위 부여, 이후 순위는 중복 순위와 상관없이 순차적으로 반환

현재는 1위 다음 순위는 신경 쓸 필요가 없기에 RANK, DENSE_RANK 어느 것을 써도 상관 없지만 이번에는 DENSE_RANK를 사용하였다.

해결책

SELECT member_nickname, likes_count
FROM (
	SELECT m.member_nickname,
    	   count(*) as likes_count,
		   DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) AS max_likes_count
	FROM likes l
	INNER JOIN todo_list tl
	ON l.todo_list_id = tl.todo_list_id
	INNER JOIN member m
	ON l.member_id = m.member_id
	WHERE tl.member_id = 3 AND tl.todo_list_due_date like '2022-09%'
	GROUP BY l.member_id
) AS result
WHERE max_likes_count = 1;

COUNT() 값을 유연하게 활용하기 위해서는 FROM 절에 sub query 내부에서 처리해야 했다. sub query 내부에서는 이전에 쿼리 했던 방식 그대로 유지하며 SELECT 절에서 member_nickname과 RANK() 함수를 사용하여 좋아요를 가장 많이 누른 순으로 순위를 매겨 주었다.

RANK() OVER (ORDER BY COUNT(*) DESC) AS max_likes_count

(max_likes_count 필드에 순위가 잘 매겨지는 것을 확인할 수 있다.)

최종적으로 마지막 WHERE 절에서 sub query에서 얻어낸 max_likes_count의 값이 1인 데이터만 가져오도록 처리했다.

결과는 원하는 대로 공동 1등 데이터 2개가 잘 조회되었다.

이로써 나의 문제는 해결 되었다.