--■■■ 중첩 그룹함수 / 분석 함수 ■■■
-- 그룹 함수 2 LEVEL 까지 중첩해서 사용할 수 있다.
-- 이마저도 MS-SQL 은 불가능하다.
SELECT MAX(SUM(SAL))
FROM EMP
GROUP BY DEPTNO;
--===>> 10875
-- RANK()
-- DENS_RANK()
-- > 이 함수는요 함수 안에서 정렬기능 포함 리소스소모심함
-- 그런데 함수가 생긴지 얼마 안됨. ORCLA 9i 부터 적용되었고
-- MSSQL 2005 부터 적용...
-- ※ 하위 버전에서는 RANK() 나 DENSE_RANK() 를 사용할 수 없기 때문에
-- 이를 대체하여 연산을 수행할 수 있는 방법을 강구해야 한다.
-- 예를 들어, 급여의 순위를 구하고자 한다면...
SELECT ENAME, SAL, 1
FROM EMP;
/*
SMITH 800 1
ALLEN 1600 1
WARD 1250 1
JONES 2975 1
MARTIN 1250 1
BLAKE 2850 1
CLARK 2450 1
SCOTT 3000 1
KING 5000 1
TURNER 1500 1
ADAMS 1100 1
JAMES 950 1
FORD 3000 1
MILLER 1300 1*/
-- 해당 사원의 급여보다 더 큰 값이 몇 개인지 확인한 수
-- 그 확인한 숫자에 +1 을 추가 연산해주면 그것이 곧 등수가 된다.
SELECT COUNT(*) +1 "SMITH의 급여 등수"
FROM EMP
WHERE SAL > 800;
--==>> SMITH의 급여 등수 : 14
-- ※ 서브 상관 쿼리(상관 서브 쿼리)
-- 메인 쿼리에 있는 테이블의 컬럼이
-- 서브 쿼리의 조건절(WHERE , HAVING 절) 에 사용되는 경우
-- 우리는 이 쿼리문을 서브 상관 쿼리라고 한다.
SELECT ENAME "사원명", SAL "급여" , 1"급여등수"
FROM EMP;
SELECT ENAME "사원명", SAL "급여" , (1) "급여등수"
FROM EMP;
SELECT ENAME "사원명", SAL "급여" , ( SELECT COUNT(*) +1
FROM EMP
WHERE SAL > 800 ) "급여등수"
FROM EMP;
/*
SMITH 800 14
ALLEN 1600 14
WARD 1250 14
JONES 2975 14
MARTIN 1250 14
BLAKE 2850 14
CLARK 2450 14
SCOTT 3000 14
KING 5000 14
TURNER 1500 14
ADAMS 1100 14
JAMES 950 14
FORD 3000 14
MILLER 1300 14
*/
SELECT ENAME "사원명", SAL "급여"
, (SELECT COUNT(*) +1
FROM EMP E2
WHERE E2.SAL > E1.SAL ) "급여등수"
-- ★5000
FROM EMP E1
ORDER BY 3;
/*
KING ★5000 1
FORD 3000 2
SCOTT 3000 2
JONES 2975 4
BLAKE 2850 5
CLARK 2450 6
ALLEN 1600 7
TURNER 1500 8
MILLER 1300 9
WARD 1250 10
MARTIN 1250 10
ADAMS 1100 12
JAMES 950 13
SMITH 800 14*/
-- ● EMP 테이블을 대상으로
-- 사원명, 급여, 부서번호, 부서내급여등수, 전체급여등수 항목을 조회한다.
-- 단, RANK() 함수를 사용하지 않고, 서브 상관 쿼리를 활용할 수 있도록 한다.
SELECT *
FROM EMP;
SELECT ENAME "사원명", SAL "급여", DEPTNO "부서번호"
, 부서내급여등수
, 전체급여등수
FROM EMP;
SELECT *
FROM EMP;
/*
7369 SMITH CLERK 7902 80/12/17 800 20
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
7566 JONES MANAGER 7839 81/04/02 2975 20
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7782 CLARK MANAGER 7839 81/06/09 2450 10
7788 SCOTT ANALYST 7566 87/07/13 3000 20
7839 KING PRESIDENT 81/11/17 5000 10
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7876 ADAMS CLERK 7788 87/07/13 1100 20
7900 JAMES CLERK 7698 81/12/03 950 30
7902 FORD ANALYST 7566 81/12/03 3000 20
7934 MILLER CLERK 7782 82/01/23 1300 10
*/
SELECT ENAME "사원명", SAL "급여", DEPTNO "부서번호"
, (SELECT COUNT(*) +1
FROM EMP B
WHERE B.SAL > A.SAL ) "전체급여등수"
, (SELECT COUNT(*) +1
FROM EMP B
WHERE B.SAL > A.SAL AND B.DEPTNO = A.DEPTNO) "부서내급여등수"
FROM EMP A
ORDER BY DEPTNO;
/*
사원명 급여 부서번호 전체급여등수 부서내급여등수
CLARK 2450 10 6 2
KING 5000 10 1 1
MILLER 1300 10 9 3
JONES 2975 20 4 3
FORD 3000 20 2 1
ADAMS 1100 20 12 4
SMITH 800 20 14 5
SCOTT 3000 20 2 1
WARD 1250 30 10 4
TURNER 1500 30 8 3
ALLEN 1600 30 7 2
JAMES 950 30 13 6
BLAKE 2850 30 5 1
MARTIN 1250 30 10 4
*/
-- ● EMP 테이블을 대상으로 다음과 같이 조회할 수 있도록 쿼리문을 구성한다.
/*
---------------------------------------------------------------------------
사원명 부서번호 입사일 급여 부서내입사별급여누적
---------------------------------------------------------------------------
CLARK 10 1981-06-09 2450 2450
KING 10 1981-11-17 5000 7450
MILLER 10 1982-01-23 1300 8750
SMITH 20 1980-12-17 800 800
JONES 20 1981-04-02 2975 3775
:
:
---------------------------------------------------------------------------
*/
SELECT ENAME "사원명", DEPTNO "부서번호", HIREDATE "입사일", SAL "급여"
, (0) 부서내입사별급여누적
FROM EMP
ORDER BY 2,3;
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
--==>>Session이(가) 변경되었습니다.
/*
CLARK 10 1981-06-09 2450 0
KING 10 1981-11-17 5000 0
MILLER 10 1982-01-23 1300 0
SMITH 20 1980-12-17 800 0
JONES 20 1981-04-02 2975 0
FORD 20 1981-12-03 3000 0
SCOTT 20 1987-07-13 3000 0
ADAMS 20 1987-07-13 1100 0
ALLEN 30 1981-02-20 1600 0
WARD 30 1981-02-22 1250 0
BLAKE 30 1981-05-01 2850 0
TURNER 30 1981-09-08 1500 0
MARTIN 30 1981-09-28 1250 0
JAMES 30 1981-12-03 950 0*/
SELECT ENAME "사원명", DEPTNO "부서번호", HIREDATE "입사일", SAL "급여"
, (SELECT SUM(SAL)
FROM EMP B
WHERE A.DEPTNO = B.DEPTNO AND B.HIREDATE <= A.HIREDATE
) "부서내입사별급여누적"
FROM EMP A
ORDER BY 2,3;
/*
CLARK 10 1981-06-09 2450 2450
KING 10 1981-11-17 5000 7450
MILLER 10 1982-01-23 1300 8750
SMITH 20 1980-12-17 800 800
JONES 20 1981-04-02 2975 3775
FORD 20 1981-12-03 3000 6775
SCOTT 20 1987-07-13 3000 10875
ADAMS 20 1987-07-13 1100 10875
ALLEN 30 1981-02-20 1600 1600
WARD 30 1981-02-22 1250 2850
BLAKE 30 1981-05-01 2850 5700
TURNER 30 1981-09-08 1500 7200
MARTIN 30 1981-09-28 1250 8450
JAMES 30 1981-12-03 950 9400*/
-- ● TBL_EMP 테이블에서 입사한 사원의 수가 제일 많았을 때의
-- 입사 년월과 인원수를 조회할 수 있는 쿼리문을 구성한다.
/*
---------------------------------
입사년월 인원수
---------------------------------
XXXX-XX XX
---------------------------------
*/
SELECT TO_CHAR(HIREDATE, 'YYYY-MM') "입사년월"
,COUNT(*) "인원수"
FROM TBL_EMP
GROUP BY TO_CHAR(HIREDATE,'YYYY-MM')
HAVING COUNT(*)=(
SELECT MAX(COUNT(*))
FROM TBL_EMP
GROUP BY(TO_CHAR(HIREDATE,'YYYY-MM')));
--==>>2021-03 5