day05 - 오라클 (형변환 함수, 날짜 타입, CASE 함수, Group 함수, group by, having)

[TO_CHAR 함수]아래 예 참조

 

 

 

[날짜 타입]

select to_date('2012/3/01', 'yyyy:mon:dd") from dual;

select to_date('2012/03/01', 'yyyy:mm:dd') from dual;

select to_date('2012/3/01', 'yyyy:mm:dd') from dual;

select to_date('20120301', 'yyyy:mm:dd') from dual;

select to_date('120301', 'yyyy:mm:dd") from dual;

select to_date('120301', 'yy:mm:dd') from dual;

select to_date('20120301', 'yy:mm:dd') from dual;

 

 

[CASE 함수]

--EX1)

select name, tel,
case(substr(tel,I,instr(tel,)) -1))
  when '02' then '서울'
  when '031' then '경기
  when '051' then '부산'
  else '기타'
end 지역명
from student;

 

 

 

--EX2)

select birthday,
nv1 (case when (substr (jumin, 3,2)) between '01' and '03' then name end, '-') "1/4",

nvl (case when (substr(jumin, 3,2)) between 04 and 06 then name end, "-") "2/4",

nvl (case when (substr(jumin, 3,2)) between 07' and '09' then name end, '-') "3/4",
nvl (case when (substr(jumin, 3,2)) between 10 and 12' then name end, '-') "4/4"
from student;

 

 

--EX3)
select birthday,
case when (substr(jumin, 3,2)) between '01' and 03 then name end "1/4", 

case when (substr (jumin, 3,2)) between 10 and '12" then name and "4/4"
case when (substr (jumin, 3,2)) between 04 and 06 then name end "2/4",
case when (substr(jumin, 3,2)) between 07' and '09 then name end "3/4",
from student;

 

 

 

 

 

 

[Group 함수]

함수이름	의미	사용예
COUNT	입력되는 데이터들의 건수를 출력	COUNT(sal)
SUM	입력되는 데이터들의 합계값을 출력	SUM(sal)
AVG	입력되는 데이터들의 평균값을 출력	AVG(sal)
MAX	입력되는 데이터들 중 최고 값을 출력	MAX(sal)
MIN	입력되는 데이터들 중 최저 값을 출력	MIN(sal)
STDDEV	입력되는 데이터 값들의 표준 편차값 출력	STDDEV(sal)
VARIANCE	입력되는 데이터 값들의 분산값 출력	VARIANCE(sal)
ROLLUP	입력되는 데이터들의 소계값을 자동으로 계산해서 출력	아래 예 참조
CUBE	입력되는 데이터들의 소계 및 전체 총계를 자동 계산 후 출력	아래 예 참조

 

[COUNT 함수]

--COUNT 함수

select count(*) 총건수, count(bonus) 보너스

from professor;

 

결과 화면

-> bonus 컬럼에는 현재 null 값이 존재한다.

-> row수와 bonus 수를 같이 출력한 결과 차이가 있다.

-> COUNT(*)의 결과는 null 값을 포함한 결과이고 COUNT(bonus)의 결과는 null 값을 제외한 결과를 출력한다.

 

-> select 문에 하나라도 복수행 함수가 있다면 반드시 나머지 컬럼도 복수행 함수를 써야한다.

-> ex) count 함수를 썼는데 옆에 name 컬럼을 같이 쓰면 에러가 난다는 뜻이다.

 

 

 

 

 

 

 

[SUM 함수]

--SUM 함수

select count(bonus), sum(bonus)

from professor;

결과 화면

 

-> 단일행 함수에서는 null 함수를 해당 되는 컬럼 값이  null 일경우 그대로 null 이지만 복수행 함수에서 해당되는 컬럼이 null을 만나면 무시하고 계산한다.

-> count 함수도 null을 무시하고 계산한 것이고 sum도 null을 무시하고 계산한다.

 

 

 

 

 

 

 

[AVG 함수]

select count(bonus), sum(bonus), avg(bonus)

