[SQL 이해하기] - 12. SQL: 합계 함수(GROUPING SETS, ROLL UP, CUBE)

 

COUNT(), SUM(), AVG(), MIN()/MAX() 등의 함수들을 보통 집계 함수(aggregation function)이라고 부른다. 집계 함수와는 별도로 GROUP BY 절과 함께 쓰이면서 그룹핑된 집합의 합계(total)들을 출력해주는 함수들도 자주 사용된다. 이들 합계 함수들인 GROUPING SETS, ROLLUP, CUBE에 대해 살펴보자.

* 합계 함수(total function)라는 용어는 편의상 부르는 명칭이며 정식 명칭은 아니다.

 

- GROUPING SETS()

GROUPING SETS() 함수는 인자로 전달한 각 컬럼들로 데이터를 그룹핑하고 각 그룹들의 소계를 출력해준다.

SELECT STUDSTATE, STUDGENDER, COUNT(*)
FROM STUDENTS
GROUP BY GROUPING SETS(STUDSTATE, STUDGENDER);

STUD ST   COUNT(*)
---- -- ----------
     M           8
     F          10
OR               4
CA               2
TX               3
WA               9

 

GROUPING SETS() 함수의 결과를 보면 STUDSTATE, STUDGENDER 각각에 대해 그룹핑하고 각 그룹의 원소 개수(=소계)를 표시하고 있다. GROUPING SETS() 함수는 함수에 전달한 인자 각각에 대해 그룹핑하고 집계함수를 이용해서 결과를 구한 결과를 합친 것과 동일하다. 위 SQL 쿼리를 아래와 같이 작성할 수도 있다. 결과도 동일하다.

SELECT STUDSTATE, COUNT(*) FROM STUDENTS GROUP BY STUDSTATE
UNION ALL
SELECT STUDGENDER, COUNT(*) FROM STUDENTS GROUP BY STUDGENDER;

STUD   COUNT(*)
---- ----------
TX            3
WA            9
CA            2
OR            4
F            10
M             8

 

GROUPING SETS() 함수는 함수에 인자로 전달한 각 컬럼 별로 소계는 구해주지만 테이블 전체의 총계는 구해주지 않는다. 만약 테이블 전체 데이터에 대한 총계를 구하고자 하면 테이블의 전체 테이블을 의미하는 ()를 GROUPING SETS() 함수의 인자로 전달하면 된다.

SELECT STUDSTATE, STUDGENDER, COUNT(*) 
FROM STUDENTS 
GROUP BY GROUPING SETS(STUDSTATE, STUDGENDER, ());

STUD ST   COUNT(*)
---- -- ----------
TX               3
WA               9
CA               2
OR               4
     F          10
     M           8
                18

 

 

- ROLLUP()

ROLLUP() 함수는 인자로 전달한 컬럼들의 제한된 조합에 대해 소계와 총계를 구해준다. 예를 들어 ROLLUP(A, B) 라고 함수를 사용하면 A, B 컬럼, A 컬럼으로 테이블을 그룹핑하고 각 그룹의 소계와 총계를 표시해준다. 예를 통해 살펴보자.

SELECT STUDSTATE, STUDGENDER, COUNT(*) FROM STUDENTS GROUP BY ROLLUP(STUDSTATE, STUDGENDER);

STUD ST   COUNT(*)
---- -- ----------
TX   F           2
WA   M           4
CA   F           1
WA   F           5
OR   M           2
OR   F           2
CA   M           1
TX   M           1
TX               3
WA               9
CA               2
OR               4
                18

 

ROLLUP() 함수는 인자로 전달한 인자들의 순서에 따라 그룹핑 기준이 되는 컬럼들이 달라진다. 예를 들어 ROLLUP(B, A)라고 함수를 사용하면 B, A 컬럼, B컬럼으로 테이블을 그룹핑하고 각 그룹별 소계와 전체 총계를 표시해준다.

 

*'말려서 올라간다'는 느낌으로 ROLLUP을 기억하고 ROLLUP() 함수에 전달한 인자들이 함수의 앞 인자 방향으로 말려들어가면서 조합을 형성한다고 기억하는 것을 추천한다. 예를 들어 GROUP BY ROLLUP(A, B, C)라고 하면 함수의 가장 앞 인자인 A 인자 방향으로 말려 올라가면서 A+B+C, A+B, A 이렇게 세 가지 조합으로 대상 테이블의 데이터들이 그룹핑된다.

SELECT STUDSTATE, STUDGENDER, COUNT(*) FROM STUDENTS GROUP BY ROLLUP(STUDGENDER, STUDSTATE);

