[프로그래머스] 대장균의 크기에 따라 분류하기2

       문제 요약        

- 문제

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

프로그래머스

 

         아이디어        

WITH 데이터이름 AS (

) SELECT * FROM 데이터이름 ORDER BY ID;

 

 

NTILE(몇개의그룹) OVER (ORDER BY 사이즈 DESC)

 

 

RANK() OVER ( [PARTITION BY partition_expression, ... ] ORDER BY sort_expression [ASC | DESC], ... )

 

         소스코드        

WITH RankedData AS (
    SELECT 
        ID,
        size_of_colony,
        NTILE(4) OVER (ORDER BY size_of_colony DESC) AS percentile_group
    FROM 
        ecoli_data
)
SELECT 
    ID,
    CASE 
        WHEN percentile_group = 1 THEN 'CRITICAL' -- 상위 25%
        WHEN percentile_group = 2 THEN 'HIGH' -- 25~50%
        WHEN percentile_group = 3 THEN 'MEDIUM' -- 50~75%
        WHEN percentile_group = 4 THEN 'LOW' -- 하위 25%
    END AS COLONY_NAME
FROM 
    RankedData
ORDER BY 
    ID;