SCOTT 계정의 EMP 테이블에서 직종별 인원수를 확인해 보자
SELECT * FROM EMP;
COUNT : 입력된 컬럼에 대해 값이 존재하는 수를 반환한다. 컬럼의 값중 NULL 값은 포함하지 않으므로 주의해야 한다.
SELECT JOB, COUNT (SAL)
FROM EMP
GROUP BY JOB
ORDER BY JOB;
이번엔 같은 방식으로 부서번호 별 인원수를 확인하자
SELECT DEPTNO, COUNT(DEPTNO)
FROM EMP
GROUP BY DEPTNO
ORDER BY DEPTNO;
위의 두 쿼리문과 같이 GROUP BY에 의해 선택된 컬럼에 대하여, COUNT 함수가 적용이 된다. COUNT 함수를 사용 시에 주의할 점은 SELECT문에 포함되어 COLUMN이 GROUP BY에 포함이 되었는가이다. 만일 포함되어 있지 않다면 오류를 확인하게 되는데, 이는 결과물을 예상해 보면 간단하다.
10이라는 부서에 CLERK와 MANAGER 란 직위가 존재하고 이를 10이란 부서값을 가지는 COLUMN인 DEPTNO를 기준으로 GROUP BY 한다고 하자. 그리고 직위를 나타내는 JOB이란 COLUMN을 같이 표시한다면, DEPTNO를 GROUP 하면서 그에 따른 JOB에 포함된 두 값 - CLERK와 MANAGER - 역시 같은 RECORD에 표시해야 한다. 하지만, 별도의 명령이나 작업이 없이 DATA를 합칠 수 없으므로 이를 표시할 수 있는 방법이 없게 된다.
그렇다면 두 개 이상의 COLUMN을 GROUP BY 하려면 어떻게 해야 하는가. 부서번호별, 직종별 인원수를 구해보자
SELECT DEPTNO, JOB, COUNT(*)
FROM EMP
GROUP BY DEPTNO, JOB
ORDER BY 1, 2;
위와 같은 식으로 정리가 가능하다. 즉, 두 개의 값이 가지는 경우의 수에 따라 GROUP BY 한 후, 그룹함수를 적용하게 된다. 이러한 식에서 조건식을 이용하여 검색을 할 수도 있다. 부서중 10, 20 부서중에서 직위를 가진 직원의 수가 2명 이상인 경우를 출력하는 쿼리문이다.
SELECT DEPTNO, JOB, COUNT(*)
FROM EMP
GROUP BY DEPTNO, JOB
HAVING DEPTNO IN (10,20) AND COUNT(*) >1
ORDER BY 1, 2;
위와 같은 식으로 GROUP BY에 대한 조건절인 HAVING을 이용하여 그룹함수를 적용할 수 있다. 단, 주의해야 할 점은 그룹함수에 대한 조건은 HAVING 절에만 사용해야 한다는 것이다. 왜냐하면 그룹함수 자체가 GROUP BY에 의해 정의된 그룹에 대한 값을 가져오기 때문에 만일, WHERE절에 그룹함수를 사용한다면 정의되지 않은 값에 대한 함수의 사용을 지시하므로 명확하지 않기 때문이다.
# SELECT문의 처리 순서
SELECT문의 구문 처리 순서는 다음과 같다.
SELECT ------------ 4
FROM -------------- 1
WHERE ------------- 2
GROUP BY ---------- 3
HAVING ------------- 5
ORDER BY ---------- 6
즉, FROM 에서 DATA가 있는 TABLE을 선택하고, WHERE에서 필요한 조건을 걸러서, GROUP BY에서 그룹화를 한 후, SELECT 문에서 값을 보여주며, 그 값에서 HAVING으로 그룹함수를 포함한 조건을 처리한다. 이후 ORDER BY에 의해서 정렬이 된다.
#EMP TABLE의 값 중에서 1) SELECT * FROM EMP;와 2) SELECT EMPNO, ENAME FROM EMP; 두 쿼리문의 경우 CPU의 소요시간은 2)가 더 빠르나 둘의 메모리에 Load되는 용적은 같다. 반면, 3) SELECT * FROM EMP WHERE EMPNO = '1101'; 와 같이WHERE 문을 포함한 경우 메모리를 사용하는 양은 3)이 작다. WHERE의 조건에 맞지 않는 값의 경우는 메모리에 올리지 앟기 때문이다.
메모리에 적재되는 양의 단계는 위의 Select문의 순서에 의존성이 있다. SELECT문에서 처리되어 보이는 부분은 CPU에 의해 계산되어 요구된 부분만 보이지만, 그에 필요한 데이터는 RECORD를 기준으로 메모리에 적재된다. 이를 감안하면 위의 쿼리문을 조금 더 효율적이게 변형할 수 있다.
SELECT DEPTNO, JOB, COUNT(*)
FROM EMP
WHERE DEPTNO IN (10,20)
GROUP BY DEPTNO, JOB
HAVING COUNT(*) >1
ORDER BY 1, 2;
이전 구문과 다른 점은 WHERE 구문이 생겼다는 것이다. WHERE 절에서 DEPTNO에 관한 필요 DATA를 정리해 줌으로써 실제 메모리에 DEPTNO = 30인 경우는 올리지 않음으로 메모리의 절감 효과를 가져올 수 있다.
ROLLUP : 주어진 값-식에 대하여 GROUP BY 할 때, 그에 따른 요약 값을 포함한다.
MS-SQL에서의 WITHROLLUP WITHCUB 같이 요약값을 나타낸다.
EMP 테이블에서 입사년도 별 인원 수를 구해보자.
SELECT TO_CHAR(HIREDATE,'YYYY') "입사년도", COUNT(*)
FROM EMP
GROUP BY ROLLUP(TO_CHAR(HIREDATE,'YYYY'));
EMP테이블에 추가로 DATA를 넣고, 그 값에 대해 적용하는 쿼리문이다.
INSERT INTO EMP VALUES (8700,'한석규','CLERK',7499,NULL,2500,NULL,NULL);
INSERT INTO EMP VALUES (8800,'이석규','SALESMAN',7499,NULL,1500,NULL,NULL);
INSERT INTO EMP VALUES (8900,'서석규','CLERK',7499,NULL,1300,NULL,NULL);
SELECT
CASE GROUPING(TO_CHAR(HIREDATE,'YYYY'))
WHEN 0 THEN NVL(TO_CHAR(HIREDATE,'YYYY'),'미입력')
ELSE '전체인원수' END "입사년도", COUNT(*) "인원수"
FROM EMP
GROUP BY ROLLUP(TO_CHAR(HIREDATE,'YYYY'));
위의 쿼리문을 이용하여 TBL_SAWON 테이블에서 연령대별 인원수를 구해보자. 연령대는 나이의 앞자리만을 가져오면 되므로 TRUNC 함수를 사용하면 구할 수 있다.
SELECT TRUNC(TP.OLD,-1)||'대' "연령대" ,COUNT(TP.OLD) "인원수"
FROM (SELECT
TO_CHAR( EXTRACT(YEAR FROM SYSDATE) - (CASE SUBSTR(JUBUN,1,1)
WHEN '0' THEN
CASE WHEN SUBSTR(JUBUN,7,1) IN ('3','4')
THEN ('20'||SUBSTR(JUBUN,1,2))
ELSE ('19'||SUBSTR(JUBUN,1,2))
END
ELSE ('19'||SUBSTR(JUBUN,1,2))
END)+1) "OLD"
FROM TBL_SAWON ) TP
GROUP BY TRUNC(TP.OLD,-1)
ORDER BY TRUNC(TP.OLD,-1);
SELECT T."연령대" ||'대' "연령대",COUNT(*) "인원수"
FROM
(SELECT TRUNC(
CASE
WHEN SUBSTR(JUBUN,7,1) IN ('1','2') THEN
EXTRACT(YEAR FROM SYSDATE)-
(TO_NUMBER(SUBSTR(JUBUN,1,2)) + 1899)
ELSE
EXTRACT(YEAR FROM SYSDATE)-
(TO_NUMBER(SUBSTR(JUBUN,1,2)) + 1999)
END,-1)"연령대"
FROM TBL_SAWON)T
GROUP BY T."연령대"
ORDER BY 1;
서브쿼리를 이용하여 주민번호로부터 출생 년도를 추출해 낸다. CASE SUBSTR(JUBUN,1,1) 구문은 주민번호 중 년도의 앞자리를 받아온다. 만일 앞자리가 0이라면 1900년대 생과 2000년대생일 경우가 있을 수 있으므로, 주민번호 뒷자리의 앞자리를 다시 가져와 비교 - SUBSTR(JUBUN,7,1) IN ('3','4') - 를 하게 된다. 2000년대생부터는 남성은 3, 여성은 4의 번호를 가지므로, 이 값이 3 또는 4라면 주민번호 앞자리에서부터 가져온 출생년도 두자리 앞에 20을, 아닐경우 19를 붙이면 출생년도가 된다. 이를 현재날짜의 년도에서 뺀다면 현재 나이가 되고 한국나이를 감안하여 +1 한 것이다. 이 나이로부터 TRUCT의 두번째 값을 -1로 하게 되면 소숫점자리에서 역으로 1자리에서 자르게 된다. 즉 10의 자리 값이 남게 되는 것이다.
두번째 쿼리문의 경우는 나이를 구하는 순서에서 검토과정을 역으로 한것이다. 2000년대생의 경우에 주민번호 뒷자리의 첫자리가 3또는 4이므로, 그를 제외한 1,2인 경우는 1900년대 생이다. 이를 응용하여 주민번호 7번째 자리가 1,2인 경우 (TO_NUMBER(SUBSTR(JUBUN,1,2)) + 1899)를 하게 되는 것이다. 1900이 아니라 1899를 하는 식은 한국나이를 감안하여 변형한 결과이다.
SELECT CASE GROUPING (TRUNC(TP.OLD,-1))
WHEN 1 THEN '총계'
ELSE TRUNC(TP.OLD,-1)||'대'
END "연령대"
,COUNT(TP.OLD) "인원수"
FROM (SELECT
TO_CHAR( EXTRACT(YEAR FROM SYSDATE) - (CASE SUBSTR(JUBUN,1,1)
WHEN '0' THEN
CASE WHEN SUBSTR(JUBUN,7,1) IN ('3','4')
THEN ('20'||SUBSTR(JUBUN,1,2))
ELSE ('19'||SUBSTR(JUBUN,1,2))
END
ELSE ('19'||SUBSTR(JUBUN,1,2))
END)+1) "OLD"
FROM TBL_SAWON ) TP
GROUP BY ROLLUP(TRUNC(TP.OLD,-1))
ORDER BY TRUNC(TP.OLD,-1));
이 값을 가로로 배열해 보자. MS-SQL에서는 PIVOT이란 함수가 있어 적용이 쉽지만, ORACLE에서의 PIVOT은 상당히 번거롭다. PIVOT이 필요할 때에는 다음과 같은 방법으로 테이블을 만든 후, 이를 VIEW로 활용하여 쓸 때가 편리할 때가 많다.
SELECT '인원수' "연령대",
MAX(CASE "연령대" WHEN '0대' THEN "인원수" END) "0대",
MAX(CASE "연령대" WHEN '10대' THEN "인원수" END) "10대",
MAX(CASE "연령대" WHEN '30대' THEN "인원수" END) "30대",
MAX(CASE "연령대" WHEN '40대' THEN "인원수" END) "40대",
MAX(CASE "연령대" WHEN '총계' THEN "인원수" END) "총계"
FROM (SELECT CASE GROUPING (TRUNC(TP.OLD,-1))
WHEN 1 THEN '총계'
ELSE TRUNC(TP.OLD,-1)||'대'
END "연령대"
,COUNT(TP.OLD) "인원수"
FROM (SELECT
TO_CHAR( EXTRACT(YEAR FROM SYSDATE) - (CASE SUBSTR(JUBUN,1,1)
WHEN '0' THEN
CASE WHEN SUBSTR(JUBUN,7,1) IN ('3','4')
THEN ('20'||SUBSTR(JUBUN,1,2))
ELSE ('19'||SUBSTR(JUBUN,1,2))
END
ELSE ('19'||SUBSTR(JUBUN,1,2))
END)+1) "OLD"
FROM TBL_SAWON ) TP
GROUP BY ROLLUP(TRUNC(TP.OLD,-1))
ORDER BY TRUNC(TP.OLD,-1)) T1 ;
DECODE : CASE 문과 유사한 구조를 가진다. 4개의 값을 받아 첫번째는 비교할 기준 값을, 두번째는 비교값을 받고, 일치시에는 세번째 값을 반환하며, 일치하지 않을 때에는 4번째 값을 반환한다. 단, 일치하는 값만을 찾으므로 CASE WHEN '비교식' 보다는 제약을 받는다.
SELECT '인원수' "연령대",
MAX(DECODE("연령대",'0대',"인원수")) "0대",
MAX(DECODE("연령대",'10대',"인원수")) "10대",
MAX(DECODE("연령대",'30대',"인원수")) "30대",
MAX(DECODE("연령대",'40대',"인원수")) "40대",
MAX(DECODE("연령대",'총계',"인원수")) "총계"
FROM (SELECT CASE GROUPING (TRUNC(TP.OLD,-1))
WHEN 1 THEN '총계'
ELSE TRUNC(TP.OLD,-1)||'대'
END "연령대"
,COUNT(TP.OLD) "인원수"
FROM (SELECT
TO_CHAR( EXTRACT(YEAR FROM SYSDATE) - (CASE SUBSTR(JUBUN,1,1)
WHEN '0' THEN
CASE WHEN SUBSTR(JUBUN,7,1) IN ('3','4')
THEN ('20'||SUBSTR(JUBUN,1,2))
ELSE ('19'||SUBSTR(JUBUN,1,2))
END
ELSE ('19'||SUBSTR(JUBUN,1,2))
END)+1) "OLD"
FROM TBL_SAWON ) TP
GROUP BY ROLLUP(TRUNC(TP.OLD,-1))
ORDER BY TRUNC(TP.OLD,-1)) T1 ;
위의 쿼리문은 원하는 결과값을 얻은 후, 이를 서브쿼리로 이용하여 처리한 방법이다. 이와는 반대로 먼저 PIVOT처리를 하고 원하는 결과값을 얻을 수도 있다. 년도별 고용된 직원의 수를 구해보자.
SELECT COUNT(*)"전체인원수",
SUM(CASE WHEN TO_CHAR(HIREDATE,'YYYY') = '1980' THEN 1 END)"1980",
SUM(CASE WHEN TO_CHAR(HIREDATE,'YYYY') = '1981' THEN 1 END)"1981",
SUM(CASE WHEN TO_CHAR(HIREDATE,'YYYY') = '1982' THEN 1 END)"1982",
SUM(CASE WHEN TO_CHAR(HIREDATE,'YYYY') = '1987' THEN 1 END)"1987",
SUM(CASE WHEN HIREDATE IS NULL THEN 1 END)"미입력"
FROM EMP;
위의 쿼리문은 고용일의 년도를 기준으로 비교하여, 기준 값이 맞다면 1을, 아닐경우엔 - ELSE 문을 생략하였으므로 DEFAULT로 - NULL을 반환한다. 반환된 1의 값들을 더하여 총 합을 구하면 해당 년도에 고용된 직원의 수를 얻을 수 있다.
이제 부서별, 직업별 급여의 값을 각각 가로와 세로로 하도록 하여 나타내보도록 하자.
WITH 'ALIAS' AS ('QUERY1') 'QUERY2' : 서브쿼리를 나타내기 쉽게 하기 위해 사용하는 표현식이다. QUERY1을 ALIAS된 이름으로 별칭을 지정하고, 이를 WITH로 하여 다음에 나오는 QUERY2의 서브쿼리로 이용하도록 한다.
WITH T AS (SELECT JOB, DEPTNO, SUM(SAL) "SALARY"
FROM EMP
GROUP BY JOB, DEPTNO
)
SELECT JOB "직종",
NVL(LTRIM(TO_CHAR(SUM(CASE DEPTNO WHEN 10 THEN SALARY END),'$99,999')),' ') "10번 부서",
NVL(LTRIM(TO_CHAR(SUM(CASE DEPTNO WHEN 20 THEN SALARY END),'$99,999')),' ') "20번 부서",
NVL(LTRIM(TO_CHAR(SUM(CASE DEPTNO WHEN 30 THEN SALARY END),'$99,999')),' ') "30번 부서",
LTRIM(TO_CHAR(SUM(SALARY),'$99,999')) "전체"
FROM T
GROUP BY JOB;
우선 서브쿼리에서 필요한 데이터를 추출한다. 이때의 COLUMN은 JOB,DEPTNO,SUM(SAL)로 각각 직업과 부서별 급여의 합을 나타내게 된다. 이를 본쿼리문에서 반복적으로 불러오면 중복되는 값들이 가로로 나열하게 된다. 이때의 중복되는 값들을 CASE문을 이용하여 부서번호(DEPTNO)에 일치하는 것만을 값으로 가져오게 되며, 나머지의 경우는 NULL값을 가지게 된다. 전체 COLUMN은 본쿼리문에서 GROUP BY된 JOB을 기준으로 연산하게 되므로 부서에 상관없이 직종별 급여의 합을 나타내게 된다.
댓글 없음:
댓글 쓰기