[MySQL] 비율별로 등급나누기 NTILE, PERCENT_RANK

 

 

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

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

프로그래머스

 

 

# NTILE 풀이
WITH COLONYGROUPS AS (
SELECT ID,
        NTILE(4) OVER (ORDER BY SIZE_OF_COLONY DESC) AS GROUPRANK
FROM ECOLI_DATA
)

SELECT ID,
            CASE
            WHEN GROUPRANK = 1 THEN 'CRITICAL'
            WHEN GROUPRANK = 2 THEN 'HIGH'
            WHEN GROUPRANK = 3 THEN 'MEDIUM'
            ELSE 'LOW'
            END AS COLONY_NAME
FROM COLONYGROUPS
ORDER BY ID;

 

ntile 결과

 

 

# PERCENT_RANK 풀이
with a as(
select ID,PERCENT_RANK() OVER (ORDER BY size_of_colony DESC) per
from ecoli_data
)

select ID,
            case
            when per <=0.25 then "CRITICAL"
            when per <=0.5 then "HIGH"
            when per <=0.75 then "MEDIUM"
            else "LOW"
            end as COLONY_NAME
from a
order by id

percent_rank 결과