[풀스택과정] 데이터베이스 6. 그룹함수

* 이 글은 제가 공부하기 위해 최대한 사실에 입각해 내용을 적으려고 하지만 일부 내용들이 정확하지 않을 수 있습니다.

   혹시나 잘못된 부분이 있으면 너그럽게 이해해 주시고 피드백 부탁드려요!

---

1. 그룹 함수의 개념

그룹함수의 개념

• 그룹함수란?

  – 테이블의 전체 행을 하나 이상의 칼럼을 기준으로 그룹화하여 그룹별로 결과를 출력하는 함수
  – 그룹함수는 통계적인 결과를 출력하는데 자주 사용

• 사용법

– GROUP BY : 전체 행을 group_by_expression을 기준으로 그룹화
– HAVING : GROUP BY 절에 의해 생성된 그룹별로 조건 부여

 

그룹 함수의 처리 과정

 

2. 그룹 함수의 종류

[표 7.1] 그룹 함수의 종류

COUNT 함수

• COUNT 함수

  – 테이블에서 조건을 만족하는 행의 개수를 반환하는 함수

• 사용법

– "*"은 NULL을 포함한 모든 행의 개수
– DISTINCT는 중복되는 값을 제외한 행의 개수
– ALL은 중복되는 값을 포함한 행의 개수, 기본값은 ALL
– expr 인수에서 사용 가능한 데이터 타입은 CHAR, VARCHAR2, NUMBER, DATE 타입

• 사용 예

– 101번 학과 교수 중에서 보직수당을 받는 교수의 수를 출력하여라.

AVG, SUM 함수

• 사용법

– expr의 데이터 타입은 NUMBER 데이터 타입만 가능

• 사용 예

– 101번 학과 학생들의 몸무게 평균과 합계를 출력하여라

MIN, MAX 함수

• 사용 예

– 102번 학과 학생 중에서 최대 키와 최소 키를 출력하여라.

STDDEV, VARIANCE 함수

• 사용 예

– 교수 테이블에서 급여의 표준편차와 분산을 출력하여라.

3. 데이터 그룹 생성

데이터 그룹 생성

• GROUP BY 절

  – 특정 칼럼 값을 기준으로 테이블의 전체 행을 그룹별로 나누기 위한 절
  – 예를 들어, 교수 테이블에서 소속 학과별이나 직급별로 평균 급여를 구하는 경우
  – GROUP BY 절에 명시되지 않은 칼럼은 그룹함수와 함께 사용할 수 없음

• GROUP BY 절 사용할 때 적용되는 규칙

  – 그룹핑 절에 WHERE 절을 사용하여 그룹 대상 집합을 먼저 선택
  – GROUP BY 절에는 반드시 칼럼 이름을 포함해야 하며 칼럼 별명은 사용할 수 없음
  – 그룹별 출력 순서는 오름차순으로 정렬
  – SELECT 절에서 나열된 칼럼 이름이나 표현식은 GROUP BY 절에서 반드시 명시
  – GROUP BY절에서 명시한 칼럼 이름은 SELECT절에서 명시하지 않아도 된다.

 

GROUP BY 절과 SELECT절

• GROUP BY 절에 명시하지 않은 칼럼을 SELECT 절에서 사용한 경우

단일 칼럼을 이용한 그룹핑

• 사용 예

– 교수 테이블에서 학과별로 교수 수와 보직수당을 받는 교수 수를 출력하여라

다중 칼럼을 이용한 그룹핑

• 다중 칼럼을 이용한 그룹핑

  – 하나 이상의 칼럼을 사용하여 그룹을 나누고, 그룹별로 다시 서브 그룹을 나눔
  – 전체 교수를 학과별로 먼저 그룹핑한 다음, 학과별 교수를 직급별로 다시 그룹핑하는 경우

• 실습 예

– 학과별로 소속 교수들의 평균급여, 최소급여, 최대급여를 출력하여라.

다중 칼럼을 이용한 그룹별 검색

• 사용 예

– 전체 학생을 소속 학과별로 나누고, 같은 학과 학생은 다시 학년별로 그룹핑하여, 학과와 학년별 인원수, 평균 몸무게를 출력하여라, 단, 평균 몸무게는 소수점 이하 첫 번째 자리에서 반올림한다.

ROLLUP, CUBE 연산자

