Oracle SQL

[SQL] GROUP BY 절의 추가 기능 (ROLLUP, CUBE, GROUPING SETS)

YoonJuHan 2023. 4. 16. 14:20

GROUP BY 절의 추가 연산자

  1. GROUP BY 절에 두 개 이상의 열을 지정하는 경우 그룹 함수는 항상 마지막 소그룹에 대한 결과만 반환
  2. GROUP BY 절에 연산자를 추가하여 하나의 쿼리문 안에서 여러 차원의 그룹화 연산 가능
  3. GROUP BY 절의 추가 연산자 종류
    1. ROLLUP 연산자
    2. CUBE 연산자
    3. GROUPING SETS 연산자

 

ROLLUP 연산자

  1. 정규 GROUP BY에 의한 그룹화 행과 하위 총계 값을 포함하는 결과 집합을 산출
    1. 기존 GROUP BY의 결과와 상위 집계를 함께 표시
    2. GROUP BY 절에 지정한 그룹화 목록에 따라 가장 하위 레벨부터 시작하여 최상위 총계까지 차례로 계산
    3. 엑셀에서의 부분합을 구하는 작업과 유사
  2. 보고서 작성 시 결과 집합에서 통계 및 요약 정보를 추출하는 데 유용하게 사용
  3. GROUP BY 절에 연산자 추가
  4. 그룹은 괄호 안에 표시
  5. GROUP BY ROLLUP (dept_id, position)에는 다음의 결과가 함께 표시
    1. GROUP BY dept_id, position
    2. GROUP BY dept_id
    3. GROUP BY 하지 않은 결과
  6. 구문
SELECT [column,] group_function(column_name)...
FROM table
[WHERE 조건(s)]
GROUP BY ROLLUP group_by_표현식
[HAVING having_표현식];
[ORDER BY column_name];

 

CUBE 연산자

  1. GROUP BY 절에 지정된 모든 그룹의 조합에 대해 하위 총계와 최상위 총계를 모두 산출
    1. GROUP BY 절에 있는 열 또는 표현식의 모든 조합의 상위 집계를 산출하는 데 사용
    2. ROLLUP 결과 집합을 포함하여 추가 행 생성
  2. GROUP BY CUBE(dept_id, position) 에는 다음의 결과가 함께 표시
    1. GROUP BY dept_id, position
    2. GROUP BY dept_id
    3. GROUP BY position
    4. GROUP BY 하지 않은 결과
  3. 구문
SELECT [column,] group_function(column_name)...
FROM table
[WHERE 조건(s)]
GROUP BY CUBE group_by_표현식
[HAVING having_표현식];
[ORDER BY column_name];

 

GROUPING 함수

  1. ROLLUP과 CUBE 연산자를 사용한 GROUP BY 표현식의 결과
    1. 열이 반영되지 않은 것을 NULL로 표현
    2. 열 값에 NULL이 있어서 반환된 행과 사용자 입장에서 구분이 모호
  2. GROUPING 함수의 결과 해석 방식
    1. 표현식을 사용하여 집계 값을 계산했으나 표현식 열에 있는 NULL 값이 열에 저장된 NULL 값이면 0을 반환
    2. 표현식을 사용하지 않고 집계 값을 계산했으며 결과에 있는 NULL 값이 ROLLUP 또는 CUBE를 통한 그룹화의 결과이면 1을 반환

 

GROUPING SETS

  1. GROUPING SETS 연산자를 사용하여 하나의 쿼리에서 데이터를 여러 개로 그룹화하도록 지정
  2. ROLLUP이나 CUBE에서 가능한 그룹화 가운데 실제 관심있는 그룹을 GROUP BY 절에 GROUPING SETS 다음에 지정
  3. GROUPING SETS((dept_id, position), (position, gender), ()) 에는 다음의 결과가 함께 표시
    1. GROUP BY dept_id, position
    2. GROUP BY position, gender
    3. GROUP BY 하지 않은 전체 사원의 수

 

조합 열

  1. 그룹을 계산하는 동안 한 단위로 처리되는 둘 이상의 열의 모음 
    1. ROLLUP, CUBE, GROUPING SETS 에서 유용하게 사용
    2. CUBE 또는 ROLLUP 에서 조합 열을 사용하면 특정 레벨에 대해 집계를 건너뛰게 된다.
    3. ROLLUP(a, (b, c), d) 과 같이 괄호 안에 열을 지정하여 사용
  2. ROLLUP(dept_id, (gender, position)) 에는 다음의 결과가 함께 표시
    1. GROUP BY(dept_id, gender, position)
    2. GROUP BY(dept_id)
    3. GROUP BY 하지 않은 결과
    4. → ROLLUP(dept_id, gender, position)의 결과에서 GROUP BY(dept_id, gender)의 결과 제외

 

  • 200번보다 큰 부서 내 각 직급별 그룹화에 대한 총 급여를 계산한 다음 각 부서 별 총 급여를 계산한다. 그리고 결과에는 전체 부서에 대한 총 급여까지 포함된다.
SELECT dept_id, position, SUM(salary)
FROM y_emp
WHERE dept_id > 200
GROUP BY ROLLUP(dept_id, position);

 

  • 부서 번호가 200보다 큰 부서에 대한 하위 총계 및 총 급여를 표시한다. 또한 부서에 상관 없이 직급별 총 급여를 함께 표시한다.
SELECT dept_id, position, SUM(salary)
FROM y_emp
WHERE dept_id > 200
GROUP BY CUBE(dept_id, position);

 

  • 다음 예제는 다음 세 그룹에 대한 사원 수를 반환한다.
  • GROUP BY dept_id, position
  • GROUP BY position, gender
  • GROUP BY 하지 않은 전체 사원의 수
SELECT dept_id, position, gender, COUNT(*)
FROM y_emp
GROUP BY GROUPING SETS((dept_id, position), (position, gender), ());

 

  • 다음 예제는 다음과 같은 사항을 결과로 표시한다.
  • 각 부서의 총 급여
  • 각 부서, 성별 및 직급 대한 총 급여
  • 최상위 총계
SELECT dept_id, gender, position, SUM(salary)
FROM y_emp
GROUP BY ROLLUP(dept_id, (gender, position));

저작자표시 (새창열림)