본문 바로가기
Oracle SQL

그룹 내 분석 함수

by Nowkeeh Ahc

 그룹 내 분석 함수는 지정된 조건의 특정 그룹 내에서 개별 행마다 결과값을 반환하는 함수를 의미한다. 그룹 내 분석 함수는 앞서 살펴본 집계 함수처럼 그룹 단위로 값을 계산하는 것은 동일하지만, 집계함수가 GROUP BY 절에 의해 그룹별로 하나의 결과를 산출하는 반면, 그룹 내 분석 함수는 그룹별이 아닌 그룹 내의 각 행마다 결과를 반환한다는 차이가 있다. 일반적으로 누적 계산, 집계 및 보고용 결과를 질의할 때 유용하게 사용된다.

 그룹 내 분석 함수의 구문의 형태는 다음과 같다.

WINDOW_FUNCTION(매개변수) OVER (PARTITION BY expr1, expr2, …
ORDER BY expr3, expr4, …
WINDOW 절)

 WINDOW_FUNCTION은 분석함수 중에서 WINDOW절을 사용하는 함수를 뜻한다. 구문의 구조를 살펴보면 그룹 내 분석 함수는 적용되는 단위 집합을 정의하는 OVER 절을 추가하여 사용한다. OVER절은 분석함수임을 표시하며 필수로 사용해야 한다. OVER 절 내의 PARTITION BY는 분석 함수로 계산될 대상의 그룹(파티션)을 지정할 때 사용한다. 해당 테이블의 모든 행에 각각 분석 함수가 적용될 수도 있고, PARTITION BY 절에 의해 분석 함수의 적용 대상 집합이 구분되어 적용될 수도 있다.

 예를 들어, 아래 그림과 같이 상품 테이블의 상품카테고리 컬럼의 값으로 의류와 잡화가 존재할 때, ‘PARTITION BY 상품카테고리 ORDER BY 상품정가’ 절을 추가하면, 그룹 내 분석 함수를 적용하고자 하는 집합이 상품카테고리가 되어 의류와 잡화별로 함수가 처리된다.

 WINDOW 절은 분석함수의 대상이 되는 범위를 지정하며 정렬된 값을 기준으로 하기 때문에 ORDER BY 절에 종속적이라고 할 수 있다. 만약 WINDOW절을 지정하지 않아서 default로 실행하게 된다면 ‘range between unbounded preceding and current row’ 가 실행된다. 정렬된 결과의 처음부터 현재 행까지 라는 의미이다.

 WINDOW 절을 자세하게 풀어낸 구문은 다음과 같다.

<BETWEEN 사용>
WINDOW_FUNCTION(매개변수) OVER (PARTITION BY expr1, expr2, …
ORDER BY expr3, expr4, …
{ROWS, RANGE} BETWEEN {UNBOUNDED PRECEDING, CURRENT ROW, value_expr PRECEDING/FOLLOWING} AND {UNBOUNDED FOLLOWING, CURRENT ROW, value_expr PRECEDING/FOLLOWING})
 
<BETWEEN 사용X>
WINDOW_FUNCTION(매개변수) OVER (PARTITION BY expr1, expr2, …
ORDER BY expr3, expr4, …
{ROWS, RANGE} {UNBOUNDED PRECEDING, CURRENT ROW, value_expr PRECEDING})

 범위를 지정할 구분으로 ROWS나 RANGE를 사용한다. ROWS는 행을 기준으로 범위를 지정하며 RANGE는 행이 가지고 있는 값을 기준으로 범위를 지정한다. UNBOUNDED는 전부 지정한다는 의미이고 PRECEDING은 현재를 기준으로 상위 즉, 앞쪽을 의미한다. FOLLOWING은 반대로 하위 즉, 뒤쪽을 의미한다. value_expr은 개수를 지정한다는 의미이다. 만약 ‘2 PRECEDING’ 이라면 위의 두 행까지 범위로 지정한다는 뜻이고, ‘3 FOLLOWING’이라면 아래의 세 행까지 범위로 지정한다는 뜻이다. BETWEEN을 사용하지 않으면 시작범위 또는 종료 범위만 지정하게 되는 것이며 BETWEEN을 사용하면 시작 범위와 종료 범위를 함께 지정할 수 있다.

 WINDOW_FUNCTION에 쓸 수 있는 분석 함수에는 순위함수(RANK, DENSE_RANK, ROW_NUMBER), 집계함수(SUM, MAX, MIN, AVG, COUNT), 행 순서 관련 함수(FIRST_VALUE, LAST_VALUE, LAG, LEAD) 등이 있다.

 

그룹 내 순위 함수

함수 기능
RANK 같은 값에 동일한 순위를 부여하고, 다음 순위는 중복 개수만큼 건너뛰고 순위를 부여한다.
DENSE_RANK 같은 값에 동일한 순위를 부여하고, 다음 순위는 연속적인 순위를 부여한다.
ROW_NUMBER 같은 값이라도 중복없이 순차적인 순번을 반환한다.

