윈도우 함수(Window Function)

✨ WINDOW FUNCTION?

기존에 inline view를 이용해 복잡한 형태로 SQL문을 작성해야 하던 것을 부분적으로나마 정의하기 쉽도록 만든 함수로, 복잡한 프로그램을 하나의 문장으로 해결이 가능하다. 분석 함수(ANALYTIC FUNCTION) / 순위 함수(RANK FUNCTION)으로도 알려져 있는 윈도우 함수는 데이터웨어하우스에서 발전했다.

WINDOW FUNCTION은 기존에 사용하던 집계 함수를 포함하여 전용으로 새로 만들어진 것도 있으며, 다른 함수와 달리 중첩 사용이 불가능하지만 서브쿼리에서는 사용이 가능하다.

* 윈도우 함수에는 ORVER 문구가 반드시 필요하다.

 

WINDOW FUNCTION의 종류는 크게 다섯 개로 분류할 수 있는데, 벤더별로 지원 함수에 차이가 있다.

1. 순위 (RANK) 함수 - RANK, DENSE_RANK, FOW_NUMBER

2. 집계 함수 - SUM, MAX, MIN, AVG, COUNT

3. 순서 관련 함수 - FIRST_VALUE, LAST_VALUE, LAG, LEAD

4. 비율 관련 함수 - CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_PERORT

5. 통계 관련 함수

 

---

 

 

 

🪡 그룹 내 순위 함수

 

1. RANK

RANK 함수는 ORDER BY를 포함한 쿼리 문에서 특정 컬럼에 대한 순위를 구한다. 이때, 특정 범위 내에서 구할 수도 있고, 전체 데이터에 대한 순위를 구할 수도 있다. 또한, 동일 값에 대해서는 동일 순위를 부여한다.

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

 

이와 같은 구문을 작성하면

 

 

다음과 같은 결과가 나오는데, 여기에서 본다면 OVER (ORDER BY SAL DESC) ALL_RANK 에서 ORDER BY와 RANK() OVER(PARTITION BY JOB ORDER BY SAL DESC) 에서 사용한 ORDER BY의 조건 자체가 중복이 되기 때문에 첫번째 선언한 OVER (ORDER BY SAL DESC) ALL_RANK 의 정렬 조건으로만 정렬이 된 것을 확인할 수 있다.

즉, JOB을 기준으로 PARTITION을 한 것과, 그냥 한 것의 차이는 있지만 결국 ORDER BY의 주체는 SAL이기 때문에 먼저 선언한 것만 적용이 된 것이다. 그렇기 때문에 후자에 해당하는 것만 작성을 한다면 ALL_RANK를 제외한 

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

 

의 형식으로 작성을 하면

 

 

의 형식으로 JAB RANK 기준 정렬을 볼 수 있다.

 

 

2. DENSE_RANK

RANK 함수와 흡사하지만, 동일 순위를 하나의 등수로 통합한다는 점이 다르다.

예를 들어, RANK에서 동일 순위 1위가 2명이 있다고 하면, 그 다음 등수는 3으로 매겨진다. 그러나 DENSE_RANK에서는 같은 등수는 한 건으로 취급되므로 1위가 2명이 있다고 하더라도, 2위는 2가 된다.

SELECT JOB, ENAME, SAL, DENSE_RANK() OVER(ORDER BY SAL DESC) SAL_RANK
FROM SCOTT.EMP;

 

의 결과를 살펴보면 

 

가 되는 것을 확인 가능하다. 2위가 두명임에도 불구하고, 4가 아닌 3이 되는 것을 볼 수 있다.

 

 

 

3. ROW_NUMBER 

이전에 살펴본 RANK와 DENSE_RANK가 동일한 값이면 동일 순위를 부여했다면, ROW_NUMBER는 동일 순위라도 고유 순위를 부여한다. 

 

SELECT JOB, ENAME, SAL, ROW_NUMBER() OVER(ORDER BY SAL DESC) SAL_RANK
FROM SCOTT.EMP;

 

이와 같은 절에서 결과는

 

 

이렇게 출력이 된다. 보면, SAL이 같은 결과도 고유 순서가 매겨지는 것을 볼 수 있다. 이것에 대한 고유 순서는 ORDER BY 절에 추가 조건을 주는 것으로 정의가 가능하다.

 

 

 

🧶 일반 집계 함수

 

1. SUM

SUM은 값을 더하는 함수로, 파티션별로의 합도 구할 수 있다.  파티션별로 합을 구하는 구문은 다음과 같다

