퀴즈로 배우는 SQL
[퀴즈] 사원의 급여 합계 및 평균을 보여주는 쿼리 4 17 24,598

by 마농 GROUPING ROLLUP GROUP_ID 퀴즈 GROUPING_ID [2012.04.22]


  퀴즈로 배워보는 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));

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

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

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

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

- 강좌 URL : http://www.gurubee.net/lecture/2191

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

by 마농 [2012.04.24 16:06:39]

오타 수정 요청 1~
- 변경전 - GROUPING(empno) ename :
- 변경후 - GROUPING_ID(empno, ename) :
오타 수정 요청 2~
- 변경전 - GROUPING(컬럼리스트)
- 변경후 - GROUPING_ID(컬럼리스트)


by 대뽕이 [2012.04.24 19:18:21]

감사합니다!!


by 김정식 [2012.04.26 10:26:23]
마농형한테 수정 권한을 드렸어요.. 수정 버튼 누르시면 수정 할 수 있어요..
우선 제가 수정할 께요. ^^

by 마농 [2012.04.26 11:24:12]

수정 버튼 안보이네~


by 김정식 [2012.04.26 22:49:21]
마농형 제가 잘못 부여해서 다시 부였습니다. 
로그인 다시 하시면 수정 버튼이 보이실 꺼에요.. ^^

by 손님 [2012.12.06 10:21:07]

정말 좋은 강좌네요

by 손님 [2012.12.06 18:00:18]

ENAME 필드에 데이터가 한글로 들어갈경우 정렬이 재대로 안되네요
무슨 방법 없을가요?

by 마농 [2012.12.07 08:08:08]

Order By 구문을 사용해 정렬하셨다면? 안될 이유가 없겠죠. ename 은 정렬과는 무관하니까요.
롤업으로만 정렬하셨다면?
롤업이 정렬된 결과를 주기도 하지만. 항상 정렬을 보장해 주지는 않을 것입니다.
정렬을 원하신다면. 반드시 정렬구문을 사용하시기 바랍니다.


by Always [2013.03.08 10:44:19]

감사합니다. 오라클클럽에 와서 항상 배우고 갑니다. 요 문제도 풀고~ 이해했습니다. ㅎㅎ


by 정진우 [2014.01.20 16:09:05]
마농님 감사합니다 ~~ :)

by 상유니 [2015.08.30 01:03:42]

정말 꿀같은 문제입니다.


by 백순성 [2015.09.25 13:53:09]

감사합니다. 마농님


by 갈매기 [2016.04.28 03:17:09]

MSSQL유전데, Group_ID와 Decode에서 막히네요. 방법이 있나요? 


by 마농 [2016.04.28 09:01:35]

MSSQL 에서의 WITH ROLLUP 구문은 Oracle 에서와 같이 괄호를 자유자재로 사용할 수 없네요.
GROUP BY deptno, empno, ename WITH ROLLUP 을 하게 되면?
너무 많은 소계들이 나오기 때문에 HAVING 절로 불필요한 소계를 제거해야 하는데 번거롭고요.
GROUPING SETS 를 이용하는게 낫겠네요.


롤업에 같은 항목을 두번 사용해도 한번사용한 것과 같은 결과가 나오며
롤업에 상수값을 사용하면 에러가 나네요.
GROUPING SETS 에서 deptno 를 두번 사용해 봤더니 합계가 두줄 발생하기는 하는데
이 두개 행을 구별해줄 GROUP_ID 함수가 존재하지 않네요.
인라인뷰에서 상수 컬럼을 하나 임의로 만들어서 이용했습니다.

 

추가로
Oracle 과 MSSQL 의 계산방식에 차이가 있네요.
Oracle 은 정수끼리의 계산도 소수점까지 계산되지만
MSSQL 은 정수끼리의 계산은 정수로 계산됩니다.
소수점 2째자리까지의 평균을 구하려면 정수를 실수로 바꾸어 평균을 내야 하겠네요.

WITH emp AS
(
SELECT 7369 empno, 'SMITH' ename, 800 sal, 20 deptno
UNION ALL SELECT 7499, 'ALLEN' , 1600, 30
UNION ALL SELECT 7521, 'WARD'  , 1250, 30
UNION ALL SELECT 7566, 'JONES' , 2975, 20
UNION ALL SELECT 7654, 'MARTIN', 1250, 30
UNION ALL SELECT 7698, 'BLAKE' , 2850, 30
UNION ALL SELECT 7782, 'CLARK' , 2450, 10
UNION ALL SELECT 7788, 'SCOTT' , 3000, 20
UNION ALL SELECT 7839, 'KING'  , 5000, 10
UNION ALL SELECT 7844, 'TURNER', 1500, 30
UNION ALL SELECT 7876, 'ADAMS' , 1100, 20
UNION ALL SELECT 7900, 'JAMES' ,  950, 30
UNION ALL SELECT 7902, 'FORD'  , 3000, 20
UNION ALL SELECT 7934, 'MILLER', 1300, 10
)
SELECT deptno
     , empno
     , CASE x WHEN 0 THEN ISNULL(ename, '합계') ELSE '평균' END ename
     , CASE x WHEN 0 THEN SUM(sal) ELSE ROUND(AVG(sal*1.), 2) END sal
  FROM (SELECT deptno, 0 x, empno, ename, sal FROM emp) a
 GROUP BY GROUPING SETS ( (deptno, x, empno, ename)
                        , (deptno, x)
                        , (deptno)
                        )
;

 


by 갈매기 [2016.04.29 01:07:48]

자세한 설명 감사합니다.

GROUP BY GROUPING SETS 은 처음 보는데, 설명부탁드려도 될까요? 
0 x 가 어떤 역할을 하는지도 궁금합니다. 
 
 

by 마농 [2016.04.29 08:13:34]

Grouping Sets 는 한가지 기준이 아닌 여러가지 기준으로 그룹바이 할 때 사용합니다.
부서별로 집계를 두번(합계, 평균) 해야 합니다.
GROUPING SETS ((deptno), (deptno))
이렇게 해야 하는데 이 두가지 집계결과를 구별해서 표시해야 하는데
똑같은 deptno 로 집계한 것이니 전혀 구별이 안가죠.
이를 구별하기 위해 x 를 투입한 것입니다.
x 는 상수값이므로 Group By deptno 에 x 를 추가해도 결과에는 영향을 안주게 되구요.
두가지 집계 결과를 구별하는 역할을 하게 됩니다.


by 갈매기 [2016.04.29 22:40:51]

마농님, 감사합니다. 잘이해 하였습니다. 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입