ex. 지점상품 테이블에서 지점코드 별로 상품의 보유량이 25 이상인 상품코드에 대해 지점코드, 상품코드, 보유량, 보유량 순위를 출력하여라. 단, 보유량 순위는 RANK, DENSE_RANK, ROW_NUMBER 기능을 각각 이용한다. 

SELECT 지점코드, 상품코드, 보유량,
	RANK() OVER (PARTITION BY 지점코드 ORDER BY 보유량 DESC) "RANK",
	DENSE_RANK() OVER (PARTITION BY 지점코드 ORDER BY 보유량 DESC) "DENSE_RANK",
	ROW_NUMBER() OVER (PARTITION BY 지점코드 ORDER BY 보유량 DESC) "ROW_NUM"
FROM 지점상품
WHERE 보유량 >= 25;

▶ RANK, DENSE_RANK, 그리고 ROW_NUMBER 함수의 인자로써 “PARTITION BY 지점코드 ORDER BY 보유량 DESC” 절을 공통적으로 적용했다. 즉, 지점코드로 파티션을 나눈 후 보유량을 기준으로 내림차순 정렬하고, 그것에 대해 순위를 정하는 코드이다.

 

그룹 내 행 참조 함수

 

FIRST_VALUE와 LAST_VALUE

FIRST_VALUE 함수는 지정한 그룹 내에서 가장 첫 번째 값을, LAST_VALUE는 가장 마지막 값을 반환하는 함수이다. 예제를 통해 그 기능을 살펴보자.

ex. 상품 테이블에서 카테고리가 가전제품인 상품의 상품코드, 상품명, 상품정가, 그리고 FIRST_VALUE, LAST_VALUE 함수를 이용하여 가전제품 상품 중 가장 낮은 상품정가와 가장 높은 상품정가를 출력하라 (가전제품은 상품코드가 4로 시작함). 

SELECT 상품코드, 상품명, 상품정가,
	FIRST_VALUE(상품정가) OVER(ORDER BY 상품정가) "FIRST_VALUE", 
	LAST_VALUE(상품정가) OVER(ORDER BY 상품정가 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) "LAST_VALUE"
FROM 상품
WHERE 상품코드 LIKE '4%';

 

 LAG와 LEAD

 LAG와 LEAD 함수는 동일한 테이블에 있는 다른 행의 값을 참조하는 함수이다. LAG 함수는 현재 행을 기준으로 이전 행의 값을, LEAD 함수는 다음 행의 값을 참조한다. 기본 문법은 다음과 같다.

• LAG(expr, offset, default_value)         * offset = 행의 간격 수
• LEAD(expr, offset, default_value)

 offset은 행의 간격 수를 의미하며 기본값은 1이다. 만약 offset 값을 1로 설정하면 현재 행을 기준으로 각각 바로 이전 행 값과 바로 다음 행 값을 참조하여 결과를 반환하고, offset 값을 2로 설정하면 두 행씩 건너뛰어 결과를 반환한다. LAG와 LEAD 함수를 통해 참조되는 행이 없을 때는 default_value로 명시한 값이 반환되며 기본값은 0이다.

ex.구매 테이블의 구매 내역을 이용하여 지점별로 2016년 3월 한 달간의 일별 매출 추이를 알아보고자 한다. 지점코드별 3월 한 달간의 당일 판매 금액 (일별 총구매액 합계)과 전일 판매 금액, 그리고 판매 금액의 차이를 계산하라.

SELECT 지점코드, 구매일, 
	SUM(총구매가격) "당일 판매 금액",
	LAG(SUM(총구매가격), 1) OVER(PARTITION BY 지점코드 ORDER BY 구매일) "전일 판매 금액",
	SUM(총구매가격)-LAG(SUM(총구매가격), 1) OVER(PARTITION BY 지점코드 ORDER BY 구매일) "판매 금액 차이"
FROM 구매
WHERE 구매일 BETWEEN '2016-03-01' AND '2016-03-31'
GROUP BY 지점코드, 구매일;

 

그룹 내 비율 함수

 

RATIO_TO_REPORT

 RATIO_TO_REPORT 함수는 특정 값이 전체 혹은 주어진 그룹 내에서 차지하는 비율을 구하는 함수이다. 가령, ‘RATIO_TO_REPORT(A)’라고 명시한다면 SUM(A) 값에 대한 A값의 비율을 반환한다.

ex. 구매상품구성 테이블에서 화장품 카테고리에 속하는 상품별로 구매수량과 구매금액의 비중을 구하고자 한다. 이를 위해 화장품 전체 카테고리의 총 구매수량과 총 구매금액을 구하고, 개별 상품들의 구매수량과 구매금액의 비율을 RATIO_TO_REPORT 함수를 이용해서 구하라 (화장품의 상품코드는 3으로 시작).

