sql, mysql의 roll up과 grouping으로 group by를 효과적으로 사용하기

2024-03-17 • infoqoch •

•

roll up

테이블 통계를 사용할 때 보통 group by를 자주 사용한다. group by와 더불어 좀 더 풍부한 내용을 제공하는 roll up에 대해 알아보자.

CREATE TABLE students (
  id INTEGER auto_increment PRIMARY KEY,
  name TEXT NOT NULL,
  gender TEXT NOT NULL,
  city TEXT NOT NULL
);
INSERT INTO students (name, gender, city) 
    VALUES ('Ryan', 'M', 'seoul'), ('Joanna', 'F', 'busan'), ('lee', 'F', 'seoul'), ('choi', 'F', 'busan');

위 테이블 students는 gender와 city를 enum으로 한다. 더불어 group by로 자주 통계 결과를 출력한다고 가정하자. 이 경우 아래와 같이 쿼리를 작성할 것이다.

select gender, city, count(1)
from students 
group by gender, city;

gender	city	count(1)
M	seoul	1
F	busan	2
F	seoul	1

충분하게 만족스러운 결과를 제공한다. 하지만, 모든 데이터의 총합, 첫 번째 그룹핑 enum인 M과 F 각각의 총합이 필요할 수 있다. 이 경우 아래와 같이 roll up을 사용하면 바로 문제가 해결된다.

select gender, city, count(1)
from students 
group by gender, city WITH ROLLUP;

gender	city	count(1)
F	busan	2
F	seoul	1
F	NULL	3
M	seoul	1
M	NULL	1
NULL	NULL	4

not null인 gender와 city 칼럼에서 null이란 값이 존재한다. 이 경우는 무엇을 의미하는가? 바로 각 enum에서의 총합을 의미한다.
- (F, NULL)은 F의 총합으로서 3을 나타낸다.
- (M, NULL)은 M의 총합으로서 1을 나타낸다.
- (NULL, NULL)의 경우 모든 총합 4를 의미한다.
다만 위 방식에는 두 가지 문제가 존재한다.
- 만약 값이 NULL 인 경우 어떻게 되는가?
- NULL이란 모호한 값 말고 다른 방식으로 총합이란 의미를 표현할 수는 없는가?

함수 grouping()

위의 문제는 grouping 함수를 통해 해소 가능하다.
아래 테이블 students는 city가 nullable이다.

CREATE TABLE students2 (
    id INTEGER auto_increment PRIMARY KEY,
    name TEXT NOT NULL,
    gender TEXT NOT NULL,
    city TEXT NULL -- NULL 이 허용된다.
);

-- choi와 Ryan의 city는 null 이다.
INSERT INTO students2 (name, gender, city)
VALUES ('Ryan', 'M', null), ('Joanna', 'F', 'busan'), ('lee', 'F', 'seoul'), ('choi', 'F', null);

상황 1. 값에 NULL이 있는 경우
순서에 따라 어떤 NULL이 총합으로서 NULL인지 예측 가능하다. 하지만 값으로서는 차이를 가지지 못한다.
아래 테이블을 보면 (M, NULL), (F, NULL)이 각 각 두 개가 있는 것을 확인할 수 있다.

select gender, city, count(1)
from students2
group by gender, city WITH ROLLUP;

gender	city	count(1)
F	NULL	1
F	busan	1
F	seoul	1
F	NULL	3
M	NULL	1
M	NULL	1
NULL	NULL	4

상황 2. grouping()으로 NULL이 그룹핑 값인지 판별한다.
grouping 함수의 결과가 1인 레코드는, NULL은 총합으로서 NULL임을 보여준다. 이를 통해 위에서 구분하지 못했던 NULL의 두 가지 의미를 구분할 수 있게 되었다.

select gender, city, grouping(gender), grouping(city), count(1)
from students2
group by gender, city WITH ROLLUP;

gender	city	grouping(gender)	grouping(city)	count(1)
F	NULL	0	0	1
F	busan	0	0	1
F	seoul	0	0	1
F	NULL	0	1	3
M	NULL	0	0	1
M	NULL	0	1	1
NULL	NULL	1	1	4

상황 3. grouping()과 case-when을 활용하여, 예쁘게 작성해보자. 이 경우 ‘총합’이란 enum이 city나 gender에는 없어야 할테다.

select 
    case when grouping(gender) = 1 then '총합' else gender end "gender"
    , case when grouping(city) = 1 then '총합' else city end "city"
    , count(1)
from students2
group by gender, city WITH ROLLUP;

gender	city	count(1)
F	NULL	1
F	busan	1
F	seoul	1
F	총합	3
M	NULL	1
M	총합	1
총합	총합	4