[SQL] 물고기 종류별 대어 찾기(프로그래머스/MySQL/Level 3)

안녕하세요! 데코입니다!

 

오늘은 프로그래머스 코딩테스트 연습에 있는 "물고기 종류별 대어 찾기" 문제를 포스팅하려고 합니다!

 

바로 포스팅 시작할게요! :)

(출처 : https://school.programmers.co.kr/learn/courses/30/lessons/293261)

---

 

1. 문제 :  물고기 종류별 대어 찾기(Lv. 3)

 

---

 

2. 문제 설명

 

 

낚시앱에서 사용하는 FISH_INFO 테이블은 잡은 물고기들의 정보를 담고 있습니다. 

FISH_INFO 테이블의 구조는 다음과 같으며

ID, FISH_TYPE, LENGTH, TIME은 각각

잡은 물고기의 ID, 물고기의 종류(숫자), 잡은 물고기의 길이(cm), 물고기를 잡은 날짜를 나타냅니다.

 

[ 테이블 구조('FISH_INFO') ]

COLUMN NAME	TYPE	NULLABLE
ID	INTEGER	FALSE
FISH_TYPE	INTEGER	FALSE
LENGTH	FLOAT	TRUE
TIME	DATE	FALSE

 

 

단, 잡은 물고기의 길이가 10cm 이하일 경우에는 LENGTH 가 NULL이며,

LENGTH에 NULL 만 있는 경우는 없습니다.

FISH_NAME_INFO 테이블은 물고기의 이름에 대한 정보를 담고 있습니다.

FISH_NAME_INFO 테이블의 구조는 다음과 같으며,

FISH_TYPE, FISH_NAME 은 각각

물고기의 종류(숫자), 물고기의 이름(문자)입니다.

 

[ 테이블 구조('FISH_NAME_INFO') ]

COLUMN NAME	TYPE	NULLABLE
FISH_TYPE	INTEGER	FALSE
FISH_NAME	VARCHAR	FALSE

 

 

[ 문제 ]

 

물고기 종류 별로 가장 큰 물고기의 ID, 물고기 이름, 길이를 출력하는 SQL 문을 작성해주세요.

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

 

[ 예시 ]

 

예를 들어 FISH_INFO 테이블이 다음과 같고

ID	FISH_TYPE	LENGTH	TIME
0	0	30	2021/12/04
1	0	50	2020/03/07
2	0	40	2020/03/07
3	1	20	2022/03/09
4	1	NULL	2022/04/08
5	2	13	2021/04/28
6	0	60	2021/07/28
7	0	55	2021/01/28
8	2	73	2020/01/28
9	1	73	2021/04/28
10	2	22	2020/06/28
11	2	17	2022/12/23

 

FISH_NAME_INFO 테이블이 다음과 같고

FISH_TYPE	FISH_NAME
0	BASS
1	SNAPPER
2	ANCHOVY

 

'BASS' 중 가장 큰 물고기는 60cm 로 물고기 ID 가 6이고,

 'SNAPPER' 중 가장 큰 물고기는 73cm 로 물고기 ID가 9입니다.

 'ANCHOVY' 중 가장 큰 물고기는 73cm 로 물고기 ID가 8입니다. 

 

따라서 물고기 ID(ID) 에 대해 오름차순 정렬한다면 결과는 다음과 같습니다.

 

[ 출력 결과 ]

ID	FISH_NAME	LENGTH
6	BASS	60
8	ANCHOVY	73
9	SNAPPER	73

 

 

---

 

3. 문제 풀이

-- 1안
SELECT  A1.ID
        , C.FISH_NAME
        , A1.LENGTH
  FROM  FISH_INFO A1
 INNER
  JOIN  (
        SELECT  FISH_TYPE
                , MAX(LENGTH) AS MAX_LENGTH
          FROM  FISH_INFO A2
         GROUP
            BY  FISH_TYPE 
        ) B
    ON  A1.FISH_TYPE = B.FISH_TYPE
        AND A1.LENGTH = B.MAX_LENGTH
  LEFT
  JOIN  FISH_NAME_INFO C
    ON  A1.FISH_TYPE = C.FISH_TYPE 
 ORDER
    BY  A1.ID ASC

 

 

 

이번 문제는 총 1안 한 가지로 작성했습니다!

 

#  1안 풀이 설명

# [ 문제 접근 설명 ]

문제를 읽고 조건에 맞는 쿼리를 작성하기 위해서 다음과 같이  세 가지 단계로 진행했어요!

1. FISH_INFO 테이블에서 물고기 종류별 가장 큰 길이 구하기(GROUP BY)

2. FISH_INFO 테이블에서 1번에서 추출한 정보와 결합하여 ID정보 추출(INNER JOIN)

3. FISH_NAME_INFO 테이블과 결합하여 FISH_NAME 정보 추출(LEFT JOIN)

4. ID를 기준으로 오름차순 정렬(ORDER BY)

 

# [ 1안 쿼리 작성 설명 ]

 

 1  첫 번째 단계에서는 FISH_INFO  테이블에서 물고기 종류별 가장 큰 길이를 구하는 쿼리를 작성했습니다!

 

GROUP BY를 이용하여 산출하였고, MAX_LENGTH라는 별칭으로 칭해주었습니다!

 

SELECT  FISH_TYPE
                , MAX(*) AS MAX_LENGTH
   FROM  FISH_INFO A2
 GROUP
         BY  FISH_TYPE

 

 

 2  두 번째 단계에서는 처음에 이용한 테이블인 FISH_INFO 테이블과 첫 번째 단계에서 생성한 쿼리를 INNER JOIN 해 주었어요!

 

이렇게 진행한 이유는 FIST_INFO에 있는 ID 컬럼을 가져오기 위함입니다!

 

SELECT  A1.*
   FROM  FISH_INFO A1
  INNER
    JOIN  (
              SELECT  FISH_TYPE
                              , MAX(LENGTH) AS MAX_LENGTH
                 FROM   FISH_INFO A2
               GROUP
                       BY   FISH_TYPE 
               ) B
      ON   A1.FISH_TYPE = B.FISH_TYPE
              AND A1.LENGTH = B.MAX_LENGTH

 

 

 3  세 번째 단계에서는 FISH_NAME_INFO 테이블과 LEFT JOIN을 통해 FISH_NAME 컬럼을 가져와주었어요!

이후에는 요구사항과 동일하게 SELECT문의 컬럼을 정리해주었습니다.

 

SELECT   A1.ID
                 , C.FISH_NAME
                 , A1.LENGTH
   FROM  FISH_INFO A1
  INNER
    JOIN  (
              SELECT  FISH_TYPE
                              , MAX(LENGTH) AS MAX_LENGTH
                 FROM   FISH_INFO A2
               GROUP
                       BY   FISH_TYPE 
               ) B
      ON   A1.FISH_TYPE = B.FISH_TYPE
              AND A1.LENGTH = B.MAX_LENGTH
  LEFT
  JOIN  FISH_NAME_INFO C
    ON  A1.FISH_TYPE = C.FISH_TYPE 

 

 4  네 번째 단계에서는 ID(물고기 ID) 기준으로 오름차순 정렬을 해주었습니다.

SELECT   A1.ID
                 , C.FISH_NAME
                 , A1.LENGTH
   FROM  FISH_INFO A1
  INNER
    JOIN  (
              SELECT  FISH_TYPE
                              , MAX(LENGTH) AS MAX_LENGTH
                 FROM   FISH_INFO A2
               GROUP
                       BY   FISH_TYPE 
               ) B
        ON   A1.FISH_TYPE = B.FISH_TYPE
                 AND A1.LENGTH = B.MAX_LENGTH
    LEFT
    JOIN    FISH_NAME_INFO C
        ON   A1.FISH_TYPE = C.FISH_TYPE 
 ORDER
         BY  A1.ID ASC

 

 

 

정답...!

---

 

4. 데코의 문제 풀이 후기

 

이번 문제에는 하나의 테이블에서 GROUP BY를 통해 최대 값을 산출하고

 

이를 다시 동일한 테이블과 INNER 조인을 하여 원하는 데이터만을 추출한 후

 

LEFT JOIN을 통해 물고기 NAME 정보들을 붙이고 원하는 정보만을 추출하는 문제였습니다!

 

이 외에는 이전 문제들에서 모두 다룬 것들을 이용해서

따로 코멘트할 것은 없습니다!

혹시나 더 좋은 쿼리나 어렵게 느껴지신 부분이 있다면 댓글로 남겨주세요! 

 

---

설명이 어려운 부분 혹은 잘 이해가 안 가는 부분

그리고 더 궁금한 내용이 있다면

언제든지 댓글로 남겨주세요!

빠르게 궁금증을 해결해 드릴게요!

공감과 댓글은 큰 힘이 됩니다!

오늘도 블로그 방문해 주시고

포스팅 읽어주셔서 감사합니다!