Oracle 기초 : 실전(9) Top-N Analysis

Introduction: Top-N Analysis - rowid, rownum

• rowid: 특정 Record에 접근하기 위한 논리적인 주소값
• rownum: 각 행에 대한 Serial Number. Oracle 내부적으로 insert한 순서대로 1부터 1씩 증가하여 부여된 것. where 조건절이나 Inline View를 통해 필터링하면 새로운 넘버를 부여받게 되고, order by를 통한 정렬 등으로는 변화하지 않는다. 즉, rownum은 Result Session의 index와 같다.

Syntax of the checking rowid/rownum:
select rowud, rownum
from Table

---

Syntax: Top-N Analysis

select Col from(select Col
from Table
order by asc/desc)
where rownum Operator Condition;

--who takes the highest salary 1-5
select rownum, Subquery.*
from(
select emp_name, salary
from employee
order by salary desc) Subquery
where rownum <= 5;

기본적으로 부여된 rownum은 from/where절을 마치고 얻을 수 있다. 단, 순차적으로 처리할 수 있는 경우에만 같은 level의 where절을 사용할 수 있다.(ex. 몇 번의 rownum까지 표시하기 위해서) 따라서 순차적으로 접근하는 조건절을 사용하지 않는(쉽게 말해 6~10위를 구하라거나) 경우 Inline View로 한 번 더 감싸고, Main Query(Inline View의 바깥)에서 Alias로 접근하는 방식으로 취할 수 있다.

---

Syntax: Between N to N Analysis

select * from (
select rownum RowNumAlias, Col1, Col1Alias, Col2 Col2Alias ...
from(
select Col1 Col2
from Table)
order by Col asc/desc)
where RowNumAlias between Num and Num
order by RowNumAlias asc/desc;

select *
from(
select rownum "RowNumAlias", SubQueryAlias.*
from(
select emp_name, salary
from employee
order by salary desc) SubQueryAlias
) SubqueryAlias
where RowNumAlias between 6 and 10;

위의 Top-N Analysis를 보충한 것. 상기했듯 1번부터 순차적으로 rownum에 접근하는 방식이 아닌 경우 하나의 Subquery만으로는 원하는 사잇값을 도출할 수 없다. (rownum은 from/where절을 마친 후에 생성되기 때문에 where절에서 조건문을 대입해봐야 rownum 생성 이전 시점이다.) 이러한 경우는 해당 코드를 한 번 더 Inline View로 감싸는 방법으로 개선할 수 있다. Inner-Inline View에서 모든 처리과정이 끝난 결과를 Outer-Inline View에서 받아 값을 도출하기 때문이다.

---

Inline View w.With As

with InlineViewAlias
as (select Col ColAlias ...
from Table TableAlias)
select Col1, Col2, ColAlias
from InlineViewAlias
where ColAlias Condition/ComparisonOperators;

with emp_gender_Age
as
(select E.*,
	trunc((sysdate-todate(decode(substr(emp_no, 8, 1)
    	 , '1', 19, '2', 19, 20)
		 ||substr(emp_no, 1, 6), 'yyyymmdd'))/365) Age
from employee E)
select emp_id, emp_name, Age
from emp_gender_Age
where Age between 30 and 49;

with InlineViewAlias as (Inline View)의 형식으로 Inline View Alias 재사용 또한 가능하다.

---