[프로그래머스] 👩🏻‍💻 (MySQL) 물고기 종류 별 대어 찾기

1. 문제설명

 

2. 접근방식

 
먼저 FISH_INFO 테이블이랑 FISH_NAME_INFO 테이블을 FISH_TYPE 컬럼으로 조인 해 준다. 
그리고 FISH_NAME으로 GROUP BY 해 주고, LENGTH의 MAX 값을 구해주면 된다고 생각 했다.
 

🙅🏻‍♀️첫번째 시도(실패)

일단 이게 내가 처음에 생각했던 쿼리이다. 
바로 구문 오류가 떴다. 
 

GROUP BY절에 집계되지 않은 컬럼 A.ID가 있다고 하는 오류 메세지가 떴다. 

현재 GROUP BY 절에 B.FISH_NAME만 있고, A.ID는 GROUP BY에 포함되지 않아서 오류가 발생한 것이다.  ORDER BY에 A.ID를 사용할 때, 그 열이 GROUP BY 절에 포함되어 있지 않으면 결과가 예기치 않게 나타날 수 있다고 한다. 그래서 GROUP BY 절에서 A.ID를 포함하거나 집계 함수(MAX())로 처리해야 한다고 한다.
그래서 GROUP BY 절에도 A.ID를 포함시켜 주었다.
 
또 잘 살펴 보니까 ALIAS도 잘못줬다.
LENGTH 라고 출력해야 하는데 MAX_LENGTH라고 해버렸다.
이 부분도 수정해 주었다. 

 

🙅🏻‍♀️두 번째 시도(실패)

 

결과창을 보니 중복된 물고기들이 계속 나오고 있었다? 읭?
FISH_NAME 앞에 DISTINCT를 추가 해 주어야 하나...? 는 말도 안되고 
 
같은 종류의 물고기 중에서 가장 큰 한 마리만 나오는게 아니라 여러 마리 나오는 이유는 group by 절에 id 값도 같이 써줬기 때문이다. group by 절에서 id를 빼버리면 첫번째 시도처럼 아예 쿼리가 작동 안 하기 때문에 다른 방법을 찾아야 한다.
 
결국 해결방법은 두 가지이다.
 
첫 번째로는 GROUP BY에서 A.ID를 제외하고 FISH_NAME만 그룹화하여, 각 FISH_NAME별로 가장 큰 물고기를 선택하고, 그 후 ID로정렬하는 것이다. 이렇게 하려면 WHERE 절에 서브쿼리를 써 주어야 한다.
두 번째로는 윈도우 함수인 ROW_NUMBER()을 써주는 것이다.

---

3. 정답코드

1) 서브쿼리로 푸는 코드

SELECT A.ID, B.FISH_NAME, A.LENGTH AS LENGTH
FROM FISH_INFO A
JOIN FISH_NAME_INFO B ON A.FISH_TYPE = B.FISH_TYPE
WHERE A.LENGTH = (
    SELECT MAX(LENGTH)
    FROM FISH_INFO
    WHERE FISH_TYPE = A.FISH_TYPE
)
ORDER BY A.ID;

 
GROUP BY에서 A.ID 제외하고 FISH_NAME만 그룹화

WHERE A.LENGTH = (SELECT MAX(LENGTH) FROM FISH_INFO WHERE FISH_TYPE = A.FISH_TYPE) 조건을 사용해 각 FISH_NAME별로 가장 큰 LENGTH를 가진 물고기를 선택

이후 **ORDER BY A.ID**를 사용하여, ID 기준으로 정렬
 

2) 윈도우 함수 ROW_NUMBER 으로 풀기 

WITH RankedFish AS (
    SELECT A.ID, B.FISH_NAME, A.LENGTH,
           ROW_NUMBER() OVER (PARTITION BY B.FISH_NAME ORDER BY A.LENGTH DESC) AS rn
    FROM FISH_INFO A
    JOIN FISH_NAME_INFO B ON A.FISH_TYPE = B.FISH_TYPE
)
SELECT ID, FISH_NAME, LENGTH AS LENGTH
FROM RankedFish
WHERE rn = 1
ORDER BY ID;

 
윈도우 함수 ROW_NUMBER로 풀려면 먼저 CTE를 정의해 주어야 한다.
아래 SELECT 부터 시작하는 조회 구문에서 FROM 절에 쓰일 임시 테이블을 만들어 주는 작업이라고 생각하면 된다.
 
WITH RankedFish AS (...) 절까지 실행된 상태는 CTE (Common Table Expression)가 정의된 상태이다. 즉, RankedFish라는 임시 테이블이 쿼리의 나머지 부분에서 사용될 준비가 된 상태이다. CTE 테이블을 가지고 후속 쿼리를 통해 실제로 최종 데이터가 선택되고 필터링된다.

임시 테이블 RankedFish CTE는 각 FISH_NAME별로 가장 큰 LENGTH를 가진 물고기를 우선순위에 따라 정렬하고, 하고 순위(rn)를 부여한 상태이다. 만약 기존 테이블의 데이터에 연어 두 마리, 참치, 배스가 각각 두 마리가 있다고 하면 길이에 따라 연어별로 1,2위 그리고 참치별로 1,2위, 배스 별로 1,2위를 매겨서 각 1,2 라는 숫자가 rn컬럼에 들어가 있는 상태이다.
 
여기서 후속 쿼리를 써 주어서 rn이 1인 데이터만 필터링하고
ID로 오름차순 정렬을 해 주는 것이다.