[Oracle] RANK, DENSE_RANK, ROW_NUMBER, NTILE, LAG, LEAD

RANK() OVER, RANK() OVER(PARTITION BY 그룹),

 

SELECT EMPNO, ENAME, DEPTNO, SAL, 
RANK() OVER (ORDER BY SAL DESC) "RANK" FROM EMP;

사원번호, 이름, 부서번호, 급여, 급여가 많은 사원부터 순위 조회

 

 

 

사원번호, 이름, 부서번호, 급여, 부서 내에서 급여가 많은 사원부터 순위 조회

SELECT EMPNO, ENAME, DEPTNO, SAL,
RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) "RANK" FROM EMP;

 

DENSE_RANK() OVER, DENSE_RANK() OVER(PARTITION BY 그룹)

 

ORDER BY 절에 사용된 컬럼이나 표현식에 대하여 순위 부여,

RANK()와 달리 동일 순위 다음의 순위는 동일 순위의 수와 상관없이 1증가된 값을 반환 

위 RANK 결과를 DENSE_RANK와 비교

SELECT EMPNO, ENAME, DEPTNO, SAL, 
RANK() OVER (ORDER BY SAL DESC) "RANK" , 
DENSE_RANK() OVER (ORDER BY SAL DESC) "DRANK" 
FROM EMP;

SELECT EMPNO, ENAME, DEPTNO, SAL,
RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) "RANK",
DENSE_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) "DRANK" 
FROM EMP;

ROW_NUMBER

ROW_NUMBER 는 각 PARTITION내에서 ORDER BY 절에 의해 정렬된 순서로 유일한 값을 반환

위 RANK, DENSE_RANK와 비교

SELECT EMPNO, ENAME, DEPTNO, SAL, 
RANK() OVER (ORDER BY SAL DESC) "RANK" , 
DENSE_RANK() OVER (ORDER BY SAL DESC) "DRANK",
ROW_NUMBER() OVER (ORDER BY SAL DESC) "ROW_NUMBER"
FROM EMP;

SELECT EMPNO, ENAME, DEPTNO, SAL,
RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) "RANK",
DENSE_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) "DRANK",
ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) "ROW_NUMBER"
FROM EMP;

 

함수	결과	예시
RANK	중복 등수 존재, 중복이 있으면 연속 등수는 없음	1, 2, 2, 4, 5, 6, 7 ...
DENSE_RANK	중복 등수 존재, 중복이 있어도 연속 등수는 있음	1, 2, 2, 3, 4, 5, 6 ...
ROW_NUMBER	중복 등수 없음, 연속하는 등수 있음	1, 2, 3, 4, 5, 6, 7 ...

 

 

사원번호, 이름, 연봉, 입사일, 순번 조회

급여가 많은 순으로, 같은 급여를 받는 경우 입사일이 빠른 사람부터 앞 번호 부여

SELECT EMPNO, ENAME, SAL, HIREDATE,
ROW_NUMBER() OVER (ORDER BY SAL DESC, HIREDATE ASC) AS "순번"
FROM EMP;

 

 

NTILE(N) OVER()

PARTITION 을 BUCKET 이라 불리는 그룹별로 나누고, PARTITION 내의 각 ROW 등을 BUCKET에 배치하는 함수

각 BECKET에는 동일한 수의 ROW가 배치

14개의 로우를 3그룹으로 분리, 1~5, 6~10, 11~14 식으로 그룹핑

SELECT ENAME, SAL, NTILE(3) OVER (ORDER BY SAL DESC) GROUPING FROM EMP;

 

---

집계함수에 OVER() 추가로 ROW별 집계구하기 

SUM

SELECT ENAME, DEPTNO, SAL, 
SUM(SAL) OVER() "TOTAL_SUM",
SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY DEPTNO) "DEPT_SUM"
FROM EMP;

OVER(), OVER(PARITION BY) 사용으로 ROW마다 SUM값 출력가능

AVG, MAX

SELECT ENAME, JOB, SAL, 
ROUND(AVG(SAL) OVER(PARTITION BY JOB), 1) "JOB_AVG",
MAX(SAL) OVER(PARTITION BY JOB) "JOB_MAX"
FROM EMP;

 

ROWS 옵션 - ROW의 계산 범위를 지정

SUM(SAL) OVER(ORDER BY SAL ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) SUM,

- ROWS를 사용하기위해서는 앞단 ORDER BY가 필수

- 앞 1ROW 부터 뒤 1ROW까지의 3ROW 합계 구하기 
SUM(SAL) OVER(ORDER BY SAL ROWS UNBOUNDED PRECEDING) SUM2

- 누적 합계

SELECT ENAME, DEPTNO, SAL,
SUM(SAL) OVER(ORDER BY SAL ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) SUM,
SUM(SAL) OVER(ORDER BY SAL ROWS UNBOUNDED PRECEDING) SUM2
FROM EMP;

 

LAG (칼럼, 위 로우 이동 값, 로우가 없을경우 반환 값)

ASC 경우 기준 로우의 정렬 칼럼 값보다 작은 값을 갖는 로우,

DSC 경우 기준 로우의 정렬 칼럼 값보다 큰 값을 갖는 로우

SELECT ENAME, DEPTNO, SAL,
LAG(SAL, 1, 0) OVER(ORDER BY SAL) AS NEXT_SAL,
LAG(SAL, 1, SAL) OVER(ORDER BY SAL) AS SAL2
FROM EMP;

LEAD는 LAG와 반대로 아래의 ROW값 반환

SELECT ENAME, DEPTNO, SAL,
LEAD(SAL, 1, 0) OVER(ORDER BY SAL) AS NEXT_SAL,
LEAD(SAL, 1, SAL) OVER(ORDER BY SAL) AS SAL2
FROM EMP;