sql문은 이걸 거쳐서 실행이 된다.
Parsing
Syntax 검사 → 문장 검사(잘못 썼으면 다음 단계로 안 간다.)
Semantics 검사 → 테이블이 실제로 있는지 확인, 테이블 안에 컬럼이 있는지 확인하는 단계.(없으면 담단계로 안감.)
실행 계획(Exectuion Plan) → 이 문장을 어떻게 실행할지 계획하는 단계.(컴파일하는 거랑 비슷함.)
ㄴ 똑같은 문장이면 기존에 실행했던 문장인지 비교함.(해쉬코드로 저장된 놈이랑 비교해서 확인함.)
실행
패치
그룹함수와 GROUP BY
그룹함수
• 여러 행에 컬럼을 기반으로 계산된 값을 제공하는 다중행 함수를 의미한다.
ㄴ 테이블에 있는 행을 합쳐서 데이터를 뽑아냄.(행의 개수랑 상관없음.)(값이 하나만 나옴.)
ㄴ 평균값, 표준편차 등등
‐그룹함수에서 NULL은 무시된다.(널이 있으면 null 빼고 계산한다.)
ㄴ 돈 계산할 때 null 들어가면 계산이 개판된다.
‐GROUP BY 절 없이 일반컬럼과 같이 기술될 수 없다.
그룹함수를 이용한 검색
• 사원의 평균급여를 검색한다.
• 월간지급급여총액과년간지급급여총액을 검색한다.
SQL> SELECT AVG(sal) 평균급여, TO_CHAR(AVG(sal),'$999,999') 평균급여
2 FROM emp;
SQL> SELECT SUM(sal), TO_CHAR(SUM(sal),'$999,999') 월간_급여_총액,
2 SUM(sal)*12, TO_CHAR(SUM(sal)*12,'$999,999') 년급여_총액
3 FROM emp;
그룹함수와 NULL
• 사원에게 지급된 보너스지급 총액과 보너스평균을 검색한다.
SQL> SELECT SUM(comm) 총액, TO_CHAR(AVG(comm),'$999,999') 보너스_평균
2 FROM emp;
SQL> SELECT SUM(comm) 총액,
2 COUNT(comm) 수령인원, TO_CHAR(AVG(comm),'999,999') 평균,
3 COUNT(*) 전체인원, TO_CHAR(AVG(NVL(comm,0)),'999,999') 평균
4 FROM emp;
그룹함수와 다중행 연산자
• 10번 부서원들보다 급여가 높은 사원을 검색한다.
SQL> SELECT eno 사번, ename 이름, dno 부서번호
2 FROM emp
3 WHERE sal > (SELECT MAX(sal)
4 FROM emp
5 WHERE dno = '10');
SQL> SELECT eno 사번, ename 이름, dno 부서번호
2 FROM emp
3 WHERE sal > ALL(SELECT sal
4 FROM emp
5 WHERE dno = '10');
‐ ANY나 ALL 같은 다중행 서브쿼리와 동일하지만 가독성은 훨씬 높다.
GROUP BY
ㄴ 카데날리티가 안 맞으면 출력을 못한다. 카데날리티를 일치시키는 작업.
ㄴ 일반 컬럼이 그룹바이에 있어야 한다. 안 그럼 수행을 안 함.
SELECT ...
FROM 테이블 ...
WHERE 조건 ...
GROUP BY 컬럼
ORDER BY 정렬_대상 ... ;
‐ GROUP BY 절은 컬럼의 동일한 값을 바탕으로 소그룹에 대한 통계정보를 검색한다. SELECT 절에 그룹함수와 같이 쓰인 일반칼럼이 GROUP BY 절에 기술되지 않으면 카디널리티(cardinality)가 일치하지 않아 ‘ORA-00937: not a single-group group function’ 에러가 발생한다.
‐ 결괏값이정렬 되길 원한다면 반드시 ORDER BY절을 추가한다.
GROUP BY 절을 이용한 검색
• 업무별 평균급여, 평균연봉을 검색한다.
• 부서별 평균급여, 평균연봉을 검색한다.
SQL> SELECT job 업무, TO_CHAR(AVG(sal),'999,999') 평균_급여,
2 TO_CHAR(AVG(sal*12+NVL(comm,0)), '999,999') 평균_연봉
3 FROM emp
4 GROUP BY job;
SQL> SELECT d.dno 부서번호, dname 부서,TO_CHAR(AVG(sal),'999,999') 평균_급여,
2 TO_CHAR(AVG(sal*12+NVL(comm,0)),'999,999') 평균_연봉
3 FROM dept d, emp e
4 WHERE d.dno = e.dno 5 GROUP BY d.dno, dname → 반드시 2개 다 써야 함
6 ORDER BY d.dno;
카디널리티(Cardinality)와 정렬
카디널리티
‐ 값의 개수(기수)를의미한다.
‐ SELECT 문의 각 컬럼은 반드시 카디널리티가 같아야 한다.
‐ '그룹 함수와 같이 검색되는 모든 컬럼은 반드시GROUP BY절에 기술한다.' 이것이 원칙이다.
． GROUP BY 절에 기술되어도 그룹함수와 같이 검색되지 않는 경우도 있다.
． 카디널리티가 일치하는 것이 중요하다.
• 정렬
‐ GROUP BY 절은 정렬을 수행한다.
‐ 오라클은 해시알고리즘을 이용함으로 정렬되지 않는다.
‐ 정렬된 결과를 위해서는 반드시 ORDER BY 절을 이용한다.
GROUP BY 절을 이용한 검색
• 각 부서별 급여평균이 가장 높은 값과 낮은 값을 검색한다.
SQL> SELECT dno 부서번호,
2 MAX(AVG(sal)) 최대평균, MIN(AVG(sal)) 최소평균
3 FROM emp
4 GROUP BY dno;
SQL> SELECT MAX(AVG(sal)) 최대평균, MIN(AVG(sal)) 최소평균
2 FROM emp
3 GROUP BY dno;
- 위에 거는 카디날리티가 맞지 않아서 실행이 안됨.
• 각 부서별 최소급여를 받는 사원의 정보를 검색한다.
SQL> SELECT d.dno, dname, eno, ename, sal
2 FROM emp e, dept d
3 WHERE d.dno=e.dno
4 AND (d.dno, sal) IN (SELECT dno, MIN(sal)
5 FROM emp
6 GROUP BY dno)
7 ORDER BY d.dno;
HAVING
SELECT ...
FROM 테이블 ...
WHERE 조건 ...
GROUP BY 컬럼
HAVING [그룹조건] ...
ORDER BY 정렬_대상 ... ;
‐ HAVING : 조건 중에 그룹함수를 포함하는 조건을 기술한다.
． 그룹핑된 조건을 기술한다.
‐ HAVING절은 그룹 된 결과에 대한 조건이므로 가능한 GROUP BY절뒤에 기술하는 것 을권한다.
GROUP BY절에 따른 그룹함수결괏값의 변화
SQL> SELECT ROUND(AVG(sal))
2 FROM emp
3 GROUP BY dno;
SQL> SELECT ROUND(AVG(sal))
2 FROM emp
3 GROUP BY job;
‐ 동일 SELECT 절(그룹함수)에서도 GROUP BY 절의그룹대상에 따라 결과가 달라진 다.
‐ HAVING절은 그룹 된 대상에 대한 조건이다.
． SELECT 절과는 무관하다.
HAVING 절을 이용한 그룹검색
• 부서별 급여평균이 3천 달러 미만인부서의 평균급여를 검색한다.
SQL> SELECT dno 부서번호, TO_CHAR(AVG(sal),'$999,999') 평균급여
2 FROM emp
3 GROUP BY dno
4 HAVING AVG(sal) < 3000;
• 부서별 급여평균이 3천 달러 미만인부서의 최대급여를 검색한다.
SQL> SELECT dno 부서번호, MAX(sal) 최대급여
2 FROM emp
3 GROUP BY dno
4 HAVING AVG(sal) < 3000;
HAVING의 조건
• HAVING절의 조건을 확인한다.
SQL> SELECT dno 부서번호, COUNT() 인원수
2 FROM emp
3 GROUP BY dno
4 HAVING job != '개발';
SQL> SELECT dno 부서번호, COUNT() 인원수
2 FROM emp
3 GROUP BY dno
4 HAVING dno != '10';
‐ 일반조건은 WHERE 절에 기술한다.
‐ 그룹 된 칼럼(GROUP BY 절에 기술된)에 대한조건은 HAVING절에 기술할 수 있다.
． 권하지 않는다.
． 일반조건: WHERE
． 그룹함수가 포함된 조건: HAVING
HAVING 절을 이용한 검색
• 부서 중 가장 급여를 많이 받는 부서를 검색한다.
SQL> SELECT d.dno, dname, AVG(sal)
2 FROM dept d, emp e
3 WHERE d.dno = e.dno
4 GROUP BY d.dno, dname
5 HAVING AVG(sal) = (SELECT MAX(AVG(sal))
6 FROM emp
7 GROUP BY dno);