[ORACLE] 형변환 함수 / 일반 함수 / 그룹함수

 

[4] 형변환 함수

 

▷ to_date 

 - 문자를 날짜로 변환

 - 날짜사이의 간격을 구할때 '문자 - 날짜' 가 불가하니, 문자를 날짜로 형변환 한 후 계산해주어야한다

 - 내가 정한 형식대로 문자 -> 날짜로 형변환하여 출력한다. 27일차 강의에도 설명있음.

to_date(문자, 패턴)
select '2022-05-07', to_date('2022-05-07') from dual;

 

★ ex ) 특정 컬럼에서 특정 날짜사이의 데이터를 조회하고싶다 ! 

select * from pd 
where regdate>='2022-03-13' and regdate< to_date('2022-03-16')+1
order by regdate desc;

 = 2022.3.13 보다 크고, 2022-03-16 보다 작은 ( = 해당 날짜 사이의 ) regdate 데이터를 출력해라

 

Q.  to_date 로 형변환 한 후 +1 , < 로 변경했는데 그 이유는 ? 

  A.  to_date('2022-03-16') 는

       2022-03-16 00:00:00 기준이므로 3/16 날짜의 데이터가 조회되지 않음

 

 

 

  응용 : 등록한지 몇시간이 지났는지 조회

select no, pdname, regdate, 
    sysdate-regdate"경과된 일수",(sysdate - regdate)*24"경과된 시간"
from pd;

 

▷ to_number() 

- 문자를 숫자로 변환

to_number(문자)
select  to_number('10') from dual;

 

[5] 일반 함수

 

▷ ★nvl(컬럼, 치환할 값) 

 - 해당 컬럼이 null이면 치환할 값으로 바꾸는 함수

 - null + null = null ( 널끼리 연산하면 결과도 널인데 ) nvl 을 사용 => null 값을 치환해서 계산이 될 수 있게 만듦

elect nvl(bonus,0) from dual;
select nvl(pay*12+bonus,pay*12) from professor;

 = > bonus 가 null 이면 0 으로 치환하여 출력

 = > pay*12+bonus 가 0 이면 pay*12 로 출력 // null값이 있으면 전자로, 없으면 후자로 출력됨

 

 

▷ nvl2(col1, col2, col3) 

 - col1 is null ( 아니면 col2를 or 맞으면 col3를 출력  )

nvl2(bonus,bonus,0)

  => bonus 값이 정상일때는 bonus값, null 이면 0

 

 

▷ decode() 

  if문을 대신하는 함수

- A가 B와 같으면 참을 처리하고, 그렇지 않으면 거짓을 처리한다.

같다비교 ( 범위값비교 불가 )

decode(A, B, 참, 거짓)

select name decode(grade,1,'1학년',
                         2, '2학년',
                         3, '3학년',     //else if
                         '4학년')        //else
from student;

 

 

▷ case 함수

  if문을 대신하는 함수

  같다비교는 물론 , 조건이 범위값을 가질때도 사용 가능

[1] 동일값 비교시 (=로 비교되는 경우)
        case 조건 when 결과1 then 출력1
                  when 결과2 then 출력2
                  else 출력3
                  end "별칭"
                  
[2] 범위값 비교시
        case when 조건1 then 출력1
             when 조건2 then 출력2
             else 출력3
             end "별칭"

 

select name, pay,
    case when pay> 400 then '400초과'
         when pay between 300 and 400 then '300~400사이'
         when pay< 300 then '300미만'
    end 급여범위
from professor;

 

 

---

 

※ 복수행 함수(그룹함수)

 - 집계함수 = 복수함수 = 그룹함수

 - 그룹함수는 그룹함수끼리만 사용해야한다.

 

▷ count()

- 입력되는 데이터의 건수를 리턴하는 함수

- 대부분의 그룹함수는 null을 제외하고 계산함

count(*) : null 포함. 그냥 select * 사용하면 null을 뺀 컬럼의 개수임

 

 

▷ sum()

- 합계 구하는 함수

- 문자, 날짜 사용불가 (연산이 불가하기 때문)

 

▷ avg(nvl()) 

 - 평균을 구하는 함수

★ 반드시 평균은 nvl 을 사용해서 계산해야함

     why ?  그룹함수는 null을 제외하고 연산하므로 avg()는 정상적인 결과값이 나오지 않음

      => nvl() 함수를 이용하여 처리해야 함
      => avg(nvl(컬럼, 0))

select avg(nvl(pay,0)) from professor;

 

 

▷ max() - 최대값을 구하는 함수

▷ min() - 최소값을 구하는 함수

  - 대소비교는 연산이 아니므로 문자,날짜형도 위 함수사용이 가능하다

 

 

위 함수들에 distinct 를 붙이면 [ 중복값을 제외한 00 값 ]이 된다

 

count(distinct 컬럼명) - 중복값을 제외한 건수

sum(distinct 컬럼명) - 중복값을 제외한 합계
avg(distinct 컬럼명) - 중복값을 제외한 평균
min(distinct 컬럼명) - 중복값을 제외한 최소값
max(distinct 컬럼명) - 중복값을 제외한 최대값

 

 

▶ group by 

- 컬럼별로 묶어서~해라 라는 의미  ex ) 학과별로 교수들의 평균 급여를 출력하시오.

- 테이블 전체에 대한 집계를 구하는 것이 아니라, 특정 범위에서의 집계 데이터를 구함  

- group by는 별칭 불가 !!!! ( order by는 별칭 가능 )

- 본디 복수행함수에서 select 절에는 컬럼이 올 수 없으나, ( 그룹함수만가능 )

  [group by절에 있는컬럼] 은 select절에 올 수 있다.

- where 절에는 group 함수가 올 수 없다 따라서 having 절을 사용함

    - group by 에서 조건을 주려면 where having 을 사용하면 됨 ! 

      ex ) 부서별 평균급여를 구한 후, 평균 급여가 450 초과인 부서의 부서번호와 평균 급여를 구하시오.

select deptno, avg(nvl(pay,0))
from professor
group by deptno
having avg(nvl(pay,0))>450;

having 과 where 을 같이 사용할수도있음

select grade, avg(nvl(height,0))
from student
where weight >= 55
group by grade
having avg(nvl(height,0))>=177;

  = > 몸무게가 55이상인 학생들에 대해서 where 학년별 키의 평균 구하고

        그 결과에서 키의 평균이 177 이상인 경우 구하기 having

 

 

▶ having

    - group by의 결과내에서 특정 조건을 만족하는 것을 구하려면 having을 이용한다
    - group by절에 의해 출력된 결과에 대한 조건을 정의한다.
    - group by된 결과를 제한하고자할 때 사용

   group by 컬럼
    having 조건