[프로그래머스 SQL 코딩테스트 연습] Lv3. 특정 조건을 만족하는 물고기별 수와 최대 길이 구하기 (MySQL)

🤔 문제

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

 

단, 잡은 물고기의 길이가 10cm 이하일 경우에는 `LENGTH` 가 `NULL` 이며, `LENGTH` 에 `NULL` 만 있는 경우는 없습니다.

`FISH_INFO`에서 평균 길이가 33cm 이상인 물고기들을 종류별로 분류하여 잡은 수, 최대 길이, 물고기의 종류를 출력하는 SQL문을 작성해주세요. 결과는 물고기 종류에 대해 오름차순으로 정렬해주시고, 10cm이하의 물고기들은 10cm로 취급하여 평균 길이를 구해주세요.

컬럼명은 물고기의 종류 `'FISH_TYPE'`, 잡은 수 `'FISH_COUNT'`, 최대 길이 `'MAX_LENGTH'`로 해주세요.

예시

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

 

물고기 종류가 0인 물고기들의 평균 길이는 (30 + 50 + 40) / 3 = 40cm 이고 물고기 종류가 1인 물고기들의 평균 길이는 (30 + 10) / 2 = 20cm 이며, 물고기 종류(가 2인 물고기들의 평균 길이는 (32) / 1 = 32cm 입니다. 따라서 평균길이가 33cm 인 물고기 종류는 0 이므로, 총 잡은 수는 3마리, 최대 길이는 50cm 이므로 결과는 다음과 같아야 합니다.

 

 

 

 

 

 

 

---

💻 나의 풀이

첫번째 시도

WITH FISH AS(
    SELECT ID, FISH_TYPE, LENGTH
    FROM FISH_INFO
    WHERE IFNULL(LENGTH, 10)
    AND (ID, LENGTH) IN (SELECT ID, LENGTH
                        FROM FISH_INFO
                        GROUP BY FISH_TYPE
                        HAVING LENGTH >= 33)
)
SELECT
    COUNT(ID) AS FISH_COUNT,
    LENGTH AS MAX_LENGTH,
    FISH_TYPE
FROM FISH
WHERE (ID, LENGTH) IN (SELECT ID, MAX(LENGTH)
                      FROM FISH
                      GROUP BY FISH_TYPE)
ORDER BY FISH_TYPE;

 

► 틀림

 

어제 풀었던 문제에서 `GROUP BY`때문에 고생했더니 이 문제도 그런식으로 풀어야하는 줄 알았다.

일단 너무 풀이가 복잡하고, 이렇게까지 CTE를 사용해서 풀 필요가 없던 문제였다.

 

 

 

 

두번째 시도

SELECT 
    COUNT(ID) AS FISH_COUNT,
    MAX(LENGTH) AS MAX_LENGTH,
    FISH_TYPE
FROM FISH_INFO
WHERE IFNULL(LENGTH, 10)
GROUP BY FISH_TYPE
HAVING AVG(LENGTH) >= 33
ORDER BY FISH_TYPE;

 

► 정답으로 처리되긴 했으나, 일부 테스트 케이스에서 '실패'가 떴다. 엄밀히 말하면 오답인 것이다.

 

 

 

 

 

 

 

---

🖍 오답노트

틀린 이유

두번째 시도가 틀린 이유는 무엇일까?

우선, 두번째 시도와 정답 풀이의 차이는 `IFNULL(LENGTH, 10)`의 위치이다.

 

 

◆ 두번째 시도 : `WHERE IFNULL(LENGTH, 10)`

• `WHERE`절은 쿼리의 행을 필터링하는데 사용되며, 조건이 참(`TRUE`)인 행만 선택하게 된다. 
• `LENGTH`가 `NULL`인 경우 `IFNULL(LENGTH, 10)`은 10을 반환하고, 10은 참(`TRUE`)로 평가되므로 이 조건을 만족한다.
• 그러나, `WHERE` 절은 행을 필터링하기 때문에 `NULL`을 10으로 변환하지만, 그 값은 나중에 `AVG(LENGTH)` 계산에 반영되지 않습니다. 왜냐하면 `AVG(LENGTH)`는 `LENGTH`가 `NULL`인 경우를 고려하지 않기 때문이다.

 

 

 

정답 풀이

SELECT 
    COUNT(ID) AS FISH_COUNT,
    MAX(LENGTH) AS MAX_LENGTH,
    FISH_TYPE
FROM FISH_INFO
GROUP BY FISH_TYPE
HAVING AVG(IFNULL(LENGTH, 10)) >= 33
ORDER BY FISH_TYPE;

 

◆ 정답 풀이 : `GROUP BY FISH_TYPE HAVING AVG(IFNULL(LENGTH, 10)) >= 33`

 

• `GROUP BY FISH_TYPE`에 의해 각 `FISH_TYPE`별로 그룹화된 후 `HAVING` 절에서 각 그룹의 평균 길이를 계산한다.
• `HAVING AVG(IFNULL(LENGTH, 10)) >= 33`는 그룹화된 각 `FISH_TYPE`별로 `LENGTH`가 `NULL`인 경우 10으로 변환한 후, 평균을 계산합니다.

 

 

 

정리하자면,

두번째 시도에서는 `WHERE` 절에 `IFNULL`을 사용하여 `NULL` 값을 10으로 대체하지만, 이 값은 그룹화된 후의 평균 계산에 반영되지 않기 때문에 문제가 발생한다.

반면, 정답 풀이는 `HAVING` 절에서 `IFNULL`을 사용하여 그룹화된 후에 평균을 올바르게 계산하게 된다.