[DB]데이터베이스 그룹함수 - WITH ROLLUP, CUBE, GROUPING SETS

 

데이터베이스 그룹함수

 

• 데이터베이스를 Group By 지어줄 때 사용할 수 있는 함수들을 알아보자.
• MariaDB 그룹함수 : WITH ROLLUP , HAVING
• ORACLE 그룹함수 ROLLUP, CUBE, GROUPING SETS

 

 

WITH ROLLUP

2개 이상의 컬럼을 이용하여 그룹 짓는 경우

1. 그룹별 중간합계(집계함수에 대해서)를 표시

2. 마지막 행에는 전체합계가 표시됨

    ex. P101건에서 총 39건, P102건에 대해서 60건, P201건에서 189건

 

 

문제1

상품분류 전체에 대한 상품이름과 구매수량의 총합을 조회하기
조회컬럼 : 전체상품분류코드, 상품명, 구매수량의 총합

1. 단, 구매년도가 2005년도

2. prod, cart 테이블만 사용

 

SELECT prod_lgu, prod_name, sum(cart_qty) AS total 
FROM prod
            LEFT OUTER JOIN cart
                      ON(prod_id = cart_prod
                             AND cart_no LIKE '2005%')
GROUP BY prod_name, prod_lgu
ORDER BY prod_lgu, prod_name;

 

문제1

※ GROUP BY prod_name, prod_lgu : 그룹이 두 개가 적용되었기 때문에 상품분류코드가 같더라도 이름이 달라서 합쳐지지 않음

 

< WITH ROLLUP 사용한 코드 >

SELECT LEFT(prod_id, 4) AS lgu, prod_name, SUM(cart_qty) AS total
FROM prod 
            LEFT OUTER JOIN cart
                      ON(prod_id = cart_prod
                            AND cart_no LIKE '2005%')
GROUP BY LEFT(prod_id, 4), prod_name WITH ROLLUP;

 

 

문제1 - with rollup

※ with rollup : 그룹이 두 개가 적용되었기 때문에 첫번째 조회컬럼 기준으로 중간합계(sum) 생성하고 마지막엔 전체합계 표시

 

CUBE

2개 이상의 컬럼을 이용하여 그룹 짓는 경우

1. CUBE함수는 항목들 간의 다차원적인 합계를 계산

2. ROLLUP과 달리 GROUP BY절에 명시한 모든 컬럼에 대해 소그룹 합계를 계산해준다.

3. 전체합계가 표시됨

    ex. lgu에 대한 그룹합계, prod_name에 대한 그룹합계, 전체합계 

단, MySQL과 MariaDB는 지원 안함

cube

 

GROUPING SETS

2개 이상의 컬럼을 이용하여 그룹 짓는 경우

1. GROUPING SETS는 특정 항목에 대한 소계를 계산하는 함수

2. SETS으로 지정한  소그룹별 합계만 간단하게 보여준다.

 

grouping sets

 

 

<참고 사이트>

SQL 집계함수 - ROLLUP, CUBE, GROUPING SETS (tistory.com)

 

 

개인적으로 내가 원하는 항목으로 지정해서 뽑아올 수 있는 sets을 이용한 그룹별 합계가 더 유용하게 쓰일 것 같다.