SQL 그룹함수

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 외에도 다음과 같은 다양한 기능을 활용할 수 있습니다.

1. ROLLUP, CUBE, GROUPING SETS – 다양한 집계 조합 생성
2. GROUPING(), GROUPING_ID() – 집계 행 판별
3. PARTITION BY – 윈도우 함수 기반 그룹핑
4. HAVING – 집계 결과 필터링
5. PIVOT, UNPIVOT – 행/열 변환
6. JSON 집계 함수 – JSON 형태로 데이터 출력
7. FILTER – 특정 조건을 만족하는 행만 집계
8. LATERAL JOIN – 그룹 내 추가 연산
9. CTE – 가독성을 높이는 SQL 구조