[Programmers] 특정 조건을 만족하는 물고기별 수와 최대 길이 구하기
정답
-- FISH_INFO: ID, FISH_TYPE, LENGTH, TIME
-- 10cm 이하의 물고기의 길이를 10cm로
WITH
ALTERED_FISH_INFO AS (
SELECT FISH_TYPE,
CASE
WHEN LENGTH < 10 OR LENGTH IS NULL THEN 10
ELSE LENGTH
END AS LENGTH
FROM FISH_INFO
),
AVG_LEN_BY_TYPE AS (
SELECT FISH_TYPE, AVG(LENGTH) AS AVG_LEN
FROM ALTERED_FISH_INFO
GROUP BY FISH_TYPE
)
SELECT
COUNT(*) AS FISH_COUNT,
MAX(T1.LENGTH) AS MAX_LENGTH,
T1.FISH_TYPE
FROM ALTERED_FISH_INFO AS T1
JOIN AVG_LEN_BY_TYPE AS T2 ON T1.FISH_TYPE = T2.FISH_TYPE
WHERE T2.AVG_LEN >= 33
GROUP BY T1.FISH_TYPE
ORDER BY 3
;
✅ Point
- 길이 정규화 (ALTERED_FISH_INFO CTE)
- LENGTH < 10 또는 NULL인 물고기를 10cm로 보정
- 데이터 전처리 단계
- 평균 길이 계산 (AVG_LEN_BY_TYPE CTE)
- 전처리된 테이블에서 FISH_TYPE별 AVG(LENGTH) 계산
- 조건 필터링에 사용될 기준 생성
- 조건 필터링 및 결과 출력
- 평균 길이(AVG_LEN)가 33 이상인 물고기 종류만 대상으로
- 잡힌 수(COUNT(*)),
- 최대 길이(MAX(LENGTH)),
- 종류(FISH_TYPE)를 조회
- GROUP BY, JOIN, WHERE 조합
- 평균 길이(AVG_LEN)가 33 이상인 물고기 종류만 대상으로
- CTE 없이도 HAVING을 활용하여 풀 수 있는 문제!
다른 풀이
-- FISH_INFO: ID, FISH_TYPE, LENGTH, TIME
SELECT
COUNT(*) AS FISH_COUNT,
MAX(CASE WHEN LENGTH < 10 OR LENGTH IS NULL THEN 10 ELSE LENGTH END) AS MAX_LENGTH,
FISH_TYPE
FROM FISH_INFO
GROUP BY FISH_TYPE
HAVING AVG(CASE WHEN LENGTH < 10 OR LENGTH IS NULL THEN 10 ELSE LENGTH END) >= 33
ORDER BY FISH_TYPE;
- 별도의 cte 선언 없이도 HAVING 절을 통해 풀이 가능