[오라클] Grouping 과 Subquery

저번 시간에 Aggregation 을 했는데 이것은 집계함수라고도 하며 이 결과는 하나의 row만 남게 된다.

그러므로 Grouping을 해주어야 같이 쓸 수 있다.

# Grouping

이렇게 deptno로 grouping을 해주면 AVG를 쓰는데 문제가 없다.

 SELECT job, avg(sal), max(sal), min(sal) FROM emp GROUP BY job

직업별 평균 월급과 최대월급 최소월급을 구하고 싶다.

저 뒤에 group by를 써주지 않는다면 이렇게 sql에러가 날 것이다.

ORA-00937: 단일 그룹의 그룹 함수가 아닙니다

00937. 00000 -  "not a single-group group function"

select deptno, count(*) from emp group by deptno

각 부서에 속해있는 수를 구하고 싶다.

# Stock Clerk 직종의 사원들의 부서별 인원수를 출력하시오.

SELECT deptno, COUNT(*) "Number" FROM emp WHERE job = 'CLERK' GROUP BY deptno;

# 직종별 최고 급여를 급여가 많은 직종부터 출력하시오. 

SELECT job, max(sal) FROM emp GROUP BY job ORDER BY max(sal) DESC;

## Having 절

Aggregation 결과에 대해 다시 condition을 검사할 때

예를들어 평균 월급이 2000이상인 부서는?

select deptno, avg(sal) from emp where avg(sal) > 2000 group by deptno

ORA-00934: 그룹 함수는 허가되지 않습니다

00934. 00000 -  "group function is not allowed here"

???

where절은 aggregation 이전, having절은 aggregation 이후를 필터링한다.

그래서 나온 것이 having절이다. having절은 group by에 참여한 컬럼이나 aggregate 함수만 사용가능하다.

# 직종별 최고 급여를 급여가 많은 직종부터 출력하시오. (단, 직원이 2명 이상인 경우만) 

SELECT job, max(sal) FROM emp GROUP BY job HAVING count(sal) >= 2 ORDER BY max(sal) DESC; 

# 부서별 인원수를 부서명과 부서번호와 같이 나타내시오

SELECT d.deptno, d.dname, count(e.ename) cnt from emp e, dept d where e.deptno = d.deptno;

얼핏 보면 맞는거같다. dept를 d로 emp를 e로 테이블별 alias를 주고 deptno(부서번호), dname(부서명), e.ename으로 카운트세고.

ORA-00937: 단일 그룹의 그룹 함수가 아닙니다

00937. 00000 -  "not a single-group group function"

?? 또 등장했다.

생각해보면 d.deptno는 모든 row의 deptno를 뱉는 것인데 count로 갯수세는 것은 하나의 row를 반환한다고 했다. 그런데 d.deptno는 여러 row여서 서로 아구가 맞지 않는다. group by 로 정리를 해줘야한다. 즉, deptno와 dname을 grouping을 해주는 것이다.

SELECT d.deptno, d.dname, count(e.ename) cnt from emp e, dept d where e.deptno = d.deptno group by d.deptno;

# deptno를 직원들이 없더라도 다 출력하고 싶다.

SELECT d.deptno, d.dname, count(e.ename) cnt from emp e, dept d where e.deptno (+) = d.deptno group by d.deptno, d.dname

# ROLLUP & CUBE 

Rollup은 각각의 그룹핑된 것의 소계가 나오고 마지막에 총 소계가 나온다. 무슨 헛소리인가?

select deptno, job, sum(sal) from emp group by deptno, job

이렇게하면 당연히 밑처럼 나온다.

근데 rollup을 쓰면

select deptno, job, sum(sal) from emp group by rollup(deptno, job)

각 depno별로 한 번씩 뽑아주고 최종적으로 뽑는다. ㅎㅎ이런 기능이있다니 부서별로 매출이 얼마고 직업별로 얼만지 파악하기에 매우 편하게 쓸 수 있겠다.

# CUBE

