오라클 RANK GROUP BY - olakeul RANK GROUP BY

  우리가 일반적으로 점수의 순위는 ORDER BY DESC를 해서 볼수가 있다.

  하지만 똑같은 점수를 가진 사람들이 많이 나올때, 예를 들면.100, 90, 90, 80 이렇게 점수가 나오면 순위를 1,2,2,4 이렇게 부여 해야 한다.

  이럴 경우 RANK 함수를 사용하여 쉽게 순위를 부여 할 수 있다.

간단 예제

  SCOTT 사용자로 접속하여 테스트를 진행.

  
-- RANK() 함수를 사용하여 급여 순으로 순위를 부여한 예제 입니다.  
-- RK의 출력값을 보면 급여가 같으면 같은 순위로 부여가 됩니다. 

SQL> SELECT empno, ename, sal, 
     RANK() OVER (ORDER BY sal DESC ) as rk
     FROM emp; 

    EMPNO ENAME           SAL         RK
--------- ---------- --------- ----------
     7839 KING            5000          1
     7788 SCOTT           3000          2
     7902 FORD            3000          2
     7566 JONES           2975          4
     7698 BLAKE           2850          5
     7782 CLARK           2450          6
     7499 ALLEN           1600          7
     7844 TURNER          1500          8
     7934 MILLER          1300          9
     7521 WARD            1250         10
     7654 MARTIN          1250         10
     7876 ADAMS           1100         12
     7900 JAMES            950         13
     7369 SMITH            800         14
    

그룹별로 순위를 부여 하는 법

  아래 예제는특정한 그룹별로 순위를 부여 하는 예제이다.

     
SQL> SELECT deptno, ename, sal, 
     RANK() OVER (PARTITION BY deptno ORDER BY sal DESC ) as rk 
     FROM emp ;
 
 DEPTNO ENAME           SAL        RK
------- ---------- -------- ---------
     10 KING           5000         1
     10 CLARK          2450         2
     10 MILLER         1300         3
     20 SCOTT          3000         1
     20 FORD           3000         1
     20 JONES          2975         3
     20 ADAMS          1100         4
     20 SMITH           800         5
     30 BLAKE          2850         1
     30 ALLEN          1600         2
     30 TURNER         1500         3
     30 WARD           1250         4
     30 MARTIN         1250         4
     30 JAMES           950         6
    

DENSE_RANK() 함수

  DENSE_RANK() 함수는 중복 RANK의 수와 무관하게 numbering을 한다. 1등, 2등, 2등 이렇게 2등이 중복 되었는데 4등이 아니라 3등이 부여 된다.

 
SQL> SELECT empno, ename, sal,  
     DENSE_RANK() OVER (ORDER BY sal DESC ) as rk
     FROM emp;     
    
    EMPNO ENAME             SAL        RK
--------- ---------- ---------- ---------
     7839 KING             5000         1
     7788 SCOTT            3000         2
     7902 FORD             3000         2
     7566 JONES            2975         3
     7698 BLAKE            2850         4
     7782 CLARK            2450         5
     7499 ALLEN            1600         6
     7844 TURNER           1500         7
     7934 MILLER           1300         8
     7521 WARD             1250         9
     7654 MARTIN           1250         9
     7876 ADAMS            1100        10
     7900 JAMES             950        11
     7369 SMITH             800        12 
    

SQL

Oracle RANK(), ROW_NUMBER() 그룹별 N개 샘플링

예제 테이블 SQL 쿼리

CREATE TABLE TEST
(
 A VARCHAR(10),
 B VARCHAR(10)
);

INSERT INTO TEST VALUES ('2','516958');
INSERT INTO TEST VALUES ('1','123458');
INSERT INTO TEST VALUES ('1','458512');
INSERT INTO TEST VALUES ('2','468521');
INSERT INTO TEST VALUES ('2','735196');
INSERT INTO TEST VALUES ('1','794528');

COMMIT;

RANK()함수와 ROW_NUMBER()함수 정의

RANK() 정의

less..

RANK()함수는 레코드단위로 순차적으로 순위(1부터 출력)을 부여하고 레코드단위로
같은값에 대해서는 동일한 순위를 부여한다.

PARTITION BY 를 사용하면 전체를 한그룹으로 보는것이 아니라 PARTITION BY 에 사용된
컬럼을 기준으로 다르게 그룹을 나누어 순위를 부여한다.
오라클 8i부터 지원하는 분석함수입니다.

