SQL에서 GROUPING SETS, ROLLUP, CUBE와 같은 그룹핑 함수들은 집계 쿼리를 보다 유연하게 만들 수 있도록 도와줍니다. 각 기능의 개념과 예제를 설명하겠습니다.
1. GROUPING SETS
GROUPING SETS는 여러 개의 GROUP BY 조건을 한 번에 처리할 수 있도록 도와줍니다. 개별적인 GROUP BY를 조합하여 원하는 그룹핑 조합만 선택적으로 사용할 수 있습니다.
예제
SELECT department, job, SUM(salary) AS total_salary
FROM employees
GROUP BY GROUPING SETS (
(department, job), -- 부서별, 직무별 집계
(department), -- 부서별 집계
(job), -- 직무별 집계
() -- 전체 합계
);결과 예시
| department | job | total_salary |
|---|---|---|
| HR | Manager | 50000 |
| HR | Analyst | 45000 |
| HR | NULL | 95000 |
| IT | Developer | 70000 |
| IT | NULL | 70000 |
| NULL | Manager | 50000 |
| NULL | Analyst | 45000 |
| NULL | Developer | 70000 |
| NULL | NULL | 215000 |
GROUPING SETS를 사용하면 특정 그룹핑 조합을 자유롭게 선택할 수 있습니다.
2. ROLLUP
ROLLUP은 계층적 그룹핑을 수행할 때 사용됩니다. 일반적으로 하나의 컬럼을 기준으로 점진적으로 그룹을 합산하는 방식으로 동작합니다.
예제
SELECT department, job, SUM(salary) AS total_salary
FROM employees
GROUP BY ROLLUP (department, job);결과 예시
| department | job | total_salary |
|---|---|---|
| HR | Manager | 50000 |
| HR | Analyst | 45000 |
| HR | NULL | 95000 |
| IT | Developer | 70000 |
| IT | NULL | 70000 |
| NULL | NULL | 165000 |
ROLLUP(department, job)은 부서-직무별, 부서별, 전체 합계를 순차적으로 생성합니다.
3. CUBE
CUBE는 ROLLUP보다 더 확장된 개념으로, 모든 가능한 그룹핑 조합을 생성합니다. 즉, 조합 가능한 모든 경우의 집계를 계산합니다.
예제
SELECT department, job, SUM(salary) AS total_salary
FROM employees
GROUP BY CUBE (department, job);결과 예시
| department | job | total_salary |
|---|---|---|
| HR | Manager | 50000 |
| HR | Analyst | 45000 |
| HR | NULL | 95000 |
| IT | Developer | 70000 |
| IT | NULL | 70000 |
| NULL | Manager | 50000 |
| NULL | Analyst | 45000 |
| NULL | Developer | 70000 |
| NULL | NULL | 165000 |
CUBE(department, job)은 부서별, 직무별, 조합별, 전체 합계를 모두 반환합니다.
4. GROUPING 함수 활용
SQL에서는 GROUPING() 함수를 사용하여 ROLLUP 또는 CUBE에 의해 생성된 집계 행을 구분할 수 있습니다.
예제
SELECT department, job, SUM(salary) AS total_salary,
GROUPING(department) AS department_grouping,
GROUPING(job) AS job_grouping
FROM employees
GROUP BY CUBE (department, job);결과 예시
| department | job | total_salary | department_grouping | job_grouping |
|---|---|---|---|---|
| HR | Manager | 50000 | 0 | 0 |
| HR | Analyst | 45000 | 0 | 0 |
| HR | NULL | 95000 | 0 | 1 |
| IT | Developer | 70000 | 0 | 0 |
| IT | NULL | 70000 | 0 | 1 |
| NULL | Manager | 50000 | 1 | 0 |
| NULL | Analyst | 45000 | 1 | 0 |
| NULL | Developer | 70000 | 1 | 0 |
| NULL | NULL | 165000 | 1 | 1 |
GROUPING()값이1이면 해당 컬럼이 집계 행임을 의미합니다.
5. 차이점 정리
| 기능 | 설명 |
|---|---|
GROUPING SETS | 개별적인 GROUP BY 조합을 선택적으로 설정 |
ROLLUP | 계층적 그룹핑을 수행 (부서 → 부서별 합계 → 전체 합계) |
CUBE | 모든 가능한 그룹핑 조합을 생성 |
GROUPING() | 특정 행이 집계된 행인지 확인 |
6. 실제 활용 사례
1) 매출 데이터 분석
SELECT region, product, SUM(sales) AS total_sales
FROM sales_data
GROUP BY ROLLUP (region, product);- 지역별 → 지역 내 제품별 → 지역 전체 → 전체 합계 제공
2) 다차원 분석 (OLAP)
SELECT region, product, SUM(sales) AS total_sales
FROM sales_data
GROUP BY CUBE (region, product);- 지역별 → 제품별 → 전체 조합의 모든 합계 제공
7. 결론
GROUPING SETS,ROLLUP,CUBE는 SQL에서 집계 연산을 더욱 유연하고 강력하게 만들어줍니다.GROUPING()을 사용하면 어느 행이 집계된 결과인지 구별할 수 있습니다.ROLLUP은 계층적 합계를 만들고,CUBE는 가능한 모든 조합의 집계를 생성합니다.- 대량의 데이터를 분석할 때 특히 유용합니다.
1. GROUP BY와 함께 사용하는 주요 집계 함수
기본적으로 GROUP BY와 함께 사용되는 다양한 집계 함수가 있습니다.
| 함수 | 설명 |
|---|---|
COUNT(*) | 그룹별 행 개수 반환 |
SUM(column) | 그룹별 합계 |
AVG(column) | 그룹별 평균 |
MAX(column) | 그룹별 최대값 |
MIN(column) | 그룹별 최소값 |
STRING_AGG(column, ', ') | 그룹별 문자열을 구분자로 연결 (SQL Server, PostgreSQL 지원) |
LISTAGG(column, ', ') WITHIN GROUP (ORDER BY column) | 그룹별 문자열을 정렬 후 연결 (Oracle, Snowflake 지원) |
예제
SELECT department, COUNT(*) AS employee_count, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;2. GROUPING() & GROUPING_ID()
GROUPING()
ROLLUP,CUBE에서 생성된 집계 행을 구별하는 데 사용됩니다.
GROUPING_ID()
- 여러 개의
GROUPING()을 조합하여 식별 값을 제공하는 함수입니다.
예제
SELECT department, job, SUM(salary) AS total_salary,
GROUPING(department) AS dept_group,
GROUPING(job) AS job_group,
GROUPING_ID(department, job) AS group_id
FROM employees
GROUP BY CUBE (department, job);결과 예시
| department | job | total_salary | dept_group | job_group | group_id |
|---|---|---|---|---|---|
| HR | Manager | 50000 | 0 | 0 | 0 |
| HR | NULL | 95000 | 0 | 1 | 1 |
| NULL | Manager | 50000 | 1 | 0 | 2 |
| NULL | NULL | 165000 | 1 | 1 | 3 |
GROUPING_ID()는 각 그룹핑 수준을 숫자로 구별할 수 있도록 도와줍니다.
3. PARTITION BY (윈도우 함수)
GROUP BY와는 다르게 행을 그룹핑하되, 집계 결과를 각 행에 다시 반환합니다.OVER(PARTITION BY column)을 사용하여 특정 열 기준으로 집계를 수행합니다.
예제
SELECT department, employee_name, salary,
SUM(salary) OVER(PARTITION BY department) AS department_salary
FROM employees;결과 예시
| department | employee_name | salary | department_salary |
|---|---|---|---|
| HR | Alice | 50000 | 95000 |
| HR | Bob | 45000 | 95000 |
| IT | Charlie | 70000 | 70000 |
PARTITION BY를 사용하면 그룹 내 개별 행이 유지된 채 집계 결과를 볼 수 있습니다.
4. HAVING 절
HAVING을 사용하면GROUP BY의 집계 결과에 조건을 걸 수 있습니다.
예제
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 3;
HAVING COUNT(*) > 3는 직원 수가 3명 이상인 부서만 출력합니다.
5. WITH ROLLUP을 활용한 누적 집계
- MySQL에서는
WITH ROLLUP을 활용하여ROLLUP기능을 적용할 수 있습니다.
예제
SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department WITH ROLLUP;부서별 총 급여와 전체 합계를 반환합니다.
6. PIVOT & UNPIVOT
PIVOT과 UNPIVOT은 그룹핑된 데이터를 열(column)로 변환하거나, 다시 행(row) 형태로 변환하는 기능입니다.
예제 (PIVOT)
sql복사편집SELECT *
FROM (
SELECT department, job, salary
FROM employees
) AS source_table
PIVOT (
SUM(salary) FOR job IN ('Manager', 'Analyst', 'Developer')
) AS pivot_table;
직무(job)별 급여 합계를 열로 변환합니다.
예제 (UNPIVOT)
SELECT department, job, salary
FROM pivot_table
UNPIVOT (
salary FOR job IN (Manager, Analyst, Developer)
) AS unpivot_table;열로 변환된 데이터를 다시 행(row)으로 변환합니다.
7. JSON 집계 함수
일부 최신 SQL 데이터베이스에서는 JSON 데이터 처리 기능도 제공합니다.
| 함수 | 설명 |
|---|---|
JSON_AGG(column) | 그룹별 JSON 배열 생성 |
JSON_OBJECTAGG(key_column, value_column) | 그룹별 JSON 객체 생성 |
예제
SELECT department, JSON_AGG(employee_name) AS employee_list
FROM employees
GROUP BY department;결과 예시
[
{"department": "HR", "employee_list": ["Alice", "Bob"]},
{"department": "IT", "employee_list": ["Charlie"]}
]8. 집계 필터링 (FILTER 절)
FILTER(WHERE condition)를 사용하면 특정 조건을 만족하는 값만 집계할 수 있습니다.
예제
SELECT department,
COUNT(*) AS total_employees,
COUNT(*) FILTER(WHERE salary > 50000) AS high_salary_employees
FROM employees
GROUP BY department;
salary > 50000인 직원만 따로 집계할 수 있습니다.
9. LATERAL JOIN을 활용한 그룹별 상세 집계
- 그룹핑된 데이터 내에서 추가적인 연산을 수행할 때 사용됩니다.
예제
SELECT department, employee_name, salary, avg_salary
FROM employees
JOIN LATERAL (
SELECT AVG(salary) AS avg_salary
FROM employees AS e2
WHERE e1.department = e2.department
) subquery ON true;부서별 평균 급여를 추가적으로 조회합니다.
10. CTE (Common Table Expressions)와 그룹핑
CTE를 활용하면 복잡한 그룹핑 연산을 좀 더 쉽게 처리할 수 있습니다.
예제
WITH department_salary AS (
SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department
)
SELECT department, total_salary
FROM department_salary
WHERE total_salary > 100000;CTE를 사용하면 가독성이 좋은 SQL 쿼리를 작성할 수 있습니다.
결론
SQL의 그룹핑 관련 함수는 매우 다양하며, 단순한 GROUP BY 외에도 다음과 같은 다양한 기능을 활용할 수 있습니다.
ROLLUP,CUBE,GROUPING SETS– 다양한 집계 조합 생성GROUPING(),GROUPING_ID()– 집계 행 판별PARTITION BY– 윈도우 함수 기반 그룹핑HAVING– 집계 결과 필터링PIVOT,UNPIVOT– 행/열 변환JSON 집계 함수– JSON 형태로 데이터 출력FILTER– 특정 조건을 만족하는 행만 집계LATERAL JOIN– 그룹 내 추가 연산CTE– 가독성을 높이는 SQL 구조