[Oracle] GROUP BY(그룹핑), 그룹 함수

그룹 함수

테이블의 전체 행을 하나 이상의 칼럼으로 그룹화하여 각 그룹의 결과를 출력

SELECT column, group function(column)
     FROM table
    [WHERE condition]
    [GROUP BY group_by_column]
    [HAVING group_condition]

GROUP BY : group_by_column(칼럼)을 기준으로 그룹화함

HAVING : 그룹화하여 생성된 그룹에 조건 추가.

 

그룹 함수의 종류

종류	의미
COUNT	행의 개수 출력
MAX	NULL을 제외한 최대 값
MIN	NULL을 제외한 최소값
SUM	NULL을 제외한 합계
AVG	NULL을 제외한 평균 값
STDDEV	NULL을 제외한 표준편차
VARIANCE	NULL을 제외한 분산 값
GROUPING	해당 칼럼이 그룹에 사용되었는가? (1 또는 0을 반환)
GROUPING SETS	한번의 질의로 여러 개의 그룹화 가능

 

COUNT 함수

행의 개수를 반환 ( 조건이 있으면 조건을 만족하는 )

COUNT( * | [DISTINCT|ALL] expr )

• '*'은 NULL을 포함하여 행의 개수를 count 한다.
• DISTINCT는 중복되는 값을 제외한(한 번만 카운트한) 행의 개수
• ALL은 중복되는 값을 포함한다.(기본값이 ALL이다.)
• expr에 사용 가능한 타입은 CHAR, VARCHAR2, NUMBER, DATE 타입

 

EX) 학과(deptno)가 101인 교수 중에서 보직수당(comm)을 받는 교수의 수

SELECT COUNT(*)
    FROM professor
    WHERE deptno=101 AND comm IS NOT NULL;

또는

SELECT COUNT(comm)
    FROM professor
    WHERE deptno=101;

-> COUNT는 NULL인 행을 제외하므로 comm IS NOT NULL과 동일한 결과

 

EX) 지도 교수가 있는 학생의 수를 출력하라. (DISTINCT의 예시이다.)

SELECT COUNT(DISTINCT profno)
    FROM student;

학생은 지도 교수(profno)를 갖는다. 여러 학생이 같은 지도 교수를 가질 수 있으니 이 값이 중복되어 있다.

그래서 DISTINCT로 지도 교수의 중복을 한 번만 카운트하여 답을 구할 수 있다.

 

DISTINCT를 사용하지 않는다면 그냥 지도 교수의 개수를 세버 릴 것이다.

SELECT COUNT(profno)
    FROM student;

AVG, SUM 함수

평균(AVG), 합계(SUM)를 반환한다. 데이터 타입은 NUMBER 타입만 가능하다.

AVG( [DISTINCT|ALL] expr )
SUM( [DISTINCT|ALL] expr )

 

EX) 101번 학과(deptno) 학생들의 몸무게 평균과 합계

SELECT AVG(weight), SUM(weight)
    FROM student
    WHERE deptno=101;

 

* 직원들의 월급(급여+보직수당) 총액을 구할때 급여, 보직수당 어느 하나가 NULL이 있으면 월급 또한 NULL이 되므로

  SUM(급여+보직수당)의 결과가 제대로 안나온다. SUM(급여) + SUM(보직수당)으로 작성해야한다.

 

MIN, MAX 함수

최소(MIN)와 최대(MAX)를 결과로 반환한다.

 

EX) 102번 학과(deptno) 학생 중 최대 키와 최소 키

SELECT MAX(height), MIN(height)
    FROM student
    WHERE deptno=102;

 

STDDEV, VARIANCE 함수

EX) 교수 테이블에서 급여(sal)의 표준편차와 분산을 출력

SELECT STDDEV(sal), VARIANCE(sal)
    FROM professor;

 

GROUP BY

특정 칼럼의 값으로 그룹을 나누어주는 GROUP BY 절이다.

GROUP BY 절에 사용되지 않은 칼럼은 그룹 함수와 함께 SELECT절에 사용할 수 없다.

그룹화 전에 WHERE 절에서 조건을 먼저 걸 수 있다.

SELECT 절에 칼럼을 사용한다면 그 칼럼은 GROUP BY 절에서 명시되어 있어야 함.

 

EX) GROUP BY절에 사용하지 않은 SELECT절 칼럼

SELECT deptno, position, AVG(sal)
    FROM professor
    GROUP BY deptno;

학과로 그룹을 지었는데 직위(position)는 각각 그룹의 직위를 어떻게 표시하겠는가? 불가능하다.

 

단일 칼럼으로 그룹화

EX) 교수 테이블에서 학과별로 교수 수와 보직수당을 받는 교수 수를 출력

SELECT deptno, COUNT(*) "교수 수", COUNT(comm) "수당받는 교수 수"
    FROM professor
    GROUP BY deptno;

 

다중 칼럼으로 그룹화

칼럼으로 그룹을 나누고, 또 그 그룹에서 칼럼으로 그룹을 나눈다.

교수는 학과와 직급이 있다. -> 교수를 학과 별로 그룹화하고 거기서 또 직급별로 그룹화가 가능

 

EX) 학생을 학과별로 나누고, 같은 학과 학생은 학년별로 그룹화하여,

       학과와 학년별로 인원수, 평균 몸무게 출력 (평균 몸무게는 소수점 첫 번째 자리에서 반올림)

SELECT deptno, grade, COUNT(*) "인원수", ROUND(AVG(weight)) "평균 몸무게"
    FROM student
    GROUP BY deptno, grade
    ORDER BY deptno;