[SQL]분석 함수 활용

분석 함수 활용

OVER

SELECT COLUMN, {AVG|SUM|...}(COLUMN) OVER({PARTITION BY|ORDER BY COLUMN ROWS BETWEEN UNBOUNDED PREDEDING AND CURRENT ROW})
FROM TABLE
WHERE CONDITIONS;

의 형식으로 작성한다.(형식을 잘못 작성하였으니, 아래 예시를 보자)

구문	설명
PARTITION BY	해당 컬럼을 묶어준다(GROUP BY)
ORDER BY	해당 컬럼을 정렬해준다.

아래 예시를 보며 이해하도록 하자.

아래 예시는 PARTITION BY를 사용한 것이다.

SELECT EMPNO, ENAME, SAL, DEPTNO, AVG(SAL)
FROM EMP;
--이 경우 에러가 나지 않게 하는 방법은 여러가지가 있다.
 
--첫번째
SELECT EMPNO, ENAME, SAL, DEPTNO,
      (SELECT AVG(SAL)
       FROM EMP
       WHERE DEPTNO = E.DEPTNO)
FROM EMP E;
 
--두번째
SELECT E.EMPNO, E.ENAME, E.SAL, E.DEPTNO, A.AVG_SAL  
FROM EMP E 
JOIN (SELECT DEPTNO, AVG(SAL) AS AVG_SAL 
      FROM EMP 
      GROUP BY DEPTNO) A 
  ON E.DEPTNO = A.DEPTNO ;
  
--여기서 좀더 쉽게하는 방법이 바로 분석함수이다.
SELECT EMPNO, ENAME, SAL, DEPTNO,
       AVG(SAL) OVER(PARTITION BY DEPTNO)
FROM EMP;

아래 예시는 ORDER BY를 사용한 예시이다.

--분석함수 사용X
SELECT a.empno, a.ename, a.sal, (SELECT SUM(sal)
FROM emp
WHERE empno <= a.empno) AS TOTAL
FROM emp a
ORDER BY a.empno ;
 
--분석함수 사용
SELECT empno, ename, sal,
SUM(sal) OVER(ORDER BY empno ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) AS TOTAL
FROM emp ;

*OVER 사용시 주의할 점으로는 쿼리문은 기본적인 쿼리문에서 FROM -> WHERE -> SELECT 순으로 이어지므로 WHERE절의 조건이 먼저 걸리고 컬럼 속성의 함수가 적용된다는 것을 생각해야한다.

LAG / LEAD

지정된 개수의 이전, 이후 행의 값을 가져오고, WINDOWING 절을 지정하지 못하며 NULL 값을 지정할 수 있다 즉 NVL이 불필요하다.

다음 예시를 참고하자

--부서 사원을 이름순으로 정렬하여 검색을 하되 이전, 다음 로우의 급여를 함께 표시하라
SELECT empno, ename, sal,
LAG (sal,1,0) over ( order by ename ) prev_sal,
LEAD (sal,1,0) over ( order by ename ) next_sal
FROM emp
WHERE deptno = 30 ;

LISTAGG

다음 예시를 참고하자.

--EMP 테이블에서 부서별 사원 이름을 검색
SELECT deptno,
       LISTAGG(ename,',') WITHIN GROUP (ORDER BY ename) AS employee
FROM emp
GROUP BY deptno ;

RATIO_TO_REPORT

WINDOW 영역의 합계 내에서 현재 값이 차지하는 백분율. 별도의 WINDOWING 절을 설정하는 것은 불가능하다.

--EMP 테이블에서 DEPTNO, SAL, ENAME 컬럼을 기준으로 정렬된 정보를 검색하면서 부서별 누적된 급여, 부서별 급여의 백분율, 전체 사원의 급여 합계에서의 백분율을 검색
SELECT deptno, ename, sal,
       SUM(sal) OVER(PARTITION BY deptno ORDER BY sal, ename) AS cum_sal,
       ROUND(100*RATIO_TO_REPORT(sal) OVER(PARTITION BY deptno),1) AS pct_dept,
       ROUND(100*RATIO_TO_REPORT(sal) OVER(),1) AS pct_overall
FROM emp ;

이외에도 여러가지가 있으니 잘 찾아서 사용하자.

SQL을 배운지 얼마 되지 않아 잘못된 내용이 있을 수 있습니다. 틀린 내용이있다면, 댓글로 달아주세요.