오라클 분석함수 rank(), max(), sum()

분석함수

함수이름(인자) over (partition by ~ order by ~ rows)
0개~3개 1 2 3
2 : 함수에따라 필수사용인경우있음
3 : 단독사용x 사용시 2번 지정

-------------------------------------

SELECT ename, deptno, sal
, rank() over (order by sal desc) 순위
, RANK() OVER (PARTITION BY deptno ORDER BY sal desc) 부서내순위
, DENSE_RANK() OVER (ORDER BY sal desc) 순위2
FROM EMP;
ORDER BY deptno;

RANK 공동등수가있을경우 다음등수는 공동등수인원수를 따로 적용한 등수가 적용
DENSE_RANK 공동등수가있을경우 다음등수가 순차적으로 적용

1. 집계함수로 사용된 max 쿼리오류
SELECT ename, sal, MAX(sal) FROM EMP;

2. 분석함수로 사용된 max 쿼리실행
SELECT ename, sal, MAX(sal) OVER () FROM EMP;

3. PARTITION BY 옵션의 의미(group by 와 비슷)
SELECT ename,sal,deptno, MAX(sal) OVER() 전체최대
, MAX(sal) OVER(PARTITION BY deptno) 부서최대
FROM EMP;

4. 사원이름, 급여, 각부서의 급여합계표시
SELECT ename, sal, SUM(sal) OVER (PARTITION BY deptno)
FROM EMP;

5. 해당 행의 앞1 뒤1 컬럼값의 합
SELECT ename,sal
, SUM(sal) OVER(ORDER BY sal ROWS BETWEEN 1 preceding AND 1 following) 부분합
FROM EMP;

6. 누적합
SELECT ename, sal
, SUM(sal) OVER(ORDER BY sal ROWS unbounded preceding) 누적합
FROM EMP;

파트별 누적합
SELECT ename, sal
, SUM(sal) OVER(ORDER BY sal ROWS unbounded preceding) 누적합
FROM EMP;

분석함수 특징 : 분석함수를 제외한 SELECT 문부터 수행한후
해당결과를 대상으로 분석함수 적용함.
ORDER BY 절의 제외하고는 제일마지막에 수행함.
(분석함수를 조건절에 사용못함)

---------------------------------------------------

분석함수중 현재 data기준 앞줄이나 뒷줄data를 가져올수있음

LEAD : 현제 행의 다음레코드값을 가져올때
SELECT ename, sal, LEAD(sal,1) OVER(ORDER BY sal) FROM EMP;

LAG : 현제 행의 앞레코드값을 가져올때
SELECT ename, sal, LAG(sal,1) OVER(ORDER BY sal) FROM EMP;

[출처] 오라클 분석함수 rank(), max(), sum()|작성자 쫑이

 

 

 

참고1.조건에따른 누적합계 구하기 ==> http://www.oracleclub.com/lecture/2203

 

참고2.오라클 분석함수 사용예 및 정리된곳 ==>

http://www.java2s.com/Code/Oracle/Analytical-Functions/CatalogAnalytical-Functions.htm