SELECT 상품코드,
	SUM(SUM(수량)) OVER() "총 구매수량",
	SUM(SUM(소계금액)) OVER() " 총 구매금액", 
	SUM(수량) "구매수량", 
	SUM(소계금액) "구매 금액",
	ROUND((RATIO_TO_REPORT(SUM(수량)) OVER())*100, 2) "수량 대비 비중",
	ROUND((RATIO_TO_REPORT(SUM(소계금액)) OVER())*100, 2) "금액 대비 비중"
FROM 구매상품구성
WHERE 상품코드 LIKE '3%'
GROUP BY 상품코드;

 

CUME_DIST

 CUME_DIST 함수는 그룹별로 특정 값의 상대적인 위치로서 누적분포 값을 반환한다. 어떤 값들이 오름차순으로 정렬되었다면, 특정 값의 CUME_DIST는 해당 값보다 같거나 작은 값을 가진 행의 수가 전체 행의 수에 대해 차지하는 비율로 정의된다. 즉, 값 자체에 대한 누적분포 값이 아니라, 특정 값의 행이 갖는 상대적인 위치를 비율로서 나타낸 것이다. 간단한 예제를 살펴보자.

ex. 상품공급 테이블로부터 공급사별로 화장품 카테고리 상품의 공급수량 순위의 누적분포를 구하고자 한다. 화장품 카테고리 상품의 상품코드가 3으로 시작함을 이용하여 공급사ID, 상품코드, 총 공급수량, 그리고 해당 상품의 총 공급수량이 해당 공급사의 전체 총 공급량 관점에서 차지하는 상대적인 위치를 누적분포 값으로 출력하여라.

SELECT 공급사ID, 상품코드, 
	SUM(공급수량) "총 공급수량",
	TRUNC(CUME_DIST() OVER(PARTITION BY 공급사ID ORDER BY SUM(공급수량)), 2) "CUME_DIST_VALUE"
FROM 상품공급
WHERE 상품코드 LIKE '3%'
GROUP BY 공급사ID, 상품코드;

 

PERCENT_RANK

 PERCENT_RANK 함수는 특정 값의 해당 그룹 내의 백분위 순위를 반환한다. CUME_DIST가 같거나 작은 행의 개수의 비율을 의미한다면, PERCENT_RANK는 해당 행의 순위를 퍼센트로 나타낸다. 따라서, PERCENT_RANK 함수에 적용된 그룹의 첫 번째 행의 PERCENT_RANK 값은 항상 0이된다. 간단한 예제를 통해 CUME_DIST와 PERCENT_RANK 차이를 살펴보자.

ex. 상품공급 테이블에서 의류 카테고리 상품의 공급수량에 따른 순위의 누적분포 값과 순위 퍼센트를 공급사별로 구하여 공급사ID, 상품코드, 총 공급수량과 함께 출력하여라(의류 상품의 상품코드는 4로 시작).

SELECT 공급사ID, 상품코드, 
	SUM(공급수량) "총 공급수량",
	TRUNC(CUME_DIST() OVER(PARTITION BY 공급사ID ORDER BY SUM(공급수량)), 2) "CUME_DIST_VALUE",
	TRUNC(PERCENT_RANK() OVER(PARTITION BY 공급사ID ORDER BY SUM(공급수량)), 2) "PERCENT_RANK_VALUE"
FROM 상품공급
WHERE 상품코드 LIKE '4%'
GROUP BY 공급사ID, 상품코드;

 

NTILE

 NTILE 함수는 인자값으로 명시된 값만큼 분할하고, 분할된 각 행에 번호를 순차적으로 부여한다. 예를 들어 행의 수가 5인 그룹에 대하여 NTILE(5)라고 명시하면 개별 행들은 정렬된 순서에 따라 1~5까지의 숫자를 반환한다. 그러나, 만약 5개의 행에 대해 NTILE(4)라고 지정하면 순서에 따라 처음 두 개의 행이 1, 나머지 행은 각각 2, 3, 4로 반환된다.

ex. 2016년도 구매 내역에서 총구매가격 합계 상위 20% 고객에게 특별한 사은행사를 진행하고자 한다. 고객별 총구매가격 기준으로 전체 고객을 5등급으로 구분하고, 각 등급의 고객에게 1~5등급 순위를 부여하라 (고객ID, 순위, 총구매가격 합계 순서로 출력)

SELECT 고객ID,
	NTILE(5) OVER(ORDER BY SUM(총구매가격) DESC) "RANK", 
	SUM(총구매가격) "총구매가격 합계"
FROM 구매
WHERE TO_CHAR(구매일, 'YYYY') = '2016'
GROUP BY 고객ID;

 

This post was written based on what I read and studied the book below.

http://www.yes24.com/Product/Goods/99002458

저작자표시 비영리 변경금지

댓글

티스토리툴바