[프로그래머스/MySQL/SUM, MAX, MIN/LV.3] 물고기 종류 별 대어 찾기

문제

문제 설명

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

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 은 각각 물고기의 종류(숫자), 물고기의 이름(문자) 입니다.

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/27
7	0	55	2021/01/18
8	2	73	2020/01/28
9	1	73	2021/04/08
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

---

✔️  문제접근

• FISH_TYPE을 GROUP BY 하여 각 물고기 타입마다 MAX(LENGTH)를 구하는 서브쿼리를 만든다.
• 이를 메인 쿼리에서 조건절로 넣는다.

⭐️   내 정답

1. GROUP BY를 활용한 정답

SELECT ID, FISH_NAME, LENGTH
FROM FISH_INFO INNER JOIN FISH_NAME_INFO USING(FISH_TYPE)
WHERE (FISH_TYPE, LENGTH) IN (
SELECT FISH_TYPE, MAX(LENGTH) AS LENGTH
FROM FISH_INFO
GROUP BY FISH_TYPE
)
ORDER BY ID ASC;

2. 서브쿼리만 사용한 정답

SELECT FI.ID, FNI.FISH_NAME, FI.LENGTH
FROM FISH_INFO AS FI
INNER JOIN FISH_NAME_INFO AS FNI USING FISH_TYPE
WHERE FI.LENGTH = (
    SELECT MAX(FI2.LENGTH)
    FROM FISH_INFO AS FI2
    WHERE FI2.FISH_TYPE = FI.FISH_TYPE
)
ORDER BY FI.ID ASC;

추가 설명  🧐

1. 주 쿼리:

   • SELECT FI.ID, FNI.FISH_NAME, FI.LENGTH: FISH_INFO 테이블에서 ID, LENGTH 열과, FISH_NAME_INFO 테이블에서 FISH_NAME 열을 선택합니다.
   • FROM FISH_INFO AS FI: FISH_INFO 테이블을 FI라는 별칭으로 참조합니다.
   • INNER JOIN FISH_NAME_INFO AS FNI ON FI.FISH_TYPE = FNI.FISH_TYPE: FISH_TYPE을 기준으로 FISH_NAME_INFO 테이블과 조인합니다.
   • WHERE FI.LENGTH = ( ... ): FI.LENGTH가 서브쿼리의 결과와 같은지 확인하는 조건입니다.

2. 서브쿼리:

   • SELECT MAX(FI2.LENGTH): FISH_INFO 테이블에서 최대 LENGTH 값을 선택합니다.
   • FROM FISH_INFO AS FI2: FISH_INFO 테이블을 FI2라는 별칭으로 참조합니다.
   • WHERE FI2.FISH_TYPE = FI.FISH_TYPE: FI2 테이블의 FISH_TYPE이 주 쿼리의 FI 테이블의 FISH_TYPE과 같은지 확인합니다.
   • GROUP BY FI2.FISH_TYPE: FI2 테이블을 FISH_TYPE별로 그룹화합니다.

---

🔎  짚고 넘어가기

GROUP BY와 HAVING 절에 대해 자세히 알지 못하는 것 같아 이를 정리하여 작성해보았습니다.

GROUP BY 절

GROUP BY 절은 결과 집합을 하나 이상의 열을 기준으로 그룹화할 때 사용됩니다. GROUP BY를 사용할 때, SELECT 절에 나오는 모든 열은 다음 중 하나여야 합니다:
1. 그룹화에 사용된 열: GROUP BY 절에 명시된 열
2. 집계 함수: SUM(), COUNT(), MAX(), MIN(), AVG() 등

예를 들어, 다음과 같은 테이블이 있다고 가정해봅시다:

CREATE TABLE sales (
    id INT,
    product_id INT,
    amount DECIMAL,
    sale_date DATE
);

이 테이블에서 각 product_id별 총 판매액을 구하려면:

SELECT product_id, SUM(amount) AS total_amount
FROM sales
GROUP BY product_id;

위 쿼리에서는 product_id로 그룹화하고, 각 그룹에 대해 amount의 합계를 계산합니다.

만약 아래 예시처럼 SELECT 절에 GROUP BY에 포함되지 않은 열을 집계 함수 없이 사용하려고 하면 오류가 발생합니다.

SELECT id, product_id, SUM(amount) AS total_amount
FROM sales
GROUP BY product_id;

이 쿼리는 오류를 발생시킵니다. 왜냐하면 id 열이 GROUP BY에 포함되지 않았고, 집계 함수로 감싸지지 않았기 때문입니다.

HAVING 절

HAVING 절은 GROUP BY로 그룹화된 결과에 대한 조건을 지정할 때 사용됩니다. WHERE 절과 유사하지만, HAVING 절은 그룹화된 결과에 적용됩니다.

예를 들어, 각 제품의 총 판매액이 1000 이상인 제품만 선택하려면:

SELECT product_id, SUM(amount) AS total_amount
FROM sales
GROUP BY product_id
HAVING SUM(amount) >= 1000;

이 쿼리는 각 product_id별로 그룹화된 결과 중 총 판매액이 1000 이상인 그룹만 반환합니다.