[SQLD] 윈도우함수 - 비율함수 (ratio_to_report, percent_rank, cume_dist, ntile)

윈도우함수 종류

순위(RANK) 관련	RANK, DENSE_RANK, ROW_NUMBER	대부분 지원
순서 관련 함수	FIRST_VALUE, LAST_VALUE, LAG, LEAD	ORACLE 만 지원
그룹 내 비율 관련 함수	CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT	PERCENT_RANK 함수는 ANSI/ISO SQL 표준과 Oracle DBMS에서 지원하고 있으며, NTILE 함수는 ANSI/ISO SQL 표준에는 없지만, Oracle, SQL Server에서 지원하고 있다. RATIO_TO_REPORT 함수는 Oracle에서만 지원되는 함수(현업에서 유용).

 

비율함수의 결과값은 모두  0  - 1의 범위를 가지며,  값의 총합은 1이됩니다.

실행을 통해 설명하겠습니다.

-- test1 ddl 
create table test1(
col1 varchar(10),
col2 varchar(10)
);
insert into test1 values('0',null);
insert into test1 values('b','2');
insert into test1 values('c','3');
insert into test1 values('d','3');
insert into test1 values('e','5');
commit;

select * from test1;

현재 test1에 6개의 값을 넣었습니다. 그중 3이 중복됩니다.

 

 

1. Ratio_to_report

전체 SUM(컬럼) 값에 대한 행별 컬럼 값의 백분율을 소수점으로 구할 수 있다. 

반드시 Numeric컬럼을 지정해줘야하며, Sum에 대한 값의 비중을 나타내므로 값이 큰행이 큰 결과치를 가진다.

select col2
       ,round(ratio_to_report(col2) over(),2) as ratio_to_report는_값의비율
 from test1
order by col2 desc
;

 

 

 

2. Percent_rank

기준컬럼이 String,numeric 모두 가능하며, 정렬된 순위에 대한 비율 값을 반환한다.

select col2
       ,percent_rank() over(order by col2) as percent_rank는_정렬된값의위치
 from test1
order by col2
;

 

 

 

3. NTILE 

기준컬럼이 가지는 값의 범위를 N등분하여 해당행이 어떤분면에 위치하는지 정수로 반환해줌 

select col2
       ,ntile(4) over(order by col2) as ntile는_n등분위치
 from test1
order by col2

 

 

4. CUME_DIST

계산 대상 값의 그룹(PARTITION BY에 의해 나누어진 그룹) 별로 각 로우를 ORDER BY 절에 명시된 순서대로 정렬한 뒤,

값의 순위에 대한 상대적인 누적 분포도(Cumulative Distribution)를 반환,

select col2
       ,cume_dist() over(order by col2) as cume_dist는_기준상에위치백분율
 from test1
order by col2
;

 

이 함수는 나온값의 순서가 중요하다, 순서에 따라 누적분포도값이 달라지므로 주의가 필요