[오라클 DB] 일반 함수/그룹함수

NVL

• 컬럼의 NULL 값을 실제값으로 대체해서 출력해주는 함수

• NULL값이 아닌 행은 원래 값을 출력하고 NULL을 가진 행에서만 대체값으로 결과를 출력한다.

• 기존의 데이터와 NULL의 대체값이 같은 행에 출력되기 때문에 대체값도 컬럼의 데이터 타입과 동일해야한다.

SQL> SELECT commission_pct, NVL(commission_pct, 0)
  2  FROM employees;
(...)
COMMISSION_PCT NVL(COMMISSION_PCT,0)
-------------- ---------------------
            .2                    .2
           .15                   .15
            .1                    .1
                                   0
                                   0
(...)

NVL2

• NULL값의 여부에 따라 간접적으로 결과를 연산하는 함수

• NVL2(NULL여부를 판단할 컬럼, NULL값이 아닌경우 출력값, NULL값인 경우 출력값)

• 2,3번째 인자가 값이 함수의 출력 결과가 되므로 데이터 타입이 통일되어야 한다.

 

NULLIF

만약(if) 첫번째 인자와 두번째 인자값을 비교해서 같으면 NULL, 다르면 첫번째인자값을 출력하는 함수

SQL> select first_name, length(first_name) "expr1", 
  2  last_name, LENGTH(last_name) "expr2", 
  3  nullif(length(first_name), length(last_name)) result 
  4  from employees; 

FIRST_NAME   expr1 LAST_NAME        expr2  RESULT 
-------------------- ---------- ------------------------- ---------- ---------- 
Ellen       5 Abel    4       5 
Sundar       6 Ande    4       6 
Mozhe       5 Atkinson    8       5 

 

COALESCE

• 여러 대체값을 줄 수 있는 함수.

• NULL값이 아닌 첫 대체값이 출력된다.

즉 널이 아닌 값을 찾아 출력하는 식이다. ( a가 널이고 b가 널이면 널이아닌 c출력)

SQL> SELECT last_name, employee_id,
  2  COALESCE(TO_CHAR(commission_pct), TO_CHAR(manager_id), 'No commission and No manager')
  3  FROM employees;


LAST_NAME                 EMPLOYEE_ID COALESCE(TO_CHAR(COMMISSION_PCT),TO_CHAR
------------------------- ----------- ----------------------------------------
King                              100 No commission and No manager
Kochhar                           101 100
De Haan                           102 100
Hunold                            103 102
Ernst                             104 103
Austin                            105 103
(...)

사장(king)은 커미션과 매니저 아이디가 없어서 마지막 인자의 값이 출력된 걸 알 수 있다.

 

 

ex) 사원들 이름과 커미션을 출력하되, 커미션 없는 사람은 No Commission출력하기

NVL 함수 이용

SQL> select last_name, nvl(to_char(commission_pct), 'No Commission')
  2  from employees;
(...)
LAST_NAME		  COMM
------------------------- ----------------------------------------
Livingston		  .2
Grant			  .15
Johnson 		  .1
Taylor			  No Commission
Fleaur			  No Commission
Sullivan		  No Commission
(...)

COALESCE 함수 이용

  1  select last_name,
  2  coalesce(to_char(commission_pct), 'No Commission') COMM
  3* from employees

 

 

그룹 함수(여러 행 함수)

• 그룹 단위로 연산하는 함수

• GROUP BY절을 통해서 그룹을 나누지 않았다면 테이블의 행 전체가 하나의 그룹

• 그룹 함수는 연산에서 NULL값을 제외하고 연산한 결과를 출력

숫자 데이터 입력 함수

AVG([DISTINCT|ALL] n) 평균

STDDEV([DISTINCT|ALL] n) 표준편차

VARIANCE([DISTINCT|ALL] n) 분산

SUM([DISTINCT|ALL] n) 합계

 

___ : 기본 옵션

 

ex) 월급의 각 항목별 연산

SQL> SELECT AVG(salary), STDDEV(salary), VARIANCE(salary), SUM(salary)
  2  FROM employees;


AVG(SALARY) STDDEV(SALARY) VARIANCE(SALARY) SUM(SALARY)
----------- -------------- ---------------- -----------
 6461.83178     3909.57973       15284813.7      691416

ex) distinct로 중복제외한 연산

SQL> select avg(distinct salary), avg(salary) from employees;

AVG(DISTINCTSALARY) AVG(SALARY)
------------------- -----------
	 7067.37931  6461.83178

 

모든 데이터를 연산하는 함수

COUNT(*|[DISTINCT|ALL] expr) 행 개수 카운트

MAX([DISTINCT|ALL] expr) 최대값

MIN([DISTINCT|ALL] expr) 최소값

 

ex) 커미션을 받는 사원이 35명뿐이기 때문에(null값이 아닌 레코드) salary의 count와 값이 다름을 알 수 있다.

SQL> select count(*) from employees;

  COUNT(*)
----------
       107

SQL> select count(salary), max(salary), min(salary) from employees;

COUNT(SALARY) MAX(SALARY) MIN(SALARY)
------------- ----------- -----------
	  107	    24000	 2100

SQL> select count(commission_pct), max(hire_date), min(employee_id) from employees;

COUNT(COMMISSION_PCT) MAX(HIRE_ MIN(EMPLOYEE_ID)
--------------------- --------- ----------------
		   35 21-APR-08 	     100

 

GROUP BY 절 결합

ex) 그룹별 카운트

 

  1  select department_id, COUNT(*), AVG(SALARY)
  2  from employees
  3* group by department_id

DEPARTMENT_ID	COUNT(*) AVG(SALARY)
------------- ---------- -----------
	  100	       6  8601.33333
	   30	       6	4150
       (...)