그룹함수
그룹함수는 복수행 함수라고도 하며, 여러행에 대한 정보를 조회하는 함수이다.
count()
- 레코드 건수
- null값 제외한 건수
- count(컬럼)
select count(*) 전체인원수, count (bonus) 보너스 from professor;
위 코드는 교수라는 테이블에서 교수 전체 인원수와 교수 중 보너스 받는 교수의 인원수를 조회하는 코드이다. 이 때 보너스 컬럼값이 null이면 counting에서 빠지기 때문에 전체인원수에서 보너스가 null값이 교수를 제한것이 count(bonus)가 될 것이다.
group by
- 그룹함수 처리명령어
- 컬럼의 데이터를 기준으로 레코드를 그룹화
- 그룹함수와 컬럼의 값을 조회하기 위해서는 반드시 조회되는 컬럼으로 group by를 설정
- count와 같은 그룹함수와 같이 사용하여 출력
select grade, count(*) from student group by grade;
위 코드는 학생이라는 테이블에서 학년과, 학년별 학생수를 출력하는 코드이다.
이때, group by와 그룹함수 count를 같이 사용하여 전체 학생수를 count로 조회하고,
이를 group by로 수를 나뉘어 출력하였다.
sum()
- 합계
- 컬럼의 값 총 합을 출력
select sum(pay) from professor;
위 코드는 교수테이블에서 교수들이 받는 월급의 총합을 출력하는 함수다.
avg()
- 평균
- 컬럼의 값 총 평균을 출력
- 모든 그룹함수는 null값이면 제외
- 평균산출에서도 null 값은 제외 (주의)
select deptno, avg(pay) from professor group by deptno;
위 코드는 교수들에게 지급되는 부서별 전체 급여 합계를 출력한 함수이다.
이때 전체 평균이 아닌 부서별 평균을 group by를 사용하여 출력하였다.
select deptno, count(*), avg(pay), avg(bonus), avg(nvl(bonus,0)) from professor group by deptno;
위 코드는 교수테이블에서 급여, 보너스 평균을 조회하는 코드이다. 이때, 보너스가 null값이라면 평균을 낼 때 제외가 된다. 이런 경우 null값을 포함하기 위해서 nvl함수를 이용해 null값을 0으로 변환해주고, 평균을 산출한다.
(평균 구할때 매우 주의!!!!)
max(), min()
- 최대값 최소값 함수
select max(height), min(height) from student;
위 코드는 학생테이블에서 키가 가장 큰 학생과 작은 학생을 출력하는 코드이다. 이때 학과별로 가장 큰 학생과 작은 학생을 출력하고 싶다면 group by deptno1을 붙여주면 된다.
having 구문
- 그룹함수의 조건문
select grade, max(height), min(height), avg(height) from student group by grade having avg(height) >= 170;
위 코드는 학년별로 최대 최소 평균 키를 조회하는데 이때, having 구문을 사용하여 평균키가 170이상인 학년만 조회를 한 코드이다. 그룹에서 조건문은 where이 아닌 having 구문을 사용한다.
- select 컬럼명들 || *(모든컬럼) from 테이블명 -> 필수
- [where 조건문] -> 레코드 선택의 조건
- [group by 컬럼] -> 그룹함수 사용시 그룹화 기준 컬럼
- [having 조건문] -> 그룹함수 조건문
- [order by 컬럼명||별명||컬럼순서 [desc || asc]] -> sql문 맨 뒤에 오며 컬럼 순서 정렬
위 정리를 기준으로 문제풀이를 해봅시다.
문제 : 주민번호를 기준으로 남학생과 여학생의 최대키, 최소키, 평균키를 출력하기
주민번호의 7번째 자리가 1: 남학생, 2: 여학생select decode(substr(jumin,7,1),1, '남학생',2,'여학생'), max(height), min(height), avg(height) from student group by substr(jumin,7,1);
위 코드에서 그룹화 할때 그 기준은 주민번호 7번째 자리를 기준으로 하고 이를
남학생과 여학생으로 나누어 출력하기 위해 decode를 사용하였다.
변형으로 더 풀어보자
문제 : 주민번호를 기준으로 여학생의 최대키, 최소키, 평균키를 출력하기
select substr(jumin,7,1) 여학생, max(height), min(height), avg(height) from student where substr(jumin,7,1)=2 group by substr(jumin,7,1);
위 코드는 이전 문제와 달리 여학생만 출력하도록 조건을 수정해 보았다.
이전 코드는 decode를 사용하여 남학생과 여학생을 구분 했다면, 이번에는 where조건문을 사용하여 주민번호가 7번째인 여학생만 출력하게 하였다.
좀 더 코드를 간결화 한다면 아래와 같이 작성할 수 있다.
select max(height), min(height), avg(height) from student where substr(jumin,7,1)=2;
stddev(), variance()
- stddev() : 표준편차함수
- variance() : 분산함수
select stdevv(height) 키편차, stdevv(weight) 채중편차, variance(height) 키분산, variance(weight) 체중 분산 from student;
위 코드는 학생의 키와 체중 편차를 구하는 코드이다.
표준편차함수와 분산함수는 주로 데이터 분석할 때 주로 사용된다.
