[Oracle] 분석함수

# 분석함수 

- SELECT 절에만 쓰일 수 있음 

- 비즈니스 분야에서 자주 행해지는 여러가지 형태의 분석에 유용하게 활용될 수 있음 

- 각 윈도우별 집합 연산 수행한 결과를 리턴하며 JOIN, 프로그램의 오버헤드 줄임 

  

# 분석함수 구문 

- 구문: SELECT 분석함수(인자) OVER ([PARTITION BY 컬럼리스트] [ORDER BY 컬럼리스트] [WINDOWING절: ((ROWS|RANGE) (BETWEEN 시작위치 AND 끝위치|시작위치))])FROM 테이블명; 

• OVER : 분석함수임을 나타내는 키워드 
• PARTITION BY : 쿼리 결과를 지정한 컬럼 별로 그룹핑 (생략시 하나의 그룹), 계산 대상 그룹 지정 
• ORDER BY : PARTITION BY로 묶인 컬럼 그룹내에 있는 행의 검색 순서 매김, 계산 대상 그룹 정렬 
• WINDOWING 절 : 명시적으로 WINDOWING절 명령어를 쓰는 것이 아님. 분석 함수의 계산 대상 범위를 지정하며 ORDER BY절에 종속적 

  

# 분석함수 수행절차 

1. SELECT절에 지정된 분석함수, ORDER BY를 제외하고 결과 도출 (JOINS, WHERE, GROUP BY, HAVING 등) 
2. 1단계의 결과를 가지고 파티션(PARTITION)을 생성하고, 각 파티션의 행에 분석 함수(RANK 등)를 적용 
3. 최종 결과 (메인쿼리) ORDER BY 

  

# 분석함수 종류 

RANK()/DENSE_RANK() 

• RANK : 값의 그룹에서 값 순위 계산하는 함수로 반드시 OVER에 ORDER BY가 들어가야 함 
• DENSE_RANK : 동률 순위가 있을 경우 건너뛰지 않고 순위 매기는 함수로 반드시 OVER에 ORDER BY가 들어가야 함 

-- ORDER BY 
SELECT empno, ename, deptno, sal, 
       RANK() OVER (ORDER BY sal DESC) rk, 
       DENSE_RANK() OVER (ORDER BY sal DESC) drk 
FROM   emp; 
 

-- PARTITION BY + ORDER BY 
SELECT empno, ename, deptno, sal, 
       RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) rk, 
       DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) drk 
FROM   emp; 

SELECT deptno, job, SUM(sal), 
       RANK() OVER (PARTITION BY deptno ORDER BY SUM(sal) DESC) rk 
FROM   emp 
GROUP BY deptno, job;

  

ROW_NUMBER() 

- ROW_NUMBER : 값의 그룹에서 값 순위 계산하는 함수로 반드시 OVER에 ORDER BY가 들어가야 함 

SELECT empno, ename, deptno, ROWNUM, sal, 
       ROW_NUMBER() OVER (ORDER BY sal DESC) "r_n by sal" 
FROM   emp 
ORDER BY ROWNUM;

  

NTILE() 

- NTILE : 인자로 입력된 개수에 따라 등급을 분류하는 함수로 반드시 OVER에 ORDER BY가 들어가야 함 

SELECT empno, ename, deptno, sal, 
       NTILE(3) OVER (ORDER BY sal DESC) "ntile by sal" 
FROM   emp; 

-- 상중하로 표시 
SELECT empno, ename, deptno, sal, 
       CASE NTILE(3) OVER (ORDER BY sal DESC) WHEN 1 THEN '상' 
                                              WHEN 2 THEN '중' 
                                              ELSE '하' 
       END 
FROM   emp;

  

RATIO_TO_REPORT() 

- RATIO_TO_REPORT() : 해당 구간에서 인자값으로 입력된 컬럼별 차지하는 비율을 리턴함 

SELECT empno, ename, deptno, sal, 
       ROUND((100 *  
       (RATIO_TO_REPORT(sal) OVER ()) 
       ), 2) rtr 
FROM   emp 
ORDER BY rtr DESC; 

SELECT job, SUM(sal), 
       (RATIO_TO_REPORT(SUM(sal)) OVER ()) * 100 "%" 
FROM   emp 
GROUP BY job;

  

LAG(), LEAD() 

- 구문 : LEAD/LAG(컬럼, 행의 위치, 없을 시 표시할 값)  

• LAG() : 파티션 내 offset에 지정된 값만큼 상대적으로 상위 위치한 로우 참조하는 함수로 반드시 OVER에 ORDER BY가 들어가야 함 
• LEAD() : 파티션 내 offset에 지정된 값만큼 상대적으로 하위 위치한 로우 참조하는 함수로 반드시 OVER에 ORDER BY가 들어가야 함 