STUD ST   COUNT(*)
---- -- ----------
TX   F           2
WA   M           4
CA   F           1
WA   F           5
OR   M           2
OR   F           2
CA   M           1
TX   M           1
     F          10
     M           8
                18

 

이전 예에서는 '학생들의 주(state) + 성별, 주(state)' 기준으로 데이터를 그룹핑했던데 반해 이번 예에서는 '학생들의 성별 + 주, 성별'을 기준으로 테이블의 데이터가 그룹핑되었음을 눈여겨 보기 바란다.

 

ROLLUP() 함수는 GROUPING SETS() 함수와는 달리 전체 그룹의 총계를 기본으로 출력해 준다.

 

 

- CUBE()

CUBE() 함수는 인자로 전달한 컬럼들의 모든 조합을 기준으로 테이블의 데이터를 그룹핑하고 각 그룹 별 소계와 전체 그룹의 총계를 출력해준다. 예를들어 GROUP BY CUBE(A, B)라고 CUBE() 함수를 사용하면 'A, B, A+B' 세 그룹으로 테이블을 그룹핑한다.

SELECT STUDSTATE, STUDGENDER, COUNT(*) FROM STUDENTS GROUP BY CUBE(STUDSTATE, STUDGENDER);

STUD ST   COUNT(*)
---- -- ----------
                18
     F          10
     M           8
CA               2
CA   F           1
CA   M           1
OR               4
OR   F           2
OR   M           2
TX               3
TX   F           2
TX   M           1
WA               9
WA   F           5
WA   M           4

 

CUBE() 함수와 ROLLUP() 함수는 전체 그룹의 총계를 출력해주고 GROUPING SETS() 함수는 각 그룹의 소계만 출력해 준다는 점도 기억해둘 필요가 있다. 그리고 함수에 인자로 전달한 컬럼들의 조합의 수가 GROUPING SETS() < ROLLUP() < CUBE() 함수 순으로 많아진다.

 

- GROUPING()

GROUPING() 함수는 테이블의 데이터를 그룹핑해 주는 함수는 아니다. GROUPING() 함수는 GROUPING SETS(), ROLLUP(), CUBE() 등의 합계 함수와 함께 쓰이면서 합계를 나타내는 행(ROW)을 구분할 수 있도록 해준다.

 

GROUPING() 함수는 인자로 전달한 컬럼 기준으로 합계를 나타내는 행에서 1의 값을 반환하고 그 외의 행에서는 0을 반환한다.

SELECT STUDSTATE, STUDGENDER, COUNT(*), GROUPING(STUDSTATE), GROUPING(STUDGENDER) 
FROM STUDENTS GROUP BY CUBE(STUDSTATE, STUDGENDER);

STUD ST   COUNT(*) GROUPING(STUDSTATE) GROUPING(STUDGENDER)
---- -- ---------- ------------------- --------------------
                18                   1                    1
     F          10                   1                    0
     M           8                   1                    0
CA               2                   0                    1
CA   F           1                   0                    0
CA   M           1                   0                    0
OR               4                   0                    1
OR   F           2                   0                    0
OR   M           2                   0                    0
TX               3                   0                    1
TX   F           2                   0                    0
TX   M           1                   0                    0
WA               9                   0                    1
WA   F           5                   0                    0
WA   M           4                   0                    0

 

아래의 예와 CASE 구문과 함께 사용하여 합계 행을 명시하는데 사용할 수 있다.

SELECT STUDSTATE, STUDGENDER, COUNT(*), 
CASE WHEN GROUPING(STUDSTATE)=1 THEN 'TOTAL' ELSE STUDSTATE END AS STUDSTATE, 
CASE WHEN GROUPING(STUDGENDER)=1 THEN 'TOTAL' ELSE STUDGENDER END AS STUDGENDER 
FROM STUDENTS GROUP BY CUBE(STUDSTATE, STUDGENDER);

STUD ST   COUNT(*) STUDSTATE  STUDGENDER
---- -- ---------- ---------- ----------
                18 TOTAL      TOTAL
     F          10 TOTAL      F
     M           8 TOTAL      M
CA               2 CA         TOTAL
CA   F           1 CA         F
CA   M           1 CA         M
OR               4 OR         TOTAL
OR   F           2 OR         F
OR   M           2 OR         M
TX               3 TX         TOTAL
TX   F           2 TX         F
TX   M           1 TX         M
WA               9 WA         TOTAL
WA   F           5 WA         F
WA   M           4 WA         M

■