🧩 문제

https://school.programmers.co.kr/learn/courses/30/lessons/301649

---

📌 문제 설명

대장균 개체의 크기를 기준으로 분류를 나누는 문제입니다. 군집의 크기가 큰 순서대로 상위 25%는 'CRITICAL', 26%~50%는 'HIGH', 51%~75%는 'MEDIUM', 76%~100%는 'LOW'로 이름을 붙여 출력해야 합니다.

• ECOLI_DATA 테이블에는 각 개체의 ID와 크기 정보가 담겨 있습니다.
• 개체의 크기가 동일한 경우에 대한 별도 조건은 없으나, 비율에 따라 정확히 4등분하는 것이 핵심입니다.

📂 테이블 구조

Column name	Type	Nullable
ID	INTEGER	FALSE
PARENT_ID	INTEGER	TRUE
SIZE_OF_COLONY	INTEGER	FALSE
DIFFERENTIATION_DATE	DATE	FALSE
GENOTYPE	INTEGER	FALSE

---

🎯 문제 목표

대장균 개체별로 크기 순위에 따른 등급(COLONY_NAME)을 부여하여 출력합니다.

• 출력 컬럼: ID, COLONY_NAME
• 정렬: ID 오름차순

---

🧠 문제 접근 (Approach)

1. 상대적 순위(비율) 계산하기 단순히 순위를 매기는 것이 아니라 전체에서 어느 정도 위치에 있는지를 0~1 사이의 값으로 계산해야 합니다. PERCENT_RANK() 함수를 사용하면 (현재 행의 순위 - 1) / (전체 행 수 - 1) 공식을 통해 백분율 위치를 구할 수 있습니다.
2. 내림차순 정렬로 상위권 정의 SIZE_OF_COLONY를 DESC(내림차순)로 정렬하여 크기가 가장 큰 개체가 0(상위 0%)에 가깝게 나오도록 설정합니다.
3. CASE 문을 이용한 등급 매핑 계산된 비율(PER)을 조건문에 따라 분기합니다.

• 0.00 <= PER <= 0.25: CRITICAL
• 0.25 < PER <= 0.50: HIGH
• 0.50 < PER <= 0.75: MEDIUM
• 0.75 < PER <= 1.00: LOW

4. 최종 결과 정렬 계산이 끝난 후 문제의 요구사항대로 ID를 기준으로 오름차순 정렬합니다.

👉 핵심 아이디어 한 줄 요약

PERCENT_RANK를 사용하여 개체 크기별 상대적 위치를 구하고, 이를 CASE 문으로 4개 등급으로 분류합니다.

---

✅ 내 정답

SELECT ID,
       CASE 
           WHEN PER <= 0.25 THEN 'CRITICAL'
           WHEN PER <= 0.50 THEN 'HIGH'
           WHEN PER <= 0.75 THEN 'MEDIUM'
           ELSE 'LOW'
       END AS COLONY_NAME
FROM (
    SELECT ID, 
           PERCENT_RANK() OVER (ORDER BY SIZE_OF_COLONY DESC) AS PER
    FROM ECOLI_DATA
) AS SUB
ORDER BY ID;

---

⚠️ AI 코드 리뷰

-- NTILE을 활용한 대안 풀이
SELECT 
    ID,
    CASE NTILE(4) OVER (ORDER BY SIZE_OF_COLONY DESC)
        WHEN 1 THEN 'CRITICAL'
        WHEN 2 THEN 'HIGH'
        WHEN 3 THEN 'MEDIUM'
        WHEN 4 THEN 'LOW'
    END AS COLONY_NAME
FROM ECOLI_DATA
ORDER BY ID;

코드리뷰 포인트

• PERCENT_RANK vs NTILE: 작성하신 PERCENT_RANK()는 데이터가 0~1 사이의 연속적인 분포를 가질 때 유리합니다. 반면 NTILE(4)를 사용하면 데이터를 강제로 4개의 그룹으로 균등하게 쪼개주므로 코드가 더 간결해질 수 있습니다.
• 서브쿼리 활용: PERCENT_RANK()의 결과값을 CASE 문에서 여러 번 비교해야 하므로, 서브쿼리(또는 CTE)를 사용하여 가독성을 높인 점이 아주 좋습니다.

---

짚고가기

이 문제의 핵심인 윈도우 함수(Window Function) 두 가지를 비교 정리합니다.

1. PERCENT_RANK()

전체 행에서 현재 행의 상대적 순위 비율을 0~1 사이로 반환합니다.

• 특징: 데이터 개수가 적을 때와 많을 때 모두 비율로 접근하기 좋습니다.
• 예시: 0.0은 1등, 1.0은 꼴찌를 의미합니다.

코드 사용 예시:

SELECT ID, 
       SIZE_OF_COLONY,
       PERCENT_RANK() OVER (ORDER BY SIZE_OF_COLONY DESC) AS PER
FROM ECOLI_DATA;
/* 결과 예시:
ID | SIZE | PER
1  | 1000 | 0.0   (상위 0%)
2  | 500  | 0.5   (상위 50%)
3  | 10   | 1.0   (상위 100%)
*/

2. NTILE(n)

데이터를 n개의 그룹으로 균등하게 배분하고 그룹 번호(1~n)를 반환합니다.

• 특징: "상위 25%씩 4등분"처럼 개수 기반으로 그룹을 나눌 때 가장 직관적입니다.
• 예시: NTILE(4)를 쓰면 전체 행이 100개일 때 1~25번까지는 1그룹, 26~50번까지는 2그룹으로 나뉩니다.

코드 사용 예시:

SELECT ID, 
       SIZE_OF_COLONY,
       NTILE(4) OVER (ORDER BY SIZE_OF_COLONY DESC) AS QUARTILE
FROM ECOLI_DATA;
/* 결과 예시:
ID | SIZE | QUARTILE
1  | 1000 | 1 (상위 25% 이내)
2  | 800  | 1 (상위 25% 이내)
3  | 500  | 2 (25% ~ 50% 사이)
...
*/

💡 정리

• 비율에 따른 임계값(0.25 등)이 명확히 주어지면 PERCENT_RANK()가 논리적으로 합당합니다.
• 단순히 행 개수를 n등분 하는 것이 목적이라면 NTILE(n)이 코드의 복잡도를 줄여줍니다.