[Oracle] WINDOW FUNCTION 윈도우 함수

WINDOW FUNCTION

-- 윈도우 함수에는 OVER 문구가 필수 포함된다.

 

SELECT * FROM SCOTT.EMP;

 

SELECT * FROM SCOTT.DEPT;

 

 

---

 

그룹 내 순위 함수

1. RANK

– 순위 구하기

SELECT JOB, ENAME, SAL,
    RANK() OVER (ORDER BY SAL DESC) ALL_RANK,
    RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK
FROM EMP;

 

SELECT JOB, ENAME, SAL,
    RANK() OVER (ORDER BY SAL DESC) ALL_RANK
FROM EMP;

SELECT JOB, ENAME, SAL,
    RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK
FROM EMP;

 

2. DENSE_RANK

SELECT JOB, ENAME, SAL,
    RANK() OVER (ORDER BY SAL DESC) ALL_RANK,
    DENSE_RANK() OVER (ORDER BY SAL DESC) DENSE_RANK
FROM EMP;

 

3. ROW_NUMBER

– 동일한 값이라도 고유한 순위를 부여한다

SELECT JOB, ENAME, SAL,
    RANK() OVER (ORDER BY SAL DESC) RANK,
    ROW_NUMBER() OVER (ORDER BY SAL DESC) ROW_NUMBER
FROM EMP;

 

---

 

일반 집계 함수

1. SUM

– 파티션별 윈도우의 합 구하기

SELECT MGR, ENAME, SAL,
    SUM(SAL) OVER (PARTITION BY MGR) MGR_SUM
FROM EMP;

SELECT MGR, ENAME, SAL,
    SUM(SAL) OVER (PARTITION BY MGR
                    ORDER BY SAL
                    RANGE UNBOUNDED PRECEDING) AS MGR_SUM
FROM EMP;                    

 

2. MAX

SELECT MGR, ENAME, SAL,
    MAX(SAL) OVER (PARTITION BY MGR) AS MGR_MAX
FROM EMP; 

SELECT MGR, ENAME, SAL
    FROM (SELECT MGR, ENAME, SAL,
                MAX(SAL) OVER (PARTITION BY MGR) AS IV_MAX_SAL
                FROM EMP)
WHERE SAL = IV_MAX_SAL;

 

3. MIN

SELECT MGR, ENAME, HIREDATE, SAL,
    MIN(SAL) OVER (PARTITION BY MGR ORDER BY HIREDATE) AS MGR_MIN
FROM EMP;

 

4. AVG

-- EMP 테이블에서 같은 매니저를 두고 있는 사원들의 평균 SALARY

-- 조건 : 같은 매니저 내에서 자기 바로 앞의 사번과 바로 뒤의 사번인 직원만을 대상으로 한다.

SELECT MGR, ENAME, HIREDATE, SAL,
    ROUND (AVG(SAL) OVER (PARTITION BY MGR ORDER BY HIREDATE ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)) AS MGR_AVG
FROM EMP;
/* ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING : 현재 행을 기준으로 파티션 내의 앞의 한건, 현재 행 뒤의 한 건을 범위로 지정 */

SELECT MGR, ENAME, HIREDATE, SAL,
    ROUND (AVG(SAL) OVER (PARTITION BY MGR ORDER BY HIREDATE)) AS MGR_AVG
FROM EMP;

 

5. COUNT

SELECT ENAME, SAL,
    COUNT(*) OVER (ORDER BY SAL RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING ) AS SIM_CNT
FROM EMP;
/* RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING : 현재 행의 SAL을 기준으로 SAL이 -50에서 +150의 범위에 포함된 모든 행을 범위로 지정 */

---

 

그룹 내 행 순서 함수

1. FIRST_VALUE

– 파티션별 윈도우에서 가장 먼저 나온 값을 구한다.

SELECT DEPTNO, ENAME, SAL,
    FIRST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC ROWS UNBOUNDED PRECEDING) AS DEPT_RICH
FROM EMP;

SELECT DEPTNO, ENAME, SAL,
    FIRST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC, ENAME ASC ROWS UNBOUNDED PRECEDING) AS RICH_EMP
FROM EMP;

 

2. LAST_VALUE

– 파티션별 윈도우에서 가장 나중에 나온 값을 구한다

SELECT DEPTNO, ENAME, SAL,
    LAST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS DEPT_POOR
FROM EMP;

 

3. LAG

– 파티션별 윈도우에서 이전 몇 번째 행의 값을 가져올 수 있다.

SELECT ENAME, HIREDATE, SAL,
    LAG(SAL) OVER (ORDER BY HIREDATE) AS PREV_SAL
FROM EMP
WHERE JOB = 'SALESMAN';

SELECT ENAME, HIREDATE, SAL,
    LAG(SAL, 2, 0) OVER (ORDER BY HIREDATE) AS PREV_SAL
FROM EMP
WHERE JOB = 'SALESMAN';

• LAG(X, Y, Z)

    X: 대상 COLUMN명

    Y: 몇 번째 앞의 행을 가져올 지 결정 (DEFAULT : 1)

    Z: 가져올 데이터가 없을 경우 NULL 값이 들어오는데, 대신 바꾸어줄 값 지정 가능

 

4. LEAD

– 파티션별 윈도우에서 이후 몇 번째 행의 값을 가져올 수 있다.

SELECT ENAME, HIREDATE,
    LEAD(HIREDATE, 1) OVER (ORDER BY HIREDATE) AS NEXTHIRED
FROM EMP;    

• LEAD(X, Y, Z)

    X: 대상 COLUMN명

    Y: 몇 번째 후의 행을 가져올 지 결정 (DEFAULT : 1)

    Z: 가져올 데이터가 없을 경우 (특히, 파티션 마지막 행의 경우) NULL 값이 들어오는데, 대신 바꾸어줄 값 지정 가능

 

---

 

그룹 내 비율 함수

1. RATIO_TO_REPORT

– 파티션 내 전체 SUM(칼럼)값에 대한 행별 칼럼 값의 백분율을 소수점으로 구할 수 있다. 차지하는 비율

SELECT ENAME, SAL,
    ROUND(RATIO_TO_REPORT(SAL) OVER (), 2) AS R_R
FROM EMP
WHERE JOB = 'SALESMAN';

 

2. PERCENT_RANK

– 파티션별 윈도우에서 제일 먼저 나오는 것을 0으로, 제일 늦게 나오는 것을 1로 하여 값이 아닌 행의 순서별 백분율을 구한다. 순서를 백분율으로 매기는 것과 유사.

SELECT DEPTNO, ENAME, SAL,
    PERCENT_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS P_R
FROM EMP;    

 

3. CUME_DIST

– 파티션별 윈도우의 전체 건수에서 현재 행보다 작거나 같은 건수에 대한 누적 백분율을 구한다. N등분

SELECT DEPTNO, ENAME, SAL,
    CUME_DIST() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS CUME_DIST
FROM EMP;

 

4. NTILE

– 파티션별 전체 건수를 ARGUMENT 값으로 N등분한 결과를 구할 수 있다.

-- NTILE(4) : (예시) 전체 팀원을 4개의 조로 나누어라

SELECT ENAME, SAL,
    NTILE(4) OVER (ORDER BY SAL DESC) AS QUAR_TILE
FROM EMP;