DAY07. SQL Group Function

그룹함수 : 집단별 통계(집계)를 구하는 함수
통계함수 : 다중행(vector:1차원) -> 상수값(scala:0차원)
다중행을 입력하면 함수에 의해 상수값이 출력된다. (1차원->0차원) = 차원 축소


1. SUM 함수

SELECT SUM(sal) FROM emp; -- emp테이블에서 14명의 급여의 합계가 출력 (1개의 row)
-- SELECT sal FROM emp; 는 14개의 row. 
SELECT ename, SUM (sal) FROM emp; -- error 이유 : ename은 14행, SUM절은 1행
SELECT SUM (comm) FROM emp; -- NULL 제외하고 연산

2. AVB 함수 (대표값)

SELECT AVG(sal) FROM emp;

[예제] ‘SCOTT’사원이 소속된 부서의 급여 합계와 평균을 구하시오. (서브쿼리 이용)

SELECT SUM(sal), AVG(sal)
FROM emp
WHERE deptno =
(SELECT deptno FROM emp WHERE ename='SCOTT');

3. MIN/MAX 함수 : 최소값, 최대값

SELECT MAX(sal), MIN(sal) FROM emp; -- 최대급여, 최소급여가 얼마인가
SELECT ename MAX(sal), MIN(sal) FROM emp; -- error. WHY? 단일 칼럼, 그룹함수 함께 사용X

[예제] 가장 최근에 입사한 사원의 입사일과 입사한지 가장 오래된 사원의 입사일을 출력하는 쿼리문을 작성하시오. (MAX, MIN 함수 이용)

SELECT * FROM emp;
SELECT
MIN (hiredate) "가장 오래된 입사일", MAX(hiredate) "가장 최근 입사일"
FROM emp;

4. COUNT 함수 : 해당 칼럼 값을 가지고 있는 행의 개수 (null 제외)

SELECT COUNT (comm), COUNT(*) FROM emp;
SELECT COUNT(job) 업무수 FROM emp; --업무 수
SELECT COUNT(DISTINCT job) 업무수 FROM emp; --중복되지 않는 업무 수

[예제] 30번 부서 소속 사원중에서 커미션을 받는 사원의 수를 구하시오

SELECT COUNT(*)
FROM emp
WHERE deptno=30 AND comm IS NOT NULL; -- 4명 출력. WHY? IS NOT NULL로는  커미션 값이 0인 사람도 함께 출력

SELECT COUNT(*)
FROM emp
WHERE deptno=30 AND comm>0; -- 1원이라도 커미션 받는 사람이 출력됨

SELECT ename, comm, deptno
FROM emp
WHERE deptno=30 AND comm>0;

5. 분산/표준편차 : 산포도(자료가 흩어진 정도를 하나의 수로 나타낸 값)
* 분산과 표준편차가 작을수록 자료값들이 평균에 모여있으므로 자료의 분포 상태가 고른 것이다.

SELECT VARIANCE(sal) FROM emp; -- 분산
SELECT STDDEV(sal) FROM emp; -- 표준편차
SELECT SQRT(VARIANCE(sal)) FROM emp; --표준편차

 

6. GROUP BY 절 : 특정 컬럼값을 기준으로 그룹화

/*
SELECT 칼럼명, 그룹함수
FROM 테이블명
WHERE 조건 (연산자) 
GROUP BY 범주형 칼럼명;

* 범주형(카테고리형) 칼럼 : 부서, 직급, 성별 등 집단을 형성할 수 있는 칼럼
* 연속성 칼럼 : 급여 등
*/

[예제] 소속 부서별 평균 급여를 구하기
* 그룹바이 절이 있으면 'SELECT 여기, 함수(인수)'에 칼럼명 삽입 가능
* 그룹바이 절이 있으면 일반 집계함수(ROUND)와 함께 사용 가능

SELECT deptno, ROUND (AVG(sal))
FROM emp
GROUP BY deptno;

[예제] 소속 부서별 최대 급여와 최소 급여를 구하기

SELECT deptno, MAX(sal), MIN(sal)
FROM emp 
GROUP BY deptno
ORDER BY deptno;

 

