7.1 하나의 열에 출력 결과를 담는 다중행 함수
다중행함수란?
SELECT SAL
FROM EMP;
SELECT SUM(SAL)
FROM EMP;
이처럼 여러 행을 바탕으로 하나의 결과 값을 도출해내기 위한 함수이다.
기본적으로 다중행 함수를 사용한 select절에는 기본적을 여러 행이 결과로 나올 수 있는 열을 같이 사용 할 수 없다.
SELECT ENAME, SUM(SAL)
FROM EMP;
Ename은 여러행을 출력하고, SUM(SAL)은 하나의 행을 출력하기 때문에 같이 출력 될 수 없다.
SUM
가장 기본적인 합계를 구하는 함수이다.
- SUM((DISTINCT,ALL 선택) 합계를 구할 열이나 데이터)
COUNT
데이터 개수를 출력하는데 사용하는 함수
- COUNT((DISTINCT,ALL 선택) 개수를 구할 열이나 데이터)
MIN
입력 데이터중 최솟값을 반환하는 함수
- MIN((DISTINCT,ALL 선택) 최솟값을 구할 열이나 데이터)
MAX
입력 데이터중 최댓값을 반환하는 함수
- MAX((DISTINCT,ALL 선택) 최댓값을 구할 열이나 데이터)
7.2 결과 값을 원하는 열로 묶어 출력하는 GROUP BY
7.1의 다중행 함수들은 지정 테이블의 데이터를 가공해 하나의 값만 출력했다. 만약 여러 부서의 평균값을 구하려면 select문을 하나하나 다 써야한다.
SELECT AVG(SAL), '10' as DEPTNO FROM EMP WHERE DEPTNO = 10
UNION ALL
SELECT AVG(SAL), '20' as DEPTNO FROM EMP WHERE DEPTNO = 20
UNION ALL
SELECT AVG(SAL), '30' as DEPTNO FROM EMP WHERE DEPTNO = 30;
이런 방법의 쿼리는 귀찮을 뿐아니라 추가 삭제할 때 마다 sql을 수정해야 하기 때문에 바람직하지 않다.
앞의 쿼리를 group by절로 그룹화시키면,
SELECT AVG(SAL), DEPTNO
FROM EMP
GROUP BY DEPTNO;
이렇게 나타낼 수 있다.
GROUP BY절에는 여러개의 열을 지정할 수 있고, 별칭은 인식이 안되므로 열이름이나 연산식을 그대로 지정해주어야 한다.
또,다중행 함수를 사용하지 않은 일반 열은 GROUP BY절에 명시하지 않으면 SELECT 절에서 사용할 수 없다. → 일반열들은 모두 GROUP BY절에 명시해주자.
7.3 GROUP BY 절에 조건을 주는 HAVING
having절은 group by절이 존재할 때만 사용 할 수 있다.
having절의 조건식은 where절처럼 쓰면 되지만,
where절은 출력대상 행을 제어하고,
having절은 그룹화 된 대상을 제어하므로 쓰임새는 전혀 다르다.
각 부서의 직책별 평균 급여를 구하되 평균 급여가 2000이상인 그룹만 출력하라
SELECT DEPTNO, JOB, AVG(SAL)
FROM EMP
GROUP BY DEPTNO, JOB
HAVING AVG(SAL) >= 2000
ORDER BY DEPTNO, JOB;
7.4 그룹화와 관련된 여러 함수
다중행 함수는 앞에서 본 내용만으로도 실무에서 어느정도 활용할 수 있다.
다음은 조금 더 난이도 있는 함수들이지만, 실무에서는 사용하지 않을 확률이 높기 때문에 간단히, 이런게 있구나 정도만 알고 넘어가자.
ROLLUP
그룹별로 정리? 해주는 느낌이다. 예제로 보면 더 잘 알수 있다.
SELECT DEPTNO, JOB, COUNT(*), MAX(SAL), SUM(SAL), AVG(SAL)
FROM EMP
GROUP BY ROLLUP(DEPTNO,JOB);
표시한 부분은 deptno와 job기준으로 각 데이터를 정리해놓은것을 볼 수 있다.
CUBE
cube는 rollup에서 각각의 그룹별 결과가 하나 더 나온다.
GROUPING SETS
grouping sets는 같은 수준의 그룹화 열이 그룹화되어 출력된다.
SELECT DEPTNO, JOB, COUNT(*)
FROM EMP
GROUP BY GROUPING SETS(DEPTNO,JOB)
ORDER BY DEPTNO, JOB;
LISTAGG
오라클 11g버전 부터 사용할 수 있는 함수이다.
그룹에 속해 있는 데이터를 가로로 나열할 때 사용한다.
SELECT DEPTNO,
LISTAGG(ENAME,', ')
WITHIN GROUP(ORDER BY SAL DESC) AS ENAMES
FROM EMP
GROUP BY DEPTNO;
PIVOT
pivot함수는 기존 테이블의 행을 열로 바꾸는 함수이다.
select deptno, job, max(sal)
from emp
group by deptno, job
order by deptno, job;
이 테이블을
select *
from (select deptno, job, sal
from emp)
pivot(max(sal)
for deptno in(10 as 기획,20,30))
order by job;
이렇게 바꿀수 있다.
데이터의 별칭도 물론 설정할 수 있다.
pivot을 사용하지 않으면 이렇게 써야한다.
select deptno,
max(decode(job,'CLERK',sal)) as CLERK,
max(decode(job,'SALESMAN',sal)) as SALESMAN,
max(decode(job,'PRESIDENT',sal)) as PRESIDENT,
max(decode(job,'MANAGER',sal)) as MANAGER,
max(decode(job,'ANALYST',sal)) as ANALYST
from emp
group by deptno
order by deptno;UNPIVOT
기존 테이블의 열을 행으로 바꾼다.
select *
from (select deptno,
max(decode(job,'CLERK',sal)) as CLERK,
max(decode(job,'SALESMAN',sal)) as SALESMAN,
max(decode(job,'PRESIDENT',sal)) as PRESIDENT,
max(decode(job,'MANAGER',sal)) as MANAGER,
max(decode(job,'ANALYST',sal)) as ANALYST
from emp
group by deptno
order by deptno)
UNPIVOT(
sal for job in (CLERK,SALESMAN,PRESIDENT,MANAGER,ANALYST))
ORDER BY deptno, job;
이러면 원래 썻던구문이랑 다른게 없지만, 예시일 뿐이고, 다른 형태로 제작한 SELECT문의 결과 열을 행형태로 바꿀때 사용한다는데... 잘모르겠다.
