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

• 오답

with percent as (
select  id,percent_rank() over (order by size_of_colony desc) as size
from ecoli_data)
select ID,
       case when size >=0 and size<=0.25 then 'CRITICAL'
            when size >=0.26 and size<=0.50 then 'HIGH'
            when size >=0.51 and size<=0.75 then 'MEDIUM'
            else 'LOW' end as COlONY_NAME
from percent
order by id

범위를 잘 봐야함.
case when size >=0 and size<=0.25 then 'CRITICAL'
when size >0.25 and size<=0.50 then 'HIGH'
when size >0.5 and size<=0.75 then 'MEDIUM'
else 'LOW' end as COlONY_NAME
from percent

• 정답

select A.ID,
case
    when A.PER <= 0.25 then 'CRITICAL'
    when A.PER <= 0.5 then 'HIGH'
    when A.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 A
order by A.ID

rank(), dense_rank(), percent_rank()

rank() over (order by size_of_colony desc)
percent_rank() over (order by size_of_colony desc)

• rank(): 동점자있으면 순위 1 2 2 4
• dense_rank() : 동점자있어도 순위 1 2 3 4
• percent_rank() : 현재 행 값에 대해 0~1 사이의 상대값을 리턴

** 찍어본 예시코드