[SQL] 11. 레포팅 함수

11. 레포팅 함수

레포팅 함수는 말 그대로 데이터를 통해 보고할때 유용하게 사용되는 함수이다.
처음 접할 땐 사용법이 다소 어려워 계속 사용하며 익숙해져야 한다.

레포팅 함수는 rollup, cube, grouping sets, grouping, grouping_id가 있다.

rollup, cube

rollup은 어떤 함수를 전체/부분 별로 사용할 수 있게 한다.
보통 group by 절에서 사용되며 상당히 유용한 기능을 가지고 있는데, 우선 예제로 살펴보자.

예제) 부서번호(deptno)별 월급의 합계와 총 사원들의 월급 합계를 출력하시오!
이는 이전에 배운 union all 등 다른 방법으로도 표현이 가능하지만,
rollup을 사용하면 쉽게 출력할 수 있다.

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

rollup 사용 시 한 가지 단점은 월급의 총합계를 나타내는 부분의 명칭이 Null이라는 것인데,
보고를 위해 사용한다면 이는 nvl을 사용하여 바꿔줄 수 있다. (nvl 안에 들어갈 데이터는 동일한 데이터 type을 가지고 있어야 한다!)

▼ nvl을 통해 보고서에 사용될 형태로 변경한 것
select nvl(to_char(deptno), '총 합') 분류, sum(sal)
from emp
group by rollup(deptno);

rollup 함수 안에 여러가지 변수를 넣을 시 분류되는 기준은 아래와 같다.
Ex) rollup(a,b,c)라면
① Data Set을 a,b,c로 나눈 그룹의 함수를 적용한 결과
② Data Set을 a,b로 나눈 그룹의 함수를 적용한 결과
③ Data Set을 a로 나눈 그룹의 함수를 적용한 결과
④ Data Set 전체에 함수를 적용한 결과

간단하게 이야기하면 처음 넣는 변수가 "대분류"이고, 두번째 변수는 "중분류", 세번째 변수는 "소분류"로 인식할 수 있다.

select nvl(to_char(deptno), '총 합') 분류,
nvl(job, '부서별 합계') "직업별 합계", sum(sal)
from emp
group by rollup(deptno, job);

하지만 변수가 늘어나면 늘어날 수록 해당 변수들로 데이터를 나눈 것마다 부분 함수값을 출력하여
더 보기 어렵고 불편한 경우가 될 수도 있다.

그렇다면 여러가지 변수를 보여주면서 맨 마지막에 총합만 보여주고 싶다면 어떻게 해야할까??

→ rollup 뒤에 괄호를 두번 붙여주는 것으로 해결할 수 있다.
예제) 사원 테이블(emp)에 있는 사람들의 부서번호(deptno), 직원번호(empno),
이름(ename)과 사원들의 월급 총합을 보여주고 싶다면
select deptno, empno, ename, sum(sal)
from emp
group by rollup( ( deptno, empno, ename ) ) ;

cube는 rollup과 거의 동일한 기능인데, 출력 시 rollup과 반대로 맨 위에 데이터가 출력된다고 생각하면 쉽다.

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

grouping sets

grouping sets는 앞에서 사용한 rollup을 조금 더 직관적으로 사용하는 함수로,
rollup에 적응하지 못한다면 대체 함수로 사용해도 좋을 것 같다.

위에서 rollup 함수로 사용했던 예제를 살펴보자.
select deptno, job, sum(sal)
from emp
group by rollup(deptno, job);

동일한 결과를 grouping sets로 사용하려면 원하는 단계의 그룹들을 grouping sets안에 넣어주면 된다.

현재 출력하고자 하는 그룹별 함수값은 grouping sets안에서 아래와 같이 기재하면 된다.
① Data Sets을 deptno로 나눈 후 job으로 또 나눈 그룹 → (deptno, job)
② Data Sets을 deptno로 나눈 그룹 → (deptno)
③ Data Sets 전체 → ()

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

주의할 점은 grouping sets안에 그룹 기재 시 세부적인 그룹(소분류)부터 쓰고,
전체 그룹인 ()을 마지막에 넣어줘야 알맞은 모양으로 출력된다.

grouping

grouping은 위의 rollup, cube 사용 시 지정해주지 않아
어쩔 수 없이 null로 출력되는 부분을 1로 출력해준다.

※ GROUPING 함수에서 사용될 컬럼(변수)은 반드시 GROUP BY 절에서 명시되어야 한다.

쉽게 앞서 진행한 예제에 적용해보면
select deptno, sum(sal), grouping(deptno)
from emp group by rollup(deptno);

컬럼 별칭(alias)을 지정하여 null 대신 다른 값이 출력된다해도 grouping의 결과는 1이 나오게 된다.
오해할 수 있는 소지가 있어 다시 정리하자면, 무조건 null값을 찾는다기 보다는
이미 지정된 그룹 데이터의 함수값을 나타낸 출력값이 있다면 1로 표현하는 것이다.

grouping id

grouping id는 위의 grouping과 동일한 기능이지만 여러 그룹에 값을 줄 수 있는 기능으로,
이를 decode를 통한 조건문 등 다양한 방향으로 사용할 수 있다.

select deptno, ename, sum(sal), grouping_id(deptno, ename)
from emp
group by rollup(deptno, ename);

결과값이 1인 부분은 deptno 별 함수값(sum(sal))을 나타내고,
결과값인 3인 부분은 전체 Data set의 함수값을 나타내는 부분이다.

Null이 있다면 그것을 1로, 없다면 0으로 처리한다. 이는 곧 2진수임을 의미하고,
위와 같이 null 값이 두개 이상일 경우 2가 아니라 2진수 11로 인식하여
10진수 3으로 결과값이 나타나게 된다.

Grouping_id의 법칙 ① null의 개수를 센다.
② Grouping_id(a,b) = Grouping(a) || Grouping(b) 2진수이다.
Ex) 1 || 1 = 1 1(2) = 2^11 + 2^01 = 3
③ 숫자는 의미없이 넘어가는 요소

예시 코드)
select deptno, ename, sum(sal),
      grouping_id(2, 3, deptno, 1, ename, 4, 6) as 도도
      from emp
      group by rollup( 2, 3, deptno, 1, ename, 4, 6) ;

→ 읽는 순서(오른쪽부터 읽음)
① ( 2, 3, deptno, 1, ename, 4, 6)
② ( 2, 3, deptno, 1, ename, 4) 이때 6을 강제 null처리 - 00000001(2진수)
③ ( 2, 3, deptno, 1, ename) 그대로 출력 후 4, 6은 null처리 - 0000011(2진수)
④ ( 2, 3, deptno, 1) ename, 4, 6을 null처리 - 0000111(2진수)
⑤ ( 2, 3, deptno) 그대로 출력 - 0001111(2진수)
⑥ ( 2, 3) deptno, 1, ename, 4, 6을 null처리 - 0011111(2진수)
⑦ (2) 3, deptno, 1, ename, 4, 6을 null처리 - 0111111(2진수)
⑧ 전체 null처리 - 1111111(2진수)

지금까지 Query에서 사용되는 함수를 살펴보았다.
다음부터는 분석이 아닌 데이터 조작에 사용되는 DML(Data Manipulation Language)문에 대하여 알아보겠다.

=================이 글은 Markdown을 활용해 작성하였습니다=================