[MYSQL] 물고기 종류 별 대어 찾기

문제

물고기 종류 별로 가장 큰 물고기의 ID, 물고기 이름, 길이를 출력하는 SQL 문을 작성해주세요. 물고기의 ID 컬럼명은 ID, 이름 컬럼명은 FISH_NAME, 길이 컬럼명은 LENGTH로 해주세요. 결과는 물고기의 ID에 대해 오름차순 정렬해주세요. 단, 물고기 종류별 가장 큰 물고기는 1마리만 있으며 10cm 이하의 물고기가 가장 큰 경우는 없습니다.

 

이번 문제는 2가지 방법으로 문제를 풀었는데, 아래에 하나씩 리뷰 후 서로 비교해서 더 효율적인 방법을 제시했다.

 

내가 푼 쿼리

1. RANK() + IN 서브쿼리

SELECT
    F1.ID,
    F2.FISH_NAME,
    F1.LENGTH
FROM FISH_INFO F1
LEFT JOIN FISH_NAME_INFO F2
ON F1.FISH_TYPE = F2.FISH_TYPE
WHERE F1.ID IN (
    SELECT ID
    FROM (
        SELECT
            ID,
            FISH_TYPE,
            RANK() OVER(PARTITION BY FISH_TYPE ORDER BY LENGTH DESC) AS FISH_RANK,
            LENGTH
        FROM FISH_INFO
    ) SUB_RANK
    WHERE FISH_RANK = 1
)
ORDER BY F1.ID;

장점

• RANK() 로 FISH_TYPE 별 가장 큰 물고기를 정확하게 추출
• 만약 동점이 있을 경우에도 모두 가져올 수 있음 (단, 이번 문제는 물고기 종류별 가장 큰 물고기는 1마리만 있어서 해당 없음)

단점

• IN 서브쿼리를 다시 원본 테이블에 매칭하는 구조는 성능 저하 가능성이 있음
• 서브쿼리 이후 JOIN을 별도로 다시 수행하므로 쿼리 복잡도 상승

IN 서브쿼리는 내부적으로 결과 집합을 임시 테이블로 만들고, 바깥 쿼리의 조건과 매 건을 비교하는 방식이다. 만약 서브쿼리 내부가 윈도우 함수 등으로 복잡한 경우 DB를 이를 먼저 전체 계산 후 외부 쿼리에서 IN 절의 ID와 FISH_INFO의 일치 여부를 검사하므로 속도가 느리다. IN이 외부 테이블의 PK와 직접 연결되는 경우, JOIN 방식이 더 효율적일 수 있다.

 

2. GROUP BY + (COL1, COL2) IN 

WITH FISH_JOIN AS (
    SELECT 
        F.ID,
        N.FISH_NAME,
        F.LENGTH
    FROM FISH_INFO F
    LEFT JOIN FISH_NAME_INFO N
      ON F.FISH_TYPE = N.FISH_TYPE
)

SELECT *
FROM FISH_JOIN
WHERE (FISH_NAME, LENGTH) IN (
    SELECT 
        FISH_NAME,
        MAX(LENGTH)
    FROM FISH_JOIN
    GROUP BY FISH_NAME
)
ORDER BY ID;

장점

• 로직이 직관적이고 이해하기 쉬움
• JOIN을 먼저 수행하고 재활용하므로 가독성 향상

단점

• (FISH_NAME, LENGTH)로 비교하므로, 동일한 이름 + 동일한 길이인 여러 행이 존재하면 다수 반환 위험 (이 문제에선 괜찮지만 일반화는 어려움)
• CTE를 2번 실행하기 때문에 성능 면에서 효율이 다소 떨어질 수 있음

튜플 비고는 일반적인 단일 컬럼 비교보다 더 많은 계산 비용이 든다. DB는 메인 쿼리와 IN 서브쿼리에서 CTE를 두 번 실행하는데, 이 때 FISH_NAME과 LENGTH를 비교하므로 인덱스를 제대로 활용하기 어렵다. 또한, MAX(LENGTH)가 FISH_NAME에 대해 그룹화한 값을 반환하지만, 같은 길이를 가진 물고기가 여러 마리 있는 경우 비교 대상이 늘어날 수 있다.

 

최종 쿼리

WITH MAX_FISH AS (
    SELECT 
    	   *,
           ROW_NUMBER() OVER(PARTITION BY FISH_TYPE ORDER BY LENGTH DESC) AS RN
    FROM FISH_INFO
)

SELECT
    F.ID,
    N.FISH_NAME,
    F.LENGTH
FROM MAX_FISH F
JOIN FISH_NAME_INFO N
	ON F.FISH_TYPE = N.FISH_TYPE
WHERE F.RN = 1
ORDER BY F.ID;

장점

• ROW_NUMBER()로 각 그룹 내에서 정확히 1개만 선택
• IN 없이 바로 원하는 조건 필터링 => 별도의 임시 테이블이 없어 성능상 유리

이번 문제는 ROW_NUMBER()와 RANK() 중 어떤 함수를 써도 무방하지만, 다양한 풀이 방법을 쓰고 싶어서 다르게 구성해보았다. 임시 테이블이 없기 때문에, 동일한 결과를 반환하더라도 성능 측면에서 더 효율적인 쿼리를 구성할 수 있었다.

 

순위 함수 비교 (윈도우 함수)

함수	동점 처리 방식	다음 순위 처리	고유 번호 보장	대표 활용
ROW_NUMBER()	동점도 다르게 부여	1씩 증가	O	정확히 하나만 추출할 때
RANK()	동점은 같은 순위	건너뜀	X	공동 순위 허용 시
DENSE_RANK()	동점은 같은 순위	건너뛰지 않음	X	순위별 통계나 구간 나눌 때