[SQL] 원도우 함수(WINDOW FUNCTION)2

앞의 글에서 윈도우 함수에 구조와 기능 순위 관련 함수와 집계 함수에 대해 정리했습니다. 

관련 내용을 보실 분들은 아래 링크로 이동해주세요

 

https://data-gongbu.tistory.com/17

[SQL] 원도우 함수(WINDOW FUNCTION)1

이어서 행 순서 관련 함수와 비율 관련 함수를 정리하겠습니다. 

---

3. 행 순서 관련 함수

• FRIST_VALUE(): 파티션에서 가장 처음에 나오는 값을 구함/  MIN 함수 결과와 동일
• LAST_VALUE(): 파티션에서 가장 나중에 나오는 값을 구함/ MAX함수 결과와 동일
• LAG(): 파티션에서 이전 행을 가져옴/ DEFAULT = 1
• LEAD(): 파티션에서 이후 행을 가져옴/ DEFAULT = 1

 

 

 

FRIST_VALUE

• 파티션에서 가장 처음에 나오는 값을 구함

부서별 직원들을 연봉이 높은 순으로 정렬하고, 파티션 내에서 가장 먼저 나오는 값을 출력해보겠습니다.

SELECT  deptno, ename, sal,
        FIRST_VALUE(ename) OVER (PARTITION BY deptno ORDER BY sal DESC
        ROWS UNBOUNDED PRECEDING) AS DEPT_A
    FROM emp;

 

 

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_A
    FROM emp;

 

 

여기서 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING은 현재 행을 포함해서 파티션 내의 마지막 행까지의 범위를 지정하는 것입니다. 사용하지 않은 경우와 한번 비교해보겠습니다.

 

SELECT deptno, ename, sal, 
      LAST_VALUE(ename) over (PARTITION BY deptno ORDER BY sal DESC) AS DEPT_A
    FROM emp;

 

파티션내의 결과 값이 계속해서 변하는 걸 확인할 수 있습니다.

DEPTNO=10인 경우를 생각해 보면 구하고자 하는 것은 월급이 가장 작은 사람인데 내림차순으로 정렬했기 때문에 다음에 불러오는 데이터가 자동으로 이전 데이터보다 낮은 값이 됩니다. 이런 갱신 과정을 피하려면 ROWS를 지정할 필요가 있습니다.

 

 

 

LAG

• 파티션에서 이전 행을 가져옴

직원들을 입사일자가 빠른 기준으로 정렬하고, 본인보다 입사일자가 한 명 앞선 사원의 급여를 본인의 급여와 함께 출력해 보겠습니다.

SELECT ename, hiredate, sal, 
        LAG(sal) over (ORDER BY hiredate asc) AS PREV_SAL
    FROM emp;

 

LAG함수는3개의 인수가 있습니다.

LAG(sal, 2, 0)을 가지고 설명하면, sal은 가져올 변수, 2는 몇 번째 앞의 행을 가져올 것인지 결정한 것(디폴트는 1), 0은 가져올 데이터가 없으면 NULL을 반환하는데 이 NULL값을 대체하고 싶은 값에 해당합니다. 

 

SELECT ename, hiredate, sal,
    LAG(sal, 2, 0) OVER (ORDER BY hiredate ASC) AS PREV_SAL2
    FROM emp;

 

LEAD

• 파티션에서 이후 행을 가져옴

 

직원들을 입사일자가 빠른 기준으로 정렬하고, 바로 다음에 입사한 사원의 입사일자를 출력해보겠습니다. 

SELECT ename, hiredate, 
        LEAD(hiredatE) OVER (ORDER BY hiredate ASC) AS NEXT
    FROM emp;

LEAD 또한 3개의 인수가 있습니다.

인수 설정 시 주의해야 할 점은 세 번째 인수를 원 데이터의 포맷과 동일한 포맷으로 설정해야 한다는 것입니다.위의 예제와 마찬가지로 NULL을 0으로 하겠다고 해서 코드를 실행하면 오류가 발생합니다.원 데이터 포맷이 숫자면 숫자, 날짜면 날짜 형식으로 지정해 준 경우에만 코드가 실행됩니다. 

SELECT ENAME, HIREDATE,
    LEAD(HIREDATE, 2, SYSDATE) OVER (ORDER BY HIREDATE ASC) AS NEXT2
FROM EMP;

 

---

4. 비율 관련 함수

• CUME_DIST(): 파티션 전체 건수에서 현재 행보다 작거나 같은 건수에 대한 누적 백분율 조회
• PERCENT_RANK(): 파티션에서 제일 먼저 나온 것을 0, 제일 늦게 나온 것을 1로 하며 값이 아닌 행 순서별 백분율을 구함
• NTILE(): 파티션별로 전체 건수를 ARGUMENT값으로 N등분한 결과 조회
• RATIO_TO_REPORT(): 파티션 내에 전체 SUM에 대한 형 별 컬럼값의 백분율을 소수점으로 조회

 

 

CUME_DIST

• 파티션 전체 건수에서 현재 행보다 작거나 같은 건수에 대한 누적 백분율 조회

 

같은 부서 소속 사원들의 집합에서 본인의 급여가 누적 순서상 몇 번째 위치에 있는지 0~1 사이의 값으로 출력해보겠습니다.

SELECT deptno, ename, sal,
    CUME_DIST() OVER (PARTITION BY deptno ORDER BY sal DESC) AS CUME_DIST
FROM emp;

 

PERCENT_RANK

• 파티션에서 제일 먼저 나온것을 0, 제일 늦게 나온 것을 1로 하며 값이 아닌 행 순서별 백분율을 구함

같은 부서 소속 사원들의 집합에서 본인의 급여가 순서상 몇 번째 위치해 있는지 0과 1 사이의 값으로 출력해보겠습니다.

SELECT deptno, ename, sal,
    PERCENT_RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) AS PERCENT_R
FROM emp;

 

 

NTILE

• 파티션별로 전체 건수를 ARGUMENT값으로 N 등분한 결과 조회

전체 사원을 급여가 높은 순서로 정렬하고, 급여 기준으로 4개의 그룹으로 분류해보겠습니다.

SELECT ename, sal,
    NTILE(4) OVER (ORDER BY sal DESC) AS N_4
FROM emp;

NTILE(4)는 총 14명의 사람들을 4등분 한다는 것입니다.

14를 4로 나누면 몫이 4, 나머지가 2가 되는데 나머지 두 명을 앞에부터 할당하게 됩니다.

즉 4 - 4 - 3 - 3으로 구분된 것을 확인할 수 있습니다. 

 

 

RATIO_TO_REPORT  

• : 파티션 내에 전체 SUM에 대한 형 별 컬럼값의 백분율을 소수점으로 조회

SALESMAN을 대상으로, 전체 급여에서 본인이 차지하는 비율을 구해보겠습니다.

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

결과값은 0~1 사이이며 개별 비율의 합은 항상 1입니다.