[Select 30~33] SQL 코딩테스트 고득점 Kit

Programmers SQL 코딩테스트 고득점 Kit
SELECT 문제 30번 ~ 33번까지 LV2 이상 문제 정답 쿼리입니다.

30. 부모의 형질을 모두 가지는 대장균 찾기 (LV2)
31. 대장균의 크기에 따라 분류하기 2 (LV3)
32. 특정 세대의 대장균 찾기 (LV4)
33. 멸종위기의 대장균 찾기 (LV5)

30. 부모의 형질을 모두 가지는 대장균 찾기 (LV2)

정답:

SELECT e2.ID
    ,  e2.GENOTYPE
    ,  e1.GENOTYPE AS PARENT_GENOTYPE
FROM ECOLI_DATA AS e1
JOIN ECOLI_DATA AS e2 ON e1.ID = e2.PARENT_ID
WHERE (e1.GENOTYPE & e2.GENOTYPE) = e1.GENOTYPE
ORDER BY e2.ID ASC

'부모의 형질을 모두 보유한' 을 토대로 WHERE문을 정확하게 작성하는게 중요했다!

31. 대장균의 크기에 따라 분류하기 2 (LV3)

정답:

WITH BASE AS(
    SELECT ID
        ,  SIZE_OF_COLONY
        ,  NTILE(4) OVER(ORDER BY SIZE_OF_COLONY DESC) AS RNK_SIZE
    FROM ECOLI_DATA
)
SELECT ID
    ,  CASE WHEN RNK_SIZE = 1 THEN 'CRITICAL'
            WHEN RNK_SIZE = 2 THEN 'HIGH'
            WHEN RNK_SIZE = 3 THEN 'MEDIUM'
            ELSE 'LOW'
        END AS COLONY_NAME
FROM BASE
ORDER BY ID ASC

WITH문에 NTILE() 사용하면 쉽게 풀렸음!

32. 특정 세대의 대장균 찾기 (LV4)

정답:

SELECT e3.ID
FROM ECOLI_DATA AS e1
JOIN ECOLI_DATA AS e2 ON e1.ID = e2.PARENT_ID
JOIN ECOLI_DATA AS e3 ON e2.ID = e3.PARENT_ID
WHERE e1.PARENT_ID IS NULL
ORDER BY e3.ID

33. 멸종위기의 대장균 찾기 (LV5)

정답:

WITH RECURSIVE GENERATION AS (
    SELECT ID
    	,  PARENT_ID
        ,  1 AS GEN
    FROM ECOLI_DATA
    WHERE PARENT_ID IS NULL

    UNION ALL

    SELECT e.ID
    	,  e.PARENT_ID
        ,  g.GEN + 1
    FROM ECOLI_DATA AS e
    JOIN GENERATION AS g ON e.PARENT_ID = g.ID
),
CHILDLESS AS (
    SELECT g.ID
    	,  g.GEN
    FROM GENERATION AS g
    LEFT JOIN ECOLI_DATA AS e ON g.ID = e.PARENT_ID
    WHERE e.ID IS NULL
)

SELECT COUNT(*) AS COUNT
	,  GEN AS GENERATION
FROM CHILDLESS
GROUP BY GEN
ORDER BY GEN

재귀함수란?
: 함수 내부에서 자기 자신을 다시 호출해서 문제를 반복적으로 작게 나눠서 해결하는 방식

• SQL은 기본적으로 반복이 없기 때문에, 재귀 CTE (Common Table Expression) 를 써서 계층 구조를 탐색

MySQL 재귀 CTE 구조:

WITH RECURSIVE CTE_NAME AS (
    -- 1. Anchor (기저 조건): 최초 시작 지점
    SELECT ...
    UNION ALL
    -- 2. Recursive part (자기 자신 호출): 이전 결과를 기반으로 확장
    SELECT ...
    FROM CTE_NAME
    JOIN ...
)
SELECT * 
FROM CTE_NAME