분석함수(Anaylitic Function)

오라클 10g부터 분석함수란 것이 등장했다.
랭킹, 누적합 또는 표준편차 등을 구하는 것을 9i까지는 복잡하게 SQL을 가공해서 사용했었는데,
10g부터는 아래의 함수를 사용함으로써 쉽게 구할 수 있게 되었다.
order by 항 이외에는 마지막으로 처리된다.

- 누적합        

SQL> SELECT deptno,  ename,  sal,
        SUM(sal) OVER (PARTITION BY DEPTNO
                                ORDER BY deptno, ename)
        FROM emp;

SQL> select d.loc, d.deptno, e.empno, e.ename, e.sal
         from emp e, dept d
         where e.deptno = d.deptno;

 

SQL> select d.loc, d.deptno, e.empno, e.ename, e.sal,
         sum(sal) over (partition by d.loc
                               order by empno, sal) as running_total
         from emp e, dept d
         where e.deptno = d.deptno;

- Rank 구하기

SQL> SELECT deptno,  ename,  sal,
                      rank() over (order by sal)        as rank,
                     dense_rank() over (order by sal)  as dense_rank,
                     row_number() over (order by sal)  as row_number
         FROM emp;

 

SQL> select *
         from (SELECT deptno,  ename,  sal,
                               rank() over (order by sal)              as rank,
                               dense_rank() over (order by sal)  as dense_rank
                 FROM emp) t
        where rank <= 10;

 

SQL> select *
         from (SELECT deptno,  ename,  sal,
                              rank() over (order by sal)              as rank,
                              dense_rank() over (order by sal)  as dense_rank
                 FROM emp) t
         where dense_rank <= 10;

 

SQL> SELECT deptno,  ename,  sal,
                     rank() over (partition by deptno
                                        order by deptno, sal)       as rank,
                     dense_rank() over (partition by deptno
                                                   order by deptno, sal) as dense_rank,
                     row_number() over (partition by deptno
                                                    order by deptno, sal) as row_number
          FROM emp
          order by deptno, sal;

 

-- 이동 평균

SQL> SELECT deptno "Deptno", ename "Ename", sal "Sal",
                      avg(SAL) OVER (ORDER BY ename) as 누적평균
         FROM emp;

 

SQL> SELECT deptno "Deptno", ename "Ename", sal "Sal",
                      avg(SAL) OVER (ORDER BY ename
                                               rows 2 preceding) as 세개이동평균
          FROM emp;

-- 최고값, 최소값

SQL> SELECT deptno, ename, sal,
                      FIRST_VALUE(ename) OVER (ORDER BY sal ASC) AS MIN_SAL_HAS,
                      FIRST_VALUE(sal)   OVER (ORDER BY sal ASC) AS MIN_SAL,
                      FIRST_VALUE(ename) OVER (ORDER BY sal DESC) AS MAX_SAL_HAS,
                      FIRST_VALUE(sal)   OVER (ORDER BY sal DESC) AS MAX_SAL,
                      LAST_VALUE(sal)    OVER (ORDER BY sal
                                             range between unbounded preceding and unbounded following) as min_sal2
         FROM emp;

   ※ 아래의 의미는 :  나를 기준으로 처음부터 끝까지
    range between unbounded preceding and unbounded following


기타참고사항

SQL*PLUS TIP

SQL> break on deptno skip1                   -- 컬럼과 컬럼 사이에 빈칸을 둠
SQL> break on deptno skip page             -- 부서 번호가 바뀔 경우 페이지로 구분
SQL> clear break                                   -- 설정한 값 해제

참조 :
 - 메뉴얼 : http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/analysis.htm#i1007779
 - 방형욱 강사님 까페 : http://cafe.naver.com/gseducation.cafe?iframe_url=/ArticleRead.nhn%3Farticleid=44