이 게시글은 패스트캠퍼스의 '데이터베이스와 SQLD 합격패스 Online' 강의를 요약한 내용입니다.
그룹 함수
- 그룹 함수를 이용하여 특정 집합의 소계, 중계, 합계, 총 합계를 구할 수 있다.
그룹 함수의 종류
| 종류 | 설명 |
|---|---|
| ROLLUP | - 소 그룹간의 소계를 계산하는 기능. - ROLLUP함수 내에 인자로 지정된 GROUPING 컬럼은 SUBTOTAL을 생성하는데 사용된다. - GROUPING 컬럼의 수가 N이라고 했을 때, N+1의 SUBTOTAL이 생성된다. - ROLLUP함수 내의 인자의 순서가 바뀌면 결과도 바뀌게 된다. (ROLLUP은 계층 구조) |
| CUBE | - 다차원적인 소계를 계산하는 기능. - 결합 가능한 모든 값에 대하여 다차원 집계를 생성. - CUBE 함수 내에 컬럼이 N개라면 2의 N승만큼의 SUBTOTAL이 생성됨. - 시스템에 많은 부담을 주기 때문에 사용상 주의가 필요함. |
| GROUPING SETS | - 특정 항목에 대한 소계를 계산하는 기능 |
그룹 함수 - GROUP BY
- 2019년 기준 부서코드, 부서명, 직원수, 연봉합계, 평균연봉을 부서코드별로 집계하고 부서코드 순서로 정려하여 조회하는 SQL문
SELECT A.DEPT_CD "부서코드"
, (SELECT L.DEPT_NM FROM TB_DEPT L WHERE L.DEPT_CD = A.DEPT_CD) AS "부서명"
, COUNT(*) AS "직원수"
, TO_CHAR(TRUNC(SUM(B."연봉")), 'L999,999,999,999') AS "연봉합계"
, TO_CHAR(TRUNC(AVG(B."연봉")), 'L999,999,999,999') AS "평균연봉"
FROM TB_EMP A
, (
SELECT B.EMP_NO
, SUM(B.PAY_AMT) AS "연봉"
FROM TB_SAL_HIS B
WHERE B.PAY_DE BETWEEN '20190101' AND '20191231'
GROUP BY B.EMP_NO
ORDER BY B.EMP_NO
) B
WHERE A.EMP_NO = B.EMP_NO
GROUP BY A.DEPT_CD
ORDER BY A.DEPT_CD
; 
그룹 함수 - ROLLUP
- ROLLUP 기능을 활용하면 조회하고자 하는 합계 뿐만 아니라, ROLLUP 함수의 GROUPING 인자로 활용된 DEPT_CD의 SUBTOTAL 또한 조회할 수 있다.
SELECT A.DEPT_CD "부서코드"
, (SELECT L.DEPT_NM FROM TB_DEPT L WHERE L.DEPT_CD = A.DEPT_CD) AS "부서명"
, COUNT(*) AS "직원수"
, TO_CHAR(TRUNC(SUM(B."연봉")), 'L999,999,999,999') AS "연봉합계"
, TO_CHAR(TRUNC(AVG(B."연봉")), 'L999,999,999,999') AS "평균연봉"
FROM TB_EMP A
, (
SELECT B.EMP_NO
, SUM(B.PAY_AMT) AS "연봉"
FROM TB_SAL_HIS B
WHERE B.PAY_DE BETWEEN '20190101' AND '20191231'
GROUP BY B.EMP_NO
ORDER BY B.EMP_NO
) B
WHERE A.EMP_NO = B.EMP_NO
GROUP BY ROLLUP(A.DEPT_CD)
ORDER BY A.DEPT_CD ;
그룹 함수 - ROLLUP 인자값 추가
- ROLLUP 인자값에 SEX_CD(성별코드) 추가.
- ROLLUP은 인자값이 N개일 때, N+1개의 SUBTOTAL이 생성된다.
- 이 경우 부서별 SUBTOTAL, 성별별 SUBTOTAL, 전체 SUBTOTAL 총 3개의 SUBTOTAL 값을 조회할 수 있다.
SELECT A.DEPT_CD "부서코드"
, (SELECT L.DEPT_NM FROM TB_DEPT L WHERE L.DEPT_CD = A.DEPT_CD) AS "부서명"
, A.SEX_CD AS "성별코드"
, COUNT(*) AS "직원수"
, TO_CHAR(TRUNC(SUM(B."연봉")), 'L999,999,999,999') AS "연봉합계"
, TO_CHAR(TRUNC(AVG(B."연봉")), 'L999,999,999,999') AS "평균연봉"
FROM TB_EMP A
, (
SELECT B.EMP_NO
, SUM(B.PAY_AMT) AS "연봉"
FROM TB_SAL_HIS B
WHERE B.PAY_DE BETWEEN '20190101' AND '20191231'
GROUP BY B.EMP_NO
ORDER BY B.EMP_NO
) B
WHERE A.EMP_NO = B.EMP_NO
GROUP BY ROLLUP(A.DEPT_CD,A.SEX_CD)
ORDER BY A.DEPT_CD, A.SEX_CD
;
그룹 함수 - GROUPING 함수
- 해당 컬럼이 집계에 사용되었으면 0, 그렇지 않으면 1을 리턴한다.
- GROUPING(A.SEX_CD) WHEN 1 THEN '모든성별' : 즉 , SEX_CD 컬럼이 집계에 사용되었다면 '모든성별' 으로 표기
SELECT CASE GROUPING(A.DEPT_CD) WHEN 1 THEN '모든부서' ELSE A.DEPT_CD END AS "부서코드"
, (SELECT L.DEPT_NM FROM TB_DEPT L WHERE L.DEPT_CD = A.DEPT_CD) AS "부서명"
, CASE GROUPING(A.SEX_CD) WHEN 1 THEN '모든성별' ELSE A.SEX_CD END AS "성별코드"
, COUNT(*) AS "직원수"
, TO_CHAR(TRUNC(SUM(B."연봉")), 'L999,999,999,999') AS "연봉합계"
, TO_CHAR(TRUNC(AVG(B."연봉")), 'L999,999,999,999') AS "평균연봉"
FROM TB_EMP A
, (
SELECT B.EMP_NO
, SUM(B.PAY_AMT) AS "연봉"
FROM TB_SAL_HIS B
WHERE B.PAY_DE BETWEEN '20190101' AND '20191231'
GROUP BY B.EMP_NO
ORDER BY B.EMP_NO
) B
WHERE A.EMP_NO = B.EMP_NO
GROUP BY ROLLUP(A.DEPT_CD,A.SEX_CD)
ORDER BY A.DEPT_CD, A.SEX_CD
;
그룹 함수 - CUBE 함수
- CUBE 함수를 사용함으로써 다차원 집계를 구할 수 있다.
- 전체, 부서별, 부서 성별별, 성별별, 합계를 구함 (CUBE 함수의 인자 컬럼은 2개, 구해진 SUB TOTAL은 4개 (2의 2승))
SELECT A.DEPT_CD "부서코드"
, (SELECT L.DEPT_NM
FROM TB_DEPT L
WHERE L.DEPT_CD = A.DEPT_CD) AS "부서명"
, A.SEX_CD AS "성별코드"
, COUNT(*) AS "직원수"
, TO_CHAR(TRUNC(SUM(B."연봉")), 'L999,999,999,999') AS "연봉합계"
, TO_CHAR(TRUNC(AVG(B."연봉")), 'L999,999,999,999') AS "평균연봉"
FROM TB_EMP A
, (
SELECT B.EMP_NO
, SUM(B.PAY_AMT) AS "연봉"
FROM TB_SAL_HIS B
WHERE B.PAY_DE BETWEEN '20190101' AND '20191231'
GROUP BY B.EMP_NO
ORDER BY B.EMP_NO
) B
WHERE A.EMP_NO = B.EMP_NO
GROUP BY CUBE(A.DEPT_CD, A.SEX_CD)
ORDER BY A.DEPT_CD ;
그룹 함수 - UNION ALL과 GROUP BY
- UNION ALL 및 GROUP BY를 이용하여 부서별, 성별별, 인원수 및 연봉합계, 평균 연봉을 출력하는 SQL 문
SELECT A.DEPT_CD "부서코드", '모든성별' AS "성별코드"
, COUNT(*) AS "직원수"
, TO_CHAR(TRUNC(SUM(B."연봉")), 'L999,999,999,999') AS "연봉합계"
, TO_CHAR(TRUNC(AVG(B."연봉")), 'L999,999,999,999') AS "평균연봉"
FROM TB_EMP A
, (
SELECT B.EMP_NO
, SUM(B.PAY_AMT) AS "연봉"
FROM TB_SAL_HIS B
WHERE B.PAY_DE BETWEEN '20190101' AND '20191231'
GROUP BY B.EMP_NO
ORDER BY B.EMP_NO) B
WHERE A.EMP_NO = B.EMP_NO
GROUP BY A.DEPT_CD
UNION ALL
SELECT '모든부서' AS "부서코드", A.SEX_CD AS "성별코드"
, COUNT(*) AS "부서별직원수"
, TO_CHAR(TRUNC(SUM(B."연봉")), 'L999,999,999,999') AS "부서별연봉합계"
, TO_CHAR(TRUNC(AVG(B."연봉")), 'L999,999,999,999') AS "부서별평균연봉"
FROM TB_EMP A
, (
SELECT B.EMP_NO
, SUM(B.PAY_AMT) AS "연봉"
FROM TB_SAL_HIS B
WHERE B.PAY_DE BETWEEN '20190101' AND '20191231'
GROUP BY B.EMP_NO
ORDER BY B.EMP_NO) B
WHERE A.EMP_NO = B.EMP_NO
GROUP BY A.SEX_CD
ORDER BY "부서코드", "성별코드"; 
그룹 함수 - GROUPING SET
- GROUPING SET를 활용하면 UNION을 쓰지 않아도 GROUPING SET에 입력한 인자들을 기준으로 소계한 값을 출력한다.
- DEPT_CD(부서코드)와 SEX_CD(성별) 기준으로 소계한 값을 출력
- GROUPING SET의 인자의 순서를 바꿔도 출력하는 결과에는 영향이 없다.
SELECT DECODE(GROUPING(A.DEPT_CD), 1, '모든부서', A.DEPT_CD) AS "부서코드"
, DECODE(GROUPING(A.SEX_CD), 1, '모든성별', A.SEX_CD) AS "성별코드"
, COUNT(*) AS "직원수"
, TO_CHAR(TRUNC(SUM(B."연봉")), 'L999,999,999,999') AS "연봉합계"
, TO_CHAR(TRUNC(AVG(B."연봉")), 'L999,999,999,999') AS "평균연봉"
FROM TB_EMP A
, (
SELECT B.EMP_NO
, SUM(B.PAY_AMT) AS "연봉"
FROM TB_SAL_HIS B
WHERE B.PAY_DE BETWEEN '20190101' AND '20191231'
GROUP BY B.EMP_NO
ORDER BY B.EMP_NO
) B
WHERE A.EMP_NO = B.EMP_NO
GROUP BY GROUPING SETS(A.DEPT_CD, A.SEX_CD)
ORDER BY "부서코드", "성별코드"
;
--GROUPING SET 인자 순서 바꾼 쿼리
SELECT DECODE(GROUPING(A.DEPT_CD), 1, '모든부서', A.DEPT_CD) AS "부서코드"
, DECODE(GROUPING(A.SEX_CD), 1, '모든성별', A.SEX_CD) AS "성별코드"
, COUNT(*) AS "직원수"
, TO_CHAR(TRUNC(SUM(B."연봉")), 'L999,999,999,999') AS "연봉합계"
, TO_CHAR(TRUNC(AVG(B."연봉")), 'L999,999,999,999') AS "평균연봉"
FROM TB_EMP A
, (
SELECT B.EMP_NO
, SUM(B.PAY_AMT) AS "연봉"
FROM TB_SAL_HIS B
WHERE B.PAY_DE BETWEEN '20190101' AND '20191231'
GROUP BY B.EMP_NO
ORDER BY B.EMP_NO
) B
WHERE A.EMP_NO = B.EMP_NO
GROUP BY GROUPING SETS(A.SEX_CD, A.DEPT_CD)
ORDER BY "부서코드", "성별코드"
;
안녕하세요