정보처리기사 PART 8 정리 - 윈도함수

윈도함수

 

윈도함수는 데이터베이스를 온라인에서 분석 처리용으로 사용하기 위해 만들어진 함수입니다. 다차원에서 데이터 분석을 할수 있도록 도와준다하여 OLAP(OnLine Analytical Processing)함수라고도 불립니다.

 

SELECT 컬럼, 함수명(파라미터) OVER ([PARTITION BY 컬럼])

[ORDER BY 컬럼] 

FROM 테이블명

 

윈도 함수는 SELECT 뒤에 사용되며 위에서 나온 PARTITION BY는 범위를 지정하는 쿼리로 선택항목입니다.

 

윈도함수는 순위함수, 행순서 함수, 그룹내 비율함수가 있습니다. 

 

순위함수

순위함수는 레코드 값을 계산하는 함수입니다.

 

- RANK : 특정 컬럼에 대한 순위를 구하는 함수로 동일 레코드가 존재하면 동일 순위를 준 뒤 다음값이 후순위로 넘어갑니다.

만약 4명 중 2등이 2명이면 1등 , 2등, 2등, 4등 이렇게 순위가 매겨집니다.

 

- DENSE_RANK : 특정 컬럼에 대한 순위를 구하는 함수로 동일 레코드가 존재하면 동일 순위를 준 뒤 RANK와는 달리 다음 값이 후순위로 넘어가지 않습니다.

만약 4명 중 2등이 2명이면 1등 , 2등, 2등, 3등 이렇게 순위가 매겨집니다.

 

- ROW_NUMBER : 특정 컬럼에 대한 순위를 구하는 함수로 동일 레코드가 존재해도 연속값을 부여합니다.

만약 4명 중 2등이 2명이면 1등 , 2등, 3등, 4등 이렇게 순위가 매겨집니다.

 

예제로 사용할 SW연봉 테이블은 다음과 같습니다.

이름	연봉
김에이	5000
박비	6000
이씨	5000
한디	4000

 

SELECT 이름, 연봉,

          RANK( ) OVER (ORDER BY 연봉 DESC) RANK,

          DENSE_RANK( ) OVER (ORDER BY 연봉 DESC) DENSE_RANK,

          ROW_NUMBER( ) OVER (ORDER BY 연봉 DESC) ROW_NUMBER, 

FROM SW연봉

ORDER BY 연봉 DESC;

 

이름	연봉	RANK	DENSE_RANK	ROW_NUMBER
박비	6000	1	1	1
김에이	5000	2	2	2
이씨	5000	2	2	3
한디	4000	4	3	4

 

행순서 함수

행순서 함수는 레코드 내에서 가장 먼저나오거나 가장 뒤에 나오는 값 또는 이후에 나올 값과 이전에 나온 값 등을 계산하는 함수입니다.

 

- FIRST_VALUE : 윈도에서 가장 먼저 나오는 값을 찾습니다.

 

- LAST_VALUE : 윈도에서 가장 나중에 나오는 값을 찾습니다.

 

- LAG : 윈도에서 이전의 값을 반환합니다.

 

- LEAD : 윈도에서 이후의 값을 반환합니다.

 

예제로 사용할 SW연봉 테이블은 아까와 같습니다.

이름	연봉
김에이	5000
박비	6000
이씨	5000
한디	4000

 

SELECT 이름, 연봉,

          FIRST_VALUE(이름) OVER (ORDER BY 연봉 DESC) FIRST_VALUE,

          LAST_VALUE(이름) OVER (ORDER BY 연봉 DESC) LAST_VALUE,

          LAG(이름) OVER (ORDER BY 연봉 DESC) LAG,

          LEAD(이름) OVER (ORDER BY 연봉 DESC) LEAD, 

FROM SW연봉

ORDER BY 연봉 DESC;

 

이름	연봉	FIRST_VALUE	LAST_VALUE	LAG	LEAD
박비	6000	박비	한디		김에이
김에이	5000	박비	한디	박비	이씨
이씨	5000	박비	한디	김에이	한디
한디	4000	박비	한디	한디

 

그룹 내 비율 함수

그룹 내 비율 함수는 백분율을 보여주는 등 통계를 보여주는 함수입니다.

 

- RATIO_TO_REPORT : 주어진 그룹에 대한 합을 기준으로 하여 행 값의 상대적인 비율을 반환하는 함수이며 총합은 1이 됩니다. 각 행의 결과 값은 0에서 1사이의 값이 됩니다.

 

- PERCENT_RANK : 주어진 결과 값에서 가장 먼저 나오는 값을 0, 가장 늦게 나오는 값을 1로하여 행의 순서를 구하는 함수입니다. 이 함수도 결과 값이 0에서 1사이가 나옵니다.

 

SW연봉 테이블을 가지고 그룹내 비율 함수를 알아봅시다.

이름	연봉
김에이	5000
박비	6000
이씨	5000
한디	4000

 

SELECT 이름, 연봉,

          RATIO_TO_REPORT( ) OVER (ORDER BY 연봉 DESC) RATIO_TO_REPORT,

          PERCENT_RANK( ) OVER (ORDER BY 연봉 DESC) PERCENT_RANK,

FROM SW연봉

ORDER BY 연봉 DESC;

 

이름	연봉	RATIO_TO_REPORT	PERCENT_RANK
박비	6000	0.3	0
김에이	5000	0.25	0.25
이씨	5000	0.25	0.25
한디	4000	0.2	1

→ 박비의 RATIO_TO_REPORT는 박비의 연봉인 6000을 총 연봉인 20000으로 나눈 값(6000/20000=0.3)이 됩니다. 그리고 PERCENT_RANK의 경우 후순위로 넘어가는 RANK를 기준으로 순서별 백분위를 구하게 됩니다.