5. 그룹함수

 

그룹합수는 행 집합 연산을 수행하여 그룹별로 하나의 결과를 산출한다.

 

• max 함수 : 최대값을 출력하는 함수
  ex) 최대 월급 출력

select max(sal)
from emp;

• min 함수 : 최소값을 출력하는 함수

select min(sal)
from emp;

• avg 함수 : 평균값을 출력하는 함수

select avg(sal)
from emp;

• select 문의 6가지 절
  - 코딩순서 : select → from → where → group by → having → order by
  - 실행순서 : from → where → group by → having → select → order by
• sum 함수 : 토탈값을 출력하는 함수

select sum(sal)
from emp;

• count 함수 : 건수를 세는 함수
  ex) emp 테이블의 전체 건수를 확인

select count(*)
from emp;

    - count(*) 는 모든 컬럼을 하나씩 세는 것이므로 null 은 count 되지 않는다.

    ex) 그룹함수는 null 값을 무시한다.

select count(empno)
from emp;

select count(comm)
from emp;

함수의 종류

- 단일행 함수 : 문자, 숫자, 날짜, 변환, 일반

- 복수행 함수 : max, min, avg, sum, count 등

- 데이터 분석 함수 : rank, dense_rank, 누적 데이터 출력, pivot, unpivot, lag, lead, ntile, rollup, cube, grouping sets 등

• rank 함수 : 순위를 출력하는 함수
  ex) 이름, 월급, 월급에 대한 순위를 출력한다.
  
  

select ename, sal, rank() over(order by sal desc) "순위"
from emp;

    ex) 공동순위

select ename, sal, dense_rank() over(order by sal desc) "순위"
from emp;

• listagg 함수 : 데이터를 가로로 출력해준다.

select deptno, listagg(ename, ',') within group(order by ename asc)
from emp
group by deptno;

    - 이름을 ABC 순으로 정렬했다.

select deptno, listagg(ename,',') within group (order by sal desc)
from emp
group by deptno;

    - 이름을 월급이 높은 순서대로 정렬했다.

    - listagg 함수를 쓸 때는 group by 절을 꼭 써야 한다.

 

누적 데이터를 출력하는 분석함수

select ename, sal, sum(sal) over(order by sal asc)누적치
from emp;

select ename, sal, sum(sal) over()누적치
from emp;

 

• lag 함수, lead 함수 : 위의 행을 아랫 행에 출력하거나, 아래의 행을 윗 행에 출력하는 함수

select ename, sal,
    lag(sal,1) over(order by sal asc) lag_sal,
    lead(sal,1) over(order by sal asc) lead_sal
from emp;

• rank 함수에서 over와 within group의 사용 : 대부분 over를 사용하지만 over()의 괄호 안에 해당하는 숫자의 순위를 보고싶을 때는 within group을 사용하고, listagg에서도 within group을 쓰고 대부분 over를 사용한다.
• 결과 데이터를 가로로 출력하는 방법 : 주로 decode 를 사용한다.

select sum(decode(deptno, 10, sal, null))"10",
        sum(decode(deptno, 20, sal, null))"20",
        sum(decode(deptno, 30, sal, null))"30"
from emp;

• pivot 문 : 세로를 가로로 출력한다.

select *
from(select deptno, sal from emp)
/* emp테이블에서 부서번호와 월급만 가져와서 그 결과를 테이블처럼 사용하겠다는 의미.
그룹함수를 사용하면 안되며, 함수를 쓰게되면 컬럼 별칭을 사용해야 한다.*/
pivot(sum(sal) for deptno in(10,20,30));
/*토탈월급을 출력 for 부서번호에 대한 in 10,20,30에 대한 부서번호*/

• unpivot 문 : 가로를 세로로 바꾼다. 컬럼이 데이터가 된다.

select *
from emp
unpivot(cnt for item in (BICYCLE, CAMERA, NOTEBOOK));

원래 테이블

unpivot한 테이블