[ORACLE] Window 함수, 분석함수

6. 분석함수(윈도우함수)

💡 윈도우 함수를 사용하면 행간 연산이 가능해짐 ⇒ 일반적으로 풀리지 않는 쿼리를 간단하게 만들수 있다.

⭐ 모든 DBMS가 윈도우 함수를 지원하진 않음 ‼️

SELECT window_function(arg)
OVER ([PARTITION BY columns] [ORDER BY columns] [WINDOWING])

• PARTITION BY 컬럼코드 (GROUP BY) : 행들을 묶을 그룹
• ORDER BY 컬럼코드 [DESC | ASC] : 묶여진 행들간의 순서
• (WINDOWING) : 파티션안에서 특정 행들에 대해서만 연산을 하고 싶을때 범위 지정

순위 관련 함수

RANK()

💡 동일값에 대해서 동일순위 부여 →1등 2명 그 다음 3

DENSE_RANK()

💡 동일값에 대해서 동일순위 부여 →1등 2명 그 다음 2

ROW_NUMBER()

💡 동일 값이라도 별도의 순위 부여 → 중복없음

SELECT ename, sal, deptno,
RANK() OVER (PARTITION BY deptno ORDER BY sal) sal_rank,
DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal) sal_danse_rank,
ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal) sal_row_number
FROM emp;

집계 관련 함수

SUM, MIN, MAX, AVG, COUNT

--COUNT

SELECT empno,ename, emp.deptno , cnt
     FROM emp ,
     (SELECT deptno, COUNT(*) cnt
     FROM emp
     GROUP BY deptno) v_cnt
     WHERE v_cnt.deptno = emp.deptno
     ORDER BY deptno;

SELECT empno,ename,deptno,count(*) OVER (PARTITION BY deptno) cnt
FROM emp;

--AVG

SELECT empno,ename,sal, ROUND(AVG(sal) OVER (PARTITION BY deptno),2) avg
FROM emp;

그룹내 행 순서

💡 파티션별 위 아래 행의 특정 컬럼 값을 가져온다 (주식 / 환율 전일대비)

LAG

💡 이전행의 특정 컬럼값 가져옴

LEAD

💡 이후행의 특정 컬럼값 가져옴

-- 전체 사원의 급여순위가 자신보다 한단계낮은 사람의 급여값을 5번째로 컬럼으로 생성
--(급여가 같을경우 입사일자가 빠른사람이 우선순위가 높다)

SELECT empno, ename, hiredate, sal, LEAD(sal) OVER (ORDER BY sal DESC, hiredate) lead_Sal
FROM emp;

SELECT empno, ename, hiredate, sal,
 LAG(sal) OVER (ORDER BY sal DESC, hiredate) lag_Sal
FROM emp;

SELECT emp_sal_rn.empno, emp_sal_rn.ename, emp_sal_rn.hiredate, emp_sal_rn.job, emp_sal_rn.sal, sal_rn.sal
FROM (SELECT emp_sal.*, ROWNUM rn
            FROM  (SELECT empno, ename, hiredate, job, sal
                    FROM emp
                    ORDER BY sal DESC) emp_sal) emp_sal_rn,
        (SELECT ROWNUM+1 rn, sal
         FROM (SELECT sal
                FROM emp
                ORDER BY sal desc) ) sal_rn
WHERE emp_sal_rn.rn= sal_rn.rn(+)
ORDER BY emp_sal_rn.sal DESC;

WINDOWING

💡 파티션 내의 행들을 세부적으로 선별하는 범위를 기술

ROWS | RANGE BETWEEN UNBOUNDED PRECEDING[CURRENT ROW] AND UNBOUNDED FOLLOWING[CURRENT ROW]

• ROWS : 물리적인행
• RANGE : 논리적인 행 / 같은값을 하나로 본다

⭐WINDOWING 기본 설정값이 존재 : RANGE UBOUNDED PRECEDING

SELECT empno, ename, deptno,sal,
  SUM(sal) OVER (PARTITION BY deptno ORDER BY sal ROWS BETWEEN  UNBOUNDED PRECEDING AND CURRENT ROW ) c_row,
  SUM(sal) OVER (PARTITION BY deptno ORDER BY sal RANGE BETWEEN  UNBOUNDED PRECEDING AND CURRENT ROW ) c_range,
  SUM(sal) OVER (PARTITION BY deptno ORDER BY sal) c_default
FROM emp;

UNBOUNDED PRECEDING

💡 현재행을 기준으로 선행(이전)하는 모든 행들

SELECT empno, ename, sal , 
            SUM(sal) OVER (ORDER BY sal ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) c_sum
FROM emp
ORDER BY sal;

CURRENT ROW

💡 현재행

UNBOUNDED FOLLOWING

💡 현재행을 기준으로 이후 모든 행들

n PRECEDING, n FOLLOWING

💡 현재행을 기준으로 n칸 앞 / 뒤

SELECT empno, ename, sal , 
            SUM(sal) OVER (ORDER BY sal ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) c_sum2
FROM emp
ORDER BY sal;