1019_그룹 함수(다중행 함수)

<특징>

1. 여러행을 바탕으로 하나의 결과 값을 도출해내기 위함

2. 다중행 함수를 사용한 SELECT 절에는 기본적으로 여러행이 나올 수 있는 열은 함께 사용할 수 없음

   (일반컬럼과 함께 사용할 수 없다. )

3. 크기 비교가 가능한 모든 타입에 사용가능하다(날짜 등)

 

 

<종류>

1. SUM : 

- 데이터의 합을 구함

- NULL 값은 제외하고 계산됨

SELECT SUM(SAL)
FROM EMP;

 

2. AVG :

- 입력 데이터의 평균값을 구함

- 숫자 또는 숫자로 암시적 형변환이 가능한 데이터만 사용 가능하다

 

- 부서 번호가 30인 사람들의 평균 급여 출력

 

SELECT AVG(SAL)
FROM EMP
WHERE DEPTNO = 30;

 

- DISTINCT 로 중복을 제거한 급여 평균값 출력

SELECT AVG(DISTINCT SAL)
FROM EMP
WHERE DEPTNO = 30;

 

3. COUNT :

- 데이터의 개수를 출력

- * 사용 시 SELECT문의 결과값으로 나온 행 데이터의 개수 반환

SELECT COUNT(*)
FROM EMP;

- WHERE 조건식과 사용시 유용

SELECT COUNT(*)
FROM EMP
WHERE DEPTNO = 30;

 

4. MAX / MIN :

- 입력 데이터 중 최댓값과 최솟값을 반환

- 숫자 이외에 날짜 데이터, 문자데이터 등 연산이 가능한 대상은 모두 사용 가능하다

 

- 부서번호가 10인 사원들의 최대 급여 출력

SELECT MAX(SAL)
FROM EMP
WHERE DEPTNO = 10;

- 부서번호가 10인 사원들의 최소 급여 출력

SELECT MIN(SAL)
FROM EMP
WHERE DEPTNO = 10;

 

- 날짜 데이터에 적용 : 부서번호 20인 사원의 입사일 중 가장 최근 입사일 출력

SELECT MAX(HIREDATE)
FROM 
WHERE DEPTNO = 20;

 

•  발생 할 수 있는 오류 : ORA-00937: 단일 그룹의 그룹 함수가 아닙니다
   : 특징 2. 단일행 함수 사용시 일반 컬럼과 사용 불가하다 (조회되는 SELECT 문의 컬럼 수가 다르기 때문)

SELECT ENAME, MAX(SAL)
FROM EMP;

 

 

 

1. CASE 문

 

  select ename, job, deptno, 
           case when deptno = 10 then 'AAA'
                when deptno = 20 then 'BBB'
                when deptno = 30 then 'CCC'
                     else '기타'
                     end as 부서명
    from emp;
    

 

 

    select ename, job, sal,
        case 
        when sal between 3000 and 5000 then '임원'
        when sal >= 2000 and  sal <3000 then '관리자'
        when sal >= 500 and sal < 2000 then '사원'
        else '기타'
        end as 직무
         from emp;
    
    select *
    from customers;
    
    

--10/19 oe 계정 
select date_of_birth
from customers;

--select date_of_birth,to_char(date_of_birth,'YYYY/MM/DD')
select to_char(date_of_birth,'YYYY/MM/DD')
from customers;

--만 나이구하기 소수점 필요x
select date_of_birth,to_char(date_of_birth,'YYYY/MM/DD'),
        round((sysdate - date_of_birth) / 365) || '살' as 나이
from customers;


--10대~70대 기타로 표현 
select Round((sysdate - date_of_birth) / 365),
        case 
           -- when Round((sysdate - date_of_birth) / 365) >= 10 and Round((sysdate - date_of_birth / 365)) < 19 then '10대'
            when Round((sysdate - date_of_birth) / 365) between 10 and 19 then '10대'
            when Round((sysdate - date_of_birth) / 365) between 20 and 29 then '20대'
            when Round((sysdate - date_of_birth) / 365) between 30 and 39 then '30대'
            when Round((sysdate - date_of_birth) / 365) between 40 and 49 then '40대'
            when Round((sysdate - date_of_birth) / 365) between 50 and 59 then '50대'
            when Round((sysdate - date_of_birth) / 365) between 60 and 69 then '60대' 
            when Round((sysdate - date_of_birth) / 365) between 70 and 79 then '70대'
            else '기타'
            end as 나이대
