# 오라클 8.1.6버전이상에서 제공되는 분석용 함수
이건 모든 DB
| 구분 | 종류 | 종류 |
| 순위(RANK) 관련 | RANK, DENSE_RANK, ROW_NUMBER | 대부분 지원 |
| 집계(AGGREGATE) 관련 | SUM, MAX, MIN, AVG, COUNT | SQL Server 경우 Over절 내 Orderby 지원 못함 |
| 순서 관련 함수 | FIRST_VALUE, LAST_VALUE, LAG, LEAD | ORACLE 만 지원 |
| 그룹 내 비율 관련 함수 | CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT | PERCENT_RANK 함수는 ANSI/ISO SQL 표준과 Oracle DBMS에서 지원하고 있으며, NTILE 함수는 ANSI/ISO SQL 표준에는 없지만, Oracle, SQL Server에서 지원하고 있다. RATIO_TO_REPORT 함수는 Oracle에서만 지원되는 함수(현업에서 유용). |
| 선형분석을 포함한 통계분석 함수 | CORR, COVAR_POP, COVAR_SAMP, STDDEV, STDDEV_POP, STDDEV_SAMP, VARIANCE, VAR_POP, VAR_SAMP, REGR_(LINEAR REGRESSION), REGR_SLOPE, REGR_INTERCEPT, REGR_COUNT, REGR_R2, REGR_AVGX, REGR_AVGY, REGR_SXX, REGR_SYY, REGR_SXY | 특화되어있으므로 생략 |
# Analytic Function ( 분석함수 ) - 분석함수,순위함수로도 알려져있는 윈도우함수란다...
: 분석함수는 그룹을 기반으로 계산하여 각 그룹에 대해 여러행을 반환할수 있는 함수를 말함
일반적으로 누적계산, 집계 및 보고용 결과를 질의 할때 유용하게 사용
분석함수는 원하는 결과를 가져다 주는 SQL을 보다 쉽게 만들수 있도록 도와주며 성능역시 향상시켜줌!!
오름차순 ascending = asc 낮은거에서 높은거로
내림차순 descending = desc 높은거에서 낮은거로
(order by 의 Default가 오름차순(asc)임)
:정의
그룹함수 + OVER절 +
OVER절은 행그룹의 정의를 지정
행들의 집합은 PARTITION BY , ORDER BY , WINDOWING 을 통해 조절됨
분석함수는 JOIN, WHERE, GROUP BY, HAVING등과 함께 쓰일 때 가장 마지막에 연산함
또한, SELECT절과 ORDER BY 절에서만 사용가능함
:문법
SELECT
분석함수(args) OVER([PARTITION BY 칼럼] ORDER BY 칼럼 [WINDOWING]
[ASC|DESC] [NULLS FIRST|NULL LAST] )
FROM 테이블;
args = 0~3
windowing = 물리적인 결과 행의수
OVER : 쿼리결과를 이용해 동작하는 함수라는 구분.
PARTITION BY : 쿼리결과를 PPP에 지정된값에 근거하여 그룹별 순위를 매김
생략할경우 전체결과가 하나의 작용단위가 된다.
ORDER BY : 순위를 매기고자하는 기준 컬럼을 설정 (partition by 뒤에 위치)
NULL FIRST | NULL LAST : NULL이 포함된 ROW가 순서상 제일앞 또는 제일 뒤에 위치할것인기를 지정
WINDOWING : 행 그룹의 범위 ( partiton by 에 의해 나누어진 기준그룹에 또다른 소그룹을 만든다 )
1. 순위함수 ( rank, dense_rank, row_number 함수 )
- 주어진 기준 값에 근거하여 레코드들을 비교하여 순위를 계산하는 함수
1.1 rank함수 , dense_rank함수
먼저 Rank와 Dense_rank가 있는데 둘의 차이는 거의 똑같고 순위를 매길때 부여하는 순위간격만 차이있다.
1 2 3 3 5 6 7 : rank (값이 같으면 같은 갯수만큼 건너뜀)
1 2 3 3 4 5 6 : Dense_rank (값이 같으면 동일한 순위가 됨)
[ex] partition by 의 유무 ( rank함수에서 order by 절은 필수 ) => 그룹을 나누어 순위를 매김
SQL> SELECT JOB, ENAME, SAL,
RANK( ) OVER (ORDER BY SAL DESC) ALL_RANK,
RANK( ) OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK
FROM EMP;
JOB ENAME SAL ALL_RANK JOB_RANK
--------- ---------- ---------- ---------- ----------
PRESIDENT KING 5000 1 1
ANALYST FORD 3000 2 1
ANALYST SCOTT 3000 2 1
MANAGER JONES 2975 4 1
MANAGER BLAKE 2850 5 2
MANAGER CLARK 2450 6 3
SALESMAN ALLEN 1600 7 1
SALESMAN TURNER 1500 8 2
CLERK MILLER 1300 9 1
SALESMAN WARD 1250 10 3
SALESMAN MARTIN 1250 10 3
CLERK ADAMS 1100 12 2
CLERK JAMES 950 13 3
CLERK SMITH 800 14 4
14 rows selected.
1.2 row_number함수
- rank나 dense_rank함수가 동일한 값에 대해서는 동일한 순위를 부여하는 반면
row_number함수는 동일한 값이라도 고유한 순위를 부여한다.
단, 주의사항이 다음을 만족하지않으면 실행마다 정확히 동일하게 정렬된다는보장은없다음
=> 분할된 열값이 고유, ORDER BY열값이 고유, PARTITION 및 ORDER BY 열값의 조합이 고유
( 동일한 값에 대한 고유순위는 어떻게 정해지지? 알아볼것 )
2. 일반 집계 함수 (sum, max, min, avg, count함수 )
3. 그룹내 행 순서 함수 (first_value, last_value, lag, lead, 함수 )
4. 그룹내 비율 함수 ( ratio_to_report, percent_rank, cume_dist, ntile, 함수 )
4.1 RATIO_TO_REPORT함수
4.2 PERCENT_RANK함수
- 랭킹을 퍼센트로 나타냄
PERCENT_RANK = (자신의 ROW순위 -1) / (전체 ROW숫자 -1)
참고로[ 0/숫자 = 0 ]이다
4.3 CUME_DIST함수 (누적분포)
- 랭킹을 0에서 1까지의 값으로 표시( 위 PERCENT_RANK와 비슷하지만 계산법이 틀림 )
최저값과 최고값사이의 상대적 위치
CUME_DIST = (첫번째순위는 1 / 총 ROW수) , (두번째순위는 2 / 총 ROW수) ......
[EX] SELECT emp_id
, PERCENT_RANK() OVER(ORDER BY salary) AS PERCENT_RANK
, (CUME_DIST() OVER(ORDER BY salary)) AS CUME_DIST
FROM TEMP;
EMP_ID PERCENTE_RANK CUME_DIST
20000119 0 0.5
20000308 0 0.5
20000407 0 0.5
20006106 0 0.5
20000305 0 0.5
20000101 0 0.5
20000203 0 0.5
20000334 0 0.5
20000102 0 0.5
20000210 0 0.5
19960303 0.5263157894736842105263157894736842105263 0.55
19960212 0.5789473684210526315789473684210526315789 0.6
19966102 0.6315789473684210526315789473684210526316 0.7
19970112 0.6315789473684210526315789473684210526316 0.7
19970201 0.7368421052631578947368421052631578947368 0.75
19950303 0.7894736842105263157894736842105263157895 0.8
19930402 0.8421052631578947368421052631578947368421 0.85
19930331 0.8947368421052631578947368421052631578947 0.9
19960101 0.9473684210526315789473684210526315789474 0.95
19970101 1 1
4.4 NTILE(N)함수
- 자신이 속한 ROW들을 단순히 지정한 숫자(N) 만큼으로 분류하여 행의 위치를 표시
전체row수를 나눠서 급여순으로 행의위치를 표시..( 급여의합이나 그런게아님...단순히 row를기준으로)
[EX] SELECT EMPNO, ENAME, SAL
,NTILE(5) OVER(ORDER BY SAL) AS NTILE
FROM EMP;
=> SAL순으로 나열하여 전체 ROW수를 5로 나눈것... 그이상도 그이하도아님..
단순히 전체 20명사원중 NTILE(5)인 5로 나눠서 급여순으로 등급을 매김....
5. 기타 분석함수와 유사한함수들이지만 분석함수는아닌것들 ( keep구문 함수 )
- 집계함수( KEEP구문 )
MAX(얻고자하는컬럼정보) KEEP(DENSE_RANK LAST ORDER BY 급여)
=> MAX(급여)에 해당하는 다른 컬럼 정보를 출력할때 사용한다
MAX(급여)로는 최고급여만 알수있지만 그 최고급여의 다른 컬럼값을 알수없다.
하지만 이 집계함수를 통해 최고급여의 다른정보를 알수있다.
활용하여 MAX말고 MIN도 사용가능하며 LAST말고 FIRST도 사용가능하다.
EX) SELECT
MIN(EMP_NAME) KEEP( DENSE_RANK LAST ORDER BY SALARY ) AS 최대급여자
FROM TEMP;
( 최소급여자의 LAST이므로 최대급여자임 )
- 일반함수( WIDTH_BUCKET함수 )
WIDTH_BUCKET( 급여, 3000, 5000, 2)
=> 분석함수인 NTILE함수와 유사한 기능을 갖는 일반함수
급여를 2등급(1,2)으로 나눔, 실제로는 4등급(0,1,2,3)
0 : 3000미만
1 : 3000이상 4000미만
2 : 4000이상 5000미만
3 : 5000이상
ex ) SELECT WIDTH_BUCKET(1000, 100, 2000, 3) FROM DUAL;
=> 답은 2 등급
0 100 미만
1 100이상 XX미만
2 xx이상 yy미만
3 yy이상 2000미만
4 2000이상
- 집계함수( ListAgg함수 )
ListAgg(이름) WITHIN GROUP( ORDER BY 사원번호 )
=> GROUP BY 에서 문자열 합칠때 사용
ex) SELECT LISTAGG(EMP_NAME) WITHIN GROUP(ORDER BY EMP_ID)
FROM TEMP;
=> 정도령강감찬이순신홍길동배뱅이설까치지문덕김길동연흥부박문수...
SELECT EMP_NAME
FROM TEMP
ORDER BY EMP_ID 한결과를LIST처럼 문자열 합쳐서 출력
# 오라클에서 Max row에 해당하는 다른 컬럼값 얻기
(서브쿼리말고 어떤값에 해당하는 다른컬럼값얻는방법은없나?? )
1. 분석함수 사용
테이블에 row_number를 통해 max row에 내림차순으로 순위를 매겨( max row가 1부터 시작 )
1순위에 해당하는 row의 모든 컬럼을 출력
SELECT *
FROM(
SELECT t.*, ROW_NUMBER() OVER(ORDER BY year_month DESC) AS rn
FROM table
)
WHERE rn = 1
2. MAX()함수의 KEEP() 구문 사용
근데 이거 쓸때 유의해야하는게 value1과 value2와 value3의 값은 같은 row가 아닐수도있음
( first나 last 또는 max, min 또는 order by절 뒤의 컬럼에 따라 값이 틀려지기때문! )
따라서 아래와같이 표현하고자하는것이 정확해야함!
( value1 - 최대달, value2 - 최대달에 해당하는 값1 , value3 - 최대달에 해당하는 값2 )
[올바른 예]
SELECT MAX(year_month) AS value1
, MAX(value2) KEEP(DENSE_RANK FIRST ORDER BY year_month DESC) AS value2
, MAX(value3) KEEP(DENSE_RANK FIRST ORDER BY year_month DESC) AS value3
FROM table
[잘못된 예] 아래와같으면 내가원하는 결과가 아닐가능성이 매우큼!!
SELECT MAX(year_month) AS value1
, MAX(value2) KEEP(DENSE_RANK LAST ORDER BY year_month DESC) AS value2
, MIN(value3) KEEP(DENSE_RANK FIRST ORDER BY year_month DESC) AS value3
FROM table
추가적으로 정리필요.. 분석함수, row_number() over(~~) , keep()
출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=26744104& 및 기타 등등...
'DB > 오라클' 카테고리의 다른 글
| 오라클 피벗( Oracle Pivot ) 기능 (0) | 2019.04.05 |
|---|---|
| 오라클 구조 (0) | 2019.04.03 |
| 오라클객체( 시퀀스, 뷰, 인덱스 ) + 트리거, 사용자와권한 , 롤 (0) | 2019.04.03 |
| 오라클 시퀀스(sequence) (0) | 2019.04.03 |
| 오라클 트랜잭션 (0) | 2019.04.03 |