29. 중첩 그룹함수 / 분석함수

 

그룹 함수는 2LEVEL 까지 중첩해서 사용할 수 있다. MSSQL은 이마저도 불가능하다.

 

그룹 함수를 중첩

SELECT MAX(SUM(SAL))
  FROM EMP
GROUP BY DEPTNO;
--==>> 10875        

 

RANK(), DENSE_RANK()

→ ORACLE 9i 부터 적용, MSSQL 2005부터 적용

하위 버전에서는 RANK() 나 DENSE_RANK()를 사용할 수 없다

만약 이전 버전에서 순위를 구하려면 다음과 같이 해야 한다.

만약 급여 순위를 구하고자 한다면 해당 사원의 급여보다 더 큰 값이 몇 개인지를 확인한 후 확인한 값에 +1을 추가 연산해 주면 그 값이 곧 해당 사원의 급여 등수가 된다.

SELECT *
  FROM EMP;
-- 급여 확인

-- SMITH 급여 등수 확인
SELECT COUNT(*) + 1
FROM EMP
WHERE SAL > 800; -- SMITH의 급여
--==>> 14 -- SMITH의 급여 등수

-- ALLEN 급여 등수 확인
SELECT COUNT(*) + 1
FROM EMP
WHERE SAL > 1600;   -- ALLEN의 급여
--==>> 7            -- ALLEN의 급여 등수

 

서브 상관 쿼리(상관 서브 쿼리)

메인 쿼리가 있는데 테이블의 컬럼이 서브 쿼리의 조건절(WHERE절, HAVING절) 에 사용되는 경우 이 쿼리문을 서브 상관 쿼리(상관 서브 쿼리)라고 부른다.

SELECT ENAME "사원명", SAL "급여"
     , (SELECT COUNT(*) + 1
				  FROM EMP
         WHERE SAL > E.SAL) "급여등수"
  FROM EMP E;
*--==>>
/**
SMITH	   800	14
ALLEN	  1600	7
WARD	  1250	10
JONES	  2975	4
MARTIN	1250	10
BLAKE	  2850	5
CLARK  	2450	6
SCOTT	  3000	2
KING	  5000	1
TURNER	1500	8
ADAMS  	1100	12
JAMES  	 950	13
FORD	  3000	2
MILLER	1300	9
*/

 

RANK()함수를 사용하지 않고 서브상관쿼리를 활용해 EMP 테이블을 대상으로 사원명, 급여, 부서번호, 부서내급여등수, 전체급여등수 항목을 조회한다.

SELECT ENAME "사원명", SAL "급여", DEPTNO "부서번호"
     , (SELECT COUNT(*) + 1
          FROM EMP
         WHERE SAL > E.SAL) "전체급여등수"
     , (SELECT COUNT(*) + 1
          FROM EMP
         WHERE SAL > E.SAL
           AND DEPTNO = E.DEPTNO) "부서내급여등수"
  FROM EMP E;
--==>>
/*
SMITH	   800	20	14	5
ALLEN	  1600	30	 7	2
WARD	  1250	30	10	4
JONES	  2975	20	 4	3
MARTIN	1250	30	10	4
BLAKE	  2850	30	 5	1
CLARK	  2450	10	 6	2
SCOTT	  3000	20	 2	1
KING  	5000	10	 1	1
TURNER	1500	30	 8	3
ADAMS	  1100	20	12	4
JAMES	   950	30	13	6
FORD	  3000	20	 2	1
MILLER	1300	10	 9	3
*/

 

 

서브쿼리문제