[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

프로그래머스

 

---

💡 해결

문제의 핵심은 상위 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 (윈도우 함수 개념을 잘 설명해주는 글이 있음)

https://velog.io/@lookin_min/%ED%94%84%EB%A1%9C%EA%B7%B8%EB%9E%98%EB%A8%B8%EC%8A%A4-%EB%8C%80%EC%9E%A5%EA%B7%A0%EC%9D%98-%ED%81%AC%EA%B8%B0%EC%97%90-%EB%94%B0%EB%9D%BC-%EB%B6%84%EB%A5%98%ED%95%98%EA%B8%B0-2MySQL (프로그래머스 문제 해답 참고)