• ROLLUP 연산자

  – GROUP BY 절의 그룹 조건에 따라 전체 행을 그룹화하고 각 그룹에 대해 부분합을 구하는 연산자
  – GROUP BY 절에 칼럼의 수가 n개이면 ROLLUP 그룹핑 조합은 n+1개

• CUBE 연산자

  – ROLLUP에 의한 그룹 결과와 GROUP BY 절에 기술된 조건에 따라 그룹 조합을 만드는 연산자
  – GROUP BY 절에 칼럼의 수가 n개이면 CUBE 그룹핑 조합은 2n개

• 사용법

ROLLUP, CUBE 연산자의 개념도

ROLLUP 연산자 예

• 사용 예

– 소속 학과별로 교수 급여 합계와 모든 학과 교수들의 급여 합계를 출력하여라

• 사용 예

– ROLLUP 연산자를 이용하여 학과 및 직급별 교수 수, 학과별 교수 수, 전체 교수 수를 출력하여라.

CUBE 연산자 예

• 사용 예

– CUBE 연산자를 이용하여 학과 및 직급별 교수 수, 학과별 교수 수, 전체 교수 수를 출력하여라.

GROUPING 함수

• GROUPING 함수

  – 인수로 지정된 칼럼이 ROLLUP이나 CUBE 연산자로 생성된 그룹조합에서 사용되었는지 여부를 1

     또는 0으로 반환
  – 사용하면 0, 아니면 1

• 사용법

• 사용 예

– 전체 학생을 학과와 학년별로 그룹화한 후, 학과와 학년별 그룹인원수, 학과별 인원수, 각 그룹 조합에서

   학과와 학년 칼럼이 사용되었는지 여부를 출력하여라

• GROUPING SETS 함수

  – GROUP BY 절에서 그룹 조건을 여러 개 지정할 수 있는 함수
  – 각 그룹 조건에 대해 별도로 GROUP BY 한 결과를 UNION ALL 한 결과와 동일

• 사용법

– GROUPING SETS : 괄호를 사용하여 복수 개의 그룹 조건 지정가능

• 사용 예

– 학과 내에서 학년별 인원 수와 태어난 연도별 인원수를 출력하여라.

 

 

4. HAVING 절

HAVING 절

• HAVING 절

  – GROUP BY 절에 의해 생성된 그룹을 대상으로 조건을 적용

• HAVING 절의 실행 과정

  – 테이블에서 WHERE 절에 의해 조건을 만족하는 행 집합을 선택
  – 행 집합을 GROUP BY 절에 의해 그룹핑
  – HAVING 절에 의해 조건을 만족하는 그룹을 선택

• 사용법

• 사용 예

– 학생 수가 4명 이상인 학년에 대해서 학년, 학생 수, 평균 키, 평균 몸무게를 출력하여라. 단, 평균 키와

   평균 몸무게는 소수점 첫 번째 자리에서 반올림하고, 출력순서는 평균 키가 높은 순부터 내림차순으로

   출력하여라.

HAVING 절과 WHERE 절의 성능 차이

• HAVING 절

  – 내부 정렬 과정에 의해 그룹화된 결과 집합에 대해 검색 조건 실행

• WHERE 절

  – 그룹화하기 전에 먼저 검색 조건 실행

    • 실무 데이터베이스 관점

  – WHERE 절의 검색 조건을 먼저 실행하는 방법이 효율적
    • 그룹화하는 행 집합을 줄여서 내부 정렬 시간을 단축
    • SQL 처리 성능 향상

• 사용 예

– WHERE 절과 HAVING 절에서 일반 조건을 사용한 성능 비교

WHERE 절에서 그룹 함수를 사용한 경우

• WHERE 절에서 그룹 함수를 사용한 경우

함수의 중첩

• SQL 함수의 중첩 사용

  – SQL 함수 여러 개의 SQL 함수 중첩 사용 가능
  – 중첩된 함수 처리 순서

    • 맨 안쪽 함수부터 처리한 후, 처리 결과를 가장 가까운 바깥쪽 함수에 넘김

• 사용 예

– 학과별 학생의 평균 몸무게 중 최대 평균 몸무게를 출력하여라

• 실습 예

– 학과별 학생 수가 최대 또는 최소인 학과의 학생 수를 출력하여라