SQL Fundamental7 ( Analytic SQL + 순위 함수)

Analytic SQL이란?

RDBMS가 Analytic SQL을 사용하게 됨으로써 다시 데이터 분석의 중심이 될 수 있던 기능이다. 기존 Group by 함수는 원본 데이터 집합의 레벨을 변경하여 적용함에 비해, Analytic SQL은 원본 데이터 집합의 레벨을 그대로 유지하면서 집계 함수를 적용함

• 일반 Aggregation Function은 원본 데이터의 레벨을 변경하여 적용
• Analytic SQL은 window를 사용하여 Row단위의 집합에 대한 연산이 가능하다.

Analytic SQL의 유형

원본 데이터의 레벨을 그대로 유지하면서 그룹핑 레벨에서 자유롭게 Window의 이동과 크기를 조절하면서 Analytic 수행.

순위 Analytic SQL

• 일반적인 순위: rank, dense_rank, row_number
• 0~1 사이 정규화 순위: cume_dist, percent_rank
• 분위: ntile

일반적인 순위

select a.empno, ename, job, sal 
	, rank() over(order by sal desc) as rank --rank
	, dense_rank() over(order by sal desc) as dense_rank --dense_rank
	, row_number() over (order by sal desc) as row_number from hr.emp a; --row_number

위의 코드는 partition을 정하지 않았기 때문에 전체 데이터에서 순위를 매긴다. 결과는 다음과 같다.

row_number는 모든 순위가 unique해야 하고, rank는 공통 부분을 제외한 갯수만큼 순위가 밀려나도록 계산. dense_rank는 공통 부분 바로 다음 순위로 계산한다.

select a.empno, ename, job, deptno, sal 
, rank() over(partition by deptno order by sal desc) as rank 
, dense_rank() over(partition by deptno order by sal desc) as dense_rank
, row_number() over (partition by deptno order by sal desc) as row_number from hr.emp a;

위의 코드는 deptno로 partition을 지정하였다. 그러면 deptno가 같은 데이터끼리 순위를 매기게 된다. 결과는 다음과 같다.

---

부서 별 가장 급여가 높은 직원 정보

--서브쿼리문 사용. row_number() 사용
select *
from ( 
select a.ename
		, a.deptno
		, b.dname
		, a.sal
		, row_number() over(partition by a.deptno order by a.sal desc) as sal_rank
from emp a 
	join dept b on a.deptno = b.deptno  ) as c 
	where sal_rank = 1

회사 내 커미션 높은 순위. (null 처리)

select *
	, rank() over (order by comm desc) as comm_rank
	, row_number() over (order by comm desc) as comm_rank
from emp

null값이 존재하는 comm에 rank()와 row_number()를 적용하면 다음가 같이 null값에 대한 row들이 우선 순위로 계산된다. 왜냐하면 order by를 적용할 때 nulls first 라는 파라미터가 default이기 때문이다. null값이 우선이 된다는 뜻이다. 즉 위의 코드는 다음 코드와 같다.

select *
	, rank() over (order by comm desc nulls first) as comm_rank
	, row_number() over (order by comm desc nulls first) as comm_rank
from emp

이러한 null들을 후순위로 사용하고 싶다면 nulls first가 아닌 nulls last를 적용하면 된다.

• nulls first: null을 최우선 순위로
• nulls last: null을 마지막 순위로

위의 문제를 nulls last로 적용해서 실행한 결과는 다음과 같다.

select *
	, rank() over (order by comm desc nulls last) as comm_rank
	, row_number() over (order by comm desc nulls last) as comm_rank
from emp

또는 coalesce를 사용하여 null값을 다른 값으로 대체해도 된다.

다음은 comm값이 null값이면 0으로 대체 후 순위를 매기는 함수이다.

select *
	, rank() over (order by coalesce(comm, 0) as comm_rank
	, row_number() over (order by coalesce(comm, 0) as comm_rank
from emp