오라클 분석함수, (FIRST_VALUE, LAST_VALUE, RANK, DENSE_RANK, ROW_NUMBER)
http://ojc.asia/bbs/board.php?bo_table=LecSQLnPlSql&wr_id=628
7.4 분석함수(FIRST_VALUE, LAST_VALUE, RANK, DENSE_RANK, ROW_NUMBER)
FIRST_VALUE : 정렬된 칼럼 값중 처음 값을 리턴한다. 값이 NULL인 경우 IGNORE NULLS를 기술하지 않았다면 NULL을 리턴한다.
LAST_VALUE : 정렬된 칼럼 값중 마지막 값을 리턴한다. 값이 NULL인 경우 IGNORE NULLS를 기술하지 않았다면 NULL을 리턴한다.
[기본 형식]
FIRST_VALUE(column) [REPECT|IGNORE NULLS] over(PARTITION BY column order by column [ASC|DESC]) [ROWS|RANGE UNBOUNDED PRECEDING] )
LAST_VALUE(column) [REPECT|IGNORE NULLS] over(PARTITION BY column order by column [ASC|DESC]) [ROWS|RANGE UNBOUNDED PRECEDING] )
REPECT|IGNORE NULLS 구문은 NULL값을 연산에 포함할지를 결정하는데 IGNORE인 경우 NULL 값은 제외된다.
--EMP 테이블에서 10번 부서 사원중에서 급여가 가장 적은 사원이름을 출력 SQL> select empno, ename, sal,deptno, first_value(ename) over(order by sal) as lowest_sal from (select * from emp where deptno=10 ) order by empno;
EMPNO ENAME SAL DEPTNO LOWEST_SAL ---------- ---------- ---------- ---------- ---------- 7782 CLARK 2450 10 MILLER 7839 KING 5000 10 MILLER 7934 MILLER 1300 10 MILLER
--10번 부서 사원들중 급여가 가장 높은 직원의 입사일을 출력. SQL>select empno, ename, sal,deptno,hiredate, last_value(hiredate) over(order by sal ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as highsal_hiredate from (select * from emp where deptno=10 order by hiredate) order by empno;
EMPNO ENAME SAL DEPTNO HIREDATE HIGHSAL_ -------- ---------- ---------- ---------- -------- -------- 7782 CLARK 2450 10 81/06/09 81/11/17 7839 KING 5000 10 81/11/17 81/11/17 7934 MILLER 1300 10 82/01/23 81/11/17
--전체 사원을 출력하는데 부서별로 가장 오래된 입사일과 최근 입사일을 같이 출력. SQL> select empno, ename, sal,deptno,hiredate, first_value(hiredate) over(PARTITION BY deptno order by hiredate ROWS UNBOUNDED PRECEDING ) as hiredate1, last_value(hiredate) over(PARTITION BY deptno order by hiredate ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as hiredate2 from emp order by deptno, empno;
EMPNO ENAME SAL DEPTNO HIREDATE HIREDATE HIREDATE ----- ---------- ---------- ---------- -------- -------- -------- 7782 CLARK 2450 10 81/06/09 81/06/09 82/01/23 7839 KING 5000 10 81/11/17 81/06/09 82/01/23 …… 7698 BLAKE 2850 30 81/05/01 81/02/20 81/12/03 7844 TURNER 1500 30 81/09/08 81/02/20 81/12/03 7900 JAMES 950 30 81/12/03 81/02/20 81/12/03
-- EMP 테이블의 모든 레코드를 급여 오름차순으로 출력하는데 EMPNO, ENAME, SAL, 이전레코드SAL값, 다음레코드SAL값을 같이 출력하세요 SQL> SELECT empno, ename, deptno, sal, FIRST_VALUE (sal) over (order by sal asc ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) as "PREVIOUS_SAL", LAST_VALUE(sal) over (order by sal asc ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) as "NEXT_SAL" FROM emp
EMPNO ENAME DEPTNO SAL PREVIOUS_SAL NEXT_SAL ------------------------------------------------------------------------------------ 7369 SMITH 20 800 800 950 7900 JAMES 30 950 800 1100 7876 ADAMS 20 1100 950 1250 7521 WARD 30 1250 1100 1250 7654 MARTIN 30 1250 1250 1300 …… 7839 KING 10 5000 3000 5000 |
RANK() OVER ( query_partition_clause ORDER_BY clause) : 전체 행을 대상으로 각각의 행에 대해 순위를 계산하는 역할을 한다.
-- KOR 테이블을 만들고 이름, 성적, 반 3개의 칼럼을 갖도록 구성하자. 아래 CREATE TABLE문의 칼default 0이라는 의미는 성적(marks)에 값이 들어 오지 않은 경우에는 기본적으로 0이 입력된다는 의미이다. SQL> create table kor ( name varchar2(10) not null, marks number(3,0) default 0, ban number(1,0) not null) ;
테이블이 생성되었습니다. SQL> insert into kor values ('가길동', 88, 1); SQL> insert into kor values ('나길동', 64, 2); SQL> insert into kor values ('다길동', 78, 1); SQL> insert into kor values ('라길동', 99, 1); SQL> insert into kor values ('마길동', 78, 1); SQL> insert into kor values ('바길동', 89, 2); SQL> commit; 커밋이 완료되었습니다.
-- 반에 관계없이 전체석차를 구하는 것이다. RANK와 DENSE_RANK의 차이에 대해 이해 바란다. 1등이 한명 2등이 2명인 경우 다음순위가 RANK인 경우에는 4등이 되지만 DENSE_RANK인 경우에는 3등이 된다. SQL> select name "이름", marks "성적", ban "반", rank() over (order by marks desc) "석차1", dense_rank() over (order by marks desc) "석차2" from kor;
이름 성적 반 석차1 석차2 ---------- ---------- ---------- ---------- ---------- 라길동 99 1 1 1 바길동 89 2 2 2 가길동 88 1 3 3 다길동 78 1 4 4 마길동 78 1 4 4 나길동 64 2 6 5
6 개의 행이 선택되었습니다.
-- 반내에서의 석차를 구하는 예문이다. 주요한 키워드는 PARTITION BY이다 SQL> select name "이름", marks "성적", ban "반", rank() over (partition by ban order by marks desc) "반 석차" from kor /
이름 성적 반 반 석차 ---------- ---------- ---------- ---------- 라길동 99 1 1 가길동 88 1 2 다길동 78 1 3 마길동 78 1 3 바길동 89 2 1 나길동 64 2 2
-- EMP 테이블에서 사원의 사번, 이름, 급여, 부서별로 급여 순위를 출력하시오(RANK, DENSE_RANK, ROW_NUMBER의 차이에 대해 이해 하세요) SQL> SELECT EMPNO, ENAME, SAL, DEPTNO RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) RANK, DENSE_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) D_RANK, ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) RNUM FROM EMP;
EMPNO ENAME SAL DEPTNO RANK D_RANK RNUM ---------------------------------------------------------------------------------- 7839 KING 5000 10 1 1 1 7782 CLARK 2450 10 2 2 2 7934 MILLER 1300 10 3 3 3 7788 SCOTT 3000 20 1 1 1 7902 FORD 3000 20 1 1 2 7566 JONES 2975 20 3 2 3 7876 ADAMS 1100 20 4 3 4 7369 SMITH 800 20 5 4 5 7698 BLAKE 2850 30 1 1 1 7499 ALLEN 1600 30 2 2 2 7844 TURNER 1500 30 3 3 3 7654 MARTIN 1250 30 4 4 4 7521 WARD 1250 30 4 4 5 7900 JAMES 950 30 6 5 6
|
댓글 없음:
댓글 쓰기