오라클 그룹 함수 over, partition by

오라클 그룹 함수 정리...

with문을 이용하여 테스트...

with emp  as (

select '7782' EMPNO, 'CLARK' ENAME  , 2450 SAL  ,'10' DEPTNO from dual union all

select '7839', 'KING'    , 5000   ,'10' from dual union all

select '7934', 'MILLER'  , 1300   ,'10' from dual union all

select '7566', 'JONES'   , 2975   ,'20' from dual union all

select '7902', 'FORD'    , 3000   ,'20' from dual union all

select '7876', 'ADAMS'   , 1100   ,'20' from dual union all

select '7369', 'SMITH'   , 800    ,'20' from dual union all

select '7788', 'SCOTT'   , 3000   ,'20' from dual union all

select '7521', 'WARD'    , 1250   ,'30' from dual union all

select '7844', 'TURNER'  , 1500   ,'30' from dual union all

select '7499', 'ALLEN'   , 1600   ,'30' from dual union all

select '7900', 'JAMES'   , 950    ,'30' from dual union all

select '7698', 'BLAKE'   , 2850   ,'30' from dual union all

select '7654', 'MARTIN'  , 1250   ,'30' from dual 

)

전체 평균을 각 행에 붙일때...

over(조건)는 그룹을 묶는다...  조건이 없으면 전체가 묶인다.

select empno, ename, sal,

        avg(sal) over( ) avg_sal

from emp;

결과

empno ename sal avg_sal

7782 CLARK 2450.00000 2073.21429

7839 KING         5000.00000 2073.21429

7934 MILLER 1300.00000 2073.21429

:

:

7900 JAMES 950.00000 2073.21429

7698 BLAKE 2850.00000 2073.21429

7654 MARTIN 1250.00000 2073.21429

부서별 평균을 구할때..

over(조건)  조건에 따라서 그룹을 묶는다..

partiton by deptno -> 부서에 따라 그룹을 묶는거다.

select empno, ename, sal, deptno

        , avg(sal) over(PARTITION BY deptno) avg_sal

from emp ;

결과

7934 MILLER 1300.00000 10 2916.66667

7839 KING         5000.00000 10 2916.66667

7782 CLARK 2450.00000 10 2916.66667

7876 ADAMS 1100.00000 20 2175.00000

:

:

7900 JAMES 950.00000 30 1566.66667

7698 BLAKE 2850.00000 30 1566.66667

7654 MARTIN 1250.00000 30 1566.66667

가벼운 응용

최소, 최대 연봉자 구하기

select  *

from    (

        select empno, ename, sal, deptno

                , count(*) over(PARTITION BY null) cnt 

                , rank() over(order BY sal DESC) col_rank

        from emp

        )

where   col_rank = 1         --최고연봉자

or      ( cnt = col_rank )     --최저연봉자

결과

7369 SMITH 800.00000 20 14.00000 1.00000

7839 KING       5000.00000 10 14.00000 14.00000