[MySQL] 그룹화 함수 (다차원 집계 함수)

MySQL에서 ROLLUP, CUBE, 그리고 GROUPING SETS는 모두 데이터의 다차원 집계를 수행하는 데 사용되는 강력한 도구들입니다. 이들은 여러 수준에서의 집계(예: 그룹별 소계, 전체 중계 등)를 자동으로 계산해 주며, 각 기능은 약간의 차이점이 있습니다. 이를 통해 데이터 분석 및 보고서 작성 시 다양한 형태로 그룹별 합계, 소계, 중계를 얻을 수 있습니다.
GROUP BY 절에서 쓴다.

ROLLUP

ROLLUP은 단일 차원의 계층적 집계를 제공합니다. 즉, 그룹화된 데이터의 부분 합계와 전체 합계를 계산할 수 있습니다.
여러 컬럼을 그룹화할 때, 지정된 순서대로 상위에서 하위로 소계를 계산하고, 마지막에 전체 중계를 구합니다.

예시:
부서와 직무별로 직원들의 급여 합계와 부서별 소계, 전체 중계를 구하는 예시입니다.

SELECT department_id, job_title, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id, job_title WITH ROLLUP;

결과 예시:

department_id	job_title	total_salary
1	Manager	30000
1	Developer	40000
1	NULL	70000
2	Developer	35000
2	Tester	45000
2	NULL	80000
NULL	NULL	150000

설명:
department_id로 그룹화한 뒤 job_title별 소계를 계산합니다.
NULL로 표시된 행이 각 그룹의 소계와 전체 중계를 나타냅니다.

특징:
그룹핑된 각 컬럼에 대해 상위 그룹의 소계를 자동으로 추가합니다.
소계는 주어진 컬럼 순서에 따라 차례로 추가되며, 최종적으로 전체 중계를 계산합니다.

CUBE

CUBE는 모든 가능한 차원 조합에 대한 집계를 계산합니다. 이는 ROLLUP보다 더 복잡한 다차원 분석을 가능하게 합니다.
각 컬럼의 모든 조합에 대해 소계 및 전체 중계를 계산하여 데이터의 모든 차원에서 결과를 제공합니다.

예시:
부서와 직무별로 급여 합계를 계산하면서, 모든 가능 조합에 대한 소계를 구하는 예시입니다.

SELECT department_id, job_title, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id, job_title WITH CUBE;

department_id	job_title	total_salary
1	Manager	30000
1	Developer	40000
1	NULL	70000
2	Developer	35000
2	Tester	45000
2	NULL	80000
NULL	Manager	30000
NULL	Developer	75000
NULL	Tester	45000
NULL	NULL	150000

설명:
모든 차원에 대해 소계가 계산됩니다.
department_id, job_title의 모든 조합에 대한 결과를 보여주며, 각 조합의 소계와 전체 중계를 계산합니다.

특징:
CUBE는 모든 차원의 조합에 대한 집계 결과를 제공하여, 다차원 데이터 분석에 적합합니다.
ROLLUP보다 더 많은 중간 집계 결과가 포함됩니다.

GROUPING SETS

GROUPING SETS은 명시적으로 그룹화할 조합을 지정할 수 있는 기능입니다.
ROLLUP과 CUBE는 사전 정의된 집계를 자동으로 계산하는 반면, GROUPING SETS는 사용자가 원하는 특정 집계 조합만 선택할 수 있습니다.

예시:
부서별, 직무별, 그리고 전체 급여 소계만을 구하는 예시입니다.

SELECT department_id, job_title, SUM(salary) AS total_salary
FROM employees
GROUP BY GROUPING SETS (
    (department_id, job_title),
    (department_id),
    ()
);

department_id	job_title	total_salary
1	Manager	30000
1	Developer	40000
1	NULL	70000
2	Developer	35000
2	Tester	45000
2	NULL	80000
NULL	NULL	150000

설명:
GROUPING SETS을 사용하여 원하는 조합만 명시적으로 지정하여 소계 및 중계를 계산합니다.
department_id와 job_title로 그룹화된 결과, department_id별 소계, 전체 합계만을 반환합니다.

특징:
GROUPING SETS는 그룹화할 조합을 명확하게 정의할 수 있어, 필요 없는 집계 결과를 제외하고 원하는 조합만을 집계할 수 있습니다.
사용자가 집계 조합을 유연하게 정의할 수 있어 성능적으로 유리할 수 있습니다.

GROUPING() 함수

GROUPING() 함수는 집계된 결과에서 각 행이 소계 또는 중계를 나타내는지 확인하는 데 사용됩니다.
예를 들어, 소계 또는 중계 행은 NULL로 표시되지만, GROUPING() 함수를 사용하면 소계인지 아닌지 구분할 수 있습니다.
예시:

SELECT department_id, job_title, SUM(salary) AS total_salary,
       GROUPING(department_id) AS is_dept_total,
       GROUPING(job_title) AS is_job_total
FROM employees
GROUP BY department_id, job_title WITH ROLLUP;

department_id	job_title	total_salary	is_dept_total	is_job_total
1	Manager	30000	0	0
1	Developer	40000	0	0
1		70000	1	1
2	Developer	35000	0	0
2	Tester	45000	0	0
		150000	1	1

설명:

• GROUPING(department_id)가 1이면 해당 행이 소계를 나타냅니다.
• GROUPING(job_title)가 1이면 직무별 소계를 나타냅니다.
  
  
  
  

요약

기능	설명	사용 예시
ROLLUP	상위에서 하위로 차원을 그룹화하여 소계 및 전체 중계를 계산	GROUP BY column1, column2 WITH ROLLUP
CUBE	모든 차원의 조합에 대해 소계 및 전체 중계를 계산	GROUP BY column1, column2 WITH CUBE
GROUPING SETS	명시적으로 그룹화할 조합을 선택하여 소계 및 중계를 계산	GROUP BY GROUPING SETS ((col1, col2), (col1), ())
GROUPING()	소계 또는 중계인지 여부를 확인할 수 있는 함수	SELECT ..., GROUPING(col) FROM ... GROUP BY ... WITH ROLLUP

• ROLLUP: 차원의 계층적 집계를 수행합니다. (단일 차원 기준으로 소계 및 중계)
• CUBE: 모든 차원 조합에 대한 집계를 계산합니다.
• GROUPING SETS: 원하는 그룹화 조합만을 지정하여 소계 및 중계를 계산합니다.
• GROUPING(): 소계 및 중계 행을 식별하는 데 사용됩니다.