from customers;


-----------------------p174 연습문제 풀이

---1번문제 정답:

select empno,rpad(substr(empno,1,2),4,'*'),
        ename, rpad(substr(ename,1,1),5,'*')
from  emp
where length(ename) >= 5
and length(ename) <6;
     
---2번문제 정답:
select empno,ename,sal,
        trunc(sal/21.5,2) as Day_pay, --소수점 둘째 뒤로 다버려
        round(sal / 21.5 /8,1) as Time_pay-- 소수점 첫째 자리까지 살린다
from emp;


---3번문제 정답:
select empno,ename,mgr,
        case
                when substr(mgr,1,2) = '75' then '5555'
                when substr(mgr,1,2) = '76' then '5555'
                when substr(mgr,1,2) = '77' then '5555'
                when substr(mgr,1,2) = '78' then '5555'
                when mgr is null then '0000'
                else to_char(mgr)
                end as CHG_MGR
        from emp;
        
--============================================ p177


--단일행함수 : 여러행의 결과를 한줄로 보여줌
--그룹 함수 :sum(), avg(), count(), max(), min()
--특징 : 일반컬럼과 함께 사용할 수 없다. 
-- 크기 비교가 가능한 모든 타입에 사용가능하다(날짜 등)

select sal
from emp;

select sum(sal)
from emp;

select avg(sal)
from emp;

select count(*)
from emp;

--급여를 가장 많이 받는 사람과 적게 받는 사람 
select max(sal),min(sal)
from emp; 
        
 --단일행 함수 사용시 일반 컬럼과 사용 불가하다 //오류 : ORA-00937: 단일 그룹의 그룹 함수가 아닙니다
 --조회되는 모든 select 문의 컬럼수는 같아야 한다
select ename, max(sal)
from emp;


--20번 부서에서 가장 최근, 나중에 입사한사람
select min(hiredate),max(hiredate)
from emp
where deptno = 20;


--================================================ group by
--select 컬럼명
--from 테이블명
-- where 조건식
-- order by 컬럼명 정렬방식 ==> 맨 마지막에 작성한다

--union: 타입과 구조만 같으면  몇개가 같아도 괜차하 
--각 부서의 급여의 평균 
select avg(sal) from emp where deptno = 10
UNION
select avg(sal) from emp where deptno = 20
UNION
select avg(sal) from emp where deptno  = 30;


--특징 1)
--group by절로 간결화
--각 기준이 같은 것들의 역할들을 묶어준다

--조회되는 컬럼의 개수가 같으면 일반함수와 사용할수 있다.
select deptno
from emp
group by deptno; --데이터 개수 :3

select avg(sal) --avg 개수 :3
from emp
group by deptno; --3개의 그룸끼리모인다

--합치면
select deptno, avg(sal)
from emp
group by deptno;

--정렬
select deptno,avg(sal)
from emp
group by deptno
order by deptno;

select avg(sal)
from emp
group by deptno, job; --deptno 중 job으로 또 나눠서 묶어주세요 , 각 부서별 job의 개수 만큼 나누어서 출력된다. 

select deptno,job,avg(sal)
from emp
group by deptno,job 
order by deptno,job; --deptno기준 정렬 후 job 기준으로 다시 정렬 (순차)

--================================================ having 절

--select 컬럼명
--from 테이블명
-- where 조건식 (그룹함수 사용불가 , 그룹함수를 조건절로 사용하는 건 having 절 밖에 없다) (group by, having 보다 먼저 실행된다)
--group by 기준 컬럼명
-- having 조건식 ( group  by 아니면 쓸일없음 짝!뀽!) , 그룹함수 사용한다
-- order by 컬럼명 정렬방식 ==> 맨 마지막에 작성한다

select deptno, avg(sal)
from emp
group by deptno
having  avg(sal) > 2000; --group화 된 값의 조건을 만들 수 있다 . group by에 의해서 조회 결과에 조건을 준다
                        -- 조건식을 작성할 때 그룹함수를 사용한다. 

select deptno,avg(sal)
from emp
where deptno != 10 --10번이 아닌 부서들을 그룹화 
group by deptno
having avg(sal) >= 2000;