SELECT JOB, SAL, SUM(SAL) OVER(PARTITION BY JOB) JOB_SUM
FROM SCOTT.EMP;

 

 

음, 이렇게 보니까, SAL은 필요가 없을 거 같고 JOB NAME 기준으로 합계는 하나만 출력되면 되므로 구문을 조금 고쳐보도록 하자

어차피 따로 조건이 있는 것도, GROUP BY를 사용한 것도 아니기 때문에 DISTINCT를 사용하여 정리했다. 

SELECT DISTINCT JOB, SUM(SAL) OVER(PARTITION BY JOB) JOB_SUM
FROM SCOTT.EMP;

 

 

 

 

2. MAX

MAX 함수는 최대값을 가질 수 있는 함수이다. 파티션 별 최대값을 구할 수 있다.

같은  JOB 내에서 제일 많은 급여를 받는 사원의 정보를 보고자 할 때 다음과 같이 쓸 수 있다.

 

SELECT ENAME, JOB, MAX(SAL) OVER(PARTITION BY JOB) JOB_MAX
FROM SCOTT.EMP;

 

 

 

 

3. MIN

MAX와 반대로 파티션별 최소값을 가져올 수 있다.

MAX의 조건과 같은데, 대신 최대값이 아닌 최솟값을 가진 사원을 찾는다면 다음과 같은 쿼리문으로 실행할 수 있다.

SELECT ENAME, JOB, MIN(SAL) OVER(PARTITION BY JOB) SAL_MIN
FROM SCOTT.EMP;

 

 

 

 

4. AVG

AVG는 평균값을 구하는 함수이다. 파티션별 조건에 맞는 통계를 가질 수 있으며 다음과 같은 형식으로 구할 수 있다.

예제가 간단하기 때문에 조금 추가하자면

같은 JOB을 기준으로 하여 평균 급여를 구하고, 평균 급여보다 높은 사원의 정보와 해당 직급의 평균 급여, 평균 급여보다 얼마나 더 많이 받는지에 대해 출력해보도록 할 것이다.

 

SELECT ENAME, JOB, SAL_AVG, SAL, SAL - SAL_AVG AS SAL_MIN
FROM (SELECT ENAME, JOB, ROUND(AVG(SAL) OVER(PARTITION BY JOB)) SAL_AVG, SAL
		FROM SCOTT.EMP) EMP
WHERE SAL > SAL_AVG
ORDER BY SAL_MIN DESC;

 

 

 

 

5. COUNT 

COUNT는 너무 자주 쓰이기도 하고, 항상 쓰이는 함수이기도 하다. 이것 또한 파티션별 조건을 가지고 그룹 내의 통계를 만들 수 있다.

이전에 쓰였던 것과 비슷하게 JOB별 평균 급여를 잡고, JOB의 그룹에서 평균 급여보다 많이 받는 사람의 수를 계산하기 위해서는 다음과 같은 쿼리를 작성하면 된다. 

SELECT DISTINCT JOB, SAL_AVG, COUNT(*) OVER(PARTITION BY JOB) OVER_COUNT
FROM (SELECT ENAME, JOB, ROUND(AVG(SAL) OVER(PARTITION BY JOB)) SAL_AVG, SAL
		FROM SCOTT.EMP) EMP
WHERE SAL > SAL_AVG;

 

 

 

 

 

 

 

 

🏹 그룹 내 행 순서 함수

 

1. FIRST_VALUE

FIRST_VALUE는 파티션 내에서 가장 첫번째 값을 가져오는 것이다. 그룹핑을 하고 제일 첫번째 값만 가져오는 것과 동일한 결과를 가져온다. 그러나 이것은 공동 순위가 있을 경우 제일 첫번째로 처리된 값만 가져오기 때문에, 이와 같은 경우가 있을 때를 대비하여 정렬 조건을 가진 INLINE VIEW 를 사용하거나 OVER() 내에 ORDER BY 절에 새로운 조건을 추가해야 한다.

 

예를 들어, 같은 JOB 내에서 제일 많은 SAL을 받는 사람을 출력하고자 한다면 다음과 같다.

 

SELECT DISTINCT JOB, FIRST_VALUE(ENAME) OVER(PARTITION BY JOB ORDER BY SAL DESC) AS HIGH_SAL_EMP
FROM SCOTT.EMP;

 

 

 

 

2. LAST_VALUE

FIRST_VALUE와 반대로 가장 마지막 값을 가져온다. 위와 마찬가지로 공동을 허용하지 않고 제일 마지막에 나온 값을 처리하기 때문에 위와 같은 문제를 방지하고자 한다면 추가로 조건을 설정해야 한다.

 

