퀴즈로 배워보는 SQL, 그 두 번째 시간은 그룹 바이(GROUP BY)와 롤업(ROLLUP)의 기본원리에 대한 이해와 이를 이용해 문제를 해결하는 방법에 대해 알아본다.

  지면 특성상 문제와 정답, 해설이 같이 있다. 진정으로 자신의 SQL 실력을 키우고 싶다면, 스스로 문제를 해결 한 후 정답과 해설을 참조하길 바란다. 공부를 잘 하는 학생의 문제집은 항상 문제지면의 밑바닥은 까맣지만 정답과 해설지면은 하얗다는 사실을 잊지말자.

  스터디 가입현황을 보여주는 쿼리를 작성하는 문제입니다.

문제

  다음과 같이 scott 유저의 emp 테이블이 있습니다

SQLPLUS scott/tiger

SCOTT@orcl > SELECT * FROM emp;

• [그림 1] 문제 테이블
• 

• [그림 2] 조회 결과
• 

  [그림 1] 문제 테이블을 이용해 [그림 2]와 같은 결과를 도출하는 쿼리를 작성해 보세요.

  쿼리 작성조건입니다.

• - 각 사원별로 부서코드, 사원번호, 성명, 급여가 조회돼야 합니다.
• - 각 부서별 급여 합계 및 평균금액을 새로운 행으로 표시해야 합니다.

정답

  문제를 스스로 해결해 보셨나요? 이제 정답을 알아보도록 하겠습니다.

 
SELECT deptno
     , empno
     , DECODE(GROUP_ID(), 0, NVL(ename,'합계'), '평균') ename 
     , DECODE(GROUP_ID(), 0, SUM(sal), ROUND(AVG(sal),2)) sal 
  FROM scott.emp
 GROUP BY deptno, ROLLUP(deptno, (empno, ename))
 ORDER BY deptno, GROUP_ID(), empno;

  어떤가요? 여러분이 짠 리스트와 같은가요? 틀렸다고 좌절할 필요는 없답니다. 첫 술에 배부를 순 없는 것이니까요. 해설을 꼼꼼히 보고 자신이 잘못한 점을 비교해 보는 것이 더 중요합니다.

해설

  이 문제는 롤업을 이용해 결과를 도출하는 문제입니다. 문제를 풀기 위해선 롤업에 대한 이해를 기본으로 이를 응용할 줄 알아야 합니다. 여기서는 기본부터 차근차근 접근해서 응용단계까지 접근해 가는 방식으로 해결 방법을 설명하겠습니다.

우선은 간단한 예제를 통해 rollup 의 기능을 살펴보도록 하겠습니다.

 
SELECT deptno
     , empno
     , SUM(sal) sal
  FROM scott.emp
 GROUP BY ROLLUP(deptno, empno);

  위 예제 쿼리와 실행 결과를 살펴보면 empno가 비어있는 행 3개와, deptno가 비어 있는 행 1개가 생성된 것을 볼 수 있습니다.

  롤업은 그룹바이 항목들의 합계를 표현하는 방법인 것입니다.

• ① 부서별 사원별 합계 가 기본으로 보여지고
• ② 부서별 사원들의 합계가 부서별로 마지막에 보여지고
• ③ 부서들의 합계가 맨 마지막에 보여집니다.

  즉, 롤업 안에 나열된 항목들의 오른쪽 항목부터 왼쪽 항목으로 합계를 보여줍니다.

  그런데 우리가 필요로 하는 자료는 ①, ② 일뿐 ③ 은 필요가 없습니다. 이때 우리는 단지 deptno를 롤업에서 제외 하면 됩니다. 그룹바이 항목들이 모두 롤업에 참여할 필요는 없습니다.

다음 쿼리의 결과를 살펴볼까요?

 
SELECT deptno
     , empno
     , SUM(sal) sal
  FROM scott.emp
 GROUP BY deptno, ROLLUP(empno);

  deptno 항목을 롤업의 밖으로 빼냄으로 부서들의 합계는 결과에서 제외되었습니다. 이처럼 우리는 롤업에 참여할 항목들을 조정할 수 있습니다.

  이번에는 empno 항목 외에 ename 항목을 추가해 보겠습니다. 어떤 결과가 나올까요?

 
SELECT deptno
     , empno
     , ename
     , SUM(sal) sal
  FROM scott.emp
 GROUP BY deptno, ROLLUP(empno, ename);

  부서별, 사원번호별 사원명별 합계가 기본으로 나오며, 부서별, 사원번호별 사원명의 합계가 나오고, 부서별, 사원번호의 합계가 나오게 됩니다.

  하지만 사원번호별 사원명의 합계는 불필요한 항목입니다. 여기서 주의해야 할 것은 ename은 그룹바이에 있어서 키의 역할을 하는 것이 아니라 단지 empno의 설명 역할, 보조 역할을 할 뿐이라는 것입니다.

  이러한 보조 항목들은 주키 역할을 하는 항목과 함께 괄호로 묶어서 따로 롤업 기능을 하지 못하도록 할 수 있습니다.

