그룹 함수의 분류
ANSI/ISO SQL 표준은 데이터 분석을 위해 다음의 세 가지 함수를 정의하고 있습니다.
AGGREGATE FUNCTION (집계 함수)
그룹함수의 한 부분으로 COUNT, SUM, AVG, MAX, MIN 외 각종 집계 함수가 있습니다.
GROUP FUNCTION (그룹 함수)
그룹 함수는 집계함수를 제외하고, 소그룹 간의 소계를 계산하는 ROLLUP 함수, GROUP BY 항목 간 다차원적인 소계를 할 수 있는 CUBE 함수, 특정 항목에 대한 소계를 계산하는 GROUPING SETS 함수가 있습니다.
WINDOW FUNCTION (윈도우 함수)
분석함수나 순위함수로도 알려진 윈도우 함수는 데이터 하우스에서 발전한 기능입니다.
그룹함수의 종류와 활용법
ROLLUP 함수
ROLLUP에 지정된 GROUPING COLUMNS의 LIST는 SUBTOTAL을 생성하기 위해 사용됩니다. GROUPING COLUMNS의 수를 N이라고 했을 때 N + 1 LEVEL의 SUBTOTAL이 생성됩니다. 소계, 중계, 합계처럼 계층적 분류를 포함하고 있는 데이터 집계에 적합합니다. 중요한 것은 ROLLUP의 인수는 계층 구조이므로 인수 순서가 바뀌면 수행 결과도 바뀌게 되므로 순서에 유의해야합니다.
SELECT B.DEPARTMENT_NAME, A.JOB_ID, COUNT(*) AS EMP_CNT, SUM(A.SAL) AS SAL_SUM
FROM HR.EMPLOYEES A, HR.DEPARTMENTS B
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
GROUP BY B.DEPARTMENT_NAME, A.JOB_ID;
DEPT_NAME 과 JOB을 기준으로 집계한 일반적인 GROUP BY SQL 문장에 ORDER BY 절을 사용함으로써 부서,업무별로 정렬합니다.
SELECT B.DEPARTMENT_NAME, A.JOB_ID, COUNT(*) AS EMP_CNT, SUM(A.SAL) AS SAL_SUM
FROM HR.EMPLOYEES A, HR.DEPARTMENTS B
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
GROUP BY B.DEPARTMENT_NAME, A.JOB_ID
ORDER BY B.DEPARTMENT_NAME, A.JOB_ID;
롤업함수로 표현하면 다음과 같습니다. 부서명과 업무명을 기준으로 집계한 일반적인 GROUP BY SQL 문장에 ROLLUP 함수를 사용합니다. 실행 결과에서 2개의 GROUPING COLUMNS(DEPARTMENT_NAME, JOB_ID)에 대한 추가 LEVEL의 집계가 생성됩니다.
-- L1 : GROUP BY 수행 시 생성되는 표준 집계
-- L2 : DNAME 별 모든 JOB의 SUBTOTAL
-- L3 : GRAND TOTAL
SELECT B.DEPARTMENT_NAME , A.JOB_ID , COUNT(*) AS EMP_CNT, SUM(A.SALARY) AS SAL_SUM
FROM HR.EMPLOYEES A, HR.DEPARTMENTS B
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
GROUP BY ROLLUP(B.DEPARTMENT_NAME, A.JOB_ID)
ROLLUP 의 경우 계층 간 집계에 대해서 LEVEL별 순서를 정렬하지만, 계층 내 GROUP BY 수행 시 생성되는 표준 집계에는 별도의 정렬을 지원하지 않습니다. 계층내 정렬을 위해서는 별도의 ORDER BY 절을 사용해야 합니다.
-- ROLLUP 함수 + ORDER BY 절 사용
-- 부서명과 업무명을 기준으로 집계한 일반적인 GROUP BY SQL 문장에 ROLLUP 함수를 사용한다.
-- 추가로 ORDER BY 절을 사용해서 부서, 업무별로 정렬한다.
SELECT B.DEPARTMENT_NAME , A.JOB_ID , COUNT(*) AS EMP_CNT, SUM(A.SALARY) AS SAL_SUM
FROM HR.EMPLOYEES A, HR.DEPARTMENTS B
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
GROUP BY ROLLUP(B.DEPARTMENT_NAME ,A.JOB_ID )
ORDER BY B.DEPARTMENT_NAME , A.JOB_IDGROUPING 함수
ROLLUP, CUBE, GROUPING SETS 등 새로운 그룹 함수를 지원하기 위해 GROUPING 함수를 사용할 수 있습니다. ROLLUP에 의해 소계가 계산된 결과에는 GROUPING(컬럼명) = 1 이 표시되고 그 외의 결과에는 GROUPING(EXPR) = 0이 표시됩니다. GROUPING 함수와 CASE/DECODE를 이용해, 소계를 나타내는 필드에 원하는 문자열을 지정할 수 있어 보고서 작성시 유용합니다.
아래의 SQL은 ROLLUP 함수를 추가한 집계 보고서에서 집계 레코드를 구분할 수 있는 GROUPING 함수가 추가된 문장입니다. 부서별, 업무별과 전체 집계를 표시한 레코드에서는 GROUPING 함수가 1을 반환하고 전체 합계를 나타내는 결과 라인에서는 부서별 GROUPING 함수와 업무별 GROUPING 함수가 둘 다 1을 반환합니다.
SELECT B.DEPARTMENT_NAME , GROUPING (B.DEPARTMENT_NAME) AS DNAME_GRP
, A.JOB_ID , GROUPING (A.JOB_ID) AS JOB_GRP
, COUNT(*) AS EMP_CNT, SUM(A.SALARY) AS SAL_SUM
FROM HR.EMPLOYEES A, HR.DEPARTMENTS B
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
GROUP BY ROLLUP(B.DEPARTMENT_NAME ,A.JOB_ID )
ORDER BY B.DEPARTMENT_NAME , A.JOB_IDGROUPING + CASE 함수
ROLLUP 함수를 추가한 집계 보고서에서 집계 레코드를 구분할 수 있는 GROUPING 함수와 CASE 함수를 함께 사용한 SQL 문장을 작성할 수 있습니다.
SELECT CASE GROUPING (B.DEPARTMENT_NAME)
WHEN 1 THEN 'ALL DEPARTMENTS' ELSE B.DEPARTMENT_NAME END AS DNAME
, CASE GROUPING (A.JOB_ID)
WHEN 1 THEN 'ALL JOBS' ELSE A.JOB_ID END AS JOB
, COUNT(*) AS EMP_CNT
, SUM(A.SALARY) AS SAL_SUM
FROM HR.EMPLOYEES A, HR.DEPARTMENTS B
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
GROUP BY ROLLUP(B.DEPARTMENT_NAME, A.JOB_ID)
ORDER BY B.DEPARTMENT_NAME, A.JOB_ID;GROUPING + DECODE 함수
ORACLE의 경우는 DECODE 함수를 사용해 좀 더 간결하게 표현할 수 있습니다. DECODE 함수를 이용해 부서별 전체 집계를 표시한 레코드에서 'ALL DEPARTMENT' 와 'ALL JOBS' 라는 사용자 정의 텍스트를 지정할 수 있습니다.
SELECT DECODE (GROUPING (B.DEPARTMENT_NAME), 1 , 'ALL DEPARTMENTS', B.DEPARTMENT_NAME) AS DNAME
, DECODE (GROUPING (A.JOB_ID), 1 ,'ALL JOBS' , A.JOB_ID) AS JOB
, COUNT(*) AS EMP_CNT
, SUM(A.SALARY) AS SAL_SUM
FROM HR.EMPLOYEES A, HR.DEPARTMENTS B
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
GROUP BY ROLLUP(B.DEPARTMENT_NAME ,A.JOB_ID )
ORDER BY B.DEPARTMENT_NAME , A.JOB_ID;ROLLUP 함수에 컬럼 일부만을 사용
GROUP BY ROLLUP(DNAME, JOB) 조건에서 GROUP BY DNAME, ROLLUP(JOB) 조건으로 변경한 경우 마지막 ALL DEPARTMENT & ALL JOBS 줄이 계산이 되지 않습니다. ROLLUP이 JOB 칼럼에만 사용돼 DNAME에 대한 집계는 필요하지 않기 때문입니다.
SELECT CASE GROUPING (B.DEPARTMENT_NAME) WHEN 1 THEN 'ALL DEPARTMENTS' ELSE B.DEPARTMENT_NAME END AS DNAME
, CASE GROUPING (A.JOB_ID) WHEN 1 THEN 'ALL JOBS' ELSE A.JOB_ID END AS JOB
, COUNT(*) AS EMP_CNT, SUM(A.SALARY) AS SAL_SUM
FROM HR.EMPLOYEES A, HR.DEPARTMENTS B
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
GROUP BY B.DEPARTMENT_NAME , ROLLUP(A.JOB_ID )
ORDER BY B.DEPARTMENT_NAME , A.JOB_ID;ROLLUP 함수에 결합 컬럼 사용
JOB과 MGR은 하나의 집합으로 간주하고 부서별, JOB & MGR에 대한 ROLLUP 결과를 출력합니다 JOB과 MGR의 경우 하나의 집합 칼럼으로 간주해 괄호 내 칼럼별 집계를 구하지 않습니다.
SELECT B.DEPARTMENT_NAME , A.JOB_ID , A.MANAGER_ID
, COUNT(*) AS EMP_CNT, SUM(A.SALARY) AS SAL_SUM
FROM HR.EMPLOYEES A, HR.DEPARTMENTS B
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
GROUP BY ROLLUP(B.DEPARTMENT_NAME, (A.JOB_ID, A.MANAGER_ID))
ORDER BY B.DEPARTMENT_NAME , A.JOB_ID, A.MANAGER_ID ;
CUBE 함수
ROLLUP 에서는 단지 가능한 SUBTOTAL 만을 생성하지만, CUBE는 결합 가능한 모든 값에 대해 다차원 집계를 생성합니다. CUBE를 사용할 때는 내부적으로는 GROUPING COLUMNS의 순서를 바꿔 또 한번의 쿼리를 추가로 수행합니다. 뿐만 아니라, GRAND TOTAL이 양쪽의 쿼리에서 모두 생성되므로 한 번의 쿼리에서는 제거되야하기 때문에 ROLLUP에 비해 시스템의 연산 대상이 많습니다. 이처럼 GROUPING 칼럼이 가질 수 있는 모든 경우에 대해 SUBTOTAL을 생성해야하는 때는 CUBE를 사용하는 것이 바람직하지만 시스템의 많은 부담을 주므로 사용에 주의해야합니다.
CUBE 함수의 경우 표시된 인수들에 대한 계층별 집계를 구할 수 있으며, 이때 표시된 인수 간에는 계층 구조인 ROLLUP과는 달리 평등한 관계이므로 인수의 순서가 바뀌는 경우 행간에 정렬 순서는 바뀔 수 있어도 데이터 결과는 같습니다 그리고 CUBE도 결과에 대한 정렬이 필요한 경우 ORDER BY 절에 명시적으로 정렬 칼럼이 표시됩니다.
CUBE 함수 활용법
GROUP BY CUBE(c1, c2) 조건으로 CUBE 함수를 실행합니다. CUBE는 GROUPING 칼럼이 가질수 있는 모든 경우의 수에 대해 SUBTOTAL을 생성하므로 GROUPING COLUMNS의 수가 N이라고 가정하면, 2의 N승 Level의 SUBTOTAL을 생성합니다.
SELECT
CASE GROUPING (B.DEPARTMENT_NAME) WHEN 1 THEN 'ALL DEPARTMENTS' ELSE B.DEPARTMENT_NAME END AS DNAME
, CASE GROUPING (A.JOB_ID) WHEN 1 THEN 'ALL JOBS' ELSE A.JOB_ID END AS JOB
, COUNT(*) AS EMP_CNT, SUM(A.SALARY) AS SAL_SUM
FROM HR.EMPLOYEES A, HR.DEPARTMENTS B
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
GROUP BY CUBE(B.DEPARTMENT_NAME, A.JOB_ID)
ORDER BY B.DEPARTMENT_NAME , A.JOB_ID;UNION ALL과 비교
UNION ALL 은 SET OPERATION으로, 여러 SQL 문장을 연결하는 역할을 수행합니다. SQL은 첫 번째 SQL 모듈부터 차례대로 결과가 나오므로 위 CUBE SQL과 결과 데이터는 같으나 정렬을 다를 수 있습니다 CUBE 함수를 사용하면 가장 크게 개선되는 부분은 반복 액세스를 CUBE 사용으로 한 번으로 줄일 수 있다는 점입니다. 결과적으로 수행속도 및 자원 사용률을 개선할 수 있으며, SQL 문장도 더 짧아져 가독성이 높아집니다.
SELECT B.DEPARTMENT_NAME AS DNAME , A.JOB_ID AS JOB
,COUNT(*) AS EMP_CNT, SUM(A.SALARY) AS SAL_SUM
FROM HR.EMPLOYEES A, HR.DEPARTMENTS B
WHERE B.DEPARTMENT_ID = A.DEPARTMENT_ID
GROUP BY B.DEPARTMENT_NAME , A.JOB_ID
UNION ALL
SELECT B.DEPARTMENT_NAME AS DNAME , 'ALL JOBS' AS JOB
,COUNT(*) AS EMP_CNT, SUM(A.SALARY) AS SAL_SUM
FROM HR.EMPLOYEES A, HR.DEPARTMENTS B
WHERE B.DEPARTMENT_ID = A.DEPARTMENT_ID
GROUP BY B.DEPARTMENT_NAME
UNION ALL
SELECT 'ALL DEPARTMENTS' AS DNAME, A.JOB_ID AS JOB
,COUNT(*) AS EMP_CNT, SUM(A.SALARY) AS SAL_SUM
FROM HR.EMPLOYEES A, HR.DEPARTMENTS B
WHERE B.DEPARTMENT_ID = A.DEPARTMENT_ID
GROUP BY A.JOB_ID
UNION ALL
SELECT 'ALL DEPARTMENTS' AS DNAME, 'ALL JOBS' AS JOB
,COUNT(*) AS EMP_CNT, SUM(A.SALARY) AS SAL_SUM
FROM HR.EMPLOYEES A, HR.DEPARTMENTS B
WHERE B.DEPARTMENT_ID = A.DEPARTMENT_ID
GROUPING SETS 함수
GROUPING SETS을 이용해 더욱 다양한 소계 집합을 만들 수 있다. GROUP BY SQL 문장을 여러 번 반복하지 않아도 원하는 결과를 쉽게 얻을 수 있습니다. GROUPING SET에 표시된 인수들에 대한 개별 집계를 구할 수 있으며, 이때 표시된 인수 간에는 계층 구조인 ROLLUP 과 달리 평등한 관계이므로 순서 상관없이 결과는 같습니다. GROUPING SETS 함수도 결과에 대한 정렬이 필요한 경우는 ORDER BY 절에 명시적으로 정렬 칼럼이 표시돼야 합니다.
일반 그룹 함수를 이용한 SQL
실행 결과는 별도의 ORDER BY 조건을 명시하지 않았기 때문에 DNAME이나 JOB에 대해 정렬돼 있지 않습니다.
SELECT B.DEPARTMENT_NAME AS DNAME, 'ALL JOBS' AS JOB
,COUNT(*) AS EMP_CNT, SUM(A.SALARY) AS SAL_SUM
FROM HR.EMPLOYEES A, HR.DEPARTMENTS B
WHERE B.DEPARTMENT_ID = A.DEPARTMENT_ID
GROUP BY B.DEPARTMENT_NAME
UNION ALL
SELECT 'ALL DEPARTMENTS' AS DNAME, A.JOB_ID AS JOB
,COUNT(*) AS EMP_CNT, SUM(A.SALARY) AS SAL_SUM
FROM HR.EMPLOYEES A, HR.DEPARTMENTS B
WHERE B.DEPARTMENT_ID = A.DEPARTMENT_ID
GROUP BY A.JOB_IDGROUPING SETS 사용 SQL
일반 그룹 함수를 GROUPING SETS 함수로 변경할 수 있습니다.GROUPING SETS 함수 사용 시, UNION ALL을 사용한 SQL과 같은 결과를 얻을 수 있으며, 괄호로 묶은 집합 별로 집계를 구할 수 있습니다. GROUPING SETS 은 일반 그룹함수를 이용한 SQL과 결과 데이터는 같으나 행들의 정렬 순서는 다를 수 있습니다.
SELECT
CASE GROUPING(B.DEPARTMENT_NAME) WHEN 1 THEN 'ALL DEPARTMENTS' ELSE B.DEPARTMENT_NAME END AS DNAME
, CASE GROUPING(A.JOB_ID) WHEN 1 THEN 'ALL JOBS' ELSE A.JOB_ID END AS JOB
,COUNT(*) AS EMP_CNT, SUM(A.SALARY) AS SAL_SUM
FROM HR.EMPLOYEES A, HR.DEPARTMENTS B
WHERE B.DEPARTMENT_ID = A.DEPARTMENT_ID
GROUP BY GROUPING SETS (B.DEPARTMENT_NAME , A.JOB_ID)
ORDER BY B.DEPARTMENT_NAME , A.JOB_ID;3개의 인수를 이용한 GROUPING SETS 이용
3개의 칼럼에 대한 집계를 GROUPING SETS 함수를 이용해 구할 수 있습니다. GROUPING SETS 함수 사용 시 괄호로 묶은 집합별로 집계를 구할 수 있습니다. 괄호 안은 계층 구조가 아닌 하나의 데이터로 간주합니다.
SELECT B.DEPARTMENT_NAME, A.JOB_ID, A.MANAGER_ID
,COUNT(*) AS EMP_CNT, SUM(A.SALARY) AS SAL_SUM
FROM HR.EMPLOYEES A, HR.DEPARTMENTS B
WHERE B.DEPARTMENT_ID = A.DEPARTMENT_ID
GROUP BY GROUPING SETS ((A.JOB_ID , B.DEPARTMENT_NAME, A.MANAGER_ID), (B.DEPARTMENT_NAME, A.JOB_ID), (A.JOB_ID,A.MANAGER_ID))
