[SQL] 그룹함수

그룹함수 정의

  - 데이터를 통계 내기 위해서는 전체 데이터에 대한 통계는 물론이고, 데이터 일부에 대한 소계, 중계 또한 필요

  - 각 레벨 별 SQL을 UNION문으로 묶어 작성할 수도 있으나, ORACLE에서는 통계 데이터를 위한 몇가지 함수를 제공함

 

 

GROUP BY

SELECT
	D.NAME as DEPT_NAME,
    J.NAME as JOB_NAME,
    AVG(F.SALARY) AS AVG_SALARY
FROM EMP E
JOIN DEPT D
 ON E.DEPT_ID = D.DEPT_ID
JOIN JOB J
 ON E.JOB_ID = J.JOB_ID
GROUP BY D.NAME, J.NAME
;

 

 

ROLL UP

  - 그룹화하는 컬럼에 대한 부분적인 통계를 제공함(그룹 별 소계 및 총계 계산)

SELECT
    D.NAME as DEPT_NAME,
    J.NAME as JOB_NAME,
    AVG(F.SALARY) AS AVG_SALARY
FROM EMP E
JOIN DEPT D
 ON E.DEPT_ID = D.DEPT_ID
JOIN JOB J
 ON E.JOB_ID = J.JOB_ID
GROUP BY
    ROLLUP(D.NAME, J.NAME)
;

 

 

CUBE

  - ROLLUP 함수에서 제공하는 결과를 포함 그룹화 하는 컬럼에 대해 결합 가능한 모든 경우의 수에 대한 다차원집계 제공

SELECT
    D.NAME as DEPT_NAME,
    J.NAME as JOB_NAME,
    AVG(F.SALARY) AS AVG_SALARY
FROM EMP E
JOIN DEPT D
 ON E.DEPT_ID = D.DEPT_ID
JOIN JOB J
 ON E.JOB_ID = J.JOB_ID
GROUP BY
    CUBE(D.NAME, J.NAME)
;

-- MARIA DB
SELECT
    D.NAME as DEPT_NAME,
    J.NAME as JOB_NAME,
    AVG(F.SALARY) AS AVG_SALARY
FROM EMP E
JOIN DEPT D
 ON E.DEPT_ID = D.DEPT_ID
JOIN JOB J
 ON E.JOB_ID = J.JOB_ID
GROUP BY
    D.NAME, J.NAME WITH ROLLUP
    
UNION

SELECT
    D.NAME as DEPT_NAME,
    J.NAME as JOB_NAME,
    AVG(F.SALARY) AS AVG_SALARY
FROM EMP E
JOIN DEPT D
 ON E.DEPT_ID = D.DEPT_ID
JOIN JOB J
 ON E.JOB_ID = J.JOB_ID
GROUP BY
    J.NAME, D.NAME WITH ROLLUP
;

 

 

GROUPING SETS

  - 명시된 컬럼에 대해 개별 통계를 생성

  - 각 컬럼에 대해 GROUP BY로 생성한 통계를 모두 UNION ALL한 결과와 동일

SELECT
    D.NAME as DEPT_NAME,
    J.NAME as JOB_NAME,
    AVG(F.SALARY) AS AVG_SALARY
FROM EMP E
JOIN DEPT D
 ON E.DEPT_ID = D.DEPT_ID
JOIN JOB J
 ON E.JOB_ID = J.JOB_ID
GROUP BY
    GROUPING SETS(D.NAME, J.NAME)
;


-- Maria DB
SELECT
    D.NAME as DEPT_NAME,
    J.NAME as JOB_NAME,
    AVG(F.SALARY) AS AVG_SALARY
FROM EMP E
JOIN DEPT D
 ON E.DEPT_ID = D.DEPT_ID
JOIN JOB J
 ON E.JOB_ID = J.JOB_ID
GROUP BY D.NAME

UNION ALL

SELECT
    D.NAME as DEPT_NAME,
    J.NAME as JOB_NAME,
    AVG(F.SALARY) AS AVG_SALARY
FROM EMP E
JOIN DEPT D
 ON E.DEPT_ID = D.DEPT_ID
JOIN JOB J
 ON E.JOB_ID = J.JOB_ID
GROUP BY J.NAME
;