다음 쿼리의 결과를 살펴볼까요?

 
SELECT deptno
     , empno
     , ename
     , SUM(sal) sal
  FROM scott.emp
 GROUP BY deptno, ROLLUP((empno, ename));

  empno와 ename을 괄호로 묶어서 롤업에서 마치 하나의 항목처럼 동작하게 만들었습니다. 자 이제 점점 정답과 가까워지고 있네요

  합계 행이 하나 있는데요, 여기에 하나의 행이 더 추가되어야 합니다. 그러려면 롤업에 항목을 하나 더 추가해야 하는데, 과연 어떤 항목을 더 추가해야 할까요? deptno 항목을 추가해 보도록 하겠습니다.

다음 쿼리의 결과를 살펴볼까요?

 
SELECT deptno
     , empno, ename
     , SUM(sal) sum_sal
  FROM scott.emp
 GROUP BY deptno, ROLLUP(deptno, (empno, ename))
 ORDER BY deptno, empno;

  자 어떤가요? Deptno 항목을 두 번 사용해서 합계 행을 두 번 나오도록 만들었습니다.

  단순 그룹바이에서 같은 항목을 두 번 사용한다면 무의미한 구문이 되어 버리겠지만 롤업에서의 항목 중복 사용은 특별한 결과를 도출해 내는군요.

  자 이제 목표결과에 대한 틀은 갖추어 졌으니 내용물을 가공하는 일만 남았습니다.

  만들어낸 두 개의 합계행중 하나는 합계(SUM)을 보여주고 나머지 하나는 평균(AVG)을 보여주도록 조건문(DECODE)을 사용하면 되겠습니다. 하지만 보이는 것과 같이 두 개의 합계 행에는 차이점이 없습니다.

  두 개 행의 차이점을 알아야지만 조건을 줄 수 있는데 말이죠. 롤업의 결과 행들을 구별해 내는 함수가 몇 가지 있는데 이에 대해 살펴보도록 하겠습니다.

다음 쿼리의 결과를 살펴볼까요?

 
SELECT deptno
     , empno, ename
     , SUM(sal) sum_sal
     , GROUPING(deptno) g1
     , GROUPING(empno) g2
     , GROUPING_ID(empno, ename) g3
     , GROUP_ID() g4
  FROM scott.emp
 GROUP BY deptno, ROLLUP(deptno, (empno, ename))
 ORDER BY deptno, empno;

• - GROUPING(empno) g1 : 이는 empno 항목별 합계의 경우엔 0 empno 항목들의 합계의 경우엔 1입니다.
• - GROUPING_ID(empno, ename) : 이는 GROUPING(empno) || GROUPING(ename) 의 결과를 2진수로 보고 십진수로 환산한 값입니다.
• - GROUP_ID() : 중복 항목에 대한 롤업 결과를 구별해주는 함수입니다.

  결과를 보시면 아시겠지만 GROUPING(컬럼), GROUPING_ID(컬럼리스트)로는 구별이 가질 않습니다.

  결국 GROUP_ID()가 0일 경우엔 합계를 1일 경우엔 평균을 보여주도록 하면 됩니다.

 
SELECT deptno
     , empno
     , DECODE(GROUP_ID(), 0, NVL(ename,'합계'), '평균') ename 
     , DECODE(GROUP_ID(), 0, SUM(sal), ROUND(AVG(sal),2)) sal 
  FROM scott.emp
 GROUP BY deptno, ROLLUP(deptno, (empno, ename))
 ORDER BY deptno, GROUP_ID(), empno;

  정답이 완성되었습니다.

  이번 퀴즈에서는 롤업과 롤업에서의 컬럼 중복사용 및 GROUP_ID()를 이용한 자료의 가공을 통해 문제를 풀어보았습니다.

  그렇다면 다음 쿼리의 결과는 어떻게 될까요?

 
SELECT deptno
     , empno
     , DECODE(GROUPING(1), 0, NVL(ename,'합계'), '평균') ename 
     , DECODE(GROUPING(1), 0, SUM(sal), ROUND(AVG(sal),2)) sal 
  FROM scott.emp
 GROUP BY deptno, ROLLUP(1, (empno, ename));

  롤업에 상수 값을 적용시켜 보았습니다.

  정답으로 제시했던 것보다 더 간결하네요.

  롤업의 기본 문법은 간단하지만 어떻게 사용하느냐에 따라서 그 응용방법은 무궁무진합니다.

  어떤가요? 이제 롤업을 이용해 합계를 구하는 방법을 자기 것으로 만드셨나요?