05.22(top-n분석, window함수)

TOP-N 분석

rowid, rownum 

rowid

테이블 특정 레코드에 접근하기 위한 논리적 주소값

rownum

각 행에 대한 순서

오라클에서 내부적으로 자동부여

insert문 데이터추가 순서에 따라 1씩 증가하며 부여됨
where절에 의한 변형 또는 inline-view 생성시 새로 부여

select rownum,
       rowid,
       employee.*
from employee
order by emp_name; --order by해도 rownum은 이미 부여된 번호이기 때문에 변하지 x

 

예제1)급여가 제일 높은 3명은? (iline view를 통해 rownum 재부여 하기)

select rownum new,
       E.*
from(select rownum old, emp_name, salary
     from employee E
     order by salary desc) E
where rownum between 1 and 3;

 

예제2) 부서별 급여평균 top3를 조회하고 부서명, 평균급여(평균급여별 내림차순)

select rownum,
       E.*
from(select nvl(dept_code,'인턴') dept_code,
            trunc(avg(salary)) avg_sal
     from employee
     group by dept_code
     order by avg_sal desc)E
where rownum between 1 and 3;

 

예제3)급여 랭킹이 6~10위 구하기
인라인뷰의 rownum은 from/where절을 끝마치고 완벽히 얻을 수 있음
다만 1부터 순차적으로 접근시에는 where절에서 결과를 조회 가능
1부터 순차적으로 접근하지 않는 경우(offset값이 있는 경우), inline-view를 한 계층 더 사용해야 함

select *                      
from(select rownum rnum,             --rownum을 이용하기 위함(별칭 필수)
            E.*
    from(select emp_name,            --급여별로 줄세우기용
                salary
         from employee
         order by salary desc)E )E   --안의 서브쿼리와 같은 별칭 사용해도 상관 X
where rnum between 6 and 10;         --rownum을 쓰면 제대로된 값이 나오지 않음

 

 

window 함수(분석함수)

데이터를 분석하는 함수로 행과 행간의 관계를 쉽게 정의하기 위한 표준 내장함수

분석함수를 사용하면, 쿼리 실행의 결과인 result set을 대상으로 전체 그룹별이 아닌 소그룹별로 각 행에 대한 계산값을 리턴

 

표현식)

분석함수명 ([전달인자1[, 전달인자2[, 전달인자3]]])  --컬럼명등 0~n개의 인자를 받음
over ([partition by 절]                            --group by 역할, 그룹핑 조건
      [order by 절]                                --정렬(순서)를 위한 조건
      [window 절]) 

 

partition by

예제)부서별 급여랭킹

select dept_code,
       emp_name,
       salary,
       rank() over(partition by dept_code order by salary desc) rank_in_dept,
       rank() over(order by salary desc) salary_topn 
from employee
order by dept_code, rank_in_dept;

 

1. 순위관련 함수

rank() over()

특정 컬럼 기준으로 순위를 부여

동일한 값이 있다면, 해당 개수만큼 건너뛰고, 다음 순위 부여

select emp_name,
       salary,
       rank() over(order by salary desc) salary_topn
from employee;

 

예제)입사 순서를 표시하고, 정렬하기

select rank() over(order by hire_date) 입사순서,       --날짜는 과거가 빠르고 미래가 늦음
       emp_name,
       hire_date
from employee;

 

dense_rank() over()

동일한 순위가 있더라도 다음 순위를 건너 뛰지 않고 순차적으로 순위 부여

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

 

window함수 topn분석

예제)급여등수 1~10등 조회 

where절에 window함수 사용 X(select절에만 사용가능)

select *
from(select emp_name,
            salary,
            rank() over(order by salary desc) rank
    from employee)E
where rank between 11 and 20;

 

 

2. 집계관련 함수

sum() over()

select emp_name,
       salary,
       sum(salary) over()   --sum(salary)는 사용할 수 없음 but over를 붙이면 window함수가 됨
from employee;

 

 예제)부서별 급여합계

select emp_name,
       dept_code,
       salary,
       sum(salary) over(partition by dept_code order by salary) sum_by_dept,  --부서별 salary 누계부여줌
       sum(salary) over(partition by dept_code) sum_by_dept,
       sum(salary) over() 전체급여합계
from employee;

 

avg() over()

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