그룹 함수
- 그룹 함수: 여러 행을 한꺼번에 처리하는 함수
그룹함수의 특징
- NULL 값은 제외하고 처리 (COUNT(*) 제외)
MAX(), MIN(), COUNT(): 여러 타입의 데이터를 처리 가능SUM(), AVG(), VARIANCE(), STDDEV(): 반드시 NUMERIC값만 사용 가능- 열 별칭 사용 불가
- 그룹 함수의 결과를 제한하려면 HAVING 절 사용
- 절의 순서는 지켜야
그룹함수 종류
MAX(), MIN() 최대, 최소 함수
- 타입의 영향을 받지 않음
SELECT MAX(salary), MAX(hire_date), MAX(last_name)
FROM employees
SELECT MIN(salary), MIN(hire_date), MIN(last_name)
FROM employees
COUNT() 행의 수가 몇 개인지 알 수 있는 함수
Ex. employees 테이블의 모든 행의 수는?
SELECT COUNT(*)
FROM employees;
>107
SELECT COUNT(commission_pct)
FROM employees;
>35
--column으로 검색 했을 시, NULL을 제외한 행 수만 계산
현장에서 모든 데이터를 확인하고 싶을 시, PK를 사용하는 컬럼을 사용하여 조회하자.
중복된 값을 제외하고 받고 싶을 때는? -> DISTINCT 사용
Ex.
SELECT COUNT(DISTINCT department_id)
FROM employees;
SUM() 합
- 인수값으로 숫자값만 받을 수 있음
SELECT SUM(salary)
FROM employees
WHERE department_id=50;
AVG() 평균
자료의 중심 위치를 나타내는 대표값들 (평균,중앙값,최빈값)
SELECT AVG(commission_pct)
FROM employees;
>0.2228571428571428571428571428571428571429
--NULL을 뺀 평균, NULL은 제외하였으니 전체 평균이라 볼 수 없음
SELECT AVG(NVL(commission_pct,0))
FROM employees;
>0.072897196261682242990654205607476635514
--결측값을 클라이언트와 조정해야 함
VARIANCE() 분산
SELECT VARIANCE(salary)
FROM employees;
STDDEV() 표준편차
SELECT STDDEV(salary)
FROM employees;
GROUP BY 절 -소그룹으로 그룹함수 사용할 때
소그룹으로 나누어서 그룹 함수를 쓰고 싶을 때 사용
-
소그룹 별 평균, 합 등을 구하기 위해서는 반드시 사용해야
- GROUP BY 절에서 열 별칭 사용 불가
- 처리 순서가 SELECT 전에 GROUP BY가 이루어지기 때문
- SELECT - FROM - WHERE - GROUP BY 순으로 작성해야함
SELECT department_id, SUM(salary)
FROM employees
WHERE department_id <> 10
GROUP BY department_id;
원하는 방식으로 GROUPING 하고 싶을 때, 컬럼 이름을 GROUP BY 절에 써야한다. 왜? 아래 예시에서 설명
Ex.
SELECT TO_CHAR(hire_date,'yyyy') AS "HIRE_YEAR", SUM(salary)
FROM employees
GROUP BY TO_CHAR(hire_date,'yyyy') --연도 별 그룹핑을 하고 싶기 때문에 select절과 같을 수 밖에 없음.
ORDER BY "HIRE_YEAR";
HAVING 절 -그룹함수의 결과를 제안할 때
그룹 함수의 결과를 제한할 때는 HAVING 절을 사용 (WHERE절은 ROW를 제한할 때 사용)
Ex.
SELECT department_id, SUM(salary)
FROM employees
WHERE SUM(salary) > 10000
--WHERE 절은 행을 제한하는 절.
--employees 테이블의 전체 sum인지, department_id의 sum인지 모름
GROUP BY department_id;
- WHERE 절은 그룹함수 사용을 못함(처리 순서 때문)
- 그래서, HAVING 절을 사용
SELECT department_id, SUM(salary)
FROM employees
GROUP BY department_id;
HAVING SUM(salary) > 10000
절들의 순서는 지켜야 한다. 하지만 내부적으로 처리하는 순서는 다르다는 것을 알아두자.
SELECT department_id, SUM(salary) -- 4
FROM employees -- 1
WHERE department_id in (20,30,40,50)-- 2
GROUP BY department_id -- 3 (HAVING절과 바꾸어 써도 됌)
HAVING SUM(salary) > 10000 -- 5
ORDER BY department_id; -- 6
주의. WHERE절과 HAVING절의 사용법을 혼동하지 말자
아래 코드를 비교
SELECT department_id, SUM(salary)
FROM employees
WHERE department_id in (10,20)
GROUP BY department_id;
SELECT department_id, SUM(salary)
FROM employees
HAVING department_id in (10,20)
GROUP BY department_id;
결과값은 같지만 아래 코드는 악성 코드가 되어버린다! 왜? WHERE절은 INDEX SCAN 유도하지만, HAVING절은 처리 순서로 인해 FULL SCAN으로 처리하기 때문.
처리 순서를 확인하고 논리적으로 생각해보자.
그룹 함수의 중첩
그룹 함수 중첩의 예시
Ex. department_id의 salary 평균의 최대값을 알아보고자 한다.
SELECT department_id, MAX(AVG(salary))
FROM employees
GROUP BY department_id;
>오류
그룹 함수를 2번 중첩하면 개별 컬럼 사용 불가
-> 사용하려면? 서브쿼리를 사용해야 한다!