[ex08~09] 집계함수(aggregation function), 수학 함수(numerical_function)

ex08_aggregation_function

    집계함수, Agregation_function
    1. count()
    2. sum()
    3. avg()
    4. max()
    5. min()

 

- 집계 함수 사용 주의점!!!
-- 1. ORA-00937: not a single-group group function
-- 컬럼 리스트에 집계 함수와 일반 컬럼을 동시에 사용할 수 없다.
-- 집계 함수 반환값(집합), 컬럼(개인)

--요구사항] 직원들의 이름과 총직원수를 가져오시오.
select name, count(*) from tblInsa; -- 에러

 

-- 2. ORA-00934: group function is not allowed here
-- where절에는 집계 함수를 사용할 수 없다. 
-- 집계 함수 반환값(집합), 컬럼(개인)
-- where절은 개인 데이터를 검사하는 영역 > 집합값에 대한 접근이 불가능하다.

-- 요구사항] 평균 급여보다 더 많이 받는 직원을 가져오시오.
select avg(basicpay) from tblInsa; --155만원

select * from tblInsa where basicpay >= avg(basicpay); --에러

 

    
    1. count()
    - 결과 테이블의 레코드 수를 반환한다.
    - number count(컬럼명)
    - null 레코드 제외(*****)

 

select name from tblCountry;
select count(name) from tblCountry;

-- 'AS'에 속한 나라 갯수?
select count(name) from tblCountry where continent = 'AS';

select count(population) from tblCountry; --null은 포함되지 않음
select count(*) from tblCountry; --null과 상관없이 테이블의 행 갯수 정확하게 파악

-- 모든 직원수?
-- 연락처가 있는 직원수?
-- 연락처가 없는 직원수?
select count(*) from tblInsa; -- 60
select count(tel) from tblInsa; -- 57
select count(*) - count(tel) from tblInsa; -- 3

select count(*) from tblInsa where tel is not null; --57
select count(*) from tblInsa where tel is null; --3


-- tblInsa. 어떤 부서들 있나요?
select distinct buseo from tblInsa;

-- tblInsa. 부서가 몇개 있나요?
select count(distinct buseo) from tblInsa; --7개

--tblComedian. 남자수? 여자수?
select * from tblComedian;
select count(*) from tblComedian where gender = 'm'; --8
select count(*) from tblComedian where gender = 'f'; --2

-- *** 자주 사용되는 패턴
select
    count(*) as 전체인원수,
    count(case
        when gender = 'm' then 1 --1 말고 아무거나 넣어도 상관X
    end) as 남자인원수,
    count(case
        when gender = 'f' then 1
    end) as 여자인원수
from tblComedian;

--tblInsa. 기획부 몇명? 총무부 몇멍? 개발부 몇명?
select count(*) from tblInsa where buseo = '기획부'; --7
select count(*) from tblInsa where buseo = '총무부'; --7
select count(*) from tblInsa where buseo = '개발부'; --14

select
    count(case
        when buseo = '기획부' then 1
    end) as 기획부,
    count(case
        when buseo = '총무부' then 1
    end) as 총무부,
    count(case
        when buseo = '개발부' then 1
    end) as 개발부
from tblInsa;


-- count 안에는 단일컬럼 or *만 가능
select count(name, buseo) from tblInsa; -- count안에 두개 이상의 컬럼X

---

    2. sum()
    - 해당 컬럼의 합을 구한다.
    - number sum(컬러명)
    - 숫자형만 적용 가능

 

select sum(height), sum(weight) from tblComedian;
select sum(first) from tblComedian; --문자열 > 에러(ORA-01722: invalid number)

select
    sum(basicpay) as "지출 급여 합",
    sum(sudang) as "지출 수당 합",
    sum(basicpay) + sum(sudang) as "총 지출",
    sum(basicpay + sudang) as "총 지출"
from tblInsa;

---

    3. avg()
    - 해당 컬럼의 평균값을 구한다.
    - number avg(컬럼명)
    - 숫자형만 적용 가능
    - null은 제외됨(*****)

-- tblInsa. 평균 급여?
select sum(basicpay) / 60 from tblInsa; --1556526
select sum(basicpay) / count(*) from tblInsa; --1556526
select avg(basicpay) from tblInsa;  --1556526

-- 평균 인구수?
select
    avg(population), --15588
    sum(population) / count(*), --14475
    sum(population) / count(population) --15588
from tblCountry;

-- 회사 > 성과급 지급 > 성과급 출처 > 1팀 공로~
-- 1. 균등 지급: 총지급액 / 모든 직원수 = sum() / count(*)
-- 2. 차등 지급: 총지급액 / 참여 직원수 = sum() / count(참여인원수) = avg()

---

    4. max()
    - objec max(컬럼명)
    - 최댓값 반환
    
    5. min()
    - objec max(컬럼명)
    - 최솟값 반환
    
    - 숫자형, 문자형, 날짜형 모두 적용

select max(height), min(height) from tblComedian; --숫자형
select max(name), min(name) from tblInsa; --문자형
select max(ibsadate), min(ibsadate) from tblInsa; --날짜형

select
    count(*) as "직원수",
    sum(basicpay) as "총급여 합",
    avg(basicpay) as "평균급여",
    max(basicpay) as "최고 급여",
    min(basicpay) as "최저 급여"
from tblInsa;

---

ex09_numerical_function

    숫자 함수(= 수학 함수)
    
    round()
    - 반올림 함수
    - number round(컬럼명) : 정수 반환
    - number round(컬럼명, 소수이하 자릿수) : 실수 반환

select
    height / weight,
    round(height / weight),
    round(height / weight, 1),
    round(height / weight, 2),
    round(height / weight, 3),
    round(height / weight, 0)
from tblComedian;


-- 평균 급여
select round(avg(basicpay)) from tblInsa; --1556526.666666666666666666666666666666667

 

    floor(), trunc()
    - 절삭 함수(바닥)
    - 무조건 내림 함수

    - number floor(컬럼명) : 무조건 정수 반환
    - number trunc(컬럼명 [, 소수이하 자릿수]) : 정수 or 실수 반환

select
    height / weight,
    round(height / weight),
    floor(height / weight),
    trunc(height / weight),
    trunc(height / weight, 1),
    trunc(height / weight, 2)
from tblComedian;

---

    ceil()
    - 무조건 올림 함수(천장)
    - number ceil(컬럼명)

select 
    height / weight,
    round(height / weight),
    floor(height / weight),
    ceil(height / weight)
from tblComedian;

---

    mod()
    - 나머지 함수
    - number mod(피제수, 제수)

 

- dual 테이블 > 레코드 1개짜리 테이블

select sysdate from dual;

select 
    10 / 3,
    mod(10, 3) as "나머지",  -- 자바 정수%정수
    floor(10 / 3) as "몫"    -- 자바 정수/정수
from dual;

---

abs() : 절대값

power() : 제곱

sqrt() : 제곱근

select
    abs(10), -- abs() : 절대값
    abs(-10),
    power(2, 2), -- power() : 제곱
    power(2, 3),
    power(2, 4),
    sqrt(4), -- sqrt() : 제곱근
    sqrt(9),
    sqrt(16)
from dual;