0809_Oracle : RANK() OVER() 순위를 매기는 함수

RANK() OVER() 함수

-- RANK() OVER()
SELECT empNo, name, sal, RANK() OVER(ORDER BY sal DESC) 순위 FROM emp;
-- empNo, name, sal 을 출력하고, sal 내림차순으로 순위를 매겨서 출력해라.
SELECT empNo, name, sal, RANK() OVER(ORDER BY sal) 순위 FROM emp;
-- empNo, name, sal 을 출력하고, sal 오름차순으로 순위를 매겨서 출력해라.

SELECT empNo, name, sal, bonus, RANK() OVER(ORDER BY sal, bonus DESC) 순위 FROM emp;
-- emp 테이블에서 empNo, name, sal, bonus를 출력하고
-- sal 내림차순으로 순위를 부여하고, sal이 동일하면 bonus 내림차순 순위를 매겨으로 정렬하고 출력.

-- 그룹별 순위
SELECT empNo, name, dept, pos, sal, 
    RANK() OVER( ORDER BY sal DESC ) 전체순위,
    RANK() OVER( PARTITION BY dept ORDER BY sal DESC ) 부서순위,
    RANK() OVER( PARTITION BY dept, pos ORDER BY sal DESC ) 부서직위순위
FROM emp;

-- 급여(sal)를 가장 많이 받는 순으로 1~10등까지 출력
SELECT empNo, name, dept, pos, SAL
FROM EMP
WHERE RANK() OVER( ORDER BY sal DESC) <= 10;
-- 에러. 분석함수는 WHERE절에 사용 불가

SELECT empNo, name, dept, pos, SAL,
    RANK() OVER( ORDER BY sal DESC) 순위
FROM EMP;

SELECT empNo, name, dept, pos, SAL,
    RANK() OVER( ORDER BY sal DESC) 순위
FROM EMP
WHERE ROWNUM <= 10; -- 이상한 결과. 10등이 2명이상이어도 한명만 출력
-- WHERE로 먼저 10명을 짜르기 때문에 순위가 아예 이상함.
-- ORDER BY가 있는 경우 ROWNUM을 사용하면 안됨.

SELECT * FROM (
    SELECT empNo, name, dept, pos, SAL,
    RANK() OVER( ORDER BY sal DESC) 순위
    FROM EMP
) WHERE 순위 <= 10;
-- 먼저 RAN() OVER()로 정렬한 후에 서브 쿼리를 이용해서 10명의 순위를 짜른다.

-- 급여 상위 10%
-- 연봉을 내림차순으로 정렬하기 때문에 10% 보다 작은 순위들이 급여 상위 10%
SELECT * FROM (
    SELECT empNo, name, dept, pos, sal,
    RANK() OVER( ORDER BY sal DESC) 순위
    FROM emp
) WHERE 순위 < (SELECT COUNT(*) FROM emp) * 0.1;

-- 급여 하위 10%
-- 연봉을 오름차순으로 정렬하기 때문에 10% 보다 작은 순위들이 급여 하위 10%
SELECT empNo, name, dept, pos, sal FROM (
    SELECT empNo, name, dept, pos, sal,
    RANK() OVER( ORDER BY sal ) 순위
    FROM emp
) WHERE 순위 < (SELECT COUNT(*) FROM emp) * 0.1;

-- dept별 급여(sal+bonus)가 가장 높은 name, dept, pos, sal, bonus 출력
SELECT name, dept, pos, sal, bonus FROM (
    SELECT name, dept, pos, sal, bonus, 
    RANK() OVER( PARTITION BY dept ORDER BY sal+bonus DESC) 순위
    FROM EMP
    -- 부서별로 연봉+보너스의 값으로 순위를 매김
) WHERE 순위 = 1;
-- 부서별 연봉+보너스 순위가 1인 사람들만 출력

안에 있는 서브쿼리를 실행해서 나온 값들

쿼리의 최종결과

-- dept별 여자인원수가 가장 많은 부서 및 인원수 출력
-- 잘라서 가져올 수 있으면 자르는 것이 1순위
SELECT dept, COUNT(*) 인원수
FROM emp
WHERE MOD(SUBSTR( rrn, 8, 1), 2) = 0
GROUP BY dept;
-- WHERE 절로 emp 테이블에서 여자들만 빼와서 부서별로 여자들의 인원수를 출력함.

SELECT dept, COUNT(*) 인원수,
    RANK() OVER( ORDER BY COUNT(*) DESC )순위
FROM emp
WHERE MOD(SUBSTR( rrn, 8, 1), 2) = 0
GROUP BY dept;
-- 위에서 여자들의 인원수 내림차순으로 순위를 매겨 출력한다.

SELECT dept, 인원수 FROM ( 
    SELECT dept, COUNT(*) 인원수,
        RANK() OVER( ORDER BY COUNT(*) DESC )순위
    FROM emp 
    WHERE MOD(SUBSTR( rrn, 8, 1), 2) = 0 
    GROUP BY dept
) WHERE 순위 = 1;
-- 위의 쿼리에서 순위가 1인 부서와 그 부서의 여자인원수를 출력한다.

마지막 쿼리의 결과

RANK() OVER() 함수와

DENSE_RANK() OVER() 함수, ROW_NUMBER() OVER() 함수의 차이점

SELECT name, sal, RANK() OVER( ORDER BY sal DESC) 순위 FROM emp;

SELECT name, sal, DENSE_RANK() OVER( ORDER BY sal DESC) 순위 FROM emp;

SELECT name, sal, ROW_NUMBER() OVER( ORDER BY sal DESC) 순위 FROM emp;

SELECT RANK( 3000000 ) WITHIN GROUP ( ORDER BY sal DESC ) 순위
FROM emp;

RANK() OVER() 함수의 결과

RANK() OVER() 함수는 동순위를 같은 순위를 주고 그 인원수 만큼 더한 후에 다음 순위를 준다. 위의 사진을 보면 제일 왼쪽의 숫자 10, 11은 같은 sal을 갖고 있기 때문에 순위가 10위로 동일하다. 그 다음 순위인 11위는 없고 12위가 있다.

DENSE_RANK() OVER() 함수의 결과

DENSE_RANK() OVER() 함수는 동순위를 같은 순위를 주고, 동순위와 관계없이 다음순위를 준다. 위의 예시 참고.

ROW_NUMBER() OVER() 함수의 결과

ROW_NUMBER() OVER() 함수는 같은 sal을 가지고 있는 사람이더라도 순위를 각각 매겨 동순위가 존재하지 않는다. ( 같은 sal을 가지고 있을 때 누가 먼저 순위를 갖느냐는 무작위)

 

RANK() WITHIN GROUP () 

RANK() WITHIN GROUP() 함수의 결과

RANK( 여기 값) 이 WITHIN GROUP (순위를 매기는 기준이 되는 그룹) 에서 몇 번째 순위이냐를 출력해주는 것으로 16위임을 알 수 있다. (위의 사진은 맞는지 확인을 위한 사진)