SELECT empno, ename, deptno, hiredate, sal,  
       LEAD(sal, 2, 0) OVER (ORDER BY hiredate) "lead1", -- 2개 뒤에 있는 sal값, 값이 없다면 0 표시 
       LAG(sal, 2, 0) OVER (ORDER BY hiredate) "lag1" -- 2개 앞에 있는 sal값, 값이 없다면 0 표시 
FROM   emp 
ORDER BY hiredate;

  

WINDOW 집계 유형 

- WINDOW별로 그룹함수(SUM, AVG 등)를 적용해서 분석용 목적의 그룹함수로 쓰는 것 

-- [각 사원에 대해 총평균급여, 속한 부서별평균급여, 최소급여, 최대급여 표시] 
-- ① 그룹함수를 분석용으로 사용하지 않는 경우 메인 쿼리 한 행마다 모든 서브쿼리문이 수행됨 
SELECT empno, ename, deptno, sal, 
      (SELECT AVG(sal) FROM emp) tot_avg_sal, 
      (SELECT AVG(sal)  
       FROM emp e2 
       WHERE e2.deptno = e1.deptno) dept_avg_sal, 
      (SELECT MIN(sal) FROM emp) min_sal, 
      (SELECT MAX(sal) FROM emp) max_sal 
FROM   emp e1; 

-- ② WINDOW 집계 함수 사용 시 PARTITION BY에 의해 ①과 다르게 정렬됨 
SELECT empno, ename, deptno, sal, 
       AVG(sal) OVER () "tot_avg_sal", 
       AVG(sal) OVER (PARTITION BY deptno) "dept_avg_sal", 
       MIN(sal) OVER () "tot_min_sal", 
       MAX(sal) OVER () "tot_max_sal" 
FROM   emp e;

  

(ROWS|RANGE) (BETWEEN 시작일 AND 종료일|시작일) 사용 

• ROWS: 물리 위치, 중복값 허용
• RANGE: 논리 위치, 중복값 불허 
• 시작일, 종료일: (CURRENT ROW), (UNBOUNDED|숫자)(FOLLOWING|PRECEDING)  

-- 부서별, 연도별, 입사 사원수 누적 합계 출력 
SELECT deptno, TO_CHAR(hiredate, 'yyyy'), COUNT(*), 
       -- 누적의 형태는 다양하게 표현 가능 
       -- 기본 형태: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 
       --SUM(COUNT(*)) OVER (ORDER BY TO_CHAR(hiredate, 'yyyy')), 
       SUM(COUNT(*)) OVER (PARTITION BY deptno ORDER BY TO_CHAR(hiredate, 'yyyy') 
                           ROWS BETWEEN UNBOUNDED PRECEDING  
                                AND     CURRENT ROW) "누적사원수" 
FROM   emp 
GROUP BY deptno, TO_CHAR(hiredate, 'yyyy'); 

SELECT deptno, TO_CHAR(hiredate, 'yyyy'), COUNT(*), 
       SUM(COUNT(*)) OVER (PARTITION BY deptno ORDER BY TO_CHAR(hiredate, 'yyyy') 
                           ROWS BETWEEN UNBOUNDED PRECEDING  
                                AND     UNBOUNDED FOLLOWING) "누적사원수" 
FROM   emp 
GROUP BY deptno, TO_CHAR(hiredate, 'yyyy'); 

SELECT deptno, TO_CHAR(hiredate, 'yyyy'), COUNT(*), 
       SUM(COUNT(*)) OVER (PARTITION BY deptno ORDER BY TO_CHAR(hiredate, 'yyyy') 
                           ROWS BETWEEN 1 PRECEDING  -- 내 데이터 기준 한 개 앞에 
                                AND     1 FOLLOWING) -- 내 데이터 기준 한 개 뒤에  
                                "누적사원수" 
FROM   emp 
GROUP BY deptno, TO_CHAR(hiredate, 'yyyy');

  

FIRST_VALUE, LAST_VALUE 

• FIRST_VALUE : 윈도우에서 정렬된 값 중에서 첫번째 값 반환 
• LAST_VALUE :  윈도우에서 정렬된 값 중에서 마지막 값 반환

SELECT ename, sal, hiredate, 
       FIRST_VALUE(hiredate) OVER (ORDER BY sal 
                                   ROWS BETWEEN UNBOUNDED PRECEDING 
                                        AND     UNBOUNDED FOLLOWING) "최저급여자입사일", 
       FIRST_VALUE(ename) OVER (ORDER BY sal 
                               ROWS BETWEEN UNBOUNDED PRECEDING 
                                    AND     UNBOUNDED FOLLOWING) "최저급여자이름", 
       LAST_VALUE(hiredate) OVER (ORDER BY sal 
                                  ROWS BETWEEN UNBOUNDED PRECEDING 
                                       AND     UNBOUNDED FOLLOWING) "최고급여자입사일", 
       LAST_VALUE(ename) OVER (ORDER BY sal 
                               ROWS BETWEEN UNBOUNDED PRECEDING 
                                    AND     UNBOUNDED FOLLOWING) "최고급여자이름" 
FROM   emp 
WHERE  deptno = 30;