23.10.11. Oracle SQL 그룹 함수

■ 그룹함수

- 여러행 당 하나의 결과를 반환하는 함수 
- SUM(합계), AVG(평균; 이상값, 이상치데이터), MEDIAN(중앙값=대표값), VARIANCE(분산, 집계 함수), STDDEV(표준편차), MAX(최대값), MIN(최솟값), COUNT(집계)
- 그룹함수에 입력하는 값이 '숫자형'만 입력해야 하는 그룹함수 : SUM, AVG, MEDIAN, VARIANCE, STDDEV
- 그룹함수에 입력하는 값이 '모든 타입'이 가능한 그룹함수 : MAX, MIN, COUNT
- 그룹함수는 NULL 을 포함하지 않는다. (단, COUNT(*) 만 NULL 포함한 행수를 구한다.)

1) COUNT : 행의 수를 구하는 함수

SELECT count(*) --null 포함
FROM hr.employees;

SELECT count(commission_pct) --null을 제외한 건수
FROM hr.employees;

/* 중복제거 : distinct, unique 키워드*/
SELECT count(distinct department_id)
FROM hr.employees;

SELECT count(unique department_id) 
FROM hr.employees;

SELECT count(*)
FROM hr.employees
WHERE department_id = 50;

SELECT count(commission_pct) 
FROM hr.employees
WHERE department_id = 50;

2) SUM : 합 ( NULL 을 제외한 합계 )

SELECT sum(salary) 
FROM hr.employees;

SELECT sum(commission_pct) 
FROM hr.employees;

3) AVG : 평균 ( NULL 을 제외한 평균 )

SELECT avg(salary) 
FROM hr.employees;

/* NULL 을 제외한 평균 */
SELECT avg(commission_pct) 
FROM hr.employees;

/* NULL 을 포함한 평균 */
SELECT avg(nvl(commission_pct,0)) 
FROM hr.employees;

ex) 10, 20, null
sum : 10+20 
avg : (10+20)/2
전체 avg : (10+20+0)/3

4) MEDIAN : 중앙값(분석)
자료 : 20, 30, 10, 50, 60, 90, 70, 40, 80
1) 자료를 크기순으로 (오름차순) 
2) 건수를 세어 본다.
    2-1) 홀수 
        (관측값수 +1) / 2 = 5
    2-2) 짝수
        (관측값수) / 2 = 5 
        (관측값수) / 2+1 = 6
            5번 위치값 , 6번 위치값 의 평균 = 중앙값

SELECT 
    avg(salary), -- 이상치
    median(salary)
FROM hr.employees;

5) VARIANCE : 분산, 내가 가진 자료(데이터)가 평균값을 중심으로 퍼져있는 평균적인 거리
    1. 평균
    2. 편차 제곱합의 평균
    (관측값 - 관측값평균)² + (관측값 - 관측값평균)² + (관측값 - 관측값평균)² 
    --------------------------------------------------------------
                        관측값의 수 - 1(자유도)

SELECT round(variance(salary))
FROM hr.employees;

6) STDDEV : 표준편차, 분산의 제곱근을 수행한 값, 이유는 단위를 맞추기 위해서이다.
ex) 웹사이트 동시접속수 등

SELECT stddev(salary)
FROM hr.employees;

7) MAX : 최대값

SELECT max(salary), max(last_name), max(hire_date)
FROM hr.employees;

8) MIN: 최소값

SELECT min(salary), min(last_name), min(hire_date)
FROM hr.employees;

- 범위 : max - min

SELECT max(salary) - min(salary)
FROM hr.employees;

[문제1] 부서별 총액 급여를 구해주세요. 

SELECT distinct department_id
FROM hr.employees
ORDER BY department_id;

SELECT sum(salary) 
FROM hr.employees
WHERE department_id = 10;

SELECT sum(salary) 
FROM hr.employees
WHERE department_id = 20;

■ GROUP BY 절 : 데이터의 행을 작은 그룹(군집)으로 나눌 수 있는 절
1) 9i R1 까지는 GROUP BY 가 SORT GROUP BY 로 수행된다. 그래서 GROUP BY 절 명시된 컬럼을 기준으로 정렬된 결과로 출력된다.
2) 9i R2 버전 부터는 GROUP BY 가 HASH GROUP BY 로 수행되기 때문에 GROUP BY 절 명시된 컬럼을 기준으로 정렬이 수행되지 않는다.

SELECT department_id, SUM(salary)
FROM hr.employees
GROUP BY department_id; -- 'HASH(나머지) GROUP BY' 알고리즘 방식으로 랜덤정렬. 

SELECT department_id, SUM(salary)
FROM hr.employees
GROUP BY department_id
ORDER BY department_id; -- 'SORT GROUP BY' 로 정렬 -> 대용량 사용, 성능 저하

■ 그룹함수 사용시 주의 사항
- NULL 을 포함하지 않는다. (COUNT(*) 제외) 
- SELECT 절에 그룹함수에 포함되지 않는 개별 컬럼은 하나도 빠짐 없이 GROUP BY 절에 명시해야 한다. 
- GROUP BY 절에는 열별칭, 위치표기법 사용할 수 없으므로, '무조건' 컬럼명으로 작성할 것.
- GROUP BY 절에 명시된 컬럼을 기준으로 군집화 할때 NULL 포함한다.
- 그룹 함수의 결과를 제한하기 위해서 WHERE 절을 사용하면 오류발생.
- 그룹함수는 '두 번만' 중첩할 수 있다.

SELECT SUM(salary)
FROM hr.employees;

SELECT department_id, SUM(salary) --오류 발생: 단일행 함수(SUM())도 아닌 것이 단일행 함수처럼 사용.
FROM hr.employees;

SELECT department_id, job_id, SUM(salary)
FROM hr.employees
GROUP BY department_id, job_id;

SELECT department_id, SUM(salary)
FROM hr.employees
GROUP BY department_id
ORDER BY 1;

SELECT department_id, SUM(salary)
FROM hr.employees
WHERE SUM(salary) >= 2000 -- 오류발생: 전체 sum(salary)으로 실행되기 때문이다. where 절은 행을 제한하는 절이다.
GROUP BY department_id
ORDER BY 1;

■ HAVING 절 : 그룹함수의 결과를 제한하는 절
- GROUP BY 절 다음에 HAVING 절 작성하는 것이 순서.
SELECT 
FROM 
WHERE
GROUP BY 
HAVING
ORDER BY 

SELECT department_id, SUM(salary)
FROM hr.employees
GROUP BY department_id
HAVING SUM(salary) >= 2000 
ORDER BY 1;

SELECT department_id, SUM(salary)
FROM hr.employees
WHERE last_name LIKE '%i%'
GROUP BY department_id
HAVING SUM(salary) >= 2000 
ORDER BY 1;

SELECT department_id, SUM(salary)
FROM hr.employees
WHERE last_name LIKE '%i%'
GROUP BY department_id
HAVING count(*) >= 5
ORDER BY 1;

- 그룹함수는 '두 번만' 중첩할 수 있다.
SELECT department_id, max(avg(salary)) -- 오류발생 : 개별 컬럼을 사용불가. 해결방법은 '서브쿼리'로 이용해야 한다.
FROM hr.employees
GROUP BY department_id;