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