[MYSQL] 대장균의 크기에 따라 분류하기 2

문제

대장균 개체의 크기를 내름차순으로 정렬했을 때 상위 0% ~ 25% 를 'CRITICAL', 26% ~ 50% 를 'HIGH', 51% ~ 75% 를 'MEDIUM', 76% ~ 100% 를 'LOW' 라고 분류합니다. 대장균 개체의 ID(ID) 와 분류된 이름(COLONY_NAME)을 출력하는 SQL 문을 작성해주세요. 이때 결과는 개체의 ID 에 대해 오름차순 정렬해주세요 . 단, 총 데이터의 수는 4의 배수이며 같은 사이즈의 대장균 개체가 서로 다른 이름으로 분류되는 경우는 없습니다.

 

내가 푼 쿼리

WITH PERCENT_COLONY AS
(
SELECT
    ID,
    PERCENT_RANK() OVER(ORDER BY SIZE_OF_COLONY DESC) AS PERCENT_OF_COLONY
FROM ECOLI_DATA
)

SELECT 
    ID,
    CASE
        WHEN PERCENT_OF_COLONY <= 0.25 THEN "CRITICAL"
        WHEN PERCENT_OF_COLONY <= 0.5 THEN "HIGH"
        WHEN PERCENT_OF_COLONY <= 0.75 THEN "MEDIUM"
        ELSE "LOW"
    END AS COLONY_NAME
FROM PERCENT_COLONY
ORDER BY ID;

ECOLI_DATA 테이블에는 각 균주의 ID와 군집 크기(SIZE_OF_COLONY) 정보가 있다. 이번 문제를 해결하기 위해서는 PERCENT_RANK() 함수를 활용하여, 특정 값을 기준으로 각 행의 상대적인 순위를 계산한 뒤 구간별로 분류해야 한다.

PERCENT_RANK()는 전체 데이터 중 해당 행이 차지하는 상대적 백분위 순위(0~1) 를 반환한다. ORDER BY SIZE_OF_COLONY DESC를 지정했기 때문에, 군집 크기가 클수록 PERCENT_RANK() 값은 0에 가까워지고, 작을수록 1에 수렴한다.

이 함수는 구간별 분류나 등급화를 할 때 특히 유용하며, 데이터가 균등하지 않거나 분포가 왜곡된 경우에도 안정적으로 작동한다. 또한 CASE문과 함께 사용하면, 데이터를 자동으로 라벨링하거나 범주화할 수 있어 다양한 실무 상황에 적용할 수 있다.