[MySQL] 프로그래머스 - 특정 조건을 만족하는 물고기별 수와 최대 길이 구하기

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

 

 

WITH FILL_TABLE AS(
SELECT ID, FISH_TYPE,
    CASE WHEN LENGTH IS NULL THEN 10 ELSE LENGTH END AS LENGTH,
    TIME
FROM FISH_INFO)

SELECT 
    COUNT(FISH_TYPE) AS FISH_COUNT,
    MAX(LENGTH) AS MAX_LENGTH,
    FISH_TYPE
FROM FILL_TABLE
GROUP BY FISH_TYPE
HAVING AVG(LENGTH) >= 33
ORDER BY FISH_TYPE

 

물고기의 null 값을 처리하기 위해 CTE에서 CASE WHEN 문을 선언하고, 문제의 조건에 따라 10CM로 변경하였다. 이후, 집계 함수를 활용해 쿼리를 작성했다. 정답을 풀었지만, 보다 효율적으로 쿼리를 구상하기 위해 COALESCE 함수를 활용해 다르게 문제를 풀어보았다.

 

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

 

COALESCE는 LENGTH COLUMN의 값이 NULL인 경우 10으로 처리한다. 이는 CASE WHEN 구문을 대체하며, 동일한 결과여도 보다 효율적으로 결과를 제시한다. 

 

조건절 문제를 풀다보면, WHERE 절과 HAVING 절의 사용이 헷갈릴 때가 있다.  이 두 절은 각각 SQL 쿼리의 다른 단계에서 작동한다.

1. WHERE 절

• WHERE 절은 데이터베이스가 데이터를 가져오는 단계에서 조건을 적용
• 즉, GROUP BY 절이 적용되기 전에 각 행을 필터링
• 따라서 집계 함수(예: AVG, COUNT, MAX)와 함께 사용할 수 없음

2. HAVING 절

• HAVING 절은 데이터를 그룹화하고 나서 각 그룹에 조건을 적용
• 집계 함수와 함께 사용할 수 있음

이 문제에서는 평균 길이가 33cm 이상인 물고기 종류를 필터링하고 있다. 이 조건은 LENGTH 값의 평균을 계산한 후에 적용되므로 HAVING 절을 사용해야 한다. 만약 WHERE 절을 사용하면, 평균 길이를 계산하기 전에 조건을 적용하게 되어 원하는 결과를 얻을 수 없다.

예를 들어, HAVING 절을 WHERE 절로 바꾸면 쿼리가 올바르게 작동하지 않는다. WHERE 절은 개별 행에 대해 조건을 적용하므로, 집계 함수가 계산되기 전에 조건을 충족하지 않는 행이 필터링되기 때문이다. 다시 말해, 이 쿼리에서 HAVING 절을 WHERE 절로 바꿀 수 없는 이유는 HAVING 절이 집계된 결과에 대해 조건을 적용하는데 사용되기 때문이다.

프로그래머스