TOP-N 분석
• 정렬된 결과의 일부를 검색하는 방법은 다양하지만 가장 기본적인 방법은인라인 뷰를 이용하는 것이다.
• 인라인뷰를이용하는이유는SQL문에서정렬이가장마지막에수행되기때문이다.
SELECT ROWNUM, [컬럼, 컬럼, ...]
FROM (SELECT ... ORDER BY ...)
WHERE ROWNUM <= N;
‐ 상/하위 N개의 행을 검색한다.
‐ 인라인뷰에 반드시 ORDER BY절이 필요하다.
‐ ROWNUM은 검색된 행의순서이다.
인라인뷰를 이용한 Top-N 분석
• 가장 높은 급여를 받는 3인을 검색
SELECT ROWNUM, eno, ename, sal
FROM (SELECT eno, ename, sal
FROM emp
ORDER BY sal DESC)
WHERE ROWNUM <= 3;
RANK()를 이용한 순위검색
RANK() OVER ([PARTITION BY 컬럼,컬럼 ...] ORDER BY ......)
DENSE_RANK() OVER ([PARTITION BY 컬럼,컬럼 ...] ORDER BY ......)
ROW_NUMBER() OVER ([PARTITION BY 컬럼,컬럼 ...] ORDER BY ......)
‐ RANK 함수를이용해서다양한순위를검색한다.
‐ RANK()
． 순위검색에서 중복순위만큼 값을 증가시킨다.
‐ DENSE_RANK()
． 동일순위를 하나의 순위 및 행으로 취급한다.
‐ ROW_NUMBER()
． 동일값이라도 반드시 순위를 다르게 결정한다.
． 정렬값이 동일한 경우 어떤 값이 우선이 될지 알 수 없다.
． 오라클의 경우 ROWID에 따라 결정된다.
． 동일한 값이 없도록 정렬을 추가하는 것이 좋다.
Ranking 검색
• 급여순위를 검색
• 부서별 급여순위를 검색
SELECT eno, ename, sal,
RANK() OVER (ORDER BY sal DESC) RANK,
DENSE_RANK() OVER (ORDER BY sal DESC) DRANK,
ROW_NUMBER() OVER (ORDER BY sal DESC) RRANK
FROM emp;
SELECT eno, ename, dno, sal,
RANK() OVER (PARTITION BY dno ORDER BY sal DESC) RANK
FROM emp;
• 사원의 연봉을 순위 별로 검색
• 부서별로 사원의 급여순위를 검색
‐ 부서명, 급여를 반드시 검색한다.
SELECT RANK() OVER (PARTITION BY d.dno ORDER BY sal DESC) RRANK,
d.dno, dname, eno, ename, sal
FROM dept d, emp e
WHERE d.dno=e.dno;
시퀀스(Sequence)
• 유일한값을 자동으로 생성한다.
• 보통 PK 값을 생성하는 데 사용된다.
• 응용프로그램을 대체한다.
• 캐시를 통해 성능을 높인다.
시퀀스생성과수정
CREATE [| ALTER] SEQUENCE 시퀀스
[ START WITH 시작_값
INCREMENT BY 증가_값
MAXVALUE [상한_값 | NOMAXVALUE]
MINVALUE [하한_값 | NOMINVALUE]
CYCLE | NOCYCLE
CACHE [cache_개수| NOCACHE] ];
‐ START WITH : 할당을 시작하는 값을 정의한다.
‐ INCREMENT BY : 생성되는 값의 증가 치를 결정한다. 지정하지 않으면 1에서 시작한다.
‐ MAXVALUE : 생성될 상한 값을 정의한다. 기본설정은 NOMAXVALUE이다.
‐ MINVALUE : 하한값을 정의한다. 기본 설정은 NOMINVALUE이다.
‐ CYCLE : 상한 값까지생성한이후 다시 하한값부터 재생성할것인지여부이다.
‐ CACHE : 메모리에 미리 생성될 값의 개수를 정의한다.
시퀀스 삭제와 확인, 사용
DROP SEQUENCE 시퀀스;
SELECT sequence_name, max_value, min_value, increment_by,
cache_size, last_number, cycle_flag
FROM user_sequences;
...... 시퀀스. NEXTVAL......
...... 시퀀스. CURRVAL......
‐ NEXTVAL : 시퀀스로부터 유일한 값을 할당받는다.
‐ CURRVAL : 시퀀스로부터 마지막으로 할당받은 값을 다시 할당받는다.
‐ 주로 INSERT문의 VALUES절이나 UPDATE의 SET절에서 많이 사용된다.
시퀀스생성
• 시퀀스를 생성하고 설정을 검색한다.
CREATE SEQUENCE emp_eno_seq
START WITH 1
INCREMENT BY 1
NOMAXVALUE
NOMINVALUE
NOCYCLE
CACHE 20;
CREATE SEQUENCE dept_dno_seq;
SELECT sequence_name, max_value, min_value, increment_by,
cache_size, last_number, cycle_flag
FROM user_sequences;
시퀀스이용
• emp table에 시퀀스를 이용해서 행을 입력
INSERT INTO emp (eno, ename)
VALUES (emp_eno_seq.nextval, '첫 번째');
SELECT emp_eno_seq.currval FROM dual;
ROLLBACK;
SELECT sequence_name, max_value, min_value, increment_by,
cache_size, last_number, cycle_flag
FROM user_sequences;