[Oracle] 통계 함수

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 개의 행이 선택되었습니다.  

 

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