[MySQL] SQL 코테 - 물고기 종류 별 대어 찾기

낚시앱에서 사용하는 FISH_INFO 테이블은 잡은 물고기들의 정보를 담고 있습니다. ID, FISH_TYPE, LENGTH, TIME은 각각 잡은 물고기의 ID, 물고기의 종류(숫자), 잡은 물고기의 길이(cm), 물고기를 잡은 날짜를 나타냅니다.

단, 잡은 물고기의 길이가 10cm 이하일 경우에는 LENGTH 가 NULL 이며, LENGTH 에 NULL 만 있는 경우는 없습니다.
FISH_NAME_INFO 테이블은 물고기의 이름에 대한 정보를 담고 있습니다. FISH_TYPE, FISH_NAME 은 각각 물고기의 종류(숫자), 물고기의 이름(문자) 입니다.

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

 

 

첫번째 시도 방법

SELECT fi.ID, fn.FISH_NAME, fi.LENGTH
from fish_info fi join fish_name_info fn on fi.fish_type=fn.fish_type

where fi.length = (select max(length)
       from fish_info
       group by fish_info.fish_type
      )

order by fi.ID asc;

서브쿼리 결과 테이블

 

위와 같은 테이블을 만드는 서브쿼리를 만들어, 해당 max값과 동일한 length값을 가지는 행을 출력하도록 하였다.

하지만 여기서 문제점은 서브쿼리의 값과 메인쿼리의 값을 where절에서 비교할때, 같은 물고기종류인지를 조건화하는 코드가 없다.

즉, 이 코드는 모든 max(length)값을 반환하여 fi,length값과 비교하고 있음

 

 

 

두번째 시도 방법

SELECT fi.ID, fn.FISH_NAME, fi.LENGTH
from fish_info fi join fish_name_info fn on fi.fish_type=fn.fish_type

where fi.length = (select max(length)
       from fish_info
       where fi.fish_type = fish_info.fish_type
      )

order by fi.ID asc;

 

(답을 착안.. ㅎ)

종류별로 max(length)값을 뽑아놓지 말고, 해당 물고기의 max(length)값만 추출하는 방법으로 변경

groupby 에서 where절로 바꾼 부분을 설명하면,

메인 쿼리에서의 fish_type과 서브쿼리에서 from으로 가져온 fish_type을 비교하여

메인에서 다루고 있는 종류의 fish만을 대상으로 서브쿼리에서 다루게 됨.

 

여기서 기본적으로 들어가는 개념은 메인쿼리에서 행을 하나씩 가져와 비교한다는 것

서브 쿼리 내의 fi.fish_type은 메인쿼리의 값을 가져오는데,

메인쿼리에서의 where절은 메인의 테이블의 행을 하나씩 가져와 where처리한다는 것!
예시를 들면

(서브쿼리)
select max(length)
from fish_info
where 100 = fish_info.fish_type

(다음 턴 서브쿼리)
select max(length)
from fish_info
where 200 = fish_info.fish_type

이와 같이 fi.fish_type 자리에 메인쿼리에서의 행을 하나씩 가져와서 비교하게 된다.

 

따라서, 이 서브쿼리에서는 메인쿼리에서 다루는 fish_type에 해당하는 모든 값들을 필터링 하고, 거기서 max(length)값을 가지는 행 하나를 추출한다.

그래서 이 max(length)값이 현재 행의 length와 같은가 를 비교하는게 메인쿼리에서의 where절이다.

조건에 만족하는 행만 추출이 되는 것이고!

 

 

 

생각보다 python 동작보다 불필요한 동작이나 메모리가 많을 것 같다는 생각이 들고,

python으로 꾸려왔던 알고리즘 동작이랑 조금 달라서 헷갈리긴 하지만, 잘 잡아놔야 할 것 같다.