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

문제

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

프로그래머스

 

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

풀이

 

대장균 개체의 크기를 내림 차순으로 정렬해야 함

상위 퍼센트 계산을 해야하는데 전체 값을 구하는 방법을 모르겠음

처음에 개체의 크기에 초점을 맞춰서 헤매었음.

 

개체의 크기를 각 행 마다 순위를 매기기 위해 rank() over(partition by 조건 또는 order by 조건)을 사용해야 했다.

그리고 전체 데이터의 개수를 각 행마다 부여해준 뒤 상위 퍼센트 계산을 진행할 것이다.

우선 위 까지 구하는 쿼리를 보면

select *,
rank() over (order by size_of_colony desc) as size_rank, 
count(*) over () as total_count
from ecoli_data

 

이 결과를 하나의 테이블로 잡음

 

그 다음 이 테이블을 가지고 상위 퍼센트에 따라 행마다 분류를 진행

 

상위 퍼센트 계산은 대상의 값 / 전체 데이터 값 * 100 으로 퍼센트를 구할 수 있다.

정답코드

select id, 
  case
    when E.size_rank / E.total_count * 100 <= 25 then 'CRITICAL'
    when E.size_rank / E.total_count * 100 <= 50 then 'HIGH'
    when E.size_rank / E.total_count * 100 <= 75 then 'MEDIUM'
    else 'LOW'
   end as `COLONY_NAME`
  from (select *, rank() over (order by size_of_colony desc) as size_rank, count(*) over () as total_count
          from ecoli_data) as E
 order by id