SQL[06] - 일반함수, 그룹함수, GROUP BY, HAVING절

*오라클 oracle 기준

---

일반함수

• NVL(컬럼명, DEFAULT값) : 컬럼에 NULL이 있을경우 DEFAULT값으로 대치
• NVL2(컬럼명1, 컬럼명2, 컬럼명3) : 컬럼1이 NULL이 아닐경우 컬럼2가 값, NULL이면 컬럼3

그룹함수 (복수행함수)

여러건의 데이터가 함수로 입력

• * > NULL 값도 포함
• 컬럼명 > NULL 제외

#그룹함수의 종류

함수명	설명
COUNT	행, 레코드의 개수
SUM	값의 합
AVG	값의 평균
MAX	최대값
MIN	최소값
MEDIAN	값의 범위에 대한 중간 값

⌨ 일반함수 사용해보기

NVL, NVL2

-- COMM 컬럼에 NULL이 있을경우 0으로 대치
SELECT EMPNO, ENAME, COMM, NVL(COMM,0) FROM EMP; --#1
-- COMM 컬럼이 NULL이 아닐경우 SAL+COMM, NULL일경우 SAL*10 반환
SELECT EMPNO, ENAME, SAL, NVL2(COMM, SAL+COMM, SAL*0) "COMM" FROM EMP; --#2

⌨ 그룹함수 사용해보기

∙ COUNT, SUM, AVG, MAX, MIN, MEDIAN

-- COMM 컬럼 데이터 조회
SELECT COMM FROM EMP;
-- 전체컬럼의 레코드 개수, COMM의 개수 (값이 있는것만), SAL의 개수(값 있는것만)
SELECT COUNT(*), COUNT(COMM), COUNT(SAL) FROM EMP;
-- SAL 컬럼의 총합, COMM컬럼의 총합
SELECT SUM(SAL), SUM(COMM) FROM EMP;
-- SAL, COMM 컬럼의 값이 있는 것들의 평균, 전체 사람들의 평균(NULL값을 0으로 치환하여 평균 구하기)
SELECT AVG(SAL), AVG(COMM), AVG(NVL(COMM, 0)) FROM EMP;
-- SAL컬럼의 최대값, 최소값, 중간값
SELECT MAX(SAL), MIN(SAL), MEDIAN(SAL) FROM EMP;

---

GROUP BY 절

∙ 특정 조건으로 세부적인 그룹화

∙ SELECT절에 사용된 그룹함수 이외의 컬럼이나 표현식은 반드시 GROUP BY절에 사용되어야 함

∙ GROUP BY절에는 별칭이 올 수 없다. 컬럼명 작성

HAVING 절

∙ 그룹핑한 조건으로 검색

∙ WHERE에서는 그룹함수를 쓸수 없기 때문에 GROUP BY절과 HAVING절이 함께 쓰임

 

⌨ GROUP BY절 사용해보기

∙ 그룹화할 컬럼을 여러개 적어주면 그룹화하고 그 그룹화 한것에서 적힌 조건으로 또 그룹화가 된다.

-- DEPTNO로 그룹화하고, JOB 컬럼으로도 그룹화 ! 그룹화 한것들의 SLA 평균 구하기
SELECT DEPTNO, JOB, AVG(SAL) "AVG" --#1
FROM EMP
GROUP BY DEPTNO, JOB
ORDER BY 1, 2; -- 오름차순 정렬
-- SELECT 절에 사용된 그룹함수 이외의 컬럼이나 표현식은
-- 반드시 GROUP BY절에 사용되어야 하기 때문에
-- 아래의 코드는 오류가 난다.
SELECT DEPTNO, JOB, AVG(SAL) "AVG" --#2
FROM EMP
GROUP BY DEPTNO;

⌨ GROUP BY + HAVING

∙ GROUP BY로 그룹화후 조건으로 검색하고 싶을땐 HAVING절을 함께쓴다.

-- EMP 테이블에서 평균 급여가 2000이상인 부서의 부서번호와 평균 급여 구하기
SELECT DEPTNO, AVG(SAL)
FROM EMP
--WHERE DEPTNO > 10
GROUP BY DEPTNO
HAVING AVG(SAL) > 2000;

SELECT JOB, COUNT(EMPNO) "사원수", AVG(SAL) "평균급여", 
MAX(SAL) "최고급여액",MIN(SAL) "최소급여액", SUM(SAL) "급여합계"
FROM EMP
GROUP BY JOB;

-- 사원수가 6명이상인 부서의 부서번호와 사원수
SELECT DEPTNO, COUNT(EMPNO)
FROM EMP
GROUP BY DEPTNO
HAVING COUNT(EMPNO) > 5;

---

DECODE() / JAVA에서의 IF문과 비슷

• DECODE(A, B, '1', NULL) : A와 B가 동일하면 1 아니면 NULL, NULL은 생략 가능
• DECODE(A, B, '1','2') : A와 B가 동일하면 1 아니면 2
• DECODE(A, B, '1', C, '2', '3') A와 B가 동일하면 1 A가 C와 동일하면 2 아니면 3
• DECODE(A, B, DOCODE(C, D, '1', NULL), E)

⌨ DECODE 예제

∙ PROFESSOR 테이블에서 학과번호와 교수명, 학과명을 출력하되
DEPTNO 101인 교수만 학과명을 'COMPUTER ENGINEERING'으로 출력하고
아니면 아무것도 출력안함

SELECT DEPTNO, NAME, DECODE(DEPTNO,'101','COMPUTER ENGINEERING') "학과명"
FROM PROFESSOR;