🤷♀️순위 분석 함수란?
순위 분석 함수는 데이터를 순위별로 정렬하거나 순위를 매기는 데 사용되는 SQL 함수들이다. Oracle SQL에서는 주로 ROW_NUMBER, RANK, DENSE_RANK 함수가 사용된다. 이 함수들은 데이터를 정렬한 후 각 행에 대해 순위를 부여하지만, 순위를 처리하는 방식에 차이가 있다.
1) ROW_NUMBER(): 각 행에 고유한 순위를 부여. 동일한 급여가 있어도 순위는 건너뛰지 않고 연속적으로 증가.
2) RANK(): 동일한 급여를 가진 직원들이 같은 순위를 받지만, 그 후의 순위는 건너뛰어 부여됨. 예를 들어, 1등이 두 명이면 그다음 순위는 2를 건너뚜고 3이 된다.
3) DENSE_RANK(): 동일한 급여를 가진 직원들이 같은 순위를 받지만, 그 후의 순위는 건너뛰지 않고 연속적으로 나온다.
-- 순위함수
WITH emp AS (
SELECT '7839' AS empno, 'KING' AS ename, 'PRESIDENT' AS job, NULL AS mgr, '1981-11-17' AS hiredate, '5000' AS sal, NULL AS comm, '10' AS deptno FROM dual UNION ALL
SELECT '7698' AS empno, 'BLAKE' AS ename, 'MANAGER' AS job, '7839' AS mgr, '1981-05-01' AS hiredate, '2850' AS sal, NULL AS comm, '30' AS deptno FROM dual UNION ALL
SELECT '7782' AS empno, 'CLARK' AS ename, 'MANAGER' AS job, '7839' AS mgr, '1981-06-09' AS hiredate, '2450' AS sal, NULL AS comm, '10' AS deptno FROM dual UNION ALL
SELECT '7566' AS empno, 'JONES' AS ename, 'MANAGER' AS job, '7839' AS mgr, '1981-04-02' AS hiredate, '2975' AS sal, NULL AS comm, '20' AS deptno FROM dual UNION ALL
SELECT '7788' AS empno, 'SCOTT' AS ename, 'ANALYST' AS job, '7566' AS mgr, '1987-04-19' AS hiredate, '3000' AS sal, NULL AS comm, '20' AS deptno FROM dual UNION ALL
SELECT '7902' AS empno, 'FORD' AS ename, 'ANALYST' AS job, '7566' AS mgr, '1981-12-03' AS hiredate, '3000' AS sal, NULL AS comm, '20' AS deptno FROM dual UNION ALL
SELECT '7369' AS empno, 'SMITH' AS ename, 'CLERK' AS job, '7902' AS mgr, '1980-12-17' AS hiredate, '800' AS sal, NULL AS comm, '20' AS deptno FROM dual UNION ALL
SELECT '7499' AS empno, 'ALLEN' AS ename, 'SALESMAN' AS job, '7698' AS mgr, '1981-02-20' AS hiredate, '1600' AS sal, '300' AS comm, '30' AS deptno FROM dual UNION ALL
SELECT '7521' AS empno, 'WARD' AS ename, 'SALESMAN' AS job, '7698' AS mgr, '1981-02-22' AS hiredate, '1250' AS sal, '500' AS comm, '30' AS deptno FROM dual UNION ALL
SELECT '7654' AS empno, 'MARTIN' AS ename, 'SALESMAN' AS job, '7698' AS mgr, '1981-09-28' AS hiredate, '1250' AS sal, '1400' AS comm, '30' AS deptno FROM dual UNION ALL
SELECT '7844' AS empno, 'TURNER' AS ename, 'SALESMAN' AS job, '7698' AS mgr, '1981-09-08' AS hiredate, '1500' AS sal, '1250' AS comm, '30' AS deptno FROM dual UNION ALL
SELECT '7877' AS empno, 'JAMES' AS ename, 'SALESMAN' AS job, '7698' AS mgr, '1981-07-21' AS hiredate, '1500' AS sal, '500' AS comm, '30' AS deptno FROM dual UNION ALL
SELECT '7900' AS empno, 'SCOTT' AS ename, 'SALESMAN' AS job, '7698' AS mgr, '1981-12-03' AS hiredate, '1500' AS sal, '600' AS comm, '30' AS deptno FROM dual UNION ALL
SELECT '7934' AS empno, 'MILLER' AS ename, 'SALESMAN' AS job, '7698' AS mgr, '1981-09-15' AS hiredate, '1250' AS sal, '700' AS comm, '30' AS deptno FROM dual UNION ALL
SELECT '7954' AS empno, 'SMITH' AS ename, 'SALESMAN' AS job, '7698' AS mgr, '1981-08-01' AS hiredate, '1600' AS sal, '400' AS comm, '30' AS deptno FROM dual UNION ALL
SELECT '7876' AS empno, 'ADAMS' AS ename, 'CLERK' AS job, '7788' AS mgr, '1987-05-23' AS hiredate, '1100' AS sal, NULL AS comm, '20' AS deptno FROM dual UNION ALL
SELECT '7900' AS empno, 'JAMES' AS ename, 'CLERK' AS job, '7698' AS mgr, '1981-12-03' AS hiredate, '950' AS sal, NULL AS comm, '30' AS deptno FROM dual UNION ALL
SELECT '7934' AS empno, 'MILLER' AS ename, 'CLERK' AS job, '7782' AS mgr, '1982-01-23' AS hiredate, '1300' AS sal, NULL AS comm, '10' AS deptno FROM dual
)
-- 이후 쿼리
-- SELECT * FROM emp;
SELECT job,sal,
ROW_NUMBER() OVER (PARTITION BY job ORDER BY sal desc) AS row_num,
RANK() OVER (PARTITION BY job ORDER BY sal desc) AS rankk,
DENSE_RANK () OVER (PARTITION BY job ORDER BY sal desc) AS dense_rankk
FROM emp;
😺결과
- PARTITION BY job으로 인해 job별로 순위를 독립적으로 매긴다.
- job이 SALESMAN일 때를 보면 sal 같을경우, RANK()는 순서를 건너뛰고(1->3), DENSE_RANK()는 순서를 건너 뛰지 않고 연속적(1->2)으로 부여됨을 알 수 있다.
냐홍