아래는 순위입니다.
순위는 동점자가 있을 수 있고 공동 순위가 있다면 중간에 비는 숫자도 있겠죠.

RANK()  사용예제

SELECT A,B, RANK() OVER(order by A,B) from TEST;

결과

1 123458 1
1 123458 1
1 458512 3
1 794528 4
2 468521 5
2 516958 6
2 735196 7

SELECT A,B, RANK() OVER(PARTITION BY A ORDER BY A,B) from TEST;

결과

1 123458 1
1 123458 1
1 458512 3
1 794528 4
2 468521 1
2 516958 2
2 735196 3

※ OVER() 함수는 필수입니다.

※ OVER() 함수안에 PARTITION BY 는 미필수지만 ORDER BY는 필수이다.

※ 보통 OVER() 함수안에 ORDER BY 절에는 출력컬럼을 모두 써준다.

※ PARTITION BY 을 사용하면 출력결과를 하나의 그룹으로 보는것이 아니라 PARTITION BY 에 사용된 컬럼을 기준으로 그룹을 나누어지게 된다.

(순위도 그룹별로 별개로 1부터 부여지게 된다.)

less..

ROW_NUMBER() 정의

less..

ROW_NUMBER()는 레코드단위로 동일한값이라도 매번 새로운 순위를 부여한다.

ROW_NUMBER() 함수는 각 PARTITION 내에서 ORDER BY절에 의해 정렬된 순서로 유일한 값을 돌려주는 함수이며 ROWNUM 과는 관계가 없습니다.

오라클 8i부터 지원하는 분석함수입니다.

위에거는 순번이구요
순번은 유일한 값이구요,

ROW_NUMBER() 기본예제

SELECT A, ROW_NUMBER() OVER(ORDER BY A,B) FROM TEST;

결과

1 123458 1
1 123458 2
1 458512 3
1 794528 4
2 468521 5
2 516958 6
2 735196 7

SELECT A, B, ROW_NUMBER() OVER(PARTITION BY A ORDER BY A,B) FROM TEST;

결과

1 123458 1
1 123458 2
1 458512 3
1 794528 4
2 468521 1
2 516958 2
2 735196 3

※ OVER() 함수는 필수입니다.

※ OVER() 함수안에 PARTITION BY 는 미필수지만 ORDER BY는 필수이다.

※ PARTITION BY 을 사용하면 PARTITION BY 에 사용된 컬럼을 기준으로 서로 별개로 1부터 순위를 매기게 됩니다.

ROW_NUMBER() 응용예제

A ,B 그룹별 A,B의 출력을 상위 2개만 출력하기

SELECT A,B FROM
(
 SELECT A, B,
 ROW_NUMBER() OVER(PARTITION BY A ORDER BY B) rn
 FROM TEST
)
WHERE rn <= 2;

또는

SELECT A,B FROM
(
 SELECT A,B,
 ROW_NUMBER() OVER(PARTITION BY A ORDER BY A,B) RM FROM TEST GROUP BY A,B
) WHERE RM <= 2;

결과

1 123458
1 123458
2 468521
2 516958

A 그룹별 A출력을 상위 1개만 출력하기

SELECT A FROM
(
 SELECT A,
 ROW_NUMBER() OVER(ORDER BY A) rn
 FROM TEST GROUP BY A
)
WHERE rn <= 1;

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

row_number()함수 예제3

직업별로 급여 합계를 계산해서 급여 합계가 많은 순으로 가장 많은 직업 3개만 출력하는 예제 입니다.

-- 일반적인 SQL방법

SELECT job, sal FROM
(
SELECT job, SUM(sal) sal FROM emp
GROUP BY job
ORDER BY sal DESC
)
WHERE rownum < 4;

JOB               SAL
---------- ----------
MANAGER         33925
ANALYST          6000
PRESIDENT        5000

-- Analytic function ROW_NUMBER()을 사용하는 방법

SELECT job, sal FROM
(
SELECT job, SUM(sal) sal, ROW_NUMBER() OVER (ORDER BY SUM(sal) DESC) num
FROM emp
GROUP BY job
)
WHERE num < 4;

JOB               SAL
---------- ----------
MANAGER         33925
ANALYST          6000
PRESIDENT        5000

http://zenice.tistory.com/17