[SQL 입문] SQL 그룹 함수(multiple row function)

- multiple row function(=group function): 복수행 함수

select ~ column, 표현식, 그룹함수 표현식, ... [as alias] ----4
from  -----1
[where ~]  ----2 filter 조건
[group by ~] ----3 컬럼선언, ##alias, column position 선언 불가##
[order by ~] ----5  컬럼, 표현식, alias, colmn position

• 전체 row를 대상으로 함수 수행 후 1개의 결과 반환
• 그룹핑된 row를 대상으로 함수 수행 후 1개의 결과 반환(group by 절과 함께 사용)

-- count(표현식) : null을 제외한 값의 개수 반환(행의 수 반환)
   count(*) : not null 제약조건(필수컬럼) 또는 PK(Primary Key) 컬럼 기준으로 값의 개수 반환(행의 수 반환)
   count(distinct 표현식) : 중복값을 제외한 unique한 값의 개수 반환

-- sum(n column) : 넘버타입에 해당하는 표현식
  : number 컬럼 대상, 누적합 반환
  : null 포함되어 있으면 무시(연산포함x)
-- avg(n column) : 넘버타입에 해당하는 표현식 
  : number 컬럼 대상, 평균 반환
  : null 포함되어 있으면 무시(연산포함x)
-- max(표현식) : 모든 컬럼 대상, 최대값 반환
-- min(표현식) : 모든 컬럼 대상, 최소값 반환
-- stddev(n column) : 넘버타입에 해당하는 표현식 / number 컬럼 대상, 표준편차 반환
-- variance(n column) : 넘버타입에 해당하는 표현식 / number 컬럼 대상, 분산 반환

※ 그룹함수는 null 제외(무시)

Q. 인수가 전체 null이라면?
SELECT count(comm), avg(comm), sum(comm), min(comm), max(comm)
FROM emp
WHERE comm is null;

 

SELECT avg(sal), max(sal), min(sal), sum(sal), stddev(sal), variance(sal)
FROM emp;

SELECT max(ename), min(ename), max(hiredate), min(hiredate)
FROM emp;

SELECT count(*) c1, count(comm) c2, count(deptno) c3, count(distinct deptno) c4
FROM emp;  
--c1 : 전체행수, 
--c2 : 커미션 받는 사원수, 
--c3 : 부서가 배정된 사원수, 
--c4 : 사원이 소속된 부서종류의 수

 

※ select 절에서 그룹함수와 그룹함수를 적용하지 않는 컬럼을 함께 선언하는 경우,
   그룹함수를 선언하지 않는 컬럼 > 반드시 group by절에 선언

※ group by절에 선언된 컬럼 : select 절에 반드시 선언되어야 하는 것은 아님(선택 조건)

Q. emp 테이블에서 부서별로 인원수, 평균급여, 최저급여, 최고급여, 급여의 합을 구하여 출력

SELECT deptno, count(*), trunc(avg(sal), 1), min(sal), max(sal), sum(sal)
FROM emp;

SELECT deptno, count(*), trunc(avg(sal), 1), min(sal), max(sal), sum(sal) ---3
FROM emp ---1
GROUP BY deptno; ----2  > hash 방식

-- hash방식 > 수행결과 정렬x
-- sorting 방식으로 수행되면 정렬보장

 

Q. 전체 사원의 커미션 평균 출력

SELECT avg(comm), sum(comm)/count(*)
FROM emp;  -- null이 제외되면서 양쪽 값이 달라짐

SELECT avg(nvl(comm, 0)), sum(comm)/count(*)
FROM emp; --null포함한 컬럼의 avg함수 적용은 반드시 null 처리 후에 적용해야함.

 

---

-- group by의 조건절 HAVING절

select ~ column, 표현식, 그룹함수 표현식, ... [as alias] ----5
from  -----1
[where ~]  ----2 filter 조건
[group by ~] ----3 컬럼선언, (alias, column position 선언 불가)
[having 그룹함수 적용 조건] ---4
[order by ~] ----6  컬럼, 표현식, alias, colmn position

 > where절에는 group 함수 적용 불가, group by절보다 먼저 수행하는 필터조건

 

Q. emp 테이블에서 부서 인원이 4명보다 많은 부서의 부서번호, 인원수, 급여의 합을 출력하라

SELECT deptno, count(*), sum(sal)
FROM emp
WHERE count(*) > 4  --사용불가
GROUP BY deptno;

SELECT deptno, count(*), sum(sal)
FROM emp
GROUP BY deptno
HAVING count(*) > 4; --이렇게 그룹함수의 조건으로 바꿔 줘야 함.

 

Q. emp 테이블에서 부서별 최고 급여가 2900 이상인 부서에 대해서 부서번호, 평균급여, 급여의 합을 출력하라

SELECT deptno, avg(sal), sum(sal)
FROM emp
GROUP BY deptno
HAVING max(sal) >= 2900;

 

conn c##scott/oracle

-- 연결을 scott 유저로 바꿈

Q. employee테이블에서 관리자(manager_id)가 없는 사원은 제외하고, 사원들의 부서(department_id)별 평균급여(salary)가 6000미만인 부서번호와 평균급여를 출력하세요. 단, 평균급여의 내림차순으로 출력하시오.

SELECT department_id, avg(salary)  --5
FROM employees --1
WHERE manager_id is not null --2
GROUP BY department_id --3
HAVING avg(salary) < 6000 --4
ORDER BY avg(salary) desc; --6
--ORDER BY 2 desc; >> 이렇게 숫자로도 지정가능

 

Q. employee테이블에서 부서별 급여 평균 중에서 최고 평균 급여를 검색하시오

SELECT trunc(max(avg(salary)), 1)
FROM employees 
GROUP BY department_id;