아 위에 rollup 진짜 좋은거 같은데 난 부서별로도 보고 싶고 JOB 직업별로도 보고싶어. 그럼 union으로 rollup을 두 번 조져야 된다.

귀찮아서 존재하는 것이 cube이다. 실행결과를 보고 이해해보자.

select deptno, job, sum(sal) from emp group by cube(deptno, job)

roll up 에서 더 확장되었다. 진짜 없는게 없다.

## Subquery

서브쿼리란 하나의 SQL 질의문 속에 다른 SQL 질의문이 포함되어 있는 상태이다.

SCOTT보다 월급이 많은 사람의 이름은??

먼저 해야할 것은 스캇의 월급을 구해야 한다. select sal from emp where ename='SCOTT' 이것을 변수처럼 어디에 넣어서 비교했으면 좋겠다.

select ename from emp where sal > ( 위의 쿼리 ) 이런식으로 말이다.

하나씩 살펴보자.

# Single-Row Subquery

Subquery의 결과가 한 ROW인 경우

# 평균 급여보다 적게 받는 사원들을 출력하시오

SELECT ename, sal FROM emp WHERE sal < (SELECT AVG(sal)FROM emp); 

# Multi-Row query

서브쿼리의 결과가 둘 이상의 row일 경우이다.

그렇다면 멀티로우에 대한 연산을 사용해야한다(any,all,in.exist...)

SELECT ename, sal, deptno

FROM emp

WHERE ename IN (SELECT MIN(ename)

FROM emp GROUP BY deptno);

# Correlated Query

outer query와 inner query가 서로 연관되어 있음

# 각 부서별로 최고급여를 받는 사원을 출력하시오

먼저 부서별 최고급여를 찾아야 한다.

(SELECT deptno, max(sal) FROM emp GROUP BY deptno); 

이러면 부서명과 최고 급여가 나올 것이다. 이것을 서브쿼리로 치고 메인쿼리에 넣는다.

SELECT deptno, empno, ename, sal FROM emp WHERE (deptno,sal) IN (SELECT deptno, max(sal) FROM emp GROUP BY deptno);  => Nested Query

이렇게 해도 된다.

테이블 alias는 그냥 테이블 시작알파벳으로 하였다.

select  e.deptno, e.empno, e.ename, e.sal from emp e, (select deptno, max(sal) as mxsal from emp group by deptno) m

where e.deptno = m.deptno and e.sal=m.mxsal    => Inline View

세 번째 방법

SELECT deptno, empno, ename, sal FROM emp e WHERE e.sal = (SELECT max(sal) FROM emp WHERE deptno = e.deptno);    => Correlated Query

# Top - K Query (ORACLE)

어떤 조건의 상위 몇 명 찾기. 이러한 쿼리에 쓰인다.

rownum이라고 예약여거 있는데 이것은 질의의 결과에 가상으로 부여되는 Oracle의 Pseudo Column 이다.

81년도에 입사한 사람 중 월급이 가장 많은 3명은 누구인가?

SELECT rownum, ename, sal FROM (SELECT * FROM emp WHERE hiredate like '81%' ORDER BY sal DESC) WHERE rownum < 4;

급여를 많이 받는 순서대로 상위 3명을 출력하시오

 SELECT rownum, ename, sal FROM (SELECT * FROM emp ORDER BY sal DESC) WHERE rownum < 4;

 

# Rank 

SELECT sal, ename, RANK() OVER (ORDER BY sal DESC) AS rank, DENSE_RANK() OVER (ORDER BY sal DESC) AS dense_rank, ROW_NUMBER() OVER (ORDER BY sal DESC) AS row_number, rownum AS "rownum“ FROM emp;

그냥 rank는 공동 2위는 같이 2라고 표시되고 다음은 해당 공동인원들의 숫자를 건너뛰어 4부터 시작한다. dense rank는 그것을 고려하여 다음은 3으로나온다.

도움이 되셨다면 공감눌러주시고 궁금한 점 댓글 남겨주세요~