코딩 공부/DataBase
[DataBase] 07_그룹함수와 그룹조회
chocbi
2020. 5. 11. 15:14
그룹함수와 그룹조회
#01. 그룹함수
- 그룹 함수란?
- 그룹함수는 테이블의 전체 행을, 하나 이상의 칼럼을 기준으로 칼럼 값에 따라 그룹화하여 그룹별로 결과를 출력하는 함수이다.
| 종류 | 설명 |
|---|---|
| COUNT | 행의 갯수 출력 |
| MAX | NULL을 제외한 모든행에서 최대값 출력 |
| MIN | NULL을 제외한 모든 행에서 최소값 출력 |
| SUM | NULL을 제외한 모든 행의 합계 |
| AVG | NULL을 제외한 모든 행의 평균값 |
예제1
- 3학년 학생은 총 몇 명인가?
mysql> select count(studno) from student where grade = '3';
+---------------+
| count(studno) |
+---------------+
| 2 |
+---------------+예제2
-101번 학과에 소속된 교수들의 보직 수당 조회
mysql> select comm from professor where deptno = 101;
+------+
| comm |
+------+
| 20 |
| 15 |
| NULL |
| NULL |
+------+예제3
- 101 학과에 소속된 교수들 중에서 보직수당을 받는 교수의 수를 조회하시오.
- COUNT 함수는 지정된 컬럼에서 NULL 데이터는 제외하고 집계하기 때문에, 보직수당이 결정되지 않은 교수는 집계에서 제외된다.
mysql> select count(comm) from professor where deptno = 101;
+-------------+
| count(comm) |
+-------------+
| 2 |
+-------------+예제4
- 101 학과에 소속된 교수는 모두 몇 명인가?
- 전체 데이터수를 조회하기 위해서는 COUNT 함수에 '*'을 지정하거나 NULL 데이터가 저장되어 있지 않은 컬럼을 지정한다.
- 일반적으로 NULL이 저장되지 않는 컬럼을 지정하는 것이 처리속도가 더 빠르다.
mysql> select count(*) from professor where deptno = 101;
+----------+
| count(*) |
+----------+
| 4 |
+----------+예제5
- 급여를 가장 많이 받는 교수는 얼마를 받는가?
mysql> select max(sal) from professor;
+----------+
| max(sal) |
+----------+
| 500 |
+----------+예제6
- 급여를 가장 적게 받는 교수는 얼마를 받는가?
mysql> select MIN(sal) from professor;
+----------+
| MIN(sal) |
+----------+
| 210 |
+----------+예제7
- 한달에 지급되는 교수의 급여는 모두 얼마인가?
mysql> select sum(sal) from professor;
+----------+
| sum(sal) |
+----------+
| 2900 |
+----------+예제8
-학생들의 평균 키는 얼마인가?
mysql> select avg(height) from student;
+-------------+
| avg(height) |
+-------------+
| 171.5625 |
+-------------+예제9
- 101번 학과 학생들의 몸무게 평균과 합계를 출력하시오.
mysql> select avg(weight), sum(weight) from student where deptno=101;
+-------------+-------------+
| avg(weight) | sum(weight) |
+-------------+-------------+
| 68.0000 | 544 |
+-------------+-------------+#02. 그룹조회
1) GROUP BY 절
-특정 칼럼 값을 기준으로 테이블의 전체 행을 그룹별로 나누기 위한 절.
select { 컬럼이름 [ as 별칭] ..., 그룹함수(컬럼명)} from <테이블이름>
[ where 검색조건]
[ group by 컬럼이름 ]
[ order by 컬럼이름 [정렬옵션] ] - 그룹핑 전에 WHERE절을 사용하여 그룹 대상 집합을 머저 선택가능.
- GROUP BY 절에는 반드시 칼럼이름이 포함되어야 하며 별명 사용 불가
- SELECT 절에서 집계 함수 없이 나열된 칼럼 이름이나 표현식은 GROUP BY절에 반드시 포함되어야 함.
- GROUP BY 절에 나열된 칼럼 이름은 SELECT 절에 명시하지 않아도 됨.
- SELECT 절에서 그룹함수를 사용할 경우 group by 절에서 나눈 그룹 안에서 집계를 수행한다.2) 그룹조회와 집계 함수의 관계
mysql> select deptno, name from professor order by deptno;
+--------+--------+
| deptno | name |
+--------+--------+
| 101 | 김도훈 |
| 101 | 성연희 |
| 101 | 이만식 |
| 101 | 전은지 |
| 102 | 염일웅 |
| 102 | 권혁일 |
| 201 | 이재우 |
| 202 | 남은혁 |
+--------+--------+- 학과 번호로 그룹을 형성하면 교수의 이름을 어떻게 처리 해야할까?
mysql> select deptno, name from professor group by deptno;
// 오류가 일어난다. 과목코드로 묶어야하는 이름이 여러개여서 오류가 난다.- group by 절에 명시되지 않은 컬럼을 SELECT에서 사용할 경우에는 집계함수를 사용하여 각 그룹별 통계 데이터를 얻을 수 있다.
mysql> select deptno, count(name) from professor group by deptno;
+--------+-------------+
| deptno | count(name) |
+--------+-------------+
| 101 | 4 |
| 102 | 2 |
| 201 | 1 |
| 202 | 1 |
+--------+-------------+예제1
- 교수 테이블에서 학과별로 교수 수와 보직 수당을 받는 교수 수를 출력하시오.
mysql> select deptno, count(*), count(comm)
-> from professor
-> group by deptno;
+--------+----------+-------------+
| deptno | count(*) | count(comm) |
+--------+----------+-------------+
| 101 | 4 | 2 |
| 102 | 2 | 1 |
| 201 | 1 | 0 |
| 202 | 1 | 1 |
+--------+----------+-------------+3) 다중 칼럼을 이용한 그룹별 검색
- GROUP BY 절에서 두 개 이상의 컬럼을 콤마(,)로 구분하여 명시할 경우 첫번째 컬럼에 대한 그룹을 형성하고, 각 그룹 안에서 두 번째 컬럼에 대한 2차 그룹을 형성한다.
select { 칼럼이름 [ as 별칭] ..., 그룹함수(컬럼명)} from <테이블이름>
[where 검색조건]
[group by 컬럼1이름, 컬럼2이름, ... , 컬럼n이름 ]
[order by 컬럼이름 [정렬옵션] ]예제1
- 학생 테이블에서 전체 학생을 소속 학과별로 나누고, 같은 학과 학생은 다시 학년별로 그룹핑하여, 학과와 학년별로 인원수, 평균 몸무게를 출력
mysql> select deptno, grade, count(*), avg(weight)
-> from student
-> group by deptno, grade;
+--------+-------+----------+-------------+
| deptno | grade | count(*) | avg(weight) |
+--------+-------+----------+-------------+
| 101 | 4 | 2 | 82.0000 |
| 101 | 1 | 2 | 62.0000 |
| 101 | 3 | 1 | 88.0000 |
| 101 | 2 | 3 | 56.0000 |
| 102 | 2 | 1 | 48.0000 |
| 102 | 4 | 1 | 92.0000 |
| 102 | 1 | 1 | 68.0000 |
| 102 | 3 | 1 | 70.0000 |
| 201 | 1 | 3 | 65.3333 |
| 201 | 2 | 1 | 51.0000 |
+--------+-------+----------+-------------+#03. 집계결과에 대한 조건 지정
1) HAVING절
- SELECT 명령문의 WHERE절과 비슷한 기능을 하는 것으로 GROUP BY절에서 조건 검색을 할 경우 반드시 HAVING 절을 사용해야 한다.
select {컬럼이름 [ as 별칭]..., 그룹함수(컬럼명)} from <테이블이름>
[where 검색조건]
[group by 컬럼1이름, 컬럼2이름, ... , 컬럼n이름 ]
[having 검색조건 ]
[order by 컬럼이름 [정렬옵션] ]2) HAVING절을 사용해야 하는 경우
- 집계 함수에 대한 검색 조건을 지정하고자 할 경우에는 GROUP BY 절 뒤에 HAVING절을 사용해야 한다.
- count(*)의 결과가 4보다 큰 데이터만 조회되었다.
mysql> select grade, count(*), avg(height) avg_height, AVG(weight) avg_weight
-> from student
-> group by grade
-> having count(*) > 4
-> order by avg_height desc;
+-------+----------+------------+------------+
| grade | count(*) | avg_height | avg_weight |
+-------+----------+------------+------------+
| 1 | 6 | 175.3333 | 64.6667 |
| 2 | 5 | 164.8000 | 53.4000 |
+-------+----------+------------+------------+예제1
- 동일 학과 내에서 같은 학년에 재학중인 학생 수가 3명이상인 그룹의 학과 번호, 학년, 학생 수, 최대 키, 최대 몸무게를 출력하세요.
mysql> select deptno, grade, count(*), max(height), max(weight)
-> from student
-> group by deptno, grade
-> having count(*) >= 3
-> order by deptno;
+--------+-------+----------+-------------+-------------+
| deptno | grade | count(*) | max(height) | max(weight) |
+--------+-------+----------+-------------+-------------+
| 101 | 2 | 3 | 171 | 72 |
| 201 | 1 | 3 | 184 | 70 |
+--------+-------+----------+-------------+-------------+
2 rows in set (0.01 sec)#### 연습문제
- 101번 학과 학생들 중에서 최대 키와 최소키를 출력하여라.
mysql> select max(height), max(weight)
-> from student
-> where deptno = '101';
+-------------+-------------+
| max(height) | max(weight) |
+-------------+-------------+
| 186 | 92 |
+-------------+-------------+- 학과별로 소속 교수들의 평균 급여, 최소 급여, 최대 급여를 출력하여라.
mysql> select avg(sal), min(sal), max(sal)
-> from professor
-> group by deptno;
+----------+----------+----------+
| avg(sal) | min(sal) | max(sal) |
+----------+----------+----------+
| 372.5000 | 210 | 500 |
| 345.0000 | 240 | 450 |
| 320.0000 | 320 | 320 |
| 400.0000 | 400 | 400 |
+----------+----------+----------+- 학과별로 평균 몸무게와 학생수를 출력하되 평균 몸무게의 내림차순으로 정렬하세요.
mysql> select avg(weight), count(*)
-> from student
-> group by deptno
-> order by avg(weight) desc;
+-------------+----------+
| avg(weight) | count(*) |
+-------------+----------+
| 69.5000 | 4 |
| 68.0000 | 8 |
| 61.7500 | 4 |
+-------------+----------+- 학과별 교수 수가 2명 이하인 학과의 학과 번호, 교수 수를 출력하세요.
mysql> select deptno, count(*)
-> from professor
-> group by deptno
-> having count(*) <= 2;
+--------+----------+
| deptno | count(*) |
+--------+----------+
| 102 | 2 |
| 201 | 1 |
| 202 | 1 |
+--------+----------+