(파일명 : 20231024_01_scott)
■ RANK()
- RANK() OVER(OREDER BY 컬럼명 ASC(생략가능)/ DESC)
- 그룹별로 순위를 반환하고 싶을 때!
RANK() OVER(PARTITION BY 컬럼명 ORDER BY 컬럼명 ASC/DESC)
- 순위 반환.
- 만약 공동 1등일 경우 2등은 2가 아닌 3을 반환함!!
--○ RANK() → 등수(순위)를 반환하는 함수
SELECT EMPNO "사원번호", ENAME "사원명", DEPTNO "부서번호", SAL "급여"
, RANK() OVER(ORDER BY SAL DESC) "전체급여순위"
FROM EMP;
--==>>
/*
7839 KING 10 5000 1
7902 FORD 20 3000 2
7788 SCOTT 20 3000 2
7566 JONES 20 2975 4
7698 BLAKE 30 2850 5
7782 CLARK 10 2450 6
7499 ALLEN 30 1600 7
7844 TURNER 30 1500 8
7934 MILLER 10 1300 9
7521 WARD 30 1250 10
7654 MARTIN 30 1250 10
7876 ADAMS 20 1100 12
7900 JAMES 30 950 13
7369 SMITH 20 800 14
*/
SELECT EMPNO "사원번호", ENAME "사원명", DEPTNO "부서번호", SAL "급여"
, RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) "부서별급여순위"
, RANK() OVER(ORDER BY SAL DESC) "전체급여순위"
FROM EMP;
--==>>
/*
7839 KING 10 5000 1 1
7902 FORD 20 3000 1 2
7788 SCOTT 20 3000 1 2
7566 JONES 20 2975 3 4
7698 BLAKE 30 2850 1 5
7782 CLARK 10 2450 2 6
7499 ALLEN 30 1600 2 7
7844 TURNER 30 1500 3 8
7934 MILLER 10 1300 3 9
7521 WARD 30 1250 4 10
7654 MARTIN 30 1250 4 10
7876 ADAMS 20 1100 4 12
7900 JAMES 30 950 6 13
7369 SMITH 20 800 5 14
*/
SELECT EMPNO "사원번호", ENAME "사원명", DEPTNO "부서번호", SAL "급여"
, RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) "부서별급여순위"
, RANK() OVER(ORDER BY SAL DESC) "전체급여순위"
FROM EMP
ORDER BY DEPTNO;
--==>>
/*
7839 KING 10 5000 1 1
7782 CLARK 10 2450 2 6
7934 MILLER 10 1300 3 9
7902 FORD 20 3000 1 2
7788 SCOTT 20 3000 1 2
7566 JONES 20 2975 3 4
7876 ADAMS 20 1100 4 12
7369 SMITH 20 800 5 14
7698 BLAKE 30 2850 1 5
7499 ALLEN 30 1600 2 7
7844 TURNER 30 1500 3 8
7654 MARTIN 30 1250 4 10
7521 WARD 30 1250 4 10
7900 JAMES 30 950 6 13
*/
■ DENS_RANK()
- DENE_RANK() OVER(OREDER BY 컬럼명 ASC(생략가능)/ DESC)
- 서열을 반환하는 함수
- 공동 1등이더라도 2등은 2를 반환함
--○ DENSE_RANK() → 서열을 반환하는 함수
SELECT EMPNO "사원번호", ENAME "사원명", DEPTNO "부서번호", SAL "급여"
, DENSE_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) "부서별급여서열"
, DENSE_RANK() OVER(ORDER BY SAL DESC) "전체급여서열"
FROM EMP
ORDER BY 3, 4 DESC;
--==>>
/*
7839 KING 10 5000 1 1
7782 CLARK 10 2450 2 5
7934 MILLER 10 1300 3 8
7902 FORD 20 3000 1 2
7788 SCOTT 20 3000 1 2
7566 JONES 20 2975 2 3 -- 내 앞이 공동 1위더라도 3이 아닌 2를 반환한다.!
7876 ADAMS 20 1100 3 10
7369 SMITH 20 800 4 12
7698 BLAKE 30 2850 1 4
7499 ALLEN 30 1600 2 6
7844 TURNER 30 1500 3 7
7654 MARTIN 30 1250 4 9
7521 WARD 30 1250 4 9
7900 JAMES 30 950 5 11
*/
■ RANK 활용 문제 풀이 1
--○ EMP 테이블의 사원 데이터를
-- 사원명, 부서번호, 연봉, 부서내연봉순위, 전체연봉순위 항목으로 조회한다.
SELECT ENAME"사원명", DEPTNO"부서번호", SAL*12+NVL(COMM,0)"연봉"
, RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL*12+NVL(COMM,0) DESC) "부서내연봉순위"
, RANK() OVER(ORDER BY SAL*12+NVL(COMM,0) DESC) "전체연봉순위"
FROM EMP;
--==>>
/*
KING 10 60000 1 1
FORD 20 36000 1 2
SCOTT 20 36000 1 2
JONES 20 35700 3 4
BLAKE 30 34200 1 5
CLARK 10 29400 2 6
ALLEN 30 19500 2 7
TURNER 30 18000 3 8
MARTIN 30 16400 4 9
MILLER 10 15600 3 10
WARD 30 15500 5 11
ADAMS 20 13200 4 12
JAMES 30 11400 6 13
SMITH 20 9600 5 14
*/
SELECT T.사원명, T.부서번호
, RANK() OVER(PARTITION BY T.부서번호 ORDER BY T.연봉 DESC) "부서내연봉순위"
, RANK() OVER(ORDER BY T.연봉 DESC) "전체연봉순위"
FROM
(
SELECT ENAME"사원명", DEPTNO"부서번호", SAL*12+NVL(COMM,0) "연봉"
FROM EMP
)T
ORDER BY 2,4;
--==>> 서브쿼리 활용
/*
KING 10 60000 1 1
CLARK 10 29400 2 6
MILLER 10 15600 3 10
FORD 20 36000 1 2
SCOTT 20 36000 1 2
JONES 20 35700 3 4
ADAMS 20 13200 4 12
SMITH 20 9600 5 14
BLAKE 30 34200 1 5
ALLEN 30 19500 2 7
TURNER 30 18000 3 8
MARTIN 30 16400 4 9
WARD 30 15500 5 11
JAMES 30 11400 6 13
*/
■ RANK 활용 문제 풀이 2
- WHERE 절에 RANK() 쓸 수 없음!
--○ EMP 테이블에서 전체연봉순위가 1등 부터 5등 까지만
-- 사원명, 부서번호, 연봉, 전체연봉순위 항목으로 조회한다.
-- 내가 한 풀이
SELECT F.*
FROM
(
SELECT T.*, RANK() OVER(ORDER BY T.연봉)"전체연봉순위"
FROM
(
SELECT ENAME"사원명", DEPTNO"부서번호", SAL*12+NVL(COMM,0)"연봉"
FROM EMP
)T
)F
WHERE F.전체연봉순위<6;
-- 같이 한 풀이
SELECT 사원명, 부서번호, 연봉, 전체연봉순위
FROM EMP
WHERE 전체연봉순위가 1등 부터 5등;
SELECT ENAME "사원명", DEPTNO "부서번호", SAL*12+NVL(COMM,0)"연봉"
, RANK() OVER(ORDER BY (연봉) DESC) "전체연봉순위"
FROM EMP
WHERE 전체연봉순위가 1등 부터 5등;
SELECT ENAME "사원명", DEPTNO "부서번호", SAL*12+NVL(COMM,0)"연봉"
, RANK() OVER(ORDER BY SAL*12+NVL(COMM,0) DESC) "전체연봉순위"
FROM EMP
WHERE RANK() OVER(ORDER BY SAL*12+NVL(COMM,0) DESC)가 1등 부터 5등;
SELECT ENAME "사원명", DEPTNO "부서번호", SAL*12+NVL(COMM,0)"연봉"
, RANK() OVER(ORDER BY SAL*12+NVL(COMM,0) DESC) "전체연봉순위"
FROM EMP
WHERE RANK() OVER(ORDER BY SAL*12+NVL(COMM,0) DESC) <= 5;
--==>> 에러 발생
-- (ORA-30483: window functions are not allowed here)
--※ 위의 내용은 RANK() OVER() 함수를 WHERE 조건절에서 사용한 경우이며
-- 이 함수는 WHERE 조건절에서 사용할 수 없기 때문에 발생하는 에러이다.
-- 이 경우, 우리는 INLINE VIEW 를 활용해서 풀이해야 한다.
SELECT T.*
FROM
(
SELECT ENAME "사원명", DEPTNO "부서번호", SAL*12+NVL(COMM,0)"연봉"
, RANK() OVER(ORDER BY SAL*12+NVL(COMM,0) DESC) "전체연봉순위"
FROM EMP
)T
WHERE T.전체연봉순위 <= 5;
--==>>
/*
KING 10 60000 1
SCOTT 20 36000 2
FORD 20 36000 2
JONES 20 35700 4
BLAKE 30 34200 5
*/
■ RANK 활용 문제 풀이 3
--○ EMP 테이블에서 각 부서별로 연봉 등수가 1등 부터 2등 까지만 조회한다.
-- 사원명, 부서번호, 연봉, 부서내연봉등수, 전체연봉등수 항목을
-- 조회할 수 있도록 쿼리문을 구성한다.
-- 내가 한 풀이 (서브쿼리 1번 사용)
SELECT T.*
FROM
(
SELECT ENAME"사원명", DEPTNO"부서번호", SAL*12+NVL(COMM,0)"연봉"
, RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL*12+NVL(COMM,0))"부서내연봉등수"
, RANK() OVER(ORDER BY SAL*12+NVL(COMM,0))"전체연봉등수"
FROM EMP
)T
WHERE T.부서내연봉등수 <= 2;
-- 서브쿼리 2번 사용
SELECT F.*
FROM
(
SELECT T.*
, RANK() OVER(PARTITION BY T.부서번호 ORDER BY T.연봉)"부서내연봉등수"
, RANK() OVER(ORDER BY T.연봉)"전체연봉등수"
FROM
(
SELECT ENAME"사원명", DEPTNO"부서번호", SAL*12+NVL(COMM,0)"연봉"
FROM EMP
)T
)F
WHERE F.부서내연봉등수 <= 2;
(파일명 20231025_01_soctt)
■ RANK함수 사용 없이 순위 구하기
- 하위 버전에서는 RANK()나 DENSE_RANK()를 사용할 수 없음!
--RANK() / DENSE_RANK()
--> ORACLE 9i 부터 적용... (MSSQL 2005 부터 적용...(?) 확실하지는 않음)
--> 하위 버전에서는 RANK()나 DENSE_RANK()를 사용할 수 없기 때문에
-- 이 함수를 활용하지 않는 다른 방법을 찾아야 한다.
-- 예를 들어 급여 순위를 구하고자 한다면
-- 해당 사원의 급여보다 더 큰 값이 몇개인지 확인한 후
-- 확인한 숫자에 『+1』을 추가로 연산해 주면
-- 그 값이 곧 해당 사원의 등수가 된다.
SELECT ENAME, SAL
FROM EMP;
-- 예를 들어.. SMITH보다 급여를 많이 받는 사람은 13명이 있다. 그럼 스미스의 급여 순위는 14!
-- 라는 로직으로 코드 작성
SELECT COUNT(*) +1
FROM EMP
WHERE SAL > 800; -- SMITH의 급여
--==>> 14 -- SMIT의 급여 등수
SELECT COUNT(*) +1
FROM EMP
WHERE SAL > 1600; -- ALLEN의 급여
--==>> 7 -- ALLEN의 급여 등수