그룹함수 NTILE

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