SQLP 요점정리 #05 : 2과목 SQL - 2장-2 SQL활용 : GROUP Function

2과목 SQL 기본 및 활용

• 20문제
• 각 상황별로 SQL문 작성이 가능할 정도로 연습해야 합니다.
• 기본적인 SQL문법 및 함수는 생략하고 Tuning에 활용될수 있는 것 위주로 정리하고자 합니다.

2장 SQL 활용 (#2)

• 표준에서 정한 데이터 분석 3가지 함수군
• Aggregate function : 앞에서 본 GROUP BY 에서 사용한 집계함수 (SUM, AVG, MIN, MAX, ...)
• Group function : 단계별 소계를 계산. SQL을 한번만 읽어 빠르게 보고서 작성이 가능하도록 도와주는 함수들 (ROLLUP, CUBE, GROUPING SET)
• Window function : 행간의 비교, 관계를 지원. DW에서 발전한 기능 (RANK로 대표됨. 함수명은 앞의 것들과 같지만 사용법이 다름)

GROUP FUNCTION (그룹함수)

솔직히 SQL 작성시 사용할 만한 함수는 ROLLUP 하나뿐이다.
나머지는 시험에 나올 수 있으므로 그냥 어떤 역할을 한다는 정도만 알아두면 될듯하다.

1. ROLLUP

소그룹간의 소계(subtotal)를 계산

부서별 업무별 인원수와 연봉의 소계를 일반적은 query로 다음과 같이 작성이 가능하다.

SELECT DNAME, JOB, COUNT(*) AS TOTAL_EMP, SUM(SAL) AS SUM_SAL
  FROM DEPT A, EMP B
 WHERE A.DEPTNO = B.DEPTNO
 GROUP BY DNAME, JOB
 ORDER BY DNAME, JOB;

위 예제의 경우 부서별, 업무별의 대한 소계를 구했다.
여기다가 부서별 소계 와 전체 합계까지 같이 출력하려면 어떻게 해야할까 ?
부서별 소계를 구하는 SQL과 전체 합계를 구하는 SQL을 각각 호출해서 전체를 UNION ALL 하는 방법이 가장 손쉬운 방법이다.

SELECT DNAME, JOB, COUNT(*) AS TOTAL_EMP, SUM(SAL) AS SUM_SAL, 1 AS DEPTH
  FROM DEPT A, EMP B
 WHERE A.DEPTNO = B.DEPTNO
 GROUP BY DNAME, JOB
 UNION ALL
SELECT DNAME, 'TOTAL' AS JOB, COUNT(*) AS TOTAL_EMP, SUM(SAL) AS SUM_SAL, 2 AS DEPTH
  FROM DEPT A, EMP B
 WHERE A.DEPTNO = B.DEPTNO
 GROUP BY DNAME
 UNION ALL
SELECT 'TOTAL' AS DNAME, '' AS JOB, COUNT(*) AS TOTAL_EMP, SUM(SAL) AS SUM_SAL, 3 AS DEPTH
  FROM EMP

그 결과들을 다시 순서대로 할려면 UNION ALL로 묶은 것을 subquery로 해서 SELECT문을 한번 더 써야 한다. 위 SQL을 그냥 A라고 하겠다.

SELECT DNAME, JOB, TOTAL_EMP, SUM_SAL
  FROM (SELECT ... ) A
 ORDER BY DNAME, DEPTH, JOB;

상당히 복잡한 과정을 거쳐서 소계를 구했는데,
그냥 ROLLUP을 이용하면 쉽게 작성이 가능하다.

SELECT DNAME, JOB, COUNT(*) AS TOTAL_EMP, SUM(SAL) AS SUM_SAL
  FROM EMP A, DEPT B
 WHERE A.DEPTNO = B.DEPTNO
 GROUP BY ROLLUP (DNAME, JOB)
 ORDER BY DNAME, JOB;

• GROUPING 함수 : 해당 컬럼을 기준으로 소계를 한 결과에 대해서는 GROUPING(컬럼) = 1을 출력한다.

위 결과에서 각 부서별 합계를 출력하는 row에 대해서는 GROUPING(JOB) = 1 이 되며,
총 계에 대해서는 GROUPING(DNAME) = 1, GROUPING(JOB) = 1 이 출력된다.
이 함수를 이용해서 그룹별 소계, 총 합계를 출력하는 row에 별도 label을 주고자 할 경우 작성법은 다음과 같다.

SELECT DECODE (GROUPING(DNAME), 1, 'Total DEPT', DNAME) AS DNAME,
       DECODE (GROUPING(JOB), 1, 'Total JOB' , JOB) AS JOB,
       COUNT(*) AS TOTAL_EMP,
       SUM(SAL) AS SUM_SAL
  FROM EMP A, DEPT B
 WHERE A.DEPTNO = B.DEPTNO
 GROUP BY ROLLUP (DNAME, JOB);

• ROLLUP을 부분적으로 적용

1. 소계에서 제외
   • 항상 모든 GROUP BY에 사용된 컬럼에 대해서 ROLLUP을 해야하는 것은 아니다.
   • 만약 DNAME, JOB 별로 GROUP BY를 하되 부서별 소계를 필요하지만, 전체 합계는 필요없을 경우
   • GROUP BY DNAME, ROLLUP(JOB) 라고 쓰면 위 결과에서 마지막줄인 Total DEPT가 출력되지 않는다.
2. 2개 이상의 컬럼으로 구분
   • 위의 경우 DNAME, JOB가 종속관계이다. DNAME 안에서 JOB별로 구분하였다.
   • 만약 DNAME 아래에 JOB, MGR이 같은 것끼리 묶어서 소계를 구하고자 할때에는 두 컬럼 이상을 괄호로 묶으면 된다.
   • GROUP BY ROLLUP( DNAME, (JOB, MGR)) 이라고 쓰면 JOB, MGR을 계층짓지않고, JOB, MGR이 모두 같은 것을 하나의 그룹으로 묶어서 소계를 구한다.

2. CUBE

결합 가능한 모든 값에 대하여 다차원 집계를 생성한다.
위 ROLLUP에서 사용한 예제에서 ROLLUP대신 CUBE를 쓸 경우 : GROUP BY CUBE(DNAME, JOB)

• DNAME 별 JOB의 소계
• JOB 별 DNAME의 소계
• 전체 합계 를 모두 출력한다.

즉, CUBE 내의 컬럼들의 순서를 바꿔가면서 ROLLUP을 수행한 뒤에 UNION 한것과 같은 결과이다.
그만큼 연산량이 많다.

또 다른 차이점은 GROUPING 함수의 경우
ROLLUP에서는 계층이 있으므로 GROUPING(DNAME) = 1 이면서 GROUPING(JOB) = 0 인 값이 없었다.
CUBE에서는 GROUPING(DNAME) = 1 이면서 GROUPING(JOB) = 0이 가능하다. JOB별 all DNAME에 대한 소계의 경우이다.

3. GROUPING SETS

다양한 소계들을 한번의 SQL로 구하는데 사용된다.
나열한 컬럼들을 평등한 관계이므로 순서를 바꿔도 상관없다.

위 예제에서 GROUPING SETS를 쓸 경우 : GROUP BY GROUPING SETS(DNAME, JOB)

• DNAME별 소계
• JOB별 소계 를 UNION ALL 해준 결과를 출력해준다.

GROUPING SET도 2개 이상의 컬럼으로 구분이 가능하다. ROLLUP에서 본것 같이 괄호로 컬럼들을 묶어주면 된다.
GROUP BY GROUPING SETS ( (DNAME, JOB, MGR) , (DNAME, JOB) , DNAME ) 이라고 할 경우

• DNAME-JOB-MGR 별 소계
• DNAME-JOB 별 소계
• DNAME 별 소계 의 결과들을 UNION ALL 한 것과 같은 결과를 출력해준다.