[SQL] 윈도우(Window) 함수(3) - 그룹 함수

집계 함수: ROLLUP(), CUBE(), GROUPING SETS()

구조

SELECT 컬럼 명, 
집계 함수(인수) 
FROM 테이블 명
GROUP 그룹 함수(컬럼1, 컬럼2)

• 종류
  

예시

• Table employees
  

• 실습을 위한 테이블 정리
  

SELECT department, job, SUM(salary)
FROM my-first-project-466601.modulabs.employees
GROUP BY department, job

SQL query 및 결과

ROLLUP

SELECT department, job, SUM(salary)
FROM employees
GROUP BY ROLLUP(department, job)

CUBE

SELECT department, job, SUM(salary)
FROM employees
GROUP BY CUBE(department, job)

GROUPING SETS

SELECT department, job, SUM(salary)
FROM employees
GROUP BY GROUPING SETS(department, job)

GROUPING SETS departments, job의 조합으로 확인할 경우 ()로 묶어줌

SELECT department, job, SUM(salary)
FROM employees
GROUP BY GROUPING SETS((department, job))

GROUPING SETS department별 계 추가: (department)

SELECT department, job, SUM(salary)
FROM employees
GROUP BY GROUPING SETS((department, job), (department))

GROUPING SETS 총계 추가: 빈 괄호 ()

SELECT department, job, SUM(salary)
FROM employees
GROUP BY GROUPING SETS((department, job), (department), ())