OracleSQL - 중첩 그룹함수 / 분석함수

중첩 함수

오라클에서는 그룹함수를 2번까지 중첩해서 사용할 수 있다. 

(MSSQL은 이마저도 불가능)

 

다음과 같은 정보를 담고 있는 EMP 테이블이 있다고 가정하자.

 

그리고 EMP 테이블에서 모든 직원들의 급여를 부서별로(DEPTNO) 합계를 내려고 한다면, 쿼리문은 다음과 같다. 

 

SELECT SUM(SAL)
FROM EMP
GROUP BY DEPTNO;

그렇다면 이 부서별 급여 합계 중 가장 큰 값을 구하고 싶을때에는 어떻게 해야 할까? 최대값을 구하기 위해서는 함수 MAX() 를 사용해야 할 것이다. 그렇기 때문에 불가피하게 SUM() 함수와 함께 중첩하여 사용해야 한다.

 

SELECT MAX(SUM(SAL))
FROM EMP
GROUP BY DEPTNO;

 

RANK(), DENSE_RANK() 의 다른 사용법

오라클SQL에는 순위를 편하게 구할 수 있게 해주는 그룹함수 RANK(), DENSE_RANK()가 있다. 하지만 이 함수는 ORACLE 9i 부터 적용되었다. (MSSQL은 2005 부터) 즉, 하위 버전에서는 RANK()나 DENSE_RANK()를 사용할 수 있기 때문에 다른 사용법이 필요하다.

 

예를 들어 EMP 테이블에서 특정 직원의 급여(SAL) 순위를 구하고자 한다면, COUNT()를 사용하여 해당 사원의 급여보다 더 큰 값이 몇 개인지 확인한 후 확인한 숫자에서 +1 해주면 그 값이 해당 사원의 등수가 된다. 쿼리문으로 만들면 다음과 같다.

 

WHERE 조건절을 이용하여 순위를 구하고자 하는 테이블의 값보다 큰 애들을 먼저 세어주면 순위를 구할 수 있는 원리이다.  

 

1. SMITH 사원의 급여 순위

SELECT COUNT(*) + 1
FROM EMP
WHERE SAL > 800;         (800은 SMITH의 급여 데이터값)

--==>> 14   (SMITH의 급여 등수)


2. ALLEN 사원의 급여 순위

SELECT COUNT(*) + 1
FROM EMP
WHERE SAL > 1600      (1600은 ALLEN의 급여 데이터값)

--==>> 7   (ALLEN의 급여 등수)