[DBMS] AggregateFunction

그룹합수

여러개의 레코드의 필드 값을 계산하여 값을 내는 함수
다양한 그룹함수들이 있다.
 

Name	Description
AVG()	평균값 출력
BIT_AND()	비트 연산 AND 값
BIT_OR()	비트 연산 OR 값
BIT_XOR()	비트 연산 XOR값
COUNT(DISTINCT)	서로 구분되는 레코드 개수 출력
COUNT()	레코드 개수 출력
GROUP_CONCAT()	문자열 연결하여 출력
MAX()	최대값 출력
MIN()	최소값 출력
STD()	표준편차
STDDEV_POP()	Return the population standard deviation
STDDEV_SAMP()	Return the sample standard deviation
STDDEV()	Return the population standard deviation
SUM()	합산 출력
VAR_POP()	Return the population standard variance
VAR_SAMP()	Return the sample variance
VARIANCE()	분산출력

GROUP BY : 그룹을 묶는 단위
HAVING : 그룹함수 결과에 대한 조건 (WHERE 와 헷갈리지 않도록 주의)

그룹 함수 예
예) SELECT COUNT(*), COUNT(hpage) FROM professor;
예) SELECT COUNT(bonus), SUM(bonus) FROM professor;
예) SELECT COUNT(bonus), SUM(bonus), AVG(bonus) FROM professor;
예) SELECT MAX(hiredate), MIN(hiredate) FROM emp;

SELECT * FROM t_professor;

SELECT count(*) FROM t_professor;
SELECT count(name), count(hpage) FROM t_professor; -- 그룹함수에서 NULL 값은 (기본적으로) 계산에서 제외
SELECT count(bonus), sum(bonus), avg(bonus) FROM t_professor;
SELECT max(hiredate), min(hiredate) FROM t_emp;

-- 교수님들의 보너스 평균값 출력 ( AVG() 사용)
-- 그룹함수는 null 값은 계산안하기 때문에
-- null 허용 필드에 대해 계산할때는 반드시 ifnull 등을 사용하여 처리해야 함
SELECT
	avg(bonus),
	avg(ifnull(bonus, 0))
FROM t_professor

GROUP BY 예
예) Professor 테이블에서 학과별로 교수들의 평균 보너스를 출력하세요

SELECT deptno, AVG(bonus) FROM professor GROUP BY deptno;
SELECT deptno, AVG(IFNULL(bonus, 0)) FROM professor GROUP BY deptno;

 

- select 절에 사용된 그룹함수 이외의 컬럼이나 표현식은 반드시 group by 절에 사용되어야 함.  (그러지 않을 경우 에러 확인해보기)
- group by 절에 사용된 컬럼이 꼭 SELECT 절에 사용되지 않아도 됩니다.
- group by 절은 반드시 컬럼명이 사용되어야 하며 별명(alias) 는 사용 불가.

-- GROUP BY

-- 교수님 테이블에서 '학과별' 로 교수님들의 평균 급여를 select 하기
SELECT deptno, avg(pay) FROM t_professor;

-- SELECT 절에 그룹함수 아닌 것과 그룹함수는 같이 올수는 없다.  
-- 이 경우 그룹함수가 아닌 것들은 GROUP BY 로 묶여야 할 것이다.
SELECT deptno, avg(pay)
FROM t_professor
GROUP BY deptno;

-- # 5101
SELECT deptno, POSITION, avg(pay) "평균급여"
FROM t_professor
GROUP BY deptno, POSITION
ORDER BY deptno ASC, POSITION ASC
;

HAVING : 그룹 함수에 조건 추가

예) SELECT deptno, AVG(pay) FROM professor WHERE AVG(pay) > 450 GROUP BY deptno;

그룹함수 부분은 WHERE 사용 불가.
그룹함수를 사용하더라도 다른 부분은 WHERE 사용 가능

-- 학과별 평균급여를 출력하되 평균급여가 300보다 많은 학과만 출력
SELECT deptno, avg(pay)
FROM t_professor
WHERE avg(pay) > 300  -- 그룹함수는 절.대.로 WHERE 절에서 사용 불가.
GROUP BY deptno;

-- HAVING 절 사용 
SELECT deptno, avg(pay)
FROM t_professor
GROUP BY deptno
HAVING avg(pay) > 300
;

SELECT 쿼리문 순서 : 순서 중요!

SELECT [컬럼명 또는 표현식] 
FROM [테이블명, 뷰명]  
WHERE [조건절]  
GROUP BY [그룹할 컬럼] 
HAVING [그룹함수 조건절]   
ORDER BY [정렬열] [ASC/DESC];

 

--               처리순서
-- SELECT      -- (5)
-- FROM        -- (1)
-- WHERE       -- (2)
-- GROUP BY    -- (3)
-- HAVING      -- (4)
-- ORDER BY    -- (6)

-- #5102)연습
-- t_emp 테이블: 매니저별(MGR)로 관리하는 직원들의 
-- ‘매니저’, ‘직원수’와 ‘급여총액’과 ‘급여평균’과 ‘교통비 (COMM) 평균’ 지급액 을 출력하세요.  
-- 단 사장님은 (job = president)제외

SELECT 
	mgr "매니저",
	count(*) 직원수,
	sum(sal) 급여총액,
	avg(sal) 급여평균,
	avg(ifnull(comm, 0)) 교통비평균
FROM t_emp
WHERE job != 'PRESIDENT'
GROUP BY mgr
;