'그룹함수'에 해당되는 글 2건

  1. 2014.08.01 그룹함수 NTILE
  2. 2014.03.14 오라클 그룹 함수 over, partition by 2

그룹함수 NTILE

sql 2014. 8. 1. 17:54

NTILE이라는 그룹함수는 특정 컬럼을 n개만큼 구분해 준다.

WITH T AS (

    SELECT  '2014125'  EMP_NO FROM DUAL UNION ALL

    SELECT  '2014455'  EMP_NO FROM DUAL UNION ALL

    SELECT  '2014532'  EMP_NO FROM DUAL UNION ALL

    SELECT  '2014234'  EMP_NO FROM DUAL UNION ALL

    SELECT  '2014765'  EMP_NO FROM DUAL UNION ALL

    SELECT  '2014321'  EMP_NO FROM DUAL UNION ALL

    SELECT  '2014012'  EMP_NO FROM DUAL UNION ALL

    SELECT  '2014056'  EMP_NO FROM DUAL UNION ALL

    SELECT  '2014089'  EMP_NO FROM DUAL UNION ALL

    SELECT  '2014022'  EMP_NO FROM DUAL UNION ALL

    SELECT  '2014044'  EMP_NO FROM DUAL UNION ALL

    SELECT  '2014055'  EMP_NO FROM DUAL UNION ALL

    SELECT  '2014011'  EMP_NO FROM DUAL UNION ALL

    SELECT  '2014442'  EMP_NO FROM DUAL UNION ALL

    SELECT  '2014232'  EMP_NO FROM DUAL

)

SELECT  EMP_NO

        , NTILE(3) OVER ( ORDER BY EMP_NO) GRP3

        , NTILE(4) OVER ( ORDER BY EMP_NO) GRP4

        , NTILE(5) OVER ( ORDER BY EMP_NO) GRP5

        , NTILE(6) OVER ( ORDER BY EMP_NO) GRP6

FROM    T


실행결과는..

2014011 1 1 1 1

2014012 1 1 1 1

2014022 1 1 1 1

2014044 1 1 2 2

2014055 1 2 2 2

2014056 2 2 2 2

2014089 2 2 3 3

2014125 2 2 3 3

2014232 2 3 3 3

2014234 2 3 4 4

2014321 3 3 4 4

2014442 3 3 4 5

2014455 3 4 5 5

2014532 3 4 5 6

2014765 3 4 5 6


Posted by 무세1
,

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


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


Posted by 무세1
,