Top-N Analysis

- 주의 사항
Where 절에는 Alias를 사용할 수 없다. 이유는?
The values in the select list do not "exist" before the where clause is executed.
The order of execution semantically is:
FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY




* TOP-N 분석

 

   ㄴ 최대값 , 최소값 => N(갯수)

   ㄴ rownum => insert할때 자동으로 삽입 + inline view

   ㄴ 참고 사이트 : http://www.oracle.com/technology/oramag/oracle/07-jan/o17asktom.html

   ㄴ 형식

SELECT 컬럼명,..., ROWNUM FROM (SELECT 컬럼명,... from 테이블명  ORDER BY top_n_컬럼명)  WHERE ROWNUM <= n;

예제1> 연봉 가장 많은 5명

SELECT empno, sal FROM ( SELECT * FROM emp ORDER BY sal DESC) WHERE rownum <= 5;

         예제2> 입사일 기준으로 4 번째 부터 10번째까지 출력? ( 7 명 )
         select  empno , ename
                  from   (select  a.* , rownum rnum  from  (select *  from emp order by hiredate ) a
                  where rownum <= 10 ) where rnum >= 4;

http://blog.naver.com/iyearbook/150047915439

ROWNUM

 

ROWNUM은 SQL 쿼리결과 집합에 대한 순번(ROW의 SEQUENCE)을 나타내는 의사컬럼(가상의 COLUMN)이다.

 

용도는 TOP-N 분석에 유용하다.

 

select [column list], ROWNUM

from

    (select [column list]

     from tbl

     where [...]

     order by [TOP-N Column]

   )

where rownum <= 100

 

=> 서브쿼리에서 검색 및 sorting한 결과 집합의 상위 100개를 가져온다.

 

 

RANK() OVER ( [PARTITION BY column] ORDER BY column [NULL FIRST | NULL LAST] ) as rank

 

 OVER : 순위를 부여하기 위한 대상 집합의 정렬 기준과 분할 기준 정의

 PARTITION BY : value expression1을 기준으로 분할, 생략시 전체 집합 대상

예 ) rank() over(partition 반 order by 점수) 반에서의 점수의 등수

         => 반(partition column)의 데이타 별로 점수(order by column)에 대한 순위(rank())를 매김.
      rank() over(order by 점수) 전체에서의 점수의 등수

        => 모든 데이타(전체 row)에서 점수(order by column)에 대한 순위(rank())를 매김

 

ORDER BY : 각 분할내에서 데이터를 정렬하는 기준 칼럼 지정

NULL FIRST | NULL LAST : 정렬 결과에서 NULL 값의 위치 지정

 

DENSE_RANK()

 

ROW_NUMBER()

 

RANK()와 DENSE_RANK()와 ROW_NUMBER()의 차이 비교

RANK      DENSE_RANK    ROW_NUMBER

1            1                     1

2            2                     2

3            3                     3

3            3                     4

3            3                     5

6            4                     6

7            5                     7

8            6                     8

http://blog.naver.com/kdm707/10025917788



top-N 분석은 최대값이나 최소값을 가진 컬럼을 질의할 때 유용하게 사용되는 분석방법이다.
• inline view에서 ORDER BY 절을 사용할 수 있으므로 데이터를 원하는 순서로 정렬도 가능하다.
• ROWNUM 컬럼은 subquery에서 반환되는 각 행에 순차적인 번호를 부여하는 pseudo 컬럼이다.
• n값은 < 또는 >=를 사용하여 정의하며, 반환될 행의 개수를 지정한다.

【형식】
 SELECT 컬럼명,..., ROWNUM
 FROM (SELECT 컬럼명,... from 테이블명
       ORDER BY top_n_컬럼명)
        WHERE ROWNUM <= n;

여기서
 FROM 절 다음의 SELECT 문은 Top-N 분석 대상 컬럼에 대한 인라인 뷰의 정의
 ORDER BY :결과 집합의 정렬 순서 지정
 ROWNUM : 결과 집합에 대해 순차적인 번호를 할당하기 위한 의사 컬럼
 tOP-n_컬럼명 : 정렬 기준으로 사용하는 컬럼 또는 컬럼 집합

【예제】
SQL> select rownum as fastdate,ename,hiredate
  2  from (select ename,hiredate from emp
  3        order by hiredate desc)
  4  where rownum <= 5;
 
  FASTDATE ENAME      HIREDATE
---------- ---------- ------------
         1 ADAMS      23-MAY-87
         2 SCOTT      19-APR-87
         3 MILLER     23-JAN-82
         4 FORD       03-DEC-81
         5 KING       17-NOV-81
 
SQL>
다음 예는 TOP_N 분석 방법이다.
 
SQL> select deptno,ename,sal,rank_value
  2  from (select deptno, ename, sal,
  3        RANK() OVER (ORDER BY sal DESC) AS rank_value
  4        FROM emp)
  5  WHERE rank_value <=5;
 
    DEPTNO ENAME             SAL RANK_VALUE
---------- ---------- ---------- ----------
        10 KING             5000          1
        20 SCOTT            3000          2
        20 FORD             3000          2
        20 JONES            2975          4
        30 BLAKE            2850          5
 
SQL>
http://radiocom.kunsan.ac.kr/lecture/oracle/function/TOP_N.html


http://thdwns2.springnote.com/pages/1007064