database - ROLLUP, GROUPING

오늘자 포스팅 중 틀린 데이터 값이 있어서 바꿔줬다.

update Goods set sell_price = 100 where goods_id = '0008';

 

ㅡ 합계 행을 함께 구하고 싶다

SELECT문 2번 사용 후 UNION ALL로 합치는 방법도 있지만, 외형적으로 보기 좋지 않고 DBMS 내부 리소스도 높아지는 방법이다.

GROUPING 연산자로 집약 단위가 다른 집약 결과를 간단하게 구할 수 있다.

 

GROUPING 연산자에는 다음 세 가지 종류가 있다.

- ROLLUP

- CUBE

- GROUPING SETS

 

ㅡ ROLLUP 사용법

-- ROLLUP으로 합계 행과 소계를 함께 구한다.

select goods_classify, sum(sell_price) as 합계
from goods
group by rollup(goods_classify);

 

이 연산자의 역할을 한마디로 말하면, 집약 키 조합이 다른 결과를 한 번에 계산하는 것이다.

이 예에서는 다음과 같은 두가지 조건에 대한 집약을 한 번에 계산하고 있다.

(1) GROUP BY()

(2) GROUP BY(goods_classify)

 

(1) 의 GROUP BY()는 집약 키가 없는 경우, 즉 GROUP BY구가 없는 경우와 동일하다.

(이것은 전체의 합계 행을 계산한다.)

이 합계 행 레코드를 초집합행(super group row)이라고 하며, GROUP BY구로는 만들 수 없는 합계 행이다.

초집합행의 goods_classify는 값이 불명확하여 기본값으로 NULL이 사용된다.

여기에 적당한 문자열을 삽입하는 방법을 곧 알아보자.

 

-- ROLLUP이 있는 GROUP BY에 등록일 추가

select goods_classify, register_date, sum(sell_price) as 합계
from goods
group by rollup(goods_classify, register_date);

 

결과를 보면 가장 위의 합계 행 및 3개의 상품분류 소계 행(집약 키로 등록일을 사용하지 않은 레코드)이 추가되어 있다.

이 4행이 초집합행이다.

이 SELECT문의 결과는 다음 세 가지 패턴의 집약 레벨이 다른 결과를 UNION으로 연결한 형태다.

1) GROUP BY ()

2) GROUP BY (goods_classify)

3) GROUP BY (goods_classify, register_date)

 

ROLLUP이란 '말아 올리다'라는 의미다.

가장 레벨이 낮은 집약레벨부터 소계 -> 합계 순으로 집약 단위가 커져가는 동작을 나타낸다.

 

ㅡ 가짜 NULL을 알아내기

방금의 결과를 보면 의류 그룹의 register_date 열이 NULL인 레코드가 2행 존재한다.

이 NULL은 각각 다른 원인으로 생겨난 것이다.

 

sum_price = 4000인 행은 원 상품 테이블에서 와이셔츠 등록일이 NULL이여서 집약키로 NULL이 사용된 것이다.

반면, sum_price = 5000인 행은 초집합행이 NULL이다.

양쪽 모두 'NULL'이라는 문자로 표시되어 매우 혼란스러울 수 있다.

 

이러한 혼동 방지를 위해 초집합행이 NULL임을 판별할 수 있는 GROUPING 함수가 존재한다.

이 함수는 인수로 지정한 열의 값이 초집합행 NULL인 경우 1, 이외의 값이면 0을 반환한다.

 

이것으로 초집합행 NULL과 원래 데이터가 NULL인 경우를 판별할 수 있다.

또한, GROUPING 함수를 사용하면 초집합행 키에 적당한 문자열을 삽입할 수 있다.

GROUPING 함수 반환 값이 1인 경우에는 '합계'나 '소계'라는 문자열을 지정하고,

그 외의 반환 값이면 열 값을 사용하면 된다.

 

SELECT CASE WHEN GROUPING(goods_classify) = 1
THEN '상품분류 합계'
ELSE goods_classify END AS 상품그룹,
CASE WHEN GROUPING(register_date) = 1
THEN '등록일 합계'
ELSE CAST(register_date AS VARCHAR(16)) END AS 등록일자,
SUM(sell_price) AS 판매가격합계
FROM Goods
GROUP BY ROLLUP(goods_classify, register_date);

 

실무에서도 합계와 소계를 구할 때 자주 쓰는 방식이다.

 

cf) register_date형을 문자열형으로 변환한 이유는, CASE 식의 반환 값은 모든 분기식에 일치해야 한다는 제약을 만족시키기 위해서이다. 이것을 하지 않으면 날짜형, 문자열 등 가지각색의 파라미터 데이터형을 반환하여 실행 시 에러가 발생한다.