database.sarang.net
UserID
Passwd
Database
ㆍDBMS
MySQL
PostgreSQL
Firebird
Oracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
DBMS Columns 445 게시물 읽기
 News | Q&A | Columns | Tutorials | Devel | Files | Links
No. 445
제2회 Analytic Function의 소개 및 활용사례
작성자
정재익(advance)
작성일
2002-07-12 23:06
조회수
7,781
첨부파일: 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)
[Top]
No.
제목
작성자
작성일
조회
477Storing XML in Databases
정재익
2002-07-30
3992
447제4회 Lag/Lead family에 대한 소개 및 활용 사례
정재익
2002-07-13
4870
446제3회 Aggregate Family(Reporting)의 소개 및 활용사례
정재익
2002-07-12
4588
445제2회 Analytic Function의 소개 및 활용사례
정재익
2002-07-12
7781
444제1회 Analytic Function의 소개
정재익
2002-07-12
4815
443이력관리 Data Model - 선분이력 [1]
정재익
2002-07-12
7508
442RDB 최적활용 기본 테크닉 - 옵티마이징 전략
정재익
2002-07-12
4225
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2021 DSN, All rights reserved.
작업시간: 0.010초, 이곳 서비스는
	PostgreSQL v13.1으로 자료를 관리합니다