그룹화(GROUP BY) , 그룹 함수(SUM, AVG, MAX, MIN, COUNT, STDDEV, VARIANCE)

그룹화 : group by

컬럼 그룹화

SELECT 컬럼 FROM 테이블 GROUP BY 그룹화할 컬럼;

 

조건 처리 후에 컬럼 그룹화

SELECT 컬럼 FROM 테이블 WHERE 조건식 GROUP BY 그룹화할 컬럼;

 

컬럼 그룹화 후에 조건 처리

SELECT 컬럼 FROM 테이블 GROUP BY 그룹화할 컬럼 HAVING 조건식;

 

 

그룹함수(집단함수) : SUM, AVG, MAX, MIN, COUNT, STDDEV(표준편차), VARIANCE(분산)

그룹함수는 함수명(컬럼명) 으로 사용하면 그룹을 묶은 기준으로 해당 컬럼을 가지고 계산한다.

그룹함수명(계산하고싶은 컬럼명) FROM 테이블명 GROUP BY 그룹 기준 컬럼

 

학생테이블 학과별 평점 평균 조회하고 싶다면, 학과가 그룹의 기준이 된다.

SELECT MAJOR_NO, AVG(SCORE) FROM STUDENT GROUP BY MAJOR_NO;

그룹을 묶었을 때는 개개인의 모든 데이터를 가져올 수 없다. 그룹으로 묶은 컬럼(MAJOR_NO)과 그것을 기준으로 계산한 그룹함수의 결과(AVG)만 출력할 수 있다.

 

SELECT MODEL_YEAR, MAKER, SUM(EA) FROM CAR GROUP BY MODEL_YEAR, MAKER;

그룹화 기준을 여러개로 묶을 수도 있다. 

 

 

 

EX. 학과별 평점 최대값, 최소값 조회

SELECT MAJOR_NO, MAX(SCORE), MIN(SCORE) FROM STUDENT GROUP BY MAJOR_NO;

MAX, MIN 이용하면 된다.

 

SELECT MAJOR_NO, MAX(SCORE) AS MAX_SCORE, MIN(SCORE) AS MIN_SCORE FROM STUDENT GROUP BY MAJOR_NO;

그룹 함수 결과 컬럼명을 바꾸고 싶으면 AS 를 이용할 수 있다.

설정해준 대로 컬럼명이 바뀌었다.

 

 

출력 값에 문자열을 추가하고 싶으면 ||을 이용한다.

SELECT MAJOR_NO, MAX(SCORE)||'점', MIN(SCORE)||'점' FROM STUDENT GROUP BY MAJOR_NO;

이렇게 데이터값에 '점'이라는 문자가 추가된 것을 확인할 수 있다. 데이터를 이용할 때는 문자열이 있으면 안되니 주의해야한다.

 

 

 

EX. 학과별 학생 인원수 조회 

SELECT MAJOR_NO, COUNT(*) FROM STUDENT GROUP BY MAJOR_NO;

학과별로 GROUP을 나누고 COUNT를 이용하면 된다. 

 

 

 

EX. 학과별 학점 표준편차, 분산 조회

SELECT MAJOR_NO, TRUNC(STDDEV(SCORE),2), TRUNC(VARIANCE(SCORE),2) FROM STUDENT GROUP BY MAJOR_NO;

표준편차, 분산은 소수점이 길어서 TRUNC나 ROUND로 소수점을 줄여준다. TRUNC 자리수 2로 설정했기 때문에 소수점 둘째자리까지 나온다.

 

 

 

EX. 학과별 평점이 3.0 이상인 학생 인원수 조회

SELECT MAJOR_NO, COUNT(*) FROM STUDENT WHERE SCORE >=3.0 GROUP BY MAJOR_NO;

조건을 넣고 싶으면 WHERE을 사용하면 된다. GROUP BY 그룹을 묶어주는 것은 가장 뒤에 나온다.

만약, 3.0을 넘는 학생이 없는 학과라면 학과 정보 자체가 나오지 않는다. 

 

 

 

EX. 학과별 평점의 평균을 조회, 단 학과별 인원수가 3명 이상인 학과들만 대상으로 조회

-- SELECT MAJOR_NO, AVG(SCORE) FROM STUDENT WHERE COUNT(*)>=3 GROUP BY MAJOR_NO; --error!

바로 위의 WHERE 조건 형태로 작성 후 실행해보았는데, "group function is not allowed here" 오류가 발생한다.

WHERE에는 GROUP이 되지 않은 상태이기때문에 그룹함수 COUNT(*)를 사용할 수 없다. GROUP BY 로 그룹을 묶어줘야 그룹함수를 실행할 수 있는데, WHERE절에서는 그룹화가 되지 않았기 때문이다. 

 

 

그룹화에 조건을 넣을 때, 그룹함수를 이용할 때는 WHERE이 아닌 HAVING을 이용한다.

SELECT MAJOR_NO, AVG(SCORE) FROM STUDENT GROUP BY MAJOR_NO HAVING COUNT(*) >= 3;

HAVING 절에 그룹함수 조건식이 들어간다. 데이터가 그룹화 되어야 함수를 적용할 수 있을 때 HAVING을 쓰는 것이다.

 

 

 

EX. 학과별 평점의 평균을 조회, 평점의 평균이 3.0 이상인 학과들만 조회

SELECT MAJOR_NO, AVG(SCORE) FROM STUDENT GROUP BY MAJOR_NO HAVING AVG(SCORE) >= 3.0;

 

 

EX. 자동차의 재고가 50 이하이고, 모델의 평균가격이 1000원 이상인 모델들을 대상으로 / 모델번호 앞자리 2개가 같은 모델의 개수, 차량금액 평균 조회

SELECT SUBSTR(MODEL_NO,1,2), COUNT(*), AVG(PRICE) FROM CAR WHERE EA<=50 GROUP BY SUBSTR(MODEL_NO,1,2);

여기서는 EA는 그룹화되지않고 테이블 내의 데이터를 그대로 이용하기 때문에 WHERE로 조건을 걸어주었다. 그룹화 된 이후에 함수를 적용하기 위해 HAVING을 이용할때와 비교해서 알아두어야 한다. 

 

 

 

JOIN + GROUP BY

테이블 내에서 칼럼을 기준으로 GROUP BY하여 그룹함수를 사용했었는데, JOIN을 이용하면 다른 테이블의 컬럼을 그룹화 할 수 있고 그룹함수 또한 사용할 수 있다.

 

 

EX. 학과별 평점의 평균, 인원수 조회 

select m.major_name,trunc(avg(s.score),2) as avg_score,count(*)
from student s, major m
where s.major_no = m.major_no(+)
group by m.major_name
order by avg_score desc;

 

student 테이블에는 major_name이 없는상태이다. 학생들의 major_name을 이용하기 위해선 major 테이블과 연결해야한다. major 테이블과 student 테이블을 join하여 연결시킨 다음에 major_name으로 그룹화 할 수 있다.

학생정보를 모두 나타내기 위해서 student 테이블이 아닌 major에 (+)를 붙여 외부조인을 수행한 뒤에 그룹화, 정렬까지 할 수 있다.