[kh정보교육원 40일차] inline view /rownum /순위- rank, dense_rank,row_number /집계-sum over,listagg,avg over

 

유형 분류
결과값에 따른 유형	메인 쿼리 관계에 따른 구분	위치에 따른 구분
1. 단일행 단일컬럼 서브쿼리 (1행 1열)	1. 일반 서브쿼리 서브쿼리의 결과값을 메인쿼리에 통보	1. 스칼라 서브 쿼리
2. 다중행 단일컬럼 서브쿼리 (n행 1열)
3. 단일행 다중컬럼 서브쿼리(1행 n열)	2. 상호연관 서브쿼리 메인쿼리값을 가지고 서브쿼리 처리 후, 메인쿼리에 다시 통보	2. inline - view
4. 다중행 다중컬럼 서브쿼리(n행 m열)

지난시간에 서브쿼리를 분류에 따라 배웠다. 이때, inline - view에 대해 배우지 못했는데, 오늘 배우게 된다.

 

inline view

- from절에 사용된 서브쿼리

- view란 실제 테이블에 근거해서 작성된 논리적 가상 테이블을 의미한다. view를 통해 실제테이블의 데이터에 접근한다.

 

방법은 두가지가 있다.

 

1. inline view 1회용

2. stored view 객제로 저장 후 재사용

 

1. inline view

inline view에 정의하지 않은 컬럼은 메인쿼리에서 사용할 수 없다.

inline view에 별칭을 사용했다면, 메인쿼리에서는 별칭으로 접근해야 한다.

 

ex ) 여사원의 사번, 사원명, 성별 조회

1. select절과 where절에 서브쿼리를 쓴 경우

select
    사번, 사원명, 성별
from (
    select
        emp_id 사번,
        emp_name 사원명,
        decode(substr(emp_no, 8, 1), '2', '여', '4', '여', '남') 성별
    from
        employee
)
where
    성별 = '여';

 

2. inline-view (from절에 서브쿼리를 쓴 경우)

select
    *
from (
    select
        emp_id, emp_name, extract(year from hire_date) hire_year
    from
        employee
)
where 
    hire_year between 1990 and 1999
order by
    hire_year;

 

 

ex ) 30 - 49세의 여사원 조회 (사번, 부서명, 성별, 나이)

select
    *
from (
    select
        emp_id, 
        emp_name,
        (select dept_title from department where dept_id = e.dept_code) dept_title,
        decode(substr(emp_no, 8, 1), '1', '남', '3', '남', '여') gender,
        ((extract (year from sysdate)) - (decode(substr(emp_no, 8, 1), '1', 1900, '2', 1900, 2000) + substr(emp_no, 1, 2)) + 1) age
    from
        employee e
)
where
    age between 30 and 39
    and
    gender = '여';

 

 

 

고급쿼리

TOP-N 분석

- 실제컬럼 / 가상컬럼에 대해 정렬 후 top또는 bottom에서 n개의 레코드를 추려낸 쿼리

 

rownum

- 테이블 레코드에 대해서 부여하는 식별번호. 오라클에서 insert시에 자동으로 부여된다.

 

rownum이 새로 부여되는 경우

1. where절을 통해 결과집합이 변경된 경우 rownum 새로 부여

2. inlineview를 통회 조회된 경우 rownum 새로부여

 

1번의 경우 )

select
    rownum,
    e.*
from
    employee e
where
    dept_code = 'D5'
order by
    emp_name;

 

2번의 경우 )

ex ) 최근 입사한 10명 조회 (사번, 사원명, 입사일)

select
    rownum, emp_id, emp_name, hire_date
from (
    select
        *
    from
        employee
    order by
        hire_date desc
)
where 
    rownum between 1 and 10;

 

from 의 inline view에 의해 조회된 경우로, rownum이 새로 부여된다.

 

응용 )

ex ) 급여가 높은 순으로 6-10위 조회

select
    e.*
from (
    select
        rownum rnum,
        e.*
    from (
        select
            emp_name, 
            salary
        from
            employee
        order by
            salary desc
    ) e
) e
where
    rnum between 6 and 10;

 

where절이 끝날 때 rownum의 새로운 부여가 완료된다.

6부터 순차접근시에만 같은 레벨에서 사용이 가능하다.

 

WINDOW FUNCTION

행과 행간의 관계를 쉽게 정의하기 위한 함수

select절에서만 사용 가능

순위관련처리	rank, dense_rank, row_number
집계관련처리	sum, avg, max, min, count
순서관련처리	first_value, last_value...
비율관련처리	cume_dist, percent_rank, ntile....
통계관련처리	corr, covar_pop....

 

window_function (args) over ([partition by절][order by절][windowing절])

args : 윈도우함수에 전달하는 인자 ( 0 - n )

over절 : 행그룹지정, 그룹당 결과 출력

partition by : 윈도우함수의 group by

order by : 행순서 지정

windowing : 대상행 지정

 

 

순위관련

rank : order by 중복된 값이 있다면, 그 다음 순위는 중복된 값만큼 건너 뛴다.

dense_rank : order by 중복된 값이 있어도 건너뛰지 않고, 순위를 부여한다.

row_number : 중복값 없이 순위 부여

select
    emp_name,
    salary,
    rank() over(order by salary desc) rank,
    dense_rank() over(order by salary desc) dense_rank,
    row_number() over(order by salary desc) r
    ow_number 
from
    employee;

 

rank는 20등이 2명이면 22등을 부여하고, dense_rink는 20등이 2명이어도 21등을 부여하고, row_number는 중복값 없이 한 순위당 한명씩 부여하는 것을 확인할 수 있다.

 

집계관련

sum( 합계를 구할 컬럼 ) over ( PARTITION 그룹항목 ORDER BY 정렬순서)

 

ex ) 사원명, 급여, 전사원의 급여 합계

select
    emp_name,
    salary,
    dept_code,
    sum(salary) over(partition by dept_code) salary_sum_by_dept,
    sum(salary) over(partition by dept_code order by salary) salary_sum_by_dept, -- 급여순에 따른 누계
    sum(salary) over() salary_sum,
    sum(salary) over(order by salary, emp_id) salary_sum
from
    employee;

 

listagg

wm_concat(10g - 11g)함수 새로운 버젼

조회된 하나의 컬럼으로 합쳐서 출력이 된다.

group 을 이용해서 그룹화도 가능하다.

select
    listagg(emp_name, ',') within group (order by emp_name) "전체사원명"
from
    employee;

 

그룹화하기 예제

select
    emp_name,
    dept_code, 
    listagg(emp_name, ', ') within group (order by emp_id) over(partition by dept_code)"부서원"
from
    employee;

 

avg() over()

select
    emp_name,
    dept_code,
    salary,
    trunc(avg(salary) over()) avg_sal, 
    trunc(avg(salary) over(partition by dept_code)) avg_sal_by_dept
from
    employee;

 

 

 

 

이 외에도

 

순위관련처리	rank, dense_rank, row_number
집계관련처리	sum, avg, max, min, count
순서관련처리	first_value, last_value...
비율관련처리	cume_dist, percent_rank, ntile....
통계관련처리	corr, covar_pop....

 

다음과 같은 함수들이 있다.

이는 다음에 시간이 날 때 정리하도록 하겠다.