24-05-20 SQL 그룹 함수

오늘도 항상 비슷하게 비슷한 공부를 하면서 하루를 보냈던 것 같다. 일정시간 알고리즘 문제도 풀고, 스프링 학습도 하고 SQL 공부도 하면서 시간을 보냈다. JPA에 대해 이해가 부족해 그 부분을 이해해보려고 하는 중인데 아직도 시간이 많이 필요할 것 같다.

 

여기에는 SQL을 공부하면서 알게된 그룹함수 사용법에 대해 정리해보았다.

 

1. SQL 그룹 함수

그룹 함수들은 여러개의 그룹화를 한 번에 처리해 주는 함수들이다. 따라서 이 함수들을 이용하지 않고도 여러 번의 GROUP BY를 이용한 다음 UNION ALL로 더하는 것으로도 똑같이 만들 수 있다. 하지만 이 함수들을 이용하면 여러줄의 긴 쿼리문을 크게 줄일 수 있다.

1-1. ROLLUP

먼저 바로 어떻게 사용하는지 활용 문법을 먼저 적어보았다. DBMS별로 사용법이 약간 다르다. 아래와 같이 사용할 수 있다.

-- ORACLE, MS SQL SERVER
SELECT 칼럼1, 칼럼2, ...,  집계함수()
FROM 테이블
GROUP BY ROLLUP(칼럼1, 칼럼2, ...)

-- MySQL
SELECT 칼럼1, 칼럼2, ...,  집계함수()
FROM 테이블
GROUP BY 칼럼1, 칼럼2, ... WITH ROLLUP

 

ROLLUP의 경우는 GROUP BY를 활용하여 집계 함수(합, 평균, 최대 등)의 소계와 총계를 모두 구할 수 있는 함수이다. 이렇게만 말하면 이해하기가 어렵다. 아래 코드에서의 한 줄이 아래 4개의 해당하는 것들을 모두 합친 것 이라고 생각하면 이해가 쉽다.

GROUP BY ROLLUP(A, B, C)

GROUP BY A,B,C
GROUP BY A,B
GROUP BY A
GROUP BY (전체)

 

ROLLUP의 경우는 순서가 중요하다. 위의 식에서는 A별, B별, C별 집계 + A별, B별 집계 + A별 집계 + 전체 집계가 결과로 나오게 된다. 순서에 따라 결과가 바뀌기 때문에 주의해야 한다.

 

직접 예시로 살펴보는 것이 이해가 더 빠르다. MySQL을 이용해서 예시를 작성해보았다.

SELECT gender, age, COUNT(*)
FROM customers
GROUP BY gender, age WITH ROLLUP
ORDER BY age DESC

편하게 보기 위해서 나이 순으로 정렬해보았다.

 

성별, 나이별 행의 개수(사람수)

+

성별 행의 개수(사람수)

+

전체 행의 개수(사람수)

 

가 결과로 나오게 된다.

 

아래와 같은 결과를 얻을 수 있다.

 

빈 곳에는 null이 들어가게 된다는 점도 기억해야한다.

 

아래와 같이 GROUP BY와 UNION ALL을 이용해서 ROLLUP을 사용한 것과 똑같이 만들 수 있다. 직접 해보면 결과도 완전히 같게 출력되는 것을 볼 수 있다.

SELECT gender, age, COUNT(*)
FROM customers
GROUP BY gender, age WITH ROLLUP
ORDER BY age DESC

-- ↑ ↓ 위 아래는 동일하다 

-- 성별, 나이별 사람의 수
SELECT gender, age, COUNT(*)
FROM customers
GROUP BY gender, age

UNION ALL

-- 성별 사람의 수
SELECT gender, null, COUNT(*)
FROM customers
GROUP BY gender

UNION ALL

-- 전체 사람의 수
SELECT null, null, COUNT(*)
FROM customers

1-2. CUBE

CUBE는 ROLLUP과 매우 유사하다. 하지만 가능한 모든 결합에 대한 집계를 구한다는 것에 차이가 있다. MySQL에서는 CUBE를 사용할 수 없다.

 

