[oracle] SQL 그룹함수(Group Function) - ROLLUP, CUBE, GROUPING SETS, GROUPING
oracle에 SQL 그룹함수(Group Function)인 ROLLUP, CUBE, GROUPING SETS, GROUPING 함수들을 알아보자!
GROUP BY, ROLLUPG, CUBE, GROUPING SETS, GROUPING
예제로 직접 확인하는 게 이해력이 더 빠르니 예제를 통해서 알아보자!
예시로 사용할 테이블을 만들어주자!
--테이블 생성
CREATE TABLE 월별매출 (
상품ID VARCHAR2(5),
월 VARCHAR2(10),
회사 VARCHAR2(10),
매출액 INTEGER );
-- 값생성
INSERT INTO 월별매출 VALUES ('P001', '2023.03', '삼성', 10000);
INSERT INTO 월별매출 VALUES ('P001', '2023.04', '삼성', 25000);
INSERT INTO 월별매출 VALUES ('P002', '2023.03', 'LG', 15000);
INSERT INTO 월별매출 VALUES ('P002', '2023.04', 'LG', 20000);
INSERT INTO 월별매출 VALUES ('P003', '2023.03', '애플', 15000);
INSERT INTO 월별매출 VALUES ('P003', '2023.04', '애플', 15000);
-- 조회
SELECT * FROM 월별매출;테이블 생성후 값을 넣어서 예제 테이블을 만들어줬다.
ROLLUP, CUBE, GROUPING SETS, GROUPING 함수를 알아보기 전 집계함수에 대해서 알아보자.
집계 함수(Aggregate Function)
- 그룹별 결과 출력
- 다중 행 함수 중 하나
- GROUP BY절이 없으면 그룹핑 대상이 존재하지 않아 에러 발생
- WHERE절에 사용 불가
- 공집합에서도 연산 수행
ALL, DISTINCT : 전체 출력, 중복 제외 출력
SUM, AVG, MAX, MIN, VARIAN, STDDEV : NULL 제외하고 연산 (↔ 숫자 연산은 NULL 출력)
COUNT : 행 수 출력
‒ COUNT(*) : NULL 포함
‒ COUNT(표현식) : NULL 제외
1. GROUP BY 절
GROUP BY
- 그룹핑 기준 설정,
- 앨리어스(별칭) 사용 불가
-- GROUP BY절
SELECT 상품ID, 월, SUM(매출액) AS 매출액
FROM 월별매출
GROUP BY 상품ID, 월;
상품ID, 월 기준으로 그룹핑되는 것을 확인할 수 있다.
2. ROLLUP 함수
ROLLUP( )
GROUP BY로 묶인 칼럼의 소계 계산 (소그룹간의 합계를 계산)
GROUP BY로 묶은 각각의 소그룹 합계와 전체 합계를 모두 구함
계층 구조로 GROUP BY의 칼럼 순서가 바뀌면 결과 값 변경
SELECT 상품ID, 월, SUM(매출액) AS 매출액
FROM 월별매출
GROUP BY ROLLUP(상품ID, 월);
빨간색으로 표현된(NULL)값으로 표시된 부분들이 바로 ROLLUP함수를 써서 나온 합계이다.
결과값은 GROUP BY절을 실행했을 때와는 다르게 그에 대한 소계(SUBTOTAL), 총계(GRAND TOTAL)이 나오는 것을 알 수 있다.
처음 명시한 컬럼에 대해서만 소그룹 합계와 총합계를 구한다.
ROLLUP함수는 인수의 순서에도 영향을 받는다.
SELECT 월, 상품ID, SUM(매출액) AS 매출액
FROM 월별매출
GROUP BY ROLLUP(월, 상품ID);
위에 코드와 다르게 결과값이 달라진 걸 볼 수 있다.
회사코드도 포함한 rollup()
SELECT 상품ID, 월,회사, SUM(매출액) AS 매출액
FROM 월별매출
GROUP BY ROLLUP(상품ID, 월, 회사);
rollup 각 그룹명은 따로 지정하지 않았기 때문에 null값으로 표시되었다.
그룹명을 지정하는 것은 맨 마지막 GROUPING 함수에서 자세히 알아볼 예정이다.
3. CUBE 함수
ROLLUP과 달리 GROUP BY절에 명시한 모든 컬럼에 대해 소그룹 합계를 계산한다.
CUBE
조합 가능한 모든 값에 대해 다차원 집계 (항목들 간의 다차원적인 소계를 계산)
SELECT 상품ID, 월, SUM(매출액) AS 매출액
FROM 월별매출
GROUP BY CUBE(상품ID, 월);
CUBE 함수는 그룹핑 컬럼이 가질 수 있는 모든 경우의 수에 대하여 소계(SUBTOTAL)과 총계(GRAND TOTAL)을 생성한다.
ROLLUP 함수와는 다르게 인자의 순서가 달라도 결과는 같다.
SELECT 상품ID, 월, SUM(매출액) AS 매출액
FROM 월별매출
GROUP BY CUBE(월, 상품ID);
인자 순서를 변경하여도 결과값은 동일한 걸 확인할 수 있다.
결과순서만 다르게 나온다.
단순한 월별 소계(SUBTOTAL)도 생성되었으며, 그룹핑 컬럼의 개수를 N이라고 한다면 2의 N승의 소계(SUBTOTAL)을 생성한다.
4.GROUPING SETS 함수
GROUPING SETS
특정 항목에 대한 소계 계산
GROUP BY의 칼럼 순서와 무관하게 개별적으로 처리
SELECT 상품ID, 월, SUM(매출액) AS 매출액
FROM 월별매출
GROUP BY GROUPING SETS(상품ID, 월);
앞의 ROLLUP과 CUBE와 달리 계층 구조가 나타나지 않으며 따라서 인자의 순서가 달라도 결과는 똑같다.
ROLLUP과 CUBE는 GROUP BY 결과에 소그룹 합계와 토탈 합계를 보여주지만 GROUPING SETS는 각 소그룹별 합계만 간단하게 보여준다.
또한, GROUPING SETS 함수는 괄호로 묶은 집합별로도 집계를 구할 수 있다.
SELECT 상품ID, 월, 회사, SUM(매출액) AS 매출액
FROM 월별매출
GROUP BY GROUPING SETS((상품ID, 월), 회사);
5.GROUPING 함수
GROUPING
직접적으로 그룹별 집계를 구하는 함수는 아니지만 ROLLUP, CUBE, GROUPING SETS 집계함수를 지원하는 함수
집계가 계산된 결과에 대해서는 1의 값(GROUPING(표현식) = 1)을 갖고 그렇지 않은 결과에 대해서는 0의 값을 갖는다.
rollup() - grouping
CASE 함수와 ROLLUP 함수를 응용해서 다음과 같은 표현도 가능하다.
SELECT
CASE GROUPING(상품ID) WHEN 1 THEN '모든 상품ID' ELSE 상품ID END AS 상품ID,
CASE GROUPING(월) WHEN 1 THEN '모든 월' ELSE 월 END AS 월,
SUM(매출액) AS 매출액
FROM 월별매출
GROUP BY ROLLUP(상품ID, 월);
cube() - grouping
SELECT
CASE GROUPING(상품ID) WHEN 1 THEN '모든 상품ID' ELSE 상품ID END AS 상품ID,
CASE GROUPING(월) WHEN 1 THEN '모든 월' ELSE 월 END AS 월,
SUM(매출액) AS 매출액
FROM 월별매출
GROUP BY CUBE(상품ID, 월);
grouping sets() - grouping
SELECT
CASE GROUPING(상품ID) WHEN 1 THEN '모든 상품ID' ELSE 상품ID END AS 상품ID,
CASE GROUPING(월) WHEN 1 THEN '모든 월' ELSE 월 END AS 월,
CASE GROUPING(회사) WHEN 1 THEN '모든 회사' ELSE 회사 END AS 회사,
SUM(매출액) AS 매출액
FROM 월별매출
GROUP BY GROUPING SETS((상품ID, 월), 회사);
grouping을 사용하여 null값 대신에 값을 넣을 수 있다.
총 정리한 sql파일이다.
[ 출처 및 참고사이트 ]