Oracle 그룹 함수(ROLLUP, CUBE, GROUPING SETS, GROUPING) 개념 정리

데이터 분석을 위한 3가지 함수 종류 요약

1) Group Function : 

① 집계 함수를 제외한 함수를 포함(그룹 간의 소계를 계산하는 ROLLUP 함수, GROUP BY 항목들을 다차원적 소계로 계산하는 CUBE 함수, 특정 항목에 대한 소계를 계산하는 GROUPING SETS 함수)

② ROLLUP, CUBE, GROUPING SETS 결과에 대한 정렬이 필요한 경우 ORDER BY절에 정렬 칼럼을 명시.

 

2) Window Function : 

① 데이터 웨어하우스에서 발전한 기능으로, 분석 함수(Analytic Function)나 순위 함수(Rank Runction)라고 불림.

  → 중요 포인트는 행과 행간의 관계를 쉽게 정의하기 위해 만든 함수라는 것.

② group by구문과 병행하여 사용할 수는 없음.

 

3) Aggregate Function : Window Function내에 분류돼있는 함수이며, COUNT, SUM, MAX, MIN 등 과 같은 각종 집계 함수를 포함.

 

 

그룹 함수(Group Function)

1) ROLLUP 함수

① 조회된 group List에서 소 그룹간의 소계(Subtotal)를 계산.

② Grouping Columns의 수를 N이라고 했을 때 N+1 Level의 Subtotal이 생성됨. ※Level ← 계층으로 이해.

③ 인수는 계층구조이므로 인수의 순서가 바뀌면 결과도 바뀜. 인수의 순서 주의!! → 사용법은 group by ROLLUP(칼럼명, 칼럼명, ……)

④ ROLLUP의 경우 계층 간 집계에 대해서는 LEVEL 별 순서를 정렬하지만, 계층 내 GROUP BY 수행 시 생성되는 표준 집계에는 별도의 정렬을 지원하지 않음. 계층 내 정렬을 위해서는 별도의 ORDER BY 절을 사용해야 함(이 부분은 cube, grouping sets도 같음)

 

 

2) CUBE 함수

① ROLLUP은 단지 가능한 Subtotal만 생성하지만, CUBE는 grouping columns가 결합가능한 모든 값에 대하여 다차원 집계를 생성. → ROLLUP에 비해 시스템에 부하를 많이 주는 단점있음.

② CUBE는 내부적으로 Grouping Columns의 순서를 바꾸어 또 한 번의 쿼리를 추가 수행해야 함. 따라서 함수의 인수 순서는 사실상 상관없음.

③ Grouping Columns의 수를 N이라고 했을 때, 2의 N승 Level의 Subtotal이 생성됨.

 

 

3) GROUPING SETS 함수

grouping sets()에 명시된 인수(칼럼)들에 대한 개별 집계를 구함. 이때 표시된 인수들 간에는 계층 구조인 ROLLUP과 달리 평등한 관계이므로 인수의 순서 달라도 결과는 같음.

 

 

+4) GROUPING() 함수 사용

① GROUPING 함수는 ROLLUP, CUBE, GROUPING SETS에 모두 사용할 수 있다.

② ROLLUP, CUBE, GROUPING SETS에 의해 계산된 결과 행에 GROUPING(칼럼명) = 1; 표시되고, 그 외의 일반 결과는 GROUPING(칼럼명) = 0; 나타남.

 

- GROUPING 함수와 CASE문을 응용하기

SELECT
CASE GROUPING(칼럼명) WHEN 1 THEN 'ALL' else 칼럼명 end as 칼럼명
FROM ……

 

- 오라클의 경우 DECODE함수로 좀 더 짧게 표현 가능.

SELECT
DECODE( GROUPING(칼럼명), 1, 'ALL', 칼럼명) as 칼럼명
FROM ……