같은 JOB 내에서 가장 적은 SAL을 받는 사람을 출력하고자 하면 다음과 같다.

 

SELECT JOB, ROW_SAL_EMP
FROM (
    SELECT 
        JOB, 
        LAST_VALUE(ENAME) OVER(PARTITION BY JOB ORDER BY SAL DESC) AS ROW_SAL_EMP,
        ROW_NUMBER() OVER(PARTITION BY JOB ORDER BY SAL ) AS rn
    FROM SCOTT.EMP
) ranked
WHERE rn = 1;

 

 

 

 

3. LAG

LAG는 파티션별 INDEX를 부여해 가져올 수 있는 함수이다. 위의 두개의 함수가 각각 첫번째와 마지막이라면, 여기에서는 선택이 가능하다. 이후가 아니라 이전의 값을 가져오며, 최대 세가지의 인자를 삽입할 수 있다.

(가져올 데이터, INDEX(현재 INDEX에서 얼마나 이전인지), NVL)

여기에서 두번째 인자의 DEFAULT값은 1이다.

 

입사일자가 빠른 기준으로 정렬된 상태에서 본인보다 입사일자가 두번째로 앞선 사원의 급여를 출력하고, 그런 사람이 없을 경우 0 을 출력하고자 하면 다음과 같다.

 

SELECT ENAME, HIREDATE, SAL, LAG(SAL, 2, 0) OVER(ORDER BY HIREDATE) AS LAG_TEST
FROM SCOTT.EMP;

 

 

 

 

4. READ 

LAG와 반대로 이후 X번째의 값을 가져올 수 있다. 위와 마찬가지이기 때문에 부연 설명은 생략하며 이전이 아닌 이후에 입사한 사람의 정보를 가져오면 다음과 같다

SELECT ENAME, HIREDATE, SAL, LEAD(SAL, 2, 0) OVER(ORDER BY HIREDATE) AS LEAD_TEST
FROM SCOTT.EMP;

 

 

 

 

 

🎁 그룹 내 비율 함수

 

 

1. RATIO_TO_REPORT

파티션 내 전체 합계에 대한 행별 칼럼 값의 백분율을 소수점으로 가져올 수 있다. 소수점으로 가져오기 때문에 값은 0보다 크고 1보다 작은 수가 된다.

각 부서 별로 직원의 급여가 전체 급여의 총합에서나오는 총합을 나타내는 예제를 표현하면 다음과 같다

SELECT 
    DEPTNO,
    ENAME,
    SAL,
    RATIO_TO_REPORT(SAL) OVER(PARTITION BY DEPTNO) AS SALARY_RATIO
FROM 
    SCOTT.EMP;

 

 

 

 

 

2. PRECENT_RANK

파티션별로 제일 먼저 나오는 것을 0, 제일 늦게 나오는 것을 1로 해서 값이 아니라 행으로 백분율을 구하는 함수이다.

마찬가지로 1 이하 0 이상의 범위를 가진다.

각 직급별로 하여 급여가 높은 순으로 랭크를 삽입하는 쿼리문과 결과는 다음과 같다.

 

SELECT 
    JOB, 
    ENAME,
    SAL,
    PERCENT_RANK() OVER(PARTITION BY JOB ORDER BY SAL DESC) AS PERCENT_RANK_SAL
FROM 
    SCOTT.EMP;

 

 

 

 

3. CUME_DIST

파티션별 건수에서 현재 가지고 있는 행보다 작거나 같은 건수에 대한 백분율을 가져온다. 누적으로 가져오는 것이 기 때문에, 총 5건이 있다면 각각 0.2씩을 가지게 되고, 첫번째는 0.2, 두번째는 0.4, 세번째는 0.6과 같이 누적된 백분율을 가지고 있으며 제일 마지막 값은 1의 값을 가진다.

 

각 부서별로 자신의 급여가 어느정도의 위치에 있는지 백분율로 나타내는 쿼리를 작성하면 다음과 같다.

 

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

 

 

 

 

 

4. NTILE

NTILE은 파티션별 전체 건수를 N등분한다. 분류는 쿼리 내에서 ORDER BY를 통해 순서를 정해 나눌 수 있으며, 나눈 것대로 그룹 분류를 할 수 있다.

급여에 따라서 세개의 그룹으로 분류하게 되면 다음과 같다.

SELECT 
    ENAME,
    SAL,
    NTILE(3) OVER(ORDER BY SAL) AS SALARY_GROUP
FROM 
    SCOTT.EMP;