아래 코드에서 위의 한 줄과 아래의 8줄을 UNION ALL로 합친것이 같다고 생각하면 이해하기가 쉽다. 

GROUP BY CUBE(A, B, C)

GROUP BY A,B,C
GROUP BY A,B
GROUP BY A,C
GROUP BY B,C
GROUP BY A
GROUP BY B
GROUP BY C
GROUP BY (전체)

모든 결합에 대해 집계를 구하기 때문에 2의 n(행의 개수)제곱 만큼의 GROUP BY를 수행한 뒤 더한 것과 같게 된다. 위의 예시에서는 칼럼이 3개이기 때문에 2의 3제곱을 해서 총 8개 만큼의 쿼리를 합친 것과 같다.

 

아래와 같이 코드를 직접 작성해서 확인해 볼 수 있다.

SELECT gender, age, COUNT(*)
FROM customers
GROUP BY CUBE(gender, age)

-- ↑ ↓ 위 아래는 동일하다 

-- 성별, 나이별 사람의 수
SELECT gender, age, COUNT(*)
FROM customers
GROUP BY gender, age

UNION ALL

-- 성별 사람의 수
SELECT gender, null, COUNT(*)
FROM customers
GROUP BY gender

UNION ALL

-- 나이별별 사람의 수
SELECT null, age, COUNT(*)
FROM customers
GROUP BY age

UNION ALL

-- 전체 사람의 수
SELECT null, null, COUNT(*)
FROM customers

 

1-3. GROUPING SETS

GROUPING SETS는 특정 항목(칼럼)에 대한 소계를 계산해준다. 이 함수 역시 MySQL에서는 지원하지 않는다.

 

아래 코드에서 위의 한 줄과 아래 세 줄을 UNION ALL로 합친 것이 같다고 생각하면 이해하기가 쉽다.

GROUP BY CUBE(A, B, C)

GROUP BY A
GROUP BY B
GROUP BY C

 

아래와 같이 코드를 작성해서 확인해볼 수 있다.

 

SELECT gender, age, COUNT(*)
FROM customers
GROUP BY GROUPING SETS(gender, age)

-- ↑ ↓ 위 아래는 동일하다 

-- 성별 사람의 수
SELECT gender, null, COUNT(*)
FROM customers
GROUP BY gender

UNION ALL

-- 나이별별 사람의 수
SELECT null, age, COUNT(*)
FROM customers
GROUP BY age

 

1-4. GROUPING

마지막으로 GROUPING 이라는 함수가 있다. 위의 3 개의 그룹 함수들을 사용했을 때 값이 없는 경우 null 값이 들어가는 것을 보았을 것이다. 그런데 그룹핑을 하면서 소계를 구하면서 생긴 null 값과 데이터가 없어서 생긴 null 값을 구분하기 어려울 수도 있을 것이다.

 

이럴 때 사용할 수 있는 함수가 GROUPING 함수이다. 그룹 함수를 사용하고 난 뒤 GROUPING 함수를 사용하면 null 값인 경우 1이 반환되고 값이 있으면 0이 반환된다.

 

이를 활용하여 아래와 같이 코드를 작성하면 null 값 대신 문자열을 넣어 구분하기 쉽게 만들어 줄 수 있다.

SELECT CASE WHEN GROUPING(gender) = 1 AND GROUPING(age) = 1 THEN '전체'
	   ELSE gender END '성별',
	   CASE WHEN GROUPING(age) = 1 THEN '소계'
	   ELSE age END '나이', COUNT(*) '총원'
FROM customers
GROUP BY gender, age WITH ROLLUP
ORDER BY age DESC

 

결과

 

2. 오늘 배운 것

- SQL의 그룹 함수라는 것을 배워 보았다. 이제 SQL에는 많이 익숙해져서 문제풀이 같은 것은 크게 막히는 부분 없이 답안을 작성할 수 있을 것 같다.

- JPA의 동작 방식에 대해서는 이제 어느정도 파악이 된 것 같다. 자세한 사용법에 대해서도 더 익혀봐야 겠다.