from professor;

결과 화면

-> 이건 사실 틀린 값이다. null을 제외하고 계산했기 때문이다. (780/10)

 

 

 

 

select count(bonus), sum(bonus) 합계, avg(nvl(bonus, 0)) 평균

from professor;

 

결과 화면

-> null 값을 0으로 변환하는 nvl 함수를 사용

-> 780/16 으로 평균 48.75가 나왔다.

 

 

 

 

 

 

 

[MAX 함수/MIN 함수]

-> 입력된 값들의 최대/최소값을 구해주는 함수

-> 속도와 성능 부분에서 문제가 될 수 있으므로 인덱스를 활용하는 것을 권장

 

--max/min 1

select max(sal), min(sal)

from emp;

 

 

 

--max/min 2

select max(hiredate), min(hiredate)

from emp;

 

 

 

 

 

 

 

[STDDEV 함수/VARIANCE 함수]

-> STDDEV 함수는 표준편차를 구하는 함수

-> VARIANCE 함수는 분산을 구하는 함수

 

 

--STDDEV/VARIANCE

select stddev(pay), variance(pay) from professor; 

select round(stddev(pay),4), round(variance(pay), 4) from professor;

 

 

 

 

[GROUP BY절 사용 시 주의 사항]

1. select 절에 사용된 그룹함수 이외의 칼럼이나 표현식은 반드시 group by 절에 사용되어야 한다.

 

--error

select deptno, position, avg(nvl(bonus, 0)) 평균 보너스 --deptno는 OK, position 는 x

from professor;

group by deptno;

 

-> 즉 위의 쿼리문에서 deptno는 group by 절에서 사용하였지만 position은 group by 절에 사용하지 않았기 때문에 에러가 뜬다.

 

 

 

2. group by 절에서 사용된 컬럼은 select 절에서 사용하지 않아도 괜찮다.

 

--not error

select deptno, avg(nvl(bonus, 0)) 평균 보너스 --deptno는 OK, position 는 x

from professor;

group by deptno, position;

 

-> deptno, position로 group by를 하였지만 position을 select 절에 사용하지 않아도 문제가 되지 않는다.

-> select는 컬럼명들을 사실상 전체를 가지고 있는 상태에서 일부를 보여주는 것이기 때문이다.

 

 

 

3. group by 절에는 반드시 컬럼 명이 사용되어아 하고 칼럼 alias 를 사용할 수 없다.

--error

select deptno dno, avg(nvl(bonus, 0)) 평균 보너스

from professor;

group by dno;

 

-> group by 절에 alias 를 사용하면 에러가 난다.

 

ex)

교수의 email 사이트별 인원수를 구하시오 단 사이트별 이름을 오름차순으로 프린트 하시오.

 

select substr(email, instr(email, '@') + 1) 사이트, count(*) 인원수

from professor

group by substr(email, instr(email, '@') + 1)

order by 사이트;

 

 

ex)

교수의 입사년도별 인원, 최고급여, 최저급여, 평균을 구하시오. 단 평균은 소수점 2자리로 프린트 할 것

(년도별 asc순으로 프린트할 것)

 

select to_char(hiredate, 'yyyy') year,
count(pay) 인원, max(pay) 최고금액,
min(pay) 최저금액,
to_char(round(avg(pay),2),'999.99') 평균
from professor
group by to_char(hiredate, 'yyyy')
order by year;

 

-> 복수행 함수를 단일행 함수로 감싸서 변환해준 컬럼은 group by 절에 사용해도 무방하다. 

 

 

 

 

[Having 절]

-> group by 절에 조건을 걸기 위해 사용

 

[쿼리 구조]

 

 

-> where 조건 절에 group by 를 쓸 수 없다

 

error 나는 쿼리문

--not error

select deptno, avg(nvl(pay, 0)) 평균

from professor

group by deptno

having avg(nvl(pay, 0)) >= 350;

 

결과 화면

 

-> having 절을 써서 조건을 걸면 에러가 나지 않는다.