02.Oracle/DataBase2012. 9. 21. 06:30
반응형


 

1. Ranking Family

(1) RANK() - 상위 순으로 등수를 부여하는 경우 정렬 결과를 기준으로 전체 순위를 출력

☞사용법

RANK() OVER(

[PRTITION BY < value expression1>] [,...]

ODER BY<value expression2> [collate clause] [ASC:DESC]

[NULLS FIRST:NULLS LAST])

OVER : 순위를 부여하기 위한 대상 집합의 정렬 기준과 분할 기준 정의

PARTITION BY : value expression1을 기준으로 분할, 생랼하면 전체 집합을 대상으로 순위부여

ODER BY : 각 분할내에서 데이터를 정렬하는 기준 칼럼 지정

NULLS FIRST|NULLS LAST : 정렬 결과에서 NULL값의 위치 지정


(2) DENSE_RANK() - RNAK함수의 변형 동일 순위를 무시한 연속 순위를 출력
RNAK함수는 1등이 2건인 경우 다음순위를 3등으로 부여 하지만, DENSE_RANK 함수는 다음순위를 2등으로 부여한다.

질의

SELECT id,score,
rank()over(ORDER BY score ASC)as rank,
dense_rank() over(order by score asc)as dense_rank
From ksdb_score;

결과
ID SCORE RANK DENSE_RANK
--------- ---------- ---------- ----------
200040394 83 1 1
200020182 88 2 2
200231047 89 3 3
200020182 90 4 4
200020183 90 4 4
200020183 92 6 5
200172058 93 7 6
200040394 95 8 7


(3) CUME_DIST()- 그룹 값 내에서 어떤 값의 cumulative distribution(누적분포)을 계산

☞사용법

CUME_DIST(expr)


(4) PERCENT_RANK()-


(5) NTILE() - 출력결과를 사용자가 지정한 그룹 수로 나누어 출력

☞사용법

NITLE(expr) OVER(

[PARTITION BY< value expression1>][,...]

ORDER BY <value espression2> [collate clause] [ASC : DESC]

[NULLS FIRST:NULLS LAST])


질의

SELECT Cid,bday,
NTILE(3) OVER(ORDER BY bday) class
FROM ksdb_customer_info;

결과

CID BDAY CLASS
------ -------- ----------
100004 60/05/02 1
100010 72/08/02 1
100011 74/09/21 1
100006 75/04/05 1
100001 75/07/01 2
100002 77/02/01 2
100007 80/01/04 2
100003 80/01/25 2

100009 81/01/30 3
100005 82/06/01 3
100008 85/04/04 3

11 개의 행이 선택되었습니다.

(6) ROW_NUMBER() - 분할별로 정렬된 결과에 대해 순위를 부여하는 기능 분할은 전체 행을 특정 칼럼을 기준으로 분리하는 기능으로 GROUP BY 절에서 그룹화하는 방법과 같은 개념

☞사용법

ROW_NUMBER() OVER(

[PARTITION BY< value expression1>][,...]

ORDER BY <value espression2> [collate clause] [ASC : DESC]

[NULLS FIRST:NULLS LAST])

질의

SELECT id,score,
RANK()OVER(ORDER BY score ASC)as rank,
DENSE_RANK()OVER(order by score asc)as dense_rank,
ROW_NUMBER()OVER(order by score asc)as row_number
From ksdb_score;

결과
ID SCORE RANK DENSE_RANK ROW_NUMBER
--------- ---------- ---------- ---------- ----------
200040394 83 1 1 1
200020182 88 2 2 2
200231047 89 3 3 3
200020182 90 4 4 4
200020183 90 4 4 5
200020183 92 6 5 6
200172058 93 7 6 7
200040394 95 8 7 8

8 개의 행이 선택되었습니다.

↑RANK나 DENSE_RANK에서는 점수가 같으면 순위가 같게4,4 나오지만 ROW_NUMBER에서는 순서대로 4,5 로 번호를 부여한다.

2. Aggregate Family

(1) SUM(), AVG(), MAX(), MIN()

☞사용법

AVG([DISTINCT|ALL] expr)

SUM([DISTINCT|ALL] expr)


expr의 데이터 타입은 NUMBER 데이터 타입만 가능

(2) COUNT() - 테이블에서 조건을 만족하는 행의 개수를 반환

COUNT(*)는 NULL을 가진 행과 중복되는 행을 모두 포함하는 행의 수를 계산

COUNT(expression)는 NULL을 가진 행을 제외한 행의 수를 계산하여 반환

(3) STDDEV() VARIANCE() - 인수로 지정된 칼럼에 대해 조건을 만족하는 행을 대상으로 표준편차와 분산을 구하는 함수로 숫자 데이터 타입에만 사용할 수 있으며, NULL 은 계산에서 제외된다.

(4)RATIO_TO_REPORT()

3. Lead/Lag Family

LEAD() LAG() - 동일한 테이블에 있는 다른 행의 값을 참조하기 위한 함수

LAG 분석함수는 현재 행을 기준으로 이전값을 참조

LEAD 분석함수는 현재 행을 기준으로 이후값을 참조

LEAD LAG 분석함수에서 지정하는 인수는 현재행을 기준으로 몇 번째 행을 참조할 것인지를 지정

음수는 사용할 수 없다.

☞사용법

ROW_NUMBER() OVER(

[PARTITION BY< value expression1>][,...]

ORDER BY <value espression2> [collate clause] [ASC : DESC]

[NULLS FIRST:NULLS LAST])


질의

SELECT id,score,
LEAD(score, 1)OVER(ORDER BY score)as next_score,
LAG(score,1)OVER(order by score)as prev_score
From ksdb_score;

결과

ID SCORE NEXT_SCORE PREV_SCORE
--------- ---------- ---------- ----------
200040394 83 88
200020182 88 89 83 ←88 이후 점수 : 89
200231047 89 90 88 88 이전 점수 : 83
200020182 90 90 89
200020183 90 92 90
200020183 92 93 90
200172058 93 95 92
200040394 95 93

8 개의 행이 선택되었습니다.

[출처] 분석함수|작성자 은영

Posted by 1010