Oracle DB #5 SQL 주요 함수, 그룹 함수

1. SQL 주요 함수

1) 선택을 위한 DECODE 함수

- DECODE 함수는 프로그램 언어에서 가장 많이 사용되는 switch case 문과 같은 기능을 갖음

- 여러 가지 경우에 대해서 선택할 수 있도록 함

DECODE (표현식, 조건1, 결과1,

                           조건2, 결과2,

                           조건3, 결과3,

                           기본결과n)

부서번호에 해당되는 부서명을 구하는 예제를 이번에는 CASE 함수를 사용하여 작성해보자

SELECT ENAME, DEPTNO,

              DECODE(DEPTNO,  10, 'ACCOUNTING',

                                              20, 'RESEARCH',

                                              30, 'SALES',

                                              40, 'OPERATIONS' )

               AS DNAME

FROM EMP;

 

2) 조건에 따라 서로 다른 처리가 가능한 CASE 함수

- CASE 함수 역시 여러 가지 경우에 대해서 하나를 선택하는 함수

- DECODE 함수와 차이점이 있다면 DECODE 함수는 조건이 일치(= 비교 연산자)하는 경우에 대해서만 적용되는 반면, CASE 함수는 다양한 비교 연산자를 이용하여 조건을 제시할 수 있으므로 범위를 지정할 수도 있음

- CASE 함수는 프로그램 언어의 if else if else 와 유사한 구조를 갖음

 

CASE 표현식  WHEN 조건1 THEN 결과1

                     WHEN 조건2 THEN 결과2

                     WHEN 조건3 THEN 결과3

                     ELSE 결과n

END

부서번호에 해당되는 부서명을 구하는 예제를 이번에는 CASE 함수를 사용하여 작성해보자

SELECT ENAME, DEPTNO,

              CASE WHEN DEPTNO=10 THEN 'ACCOUNTING'

                        WHEN DEPTNO=20 THEN 'RESEARCH'

                        WHEN DEPTNO=30 THEN 'SALES'

                        WHEN DEPTNO=40 THEN 'OPERATIONS'

               END AS DNAME

FROM EMP;

 

2. 그룹 함수

- 그룹 함수는 하나 이상의 행을 그룹으로 묶어 연산하여 총합, 평균 등 하나의 결과로 나타납니다.

구 분	설 명
SUM	그룹의 누적 합계를 반환합니다.
AVG	그룹의 평균을 반환합니다.
COUNT	그룹의 총 개수를 반환합니다.
MAX	그룹의 최대값.을 반환합니다.
MIN	그룹의 최소값.을 반환합니다.
STDDEV	그룹의 표준편차를 반환합니다.
VARIANCE	그룹의 분산을 반환합니다.

1) 함께 구하는 SUM 함수

- SUM 함수는 해당 칼럼 값들에  대한 총합을 구하는 함수

급여 총액 구하는 예제입니다.

SELECT SUM(SAL)

  FROM EMP;

  SELECT SUM(COMM)

  FROM EMP;

2) 평균 구하는 AVG 함수

AVG 함수는 해당 칼럼 값들에 대해 평균을 구하는 함수. 이때 해당 칼럼 값이 NULL 인 것에 대해서는 제외하고 계산한다.

SELECT AVG(SAL)

 FROM EMP;

3) 최대값 구하는 MAX, 최소값 구하는 MIN 함수

- 지정한 칼럼 값들 중에서 최대값을 구하는 함수가 MAX이고, 최소값을 구하는 함수가 MIN

SELECT MAX(SAL), MIN(SAL)

FROM EMP;

4) 로우 개수 구하는 COUNT 함수

- COUNT 함수는 테이블에서 조건을 만족하는 행의 개수를 반환하는 함수. COUNT 함수에 특정 칼럼을 기술하면 해당 칼럼 값을 갖고 있는 로우의 개수를 계산하여 되돌림

 

사원 테이블의 사원들 중에서 커미션을 받은 사원의 수를 구해보자

SELECT COUNT(COMM)

FROM EMP;

사원 테이블에서 사원들의 직업의 개수를 카운트 해보자

SELECT COUNT(JOB) 업무수

FROM EMP;

직업의 종류가 몇 개인지 즉, 중복되지 않은 직업의 개수를 카운트 해보자

SELECT COUNT(DISTINCT JOB) 업무수

FROM EMP;

5) GROUP BY 절

- 그룹함수를 쓰되 어떤 컬럼 값을 기준으로 그룹함수를 적용할 경우 GROUP BY 절 뒤에 해당 컬럼을 기술하면 됨

SELECT 칼럼명, 그룹함수

FROM 테이블명

WHERE 조건 (연산자)

GROUP BY 칼럼명;

- 합계, 평균, 최대값.이나, 최소값. 등을 어떤 칼럼을 기준으로 그 칼럼의 값 별로 보고자 할 때 GROUP BY 절 뒤에 해당 칼럼을 기술하면 됨

- GROUP BY 절을 사용할 때 주의할 점은 GROUP BY 절 다음에는 칼럼의 별칭을 사용할 수 없고, 반드시 칼럼명을 기술해야 한다는 점

 

사원 테이블을 부서 번호로 그룹지어 보자

SELECT DEPTNO

FROM EMP

GROUP BY DEPTNO;

소속 부서별 최대 급여와 최소 급여를 구해보자

SELECT DEPTNO, MAX(SAL), MIN(SAL)

FROM EMP

GROUP BY DEPTNO;

6) HAVING 조건

- SELECT 절에 조건을 사용하여 결과를 제한할 때는 WHERE 절을 사용하지만 그룹의 결과를 제한할 때는 HAVING 절을 사용

 

예를 들어 설명하자면 부서별로 그룹지은 후(GROUP BY), 그룹 지어진 부서별 평균 급여가 2000 이상인(HAVING) 부서번호와 부서별 평균 급여를 출력하는 경우를 알아보자

 

SELECT DEPTNO, AVG(SAL)

FROM EMP

GROUP BY DEPTNO

HAVING AVG(SAL) >= 2000;

부서의 최대값과 최소값을 구하되 최대 급여가 2900이상인 부서만 출력해보자

SELECT DEPTNO, MAX(SAL), MIN(SAL)

FROM EMP

GROUP BY DEPTNO

HAVING MAX(SAL) > 2900;