고정 헤더 영역
상세 컨텐츠
본문
첨부파일: anal2-Pictures.zip (90,547bytes)
이번 회에서는 지난 회에 연재된 Analytic Function 또는 OLAP function의 한 종류인 Ranking Family의 소개 및 활용사례를 다루고자 한다. 활용 사례는 Ranking Family를 위주로 하여 추후로 소개될 예정인 다른 Analytic Function들과 함께 사용되어 실무에서 쉽게 응용할 수 있는 것들을 위주로 하고 있다
1. Ranking Family의 특징
Ranking Family에 속하는 Analytic Function 은 대상 집합에 대하여 특정 컬럼(들)을 기준으로 순위나 등급을 부여하는 것으로 다음과 같은 특징을 지니고 있다.
오름차순 또는 내림차순으로 순위나 등급을 부여할 수 있다
오름차순 내림차순과 관계없이 NULL은 순위의 가장 처음 또는 마지막으로 강제 처리 가능하다.
Rank functions은 각 파티션마다 초기화된다.
순위 또는 등급은 GROUP BY CUBE와 ROLLUP 절마다 초기화된다.
2. Ranking Family의 종류
1) RANK()
RANK 함수는 각 로우마다 순위를 매겨주는 함수로 각 PARTITION 내에서 ORDER BY절에 명시된 대로 정렬한 후의 순위를 의미하고 1부터 시작하여 동일한 값은 동일한 순위를 가지며, 동일한 순위의 수만큼 다음 순위는 건너뛴다.
[그림 1]은 "판매실적에 의한 제품의 순위를 각 지역별과 전체 지역에서 각각 부여하라"는 RANK()함수의 활용한 예로 GROUP BY절과 RANK()함수가 같이 사용되었다
SELECT r_regionkey, p_productkey, sum(s_amount),
RANK() OVER (PARTITION BY r_regionkey ORDER BY sum(s_amount) DESC)
AS rank_of_product_per_region,
RANK() OVER (ORDER BY sum(s_amount) DESC)
AS rank_of_product_total
FROM product, region, sales
WHERE r_regionkey = s_regionkey AND p_productkey = s_productkey
GROUP BY r_regionkey, p_productkey;
[예제 1] RANK() 활용
[그림 1]에서 동일 영업실적에 대해서는 동일한 순위를 부여하고 있고 다음 영업실적은 동일 순위의 수만큼을 건너 뛴 순위가 부여되었음을 확인할 수 있다.
2) DENSE_RANK()
DENSE_RANK()는 Rank()와 유사한 함수로ORDER BY절에 사용된 컬럼이나 표현식에 대하여 순위를 부여하는데 RANK()와 달리 동일 순위 다음의 순위는 동일 순위의 수와 상관없이 1 증가된 값을 돌려준다.
[그림 2]는 RANK()와 DENSE_RANK()가 어떻게 다른 것인지를 보여주고 있다.
[그림 2] RANK()와 DENSE_RANK()의 비교
3) CUME_DIST() : Cumulative Distribution Function
PARTITION BY에 의해 나누어진 그룹별로 각 row를 ORDER BY절에 명시된 순서로 정렬한 후 그룹별 상대적인 위치(누적된 분산정도)를 구한다. 상대적인 위치는 구하고자 하는 값보다 작거나 같은 값을 가진 ROW수를 그룹내 총 ROW 수로 나눈 것을 의미하며 결과 값의 범위는 0보다 크고 1보다 작거나 같다.
SELECT r_regionkey, p_productkey, SUM(s_amount) as s_amount,
CUME_DIST() OVER (PARTITION BY r_regionkey ORDER BY SUM(s_amount))
AS cume_dist_per_region
FROM region, product, sales
WHERE r_regionkey = s_regionkey AND p_productkey = s_productkey
GROUP BY r_regionkey, p_productkey ORDER BY r_regionkey, s_amount DESC;
[그림 3] CUME_DIST()의 활용
4) PERCENT_RANK()
CUME_DIST와 유사한 함수이나 PARTITION별 각 row의 순위 -1 / PARTITION내의 ROW의 수를 결과값으로 하며, 결과값 범위는 0 <= 결과값 <= 1이고 집합의 첫번째 row의 PERCENT_RANK는 항상 0이 된다.
[그림 4] CUME_DIST()와 PERCENT_RANK의 비교
5) NTILE()
NTILE()함수는 정렬된 PARTITION을BUCKET이라 불리는 그룹별로 나누고 PARTITION내의 각 ROW등을 BUCKET에 배치하는 함수로 각 BECKET에는 동일한 수의 ROW가 배치된다. 예를 들어 PARTITION내에 100개의 ROW를 가지고 있고 4개의 BUCKET으로 나누는 NTILE(4)를 사용하면 1개의 BUCKET당 25개의 ROW가 배정된다. 만일 각 PARTION의 수가 정확하게 분배되지 않을 경우 근사치로 배분한 후 남는 값에 대하여 최초 PARTITION부터 한 개씩 배분한다. 즉, 만일 103개의 ROW에 대하여 NTILE(5)를 적용하면 첫번째 BUCKET부터 세번째까지는 21개의 ROW가, 나머지는 20개의 ROW가 배치된다.
[그림 5]는 "제품별 판매량을 구하고, 이를 4등급으로 나누어, 다시 등급 내에서 순위를 매겨라"는 질의를 NTILE()함수를 이용하여 구현한 것이다.
SELECT p_productkey, sum(s_amount) AS sum_s_amount,
NTILE(4) over (ORDER BY sum(s_amount) DESC) as 4_tile,
RANK() OVER (PARTITION BY NTILE(4) over
(ORDER BY sum(s_amount) DESC) ORDER BY sum_s_amount DESC)
AS rank_in_quartile
FROM product, sales
WHERE p_productkey = s_productkey
GROUP BY p_productkey);
[그림 4-5] NTILE() 활용
6) ROW_NUMBER()
ROW_NUMBER()는 각 PARTITION내에서 ORDER BY절에 의해 정렬된 순서로 유일한 값을 돌려주는 함수로 ROWNUM과는 관계가 없다. [그림 6]은 "제품별 판매량의 내림차순으로 unique한 일련번호를 부여하라"는 질의에 ROW_NUMBER()를 활용한 예이다.
[그림 6] ROW_NUMBER() 활용
3. Ranking Family의 활용 사례
이 절에서는 위에서 소개된 Ranking Family Analytic Function의 실제업무에 이용가능한 활용사례에 대해 살펴보고자 한다. 아래에 소개된 예들은 ORACLE 8i Version을 중심으로 작성되었으나 UNIX Version의 IBM UDB 7에서도 OLAP Function이라하여 동일한 문법의 함수를 지원하므로 사용가능할 것으로 보여진다. 다만 DBMS별로 지원하는 함수의 종류에는 차이가 있으면 RANK 계열 함수의 경우 UDB 7의 경우 RANK, DENSE_RANK, ROW_NUMBER가 지원됨을 확인하였다. 다른 DBMS에서도 Analytic Function이 SQL 1999의 표준으로 채택된 만큼 이미 사용 가능하거나 아닐지라도 가까운 시일내에 지원되리라고 보여진다.
1) 달력상의 주별로 순번을 부여하는 SQL을 작성하시오. (ROW_NUMBER활용사례 1)
아래 그림과 같은 결과가 나오도록 SQL을 작성하시오. 여기에서 주의할 점은 달(월)이 바뀐 경우이다. 즉 달력상의 1 주(일 ~ 월)를 하나의 Grouping 으로 해야 한다. 또한 달이 바뀔 경우에는 이를 무시하고 다시 순번을 시작해야 한다.
일자 순번 일자 순번
20020101 1 20020201 1
20020104 2 20020203 1
20020105 3 20020207 2
20020106 1 20020209 3
20020107 2 20020210 1
20020111 3 20020211 2
20020112 4 20020221 1
20020113 1 20020223 2
20020114 2 20020224 1
20020117 3 20020225 2
SQL) SELECT 일자,
ROW_NUMBER() OVER (PARTITION BY substr(일자,1,6)||
to_char(to_date(일자,'yyyymmdd') -
to_char(to_date(일자,'yyyymmdd'),'d') + 1,'yyyymmdd')
ORDER BY 일자) 순번
FROM SAMPLE1;
2. 아래 그림의 월별 급여 집계테이블에서 2001년 1월의 각 부서별 급여액순으로 상위 2위에 해당 하는 사원 1명의 이름, 급여액을 구하는 SQL을 작성하시오. 단, 급여액이 동일할 경우 성명 순으로 한다.( 즉, 동률의 급여금액이 존재하도라도 부서별 급여금액 역순, 이름순으로 정렬하여 그 순서에 따른 2위(두번째) 를 의미) (ROW_NUMBER활용사례 2)
[rpe]
[월별 급여집계 테이블]
부서 성 명 년월 급여액
총무부 홍길동 200101 200
총무부 임꺽정 200101 250
총무부 장길산 200101 100
총무부 최고봉 200101 200
총무부 황비홍 200101 120
영업부 궁해 200101 300
영업부 왕곤 200101 310
영업부 견온 200101 220
생산부 박임자 200101 350
생산부 전본인 200101 300
생산부 노친구 200101 300
생산부 김갱제 200101 230
[결과 집합]
부 서 성명 급여액
총무부 최고봉 200
영업부 궁해 300
생산부 노친구 300
SQL) SELECT 부서, 성명, 급여액
FROM (SELECT 부서, 성명, 급여액,
ROW_NUMBER() OVER (PARTITION BY 부서
ORDER BY 급여액 DESC, 성명) SEQ
FROM 월별급여테이블
WHERE 년월 = '200101' )
WHERE SEQ = 2;
[/pre]
※ 이 경우 Analytic Function중 RANK 또는 DENSE_RANK를 사용하면 문제에서 원하는 금액역순 + 성명의 순서중 2번째를 구하는데 1위에 해당하는 동률이 있을 경우 답이 틀리게 됩니다.
3. 아래 테이블의 데이터를 참조하여 결과집합을 구하는 SQL을 작성하시오. CO001T는 코드 테이블로 하나의 MAJOR 코드에 대하여 최대 3개의 MINOR 코드를 가질 수 있다.우리는 보고자 하는 것은 각 MAJOR 코드에 대하여 MINOR 코드 를 가로로 정렬하여 보고자 한다. 이때 MINOR 코드가 3개가 아닌 경우에 나머지는 공백으로 둔다. (RANK 활용사례 1)
[코드 테이블인 CO001T의 데이터 구조 ]
Major Minor
영업 가마니
영업 나오미
영업 마동탁
물류 사시미
물류 아사달
회계 자몽돌
회계 차이나
회계 카이로
전산 파김치
∴ 결과 집합
MAJOR MINOR1 MINOR2 MINOR3
물류 사시미 아사달
영업 가마니 나오미 마동탁
전산 파김치
회계 자몽돌 차이나 카이로
SQL) SELECT t1.major,
max(decode(no, 1, minor)) minor1,
max(decode(no, 2, minor)) minor2,
max(decode(no, 3, minor)) minor3
FROM (
SELECT major,minor,
RANK() OVER (PARTITION BY major ORDER BY minor ) as no
FROM co001t
GROUP BY major,minor
) t1
GROUP BY t1.major
4. 두 개의 상품이력 테이블을 선분 BETWEEN 조인과 데이터의 복제 방법을 활용하여 아래의 결과 집합 을 구하는 SQL을 작성하시오.
(주의사항: 결과집합에는 가입계약별로 할인전화번호가 반드시 3개가 존재해야만 한다. 따라서 상품이력에는 존재하고 상품 별 할인전화번호가 없는 경우는 계약별 상품이력에 있는 전화번 호로 3개의 전화번호를 생성 해야만 한다.) (ROW_NUMBER 활용사례 3)
- 계약번호별 상품이력
계약번호 상품코드 전화번호 사용시작일 사용종료일
200100001 FAMILY01 01201111212 20010107 20010502
200100002 FAMILY02 01201112020 20010301 20010707
200100003 COUPLE01 01208711212 20010601 99991231
200100002 FAMILY01 01208721212 20010707 99991231
200100001 COUPLE02 01208731212 20010502 99991231
- 상품별 할인전화번호 이력
계약번호 등록전화번호 사용시작일 사용종료일
200100001 00022341212 20010107 99991231
200100001 01190661868 20010107 99991231
200100001 01608711313 20010107 20010502
200100001 00024527979 20010502 99991231
200100002 00025441868 20010301 99991231
200100002 01720661868 20010301 99991231
200100002 01608713313 20010301 99991231
200100003 00322340101 20010601 99991231
200100003 01198705868 20010601 99991231
∴ 결과 집합
계약번호 상품명 순번 등록전화번호 사용시작일 사용종료일
200100001 FAMILY01 1 00022341212 20010107 20010502
200100001 FAMILY01 2 01190661868 20010107 20010502
200100001 FAMILY01 3 01608711313 20010107 20010502
200100001 COUPLE02 1 00024527979 20010502 99991231
200100001 COUPLE02 2 01190661868 20010502 99991231
200100001 COUPLE02 3 00024527979 20010502 99991231
200100002 FAMILY02 1 00025441868 20010301 20010707
200100002 FAMILY02 2 01720661868 20010301 20010707
200100002 FAMILY02 3 01608713313 20010301 20010707
200100002 FAMILY01 1 00025441868 20010107 99991231
200100002 FAMILY01 2 01720661868 20010107 99991231
200100002 FAMILY01 3 01608713313 20010107 99991231
200100003 COUPLE01 1 00322340101 20010601 99991231
200100003 COUPLE01 2 01198705868 20010601 99991231
200100003 COUPLE01 3 01208711212 20010601 99991231
SQL) SELECT 계약번호, 상품번호,
순번+NO-1 순번,
decode(sign(1-no),-1,전화번호,등록전화번호) 등록전화번호,
GREATEST(X_SDATE, Y_SDATE) 사용시작일,
LEAST(X_EDATE, Y_EDATE) 사용종료일
FROM (SELECT x,계약번호, x.상품번호, x.전화번호,
row_number() over (partition by
x.계약번호,x.상품명 order by x.등록전화번호)
As 순번,
count(*) over (partition by x.계약번호,x.상품명 order by x.등록전화번호) As c_no,
nvl(y.등록전화번호,x.전화번호) 등록전화번호,
x.사용시작일 X_SDATE, x.사용종료일 X_EDATE,
nvl(y.사용시작일,'10000101') Y_SDATE,
nvl(y.사용종료일,'99991231') Y_EDATE
FROM 계약별상품이력 x, 상품별할인전화번호이력 y
WHERE y.계약번호(+) =x.계약번호
AND y.시작일(+) <=x.종료일 --- 선분 Between 조인
AND y.종료일(+) > x.시작일 --- 선분이력이 양편 넣기일 때 ) a,
copy_t b --- 복제용 Dummy Table
WHERE no <= decode(c_no||순번,'11',3,'22',2,'21',1,'31',1','32',1,'33',1,0)
