SQL에서 Roll up 함수 활용하기: 계층적 데이터 집계 방법

Roll up 함수는 SQL에서 집계 함수를 사용할 때, 그룹핑을 계층적으로 구조화하여 결과를 반환하는 함수입니다. 이 함수는 주로 계층적인 데이터를 집계할 때 사용되며, 여러 레벨에서 총합, 부분합 등을 계산할 때 유용합니다.

해당 함수를 사용한 쿼리를 예시로 들어보겠습니다. store_item 테이블을 생성하고 데이터를 삽입하였습니다.

CREATE TABLE store_item (
  store_name VARCHAR(50),
  store_item_id VARCHAR(50)
);
INSERT INTO store_item (store_name, store_item_id) VALUES ('Store A', 'Item 1');
INSERT INTO store_item (store_name, store_item_id) VALUES ('Store A', 'Item 2');
INSERT INTO store_item (store_name, store_item_id) VALUES ('Store A', 'Item 3');
INSERT INTO store_item (store_name, store_item_id) VALUES ('Store B', 'Item 1');
INSERT INTO store_item (store_name, store_item_id) VALUES ('Store B', 'Item 2');
INSERT INTO store_item (store_name, store_item_id) VALUES ('Store C', 'Item 1');
INSERT INTO store_item (store_name, store_item_id) VALUES ('Store C', 'Item 2');
INSERT INTO store_item (store_name, store_item_id) VALUES ('Store C', 'Item 3');
INSERT INTO store_item (store_name, store_item_id) VALUES ('Store D', 'Item 1');
INSERT INTO store_item (store_name, store_item_id) VALUES ('Store D', 'Item 2');
INSERT INTO store_item (store_name, store_item_id) VALUES ('Store D', 'Item 3');
INSERT INTO store_item (store_name, store_item_id) VALUES ('Store D', 'Item 4');

 

먼저 Roll up 함수를 사용하지 않았을 경우입니다.

select
    store_name,
    count(distinct store_item_id) as store_item_cnt
from
    store_item
group by
    1

해당 쿼리는 store_item 테이블에서 store_name과 store_item_id를 그룹핑하여, 각 store_name 별로 count(distinct store_item_id)를 계산합니다. 이렇게 함으로써, 각 store_name에 대해 중복되지 않는 store_item_id의 개수를 계산하게 됩니다. 그리고 group by 구문을 사용하여 store_name 컬럼을 기준으로 그룹핑을 수행합니다. 이 결과를 토대로 출력 결과에서는 store_name과 각 store_name의 store_item_id 개수(store_item_cnt)가 나타납니다. 위의 쿼리를 실행하면 아래와 같은 결과를 얻을 수 있습니다.

Roll up 함수 사용하지 않은 경우

Roll up 함수를 사용한 아래 쿼리를 살펴봅시다.

select
    coalesce(store_name,'Total') as store_name,
    count(distinct store_item_id) as store_item_cnt
from
    store_item
group by
    rollup(store_name)

store_item 테이블에서 store_name과 store_item_id를 그룹핑하여, 각 store_name별로 몇 개의 store_item_id가 있는지를 계산합니다. 그리고 rollup 함수를 사용하여, store_name의 총합을 계산한 결과를 출력합니다. rollup 함수는 그룹핑 대상의 컬럼들을 차례대로 나열하면, 각 레벨별로 결과를 계산하여 반환합니다. 위의 쿼리에서는 store_name 컬럼을 기준으로 rollup 함수를 사용하였으므로, store_name의 총합인 Null값이 추가로 반환되게 됩니다.  실무에서는 주로 coalesce(store_name, 'Total')를 사용하여 store_name이 NULL인 경우 'Total'이라는 값을 사용하도록 지정하여 사용합니다.

Roll up 함수 사용

위의 결과를 보면, store_name이 Total인 레코드가 추가되어 모든 store_name의 총합이 출력됩니다. 이와 같이 rollup 함수를 사용하면 여러 레벨에서의 총합을 계산할 수 있으며, 계층적인 데이터를 집계할 때 유용합니다.