[SQL-오라클] 그룹 함수

그룹 함수

- 테이블의 전체 행을 하나 이상의 칼럼을 기준으로 그룹화하여 그룹별로 결과를 출력하는 함수

- 그룹함수는 통계적인 결과를 출력하는데 자주 사용

select 칼럼명, 그룹함수(칼럼명)
from 테이블명
[where 조건]
[group by 그룹_컬렴명|표현식]
[having 그룹_조건]

▪ group by : 전체 행을 그룹_컬렴명|표현식을 기준으로 그룹화

▪ having : group by절에 의해 생성된 그룹별로 조건 부여

 

그룹 함수 종류

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

 

COUNT  행 개수

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

- count(*) : null 포함 전체 행 개수

- count(칼럼명) : null 제외한 전체 행 개수 (중복값 포함)

count({*|[distinct all] expr})

▪ ' * '은 NULL을 포함한 모든 행의 개수

▪ DISTINCT 중복되는 값을 제외한 행의 개수

▪ ALL 중복되는 값을 포함한 행의 개수 (기본값)

▪ expr 인수에서 사용 가능한 데이터 타입 : char, varchar2, number, date

 

AVG 평균, SUM 합계

- 인수로 지정된 칼럼에 대해 조건을 만족하는 행 중에서 NULL 제외한 평균과 합계

- 숫자 데이터 타입만 사용 가능

avg([distinct|all] 숫자로된칼럼) # 평균
sum([distinct|all] 숫자로된칼럼) # 합계

 

MAX 최대, MIN 최소

- 인수로 지정된 칼럼에 대해 조건을 만족하는 행 중에서 최소값과 최대값

- 문자 데이터 타입에도 사용 가능

 

 

STDDEV 표준편차, VARIANCE 분산

- 인수로 지정된 칼럼에 대해 조건을 만족하는 행을 대상으로 표준편차, 분산

- 숫자 데이터 타입에만 사용 가능

- NULL은 연산에서 제외

---

데이터 그룹 생성

GROUP BY절

- 특정 칼럼 값을 기준으로 테이블의 전체 행을 그룹별로 나누기 위한 절

- GROUP BY 절에 명시되지 않은 칼럼은 그룹함수와 함께 사용할 수 없음

 

GROUP BY절 적용 규칙

- 그룹핑 전에 WHERE절을 사용하여 그룹 대상 집합을 먼저 선택

- GROUP BY절에는 반드시 칼럼 이름을 포함해야 하며, 칼럼 별명은 사용할 수 없음

- SELECT절에서 나열된 칼럼 이름이나 표현식은 GROUP BY절에서 반드시 명시되어야 함

  (GROUP BY절에 명시된 칼럼이 SELECT절에 없는 것은 가능)

 

단일 칼럼 그룹핑

- GROUP BY 절에 하나의 칼럼

 

다중 칼럼 그룹핑

- GROUP BY 절에 하나 이상의 칼럼을 사용하여 그룹을 나누고, 그룹별로 다시 서브그룹 나눔

ex) group by (deptno, postion) 전체 교수를 학과별로 먼저 그룹핑한 다음, 학과별 교수를 직급별로 다시 그룹핑 하는 경우

 

ROLLUP, CUBE 연산자

> ROLLUP 연산자

- GROUP BY 절의 그룹조건에 따라 전체 행을 그룹화하고 각 그룹에 대해 부분합을 구하는 연산자

- GROUP BY 절에 칼럼의 수가 N개이면 ROLLUP의 그룹핑 조합은 N+1개

select 칼럼명, 그룹함수(칼럼명)
from 테이블
[where 조건]
[group by rollup(그룹_칼럼명|표현식)]
[having 그룹_조건]

 

> CUBE 연산자

- ROLLUP에 의한 그룹 결과와 GROUP BY 절에 기술된 조건에 따라 그룹 조합을 만드는 연산자

- GROUP BY절에 칼럼의 수가 N개이면 CUBE 그룹핑 조합은 2N개

select 칼럼명, 그룹함수(칼럼명)
from 테이블
[where 조건]
[group by cube(그룹_칼럼명|표현식)]
[having 그룹_조건]

 

GROUPING 함수

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

- 사용하면 0, 아니면 1

- grouping 함수에 사용되는 인수는 group by 절에 지정된 칼럼 중 하나여야 함

select 칼럼명, 그룹함수(칼럼명), grouping(칼럼명)
from 테이블
[where 조건]
[group by [rollup|cube] 그룹_칼럼명|표현식]
[having 그룹_조건]

 

GROUPING SETS 함수

- GROUP BY절에서 그룹 조건을 여러 개 지정할 수 있는 함수

- 각 그룹 조건에 대해 별도로 GROUP BY한 결과를 UNION ALL 한 결과와 동일

select 칼럼명, 그룹함수(칼럼명), grouping(칼럼명)
from table
[where 조건]
[group by [rollup|cube] 그룹_칼럼명|표현식]
          [grouping sets(칼럼명, 칼럼명, ...), ...]
[having 그룹_조건]

▪  괄호를 사용하여 복수 개의 그룹 조건 지정 가능

 

HAVING절

- GROUP BY절에 의해 생성된 그룹을 대상으로 조건을 적용 (where절은 from절에서 지정된 테이블 전체 대상)

- HAVING절의 실행 과정

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

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

- where절 그룹화하기 전에 먼저 검색 조건 실행 -> 그룹화하는 행 집합 줄여서 내부 정렬 시간 단축

- where절에서는 그룹함수 사용 불가(아직 group화 이루어지지 않음)

select 칼럼명, 그룹함수(칼럼명)
from 테이블
[where 조건]
[group by 그룹_칼럼명|표현식]
[having 그룹_조건]
[order by 칼럼명]

 

 

함수의 중첩

- SQL 함수 여러 개의 SQL 함수 중첩 사용 가능

- 중첩된 함수 처리 순서

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

- 함수에 대한 중첩 레벨 제한 없음 ( 실무에서는 3~4 정도가 적당 )

 

 

 

 

 

 

---

참고도서 : 오라클 중심의 SQL배움터, 우용태 외 공저, 생능출판사, 2007년