2010년 12월 25일 토요일

GROUP BY 결과의 Roll up 기능

MySQL 에서는 GROUP BY 결과에 대한 RollUp을 처리해주는 기능이 있다.

+-------+-----------------------+------+-----+---------+----------------+
| Field | Type                  | Null | Key | Default | Extra          |
+-------+-----------------------+------+-----+---------+----------------+
| id    | mediumint(9)          | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50)           | YES  |     | NULL    |                |
| age   | tinyint(3) unsigned   | YES  | MUL | NULL    |                |
| sex   | enum('MALE','FEMALE') | YES  | MUL | NULL    |                |
+-------+-----------------------+------+-----+---------+----------------+
예를 들어서, 위 테이블에서 sex와 age 컬럼으로 GROUP BY를 실행하고,
실행 결과를 아래 3가지 케이스로 모두 다 조회하고자 하는 경우에는 Rollup 기능을 사용하면 쉽게 구할 수 있다.
  • 성별 및 나이별 합계
  • 성별 합계
  • 전체 합계

select sex, truncate(age/10,0), count(*) from user group by sex, truncate(age/10,0) with rollup;  
-- // truncate 함수는 단순히 나이를 10 단위로 자르기 위해서 사용한 것이지, Rollup 기능과는 무관함
+--------+--------------------+----------+
| sex    | truncate(age/10,0) | count(*) |
+--------+--------------------+----------+
| MALE   |                  0 |      198 |
| MALE   |                  1 |      232 |
| MALE   |                  2 |      282 |
| MALE   |                  3 |      267 |
| MALE   |                  4 |      242 |
| MALE   |                  5 |      193 |
| MALE   |                  6 |      273 |
| MALE   |                  7 |      228 |
| MALE   |                  8 |      210 |
| MALE   |                  9 |      246 |
| MALE   |                 10 |       42 |
| MALE   |               NULL |     2413 | <== 성별(남자) 합계
| FEMALE |                  0 |      233 |
| FEMALE |                  1 |      237 |
| FEMALE |                  2 |      310 |
| FEMALE |                  3 |      258 |
| FEMALE |                  4 |      232 |
| FEMALE |                  5 |      198 |
| FEMALE |                  6 |      265 |
| FEMALE |                  7 |      270 |
| FEMALE |                  8 |      218 |
| FEMALE |                  9 |      228 |
| FEMALE |                 10 |       54 |
| FEMALE |               NULL |     2503 | <== 성별(여자) 합계
| NULL   |               NULL     4916 | <== 전체 합계
+--------+--------------------+----------+

위의 결과를 보면, 
빨간색으로 된 결과들이 Rollup된 결과 레코드들이며,
파란색의 NULL 값들을 보면 알 수 있겠지만, Rollup 된 컬럼의 필드값 NULL로 표기된다.

단, Rollup의 사용은 아래와 같은 제한 사항을 가지고 있다.
  • Rollup이 ORDER BY와 함께 사용될 수는 없으며,
  • Rollup이 완료된 이후 LIMIT 절이 수행이 되므로, LIMIT절이 같이 사용될 경우 결과 해석이 상당히 어려울 수 있다.

댓글 2개:

  1. 결과의 NULL 값의 이름은 어떻게 바꾸나요?>?

    답글삭제
    답글
    1. SELECT CASE WHEN sex IS NULL THEN '총계' ELSE sex END AS sex
      , CASE WHEN truncate(age/10,0) IS NULL THEN '성별계' ELSE truncate(age/10,0) END AS age, COUNT(*)....

      삭제