[SQL] 프로그래머스 - 대장균의 크기에 따라 분류하기 2 (feat. 순위 관련 윈도우 함수)
intro.
SQL문제를 풀다가 백분율에 관련된 문제를 만났다.
고민을 하다 해결하지 못해, 검색을 해봤더니 순위 관련 윈도우 함수를 사용하면 해결할 수 있다는 것을 알았다.
평소 자주쓰지 않아서 잊고 살았던, 새롭게 알게된 순위 관련 윈도우 함수를 정리해보고
문제에 대한 접근도 정리해보려 한다.
윈도우 함수란?
행과 행 간의 관계를 쉽게 정의, 비교, 계산하기 위해 만든 함수이다.
쉽게 말해 SUM(), MAX()는 컬럼 단위의 계산이고, 행 단위의 비교 및 계산을 할 수 있는 것이 윈도우 함수라고 생각하면 된다.
윈도우 함수에는 순위, 백분위, 집계, 특정 행 앞 뒤로 참조 가능한 함수 등이 있다.
순위 기반 윈도우 함수
데이터의 순위를 매기는 함수이다.
| 함수 | 설명 |
| RANK() | 중복값 존재 시 같은 순위, 다음 순위 건너뜀 |
| DENSE_RANK() | 중복값 존재 시 같은 순위, 다음 순위 건너뛰지 않음 |
| ROW_NUMBER() | 중복값 없이 행마다 고유한 순위 부여 |
| PERCENT_RANK() | 백분율 순위 |
| CUME_DIST() | 누적 분포 백분율 |
표에서 아래 두 개의 함수에 대해 비교를 해보자면,
PERCENT_RANK()는 순위에 대한 백분율이고 , CUME_DIST()는 현재 값 이하의 값들을 누적하여 백분율을 계산한다.
예를 들어 학생 ID와 학생에 대한 점수가 있는 테이블이 있다고 해보자.
각각의 학생이 상대적으로 상위 몇 퍼센트인지 궁금할 때는 PERCENT_RANK()를 사용하고
특정 점수 이하의 학생들이 전체 몇 퍼센트를 차지하고 있는지 궁금할 때는 CUME_DIST()를 사용하는 것 같다.
이제 문제를 살펴 보자.
문제 링크
https://school.programmers.co.kr/learn/courses/30/lessons/301649
프로그래머스
SW개발자를 위한 평가, 교육, 채용까지 Total Solution을 제공하는 개발자 성장을 위한 베이스캠프
programmers.co.kr
💡 해결
문제의 핵심은 상위 0%~ 25%, 상위 26%~50%, ... 이렇게 상위 퍼센트의 구간별로 구분값을 적용하는 것이다.
이 경우 행과 행간의 계산을 해야하기 때문에 윈도우 함수를 사용해야하고
그 중 백분율과 관련된 함수를 사용해야 한다.
각각의 대장균의 상대적 순위에 대한 퍼센트를 구한 후 문제에 정의해놓은 구간에 맞게 구분값을 설정해야 함으로
PERCENT_RANK()를 활용하는 것이 좋겠다.
select
e.ID as ID,
(case when e.percent <= 0.25 then 'CRITICAL'
when e.percent <= 0.5 then 'HIGH'
when e.percent <= 0.75 then 'MEDIUM'
else 'LOW'
end) as COLONY_NAME
from
(select
ID,
percent_rank() over (order by SIZE_OF_COLONY desc) as percent
from
ECOLI_DATA) e
order by
e.ID asc;ref.
https://schatz37.tistory.com/12 (윈도우 함수 개념을 잘 설명해주는 글이 있음)