[예제4] 부서별로 가장 급여를 많이 받는 사원의 정보(사원 번호, 사원이름, 급여, 부서번호)를 출력하시오.
(IN, MAX(), GROUP BY, subQuery 이용)

SELECT * FROM dept;
SELECT * FROM emp;

SELECT empno, ename, sal, deptno
FROM emp
WHERE sal IN
(SELECT MAX(sal) FROM emp GROUP BY deptno); -- 각 부서의 최대 sal값 출력 (다중행 서브쿼리)

7. HAVING절 : GROUP BY절에서 조건에 따라 검색
/*
일반 SQL문 : WHERE 조건식
GROUP BY절 : HAVING 조건식
*/

SELECT deptno, AVG(sal) 
FROM emp
GROUP BY deptno
HAVING AVG(sal) >= 2000;

SELECT deptno, AVG(sal) 
FROM emp
GROUP BY deptno
WHERE AVG(sal) >= 2000; -- error. WHY? GROUP BY절에서는 WHERE (X)

[예제] 부서의 최대값과 최소값을 구하되 최대 급여가 2900이상인 부서만 출력

SELECT deptno, MAX(sal), AVG(sal)
FROM emp
GROUP BY deptno
HAVING MAX(sal) > 2900;

 

[예제] 예제4의 결과에서 'SCOTT' 사원을 제외하고, 급여(SAL)를 내림차순으로 정렬하시오.

SELECT empno, ename, sal, deptno
FROM emp
WHERE ename != 'SCOTT' AND sal IN -- AND절의 sal은 서브쿼리에서 넘어온 값
(SELECT MAX(sal) FROM emp GROUP BY deptno)
ORDER BY sal DESC;

 

 

 

 

 

연습문제

 

/*
 * 집합 함수(COUNT,MAX,MIN,SUM,AVG) 
 * 작업 대상 테이블 : EMP, STUDENT, PROFESSOR
 */

Q1. PROFESSOR 테이블에서 POSITION의 수 출력하기

SELECT COUNT (DISTINCT position) 직위수
FROM professor;

Q2. EMP 테이블에서 소속 부서별 최대 급여와 최소 급여 구하기

SELECT deptno, MAX(sal), MIN(sal)
FROM emp
GROUP BY deptno; -- ~별로 : GROPU  BY

Q3. EMP 테이블에서 전체 사원의 급여에 대한 분산과 표준편차 구하기

SELECT VARIANCE(sal), STDDEV(sal) FROM emp; -- 분산

Q4. EMP 테이블에서 각 부서별 사원수와 수당이 null 아닌 사원수를 카운트 하시오.

--<출력 결과>
/*
부서번호     전체사원수     수당사원수
30        6        4  
20        5        0
10        3        0 
*/

SELECT deptno 부서번호, COUNT (*) 전체사원수, COUNT (comm) 수당사원
-- COUNT 함수의 특징 : NULL이 아닌 값만 출력하기 때문에 IS NOT NULL 명령이 없어도 됨
FROM emp
GROUP BY deptno;

Q5. PROFESSOR 테이블에서 학과별 급여(pay) 평균이 400 이상 레코드 출력하기

SELECT deptno, AVG(pay)
FROM professor
GROUP BY deptno -- 학과별
HAVING AVG(pay) >= 400;

Q6. PROFESSOR 테이블에서 학과별,직위별 급여(pay) 평균 구하기

SELECT deptno, AVG(pay)
FROM professor
GROUP BY deptno, position; -- 1차 : 학과별, 2차 : 직위별 출력
-- 같은 학과의 같은 직책끼리 급여를 계산해줌

Q7. STUDENT 테이블에서 grade별로 weight, height의 평균값, 최대값, 최소값을 구한 결과에서 키의 평균이 170 이하인 경우 구하기

SELECT grade, AVG(weight), MAX(weight), MIN(weight), AVG(height), MAX(height), MIN(height)
FROM student
GROUP BY grade -- grade별
HAVING AVG(height) <=170 ; -- 키 평균 170 이하