[프로그래머스 SQL/293261] 물고기 종류 별 대어 찾기

25 Apr 2025 in Study / Coding Test

• ☑️ 문제
• ☑️ 풀이
  • 첫 번째 풀이
  • 두 번째 풀이
  • 세 번째 풀이
• ☑️ 풀이
  • CTE(Common Table Expression)

☑️ 문제

프로그래머스 293261

☑️ 풀이

첫 번째 풀이

SELECT FI.ID, FNI.FISH_NAME, FI.LENGTH
FROM FISH_INFO FI JOIN FISH_NAME_INFO FNI
ON FI.FISH_TYPE = FNI.FISH_TYPE
WHERE (FI.FISH_TYPE, FI.LENGTH) IN (
    SELECT FISH_TYPE, MAX(LENGTH)
    FROM FISH_INFO
    GROUP BY FISH_TYPE)
ORDER BY FI.ID

• WHERE절 서브쿼리를 사용했다.
  • 서브쿼리 내에서 각 FISH_TYPE 별 MAX(LENGTH)를 구한다.
• 외부 쿼리에서는 서브 쿼리의 결과와 일치하는 (FI.FISH_TYPE, FI.LENGTH)값들만 필터링한다.

두 번째 풀이

SELECT FI.ID, FNI.FISH_NAME, FI.LENGTH
FROM FISH_INFO FI JOIN FISH_NAME_INFO FNI
ON FI.FISH_TYPE = FNI.FISH_TYPE
WHERE FI.LENGTH = (
    SELECT MAX(FI2.LENGTH)
    FROM FISH_INFO FI2
    WHERE FI.FISH_TYPE = FI2.FISH_TYPE)
ORDER BY FI.ID

• 여기서는 상관 서브쿼리를 사용했다. (메인 쿼리의 각 행마다 실행됨)
  • 현재 행의 FISH_TYPE에 해당하는 MAX(LENGTH)를 계산한다.
  • 해당 MAX(LENGTH)와 일치하는 LENGTH를 가진 값만 필터링한다.

세 번째 풀이

WITH RANKEDFISH AS (
    SELECT FI.ID, FI.FISH_TYPE, FI.LENGTH, FNI.FISH_NAME, 
    MAX(LENGTH) OVER (PARTITION BY FI.FISH_TYPE) AS MAX_LENGTH
    FROM FISH_INFO FI JOIN FISH_NAME_INFO FNI
    ON FI.FISH_TYPE = FNI.FISH_TYPE)
    
SELECT ID, FISH_NAME, MAX_LENGTH AS 'LENGTH'
FROM RANKEDFISH
WHERE LENGTH = MAX_LENGTH
ORDER BY ID;

• 공통 테이블 표현식(CTE)와 윈도우 함수를 사용하였다.
  • WITH RANKEDFISH AS (...)
  • MAX(LENGTH) OVER (PARTITION BY FI.FISH_TYPE) : 각 FISH_TYPE 별로 최대 LENGTH를 모든 행에 붙여준다.
• 이후 RANKEDFISH 테이블을 사용하여 WHERE LENGTH = MAX_LENGTH를 통해 물고기 종류 별 최대 길이를 가진 행만 필터링했다.

☑️ 풀이

CTE(Common Table Expression)

WITH 테이블이름 AS (
		SELECT ...
)

SELECT ...
FROM 테이블이름

• 쿼리의 결과를 임시 테이블처럼 정의해서 이름을 붙이고, 이후 SELECT, JOIN, WHERE 등에서 재사용한다.

예시

WITH BigFish AS (
    SELECT FISH_TYPE, MAX(LENGTH) AS MAX_LEN
    FROM FISH_INFO
    GROUP BY FISH_TYPE
)

SELECT *
FROM BigFish
WHERE MAX_LEN > 50;