220705 TIL

Yeoni·2022년 7월 5일

국비교육TIL

목록 보기

25/41

국비교육 25일차 Oracle : stored view/inline view 연습문제, 그룹함수, group by 절

1. Stored View

1) 생성된 Stored View 조회하기

select *
from user_views;

2) 생성된 Stored View에서 특정 View 소스 보기

select text
from user_views
where view_name = '찾는 view 이름';

3) 생성된 Stored View 삭제하기

drop view view_loan;

2. 그룹 함수(집계 함수)

예시는 hr 테이블 활용

1) 그룹함수의 종류

(1) sum -- 합계
(2) avg -- 평균
(3) max -- 최대값
(4) min -- 최소값
(5) count -- select 되어서 나오는 결과물의 행의 개수
(6) variance -- 분산
(7) stddev -- 표준편차

분산 : 분산의 제곱근이 표준편차 (평균에서 떨어진 정도)
표준편차 : 표준편차의 제곱승이 분산 (평균과의 차액)

2) 그룹함수의 특징

그룹함수는 결과값이 오로지 1개 행만 나온다.

select sum(salary)
from employees;
-- 691416

단일행함수는 결과값이 메모리에 로드되어진 행의 개수와 동일한 개수로 나온다

select substr(jubun,7,1)
from employees;
-- 결과값 107개 나옴, 메모리에 107개가 올라와있다.

그룹함수에서는 null이 있으면 무조건 null을 제외시킨 후 연산을 하며, 그룹함수를 사용하면 1개의 결과값을 가진다.
NVL 등을 이용하여 null 값에 0과 같은 다른 값을 부여하여 계산에 오류가 없도록 사용하여야 한다.

select sum(salary), count(salary)
from employees;
-- 691416 	107

select sum(salary * commission_pct), count(salary * commission_pct)
from employees;
-- 73690		35
-- commssion_pct가 없는 행은 제외되고 계산이 되기 때문에 부정확한 값이 나올 수 있다.

3. group by절

1) 그룹함수와 group by절 함께 사용하기

-- employees 테이블에서 부서번호별로 인원수를 나타내세요. 
select department_id as 부서번호         -- group 지은 애들을 어떻게 보여줄지
     , count(*) as 인원수
from employees  
group by department_id -- department_id 컬럼의 값이 같은 것 끼리 그룹을 짓는다.  
order by 1;

2) 1차 그룹, 2차 그룹

-- employees 테이블에서 부서번호별, 성별, 인원수를 나타내세요.
select V.department_id as 부서번호
     , V.gender as 성별
     , count(*) as 인원수
from 
    (
        select department_id
              , case when substr(jubun, 7, 1) in('1','3') then '남' else '여' end AS GENDER    
        from employees
     ) V
group by V.department_id, V.GENDER 
order by 1,2;