[Oracle - 예제_LEAD.LAG]
[CHAPTER 1] 자료의 조회
☞ Guidelines
commit 과 rollback 과의 차이점
commit은 저장이고 rollback은 취소다.
취소는 바로 직전까지 commit된 이후의 작업에 대한 취소이다
하나의 트랜잭션이 완료된다는 것은 commit까지를 의미한다
commit를 하지 않아도 commit되는 경우는 다음과 같다
① DDL 이나 DCL 문장을 만났을 때
② SQL*PLUS 에서 EXIT이용하여 정상 종료되었을 때
③ SET autocommit on 으로 환경이 설정되어 있을때
예를 들면
create table test as select ykiho from tbjcc10 where rownum < 10;
● null 의 이해
정의: null 은 column 에 값이 아무것도 없다는 의미
질의: where 문에서는 반드시
where a is null 혹은 where a is not null 를 사용해야 한다
만약 아래와 같이 사용시 예상치 않는 결과가 초래할 수 있다
where a = null 혹은 where a <> null
NVL사용:select emp_name, nvl(hobby,'없음') from temp;
즉 hobby 가 null 인 사람은 모두 hobby를 '없음'이라고 표시하라는 의미임
● alias를 반드시 사용할 경우
■ table alias 사용시
self join 일 경우에는 반드시 table alias를 사용해야 한다
※ self join 이란
때때로 자체적으로 테이블을 조인할 필요가 있다.
각 종업원의 관리자 명을 알기 위해서 자체적으로 EMP 테이블을 조인하는 것이
필요함.
☞ Guidelines
1) Self join을 사용하여 한 테이블의 행들을 같은 테이블의 행들과 조인한다.
2) 같은 테이블에 대해 두 개의 alias를 작성(테이블 구분)함으로 FROM절에 두 개의
테이블을 사용하는 것과 같이 한다.
3) Column에 대해서도 어떤 테이블에서 왔는지 반드시 Alias명을 기술하여야 한다.
4) 테이블 하나를 두개 또는 그 이상으로 Self join할 수 있다.
예시) EMP 테이블에서 Self join하여 관리자를 출력시
SQL> SELECT worker.ename || '의 관리자는 ' || manager.ename || '이다'
2 FROM emp worker, emp manager
3 WHERE worker.mgr = manager.empno;
WORKER.ENAME||'의관리자는'||MANAGER.
------------------------------------
BLAKE의 관리자는 KING이다
CLARK의 관리자는 KING이다
13 rows selected.
■ column alias 사용시
rownum 을 사용할 경우 즉 inline view안에서 사용되어 진 후 다시 이들을 from 절로
사용하는 query에서 rownum 이용시 반드시 column alias 를 사용해야 한다.
예시) 요양기호중 3번째로 큰 요양기호를 추출하는 SQL 문장 작성하기.
select YKIHO
from ( select /*+ index_desc(TBJCC10 PK_TBJCC10) */ rownum num, YKIHO
from TBJCC10 where YKIHO > 0 and rownum < 4)
where num = 3;
● 합성연산자 사용시 (concatenation )
두개 이상의 문자열을 연결하여 하나의 문자열을 만들어낼 때 ( || )
set heading off
set feedback off
set pause off
spool s_drop_all.sql
select 'drop table '|| table_name|| ' cascade constraints;' from user_tables;
spool off
위의 합성연산자로 만든 자료는 pc excel file로 활용을 많이 하고 있음
● where 절 사용시 기본사항
오라클의 rule base optimizer mode에서 index이용시 where절을 이용하여
index를 사용하거나 유도할 수 있다
① select ykiho from tbjcc10; // 입력순서대로 display
② select ykiho from tbjcc10 where ykiho > 0; // ykiho 가 pk_ykiho 존재. ykiho 순서적 display
③ select max(ykiho) from tbjcc10; // full table scan 한후 max 값을 reutrn 한다
④ select /*+ index_desc(TBJCC10 PK_TBJCC10) */ YKIHO // index search 내림순중
1번째를 return
from TBJCC10
where YKIHO > 0 and rownum = 1;
■ optimizer mode와 실행계획
optimizer는 수행하고자 하는 DML(select,delete,update,insert)을 가장 효율적으로
처리할 수 있는 최적의 경로를 찾아 주는 것이 주임무다
이러기 위해 필요한 내부요인을 보면
① 어떤table를 먼저 읽을 것인가
② table읽을시 index사용할 것인가
③ index사용시 어떤 index를 사용할 것인가
④ join 시 어떤 방법 join할 것인가(동등join,outer join, self join )
위와 같은 실행계획에 의거 수핼경로는 PLAN을 통해서 알 수 있다
▶ PLAN 정보 확인 방법
구성: PLAN TABLE를 만들어야 한다
예시: sqlplus hira_insp/hira_insp
SQL>@ORACLE_HOME/rdbms/admin/utlxplan.sql 를 실행하여 plan_table생성 후
public synonym 을 만든다
SQL> create public synonym plan_table for sys.plan_table;
● INDEX를 조회할 수 있는 DATA DICTIONARY
INDEX를 조회할 수 있는 DATA DICTIONARY를 보면
① USER_INDEXES
TABLE이 소유하고 있는 INDEX가 어떤 것인지 알아볼 수 있다
SQL> select table_name,index_name,uniqueness
from user_indexes
where table_name = 'TBJFC02';
② USER_IND_COLUMNS
INDEX가 어떤 COLUMN으로 구성되어 있는가를 확인할 수 있다
SQL>select table_name,index_name,column_position,column_name
from user_ind_columns
where table_name = 'TBJCC10'
order by table_name,index_name,column_position
● PCTFREE 와 PCTUSED
① PCTFREE 10 (defalut)
이미 블럭에 쓰여진 UPDATE,INSERT를 위하여 예역되는 공간을 말한다
PCTFREE 10 즉 블럭의 공간중 10%를 이미 블록에 쓰여진 ROW의 UODATE나
INSERT를 위하여 사용되지 않고 남게 하겠다는 의미임
② PCTUSED 40 (defalut)
PCTFREE에서 지정한 영역만큼만 FREE SPACE가 남게 되면 오라클은 더 이상
해당 블록에 새로운 ROW를 삽입할 수 없다
사용공간이 ROW의 삭제등으로 인하여 PCTUSED에서 지정한 값 아래로 떨어지게
되면 그때 다시 해당 BLOCK에 새로운 ROW가 삽입될 수 있다
● 연산자
총6자리 부서코드중 2번째 자리에 A 가 들어가는 ROW를 검색
SQL>select emp_id, emp_name, dept_code
from temp
where dept_code like '_A____';
[CHAPTER 2] 함수
● SUBSTR
SQL>select substr(ykiho,3,1),count(*)
from tbjcc10
group by substr(ykiho,3,1)
■ SUBSTR와 SUBSTRB 차이점 확인
SQL> select SUBSTR(ADDR,3,10)
from tbjcc10
WHERE ROWNUM < 10
SQL> select SUBSTRB(ADDR,3,10)
from tbjcc10
WHERE ROWNUM < 10
● CHR
CHR를 이용해 개행문자의 ASCII 코드를 찾고 그 결과를 확인한다
SQL> select YKIHO||CHR(10)||SUBSTR(YOYANG_NM,1,10)||CHR(10)||SUBSTR(ADDR,1,20)
from tbjcc10
WHERE ROWNUM < 2
YKIHO||CHR(10)||SUBSTR(YOYANG_NM,1,10)||CHR(10)||SUBSTR(ADDR,1,20)
----------------------------------------------------------------------
11100010
국립의료원
서울 중구 을지로6가 18-79 국립
● 숫자함수(ROUND)
반올림에 사용하는 함수로 양수면 소수점 아래를 의미하며, 음수면 소수점 위를 의미,
생략하면 반올림해서 정수를 만든다
SQL> select ROUND('1234.567',1),
ROUND('1234.567',-1),
ROUND('1234.567')
from dual;
1234.6............1230............1235
● 숫자함수(TRUNC)
버림에 사용하는 함수
SQL> select TRUNC('1234.567',1),
TRUNC('1234.567',-1),
TRUNC('1234.567')
from dual;
1234.5............1230............1234
● 숫자함수(MOD)
주어진 숫자를 지정된 숫자로 나머지를 돌려준다
SQL> select MOD(1,3),MOD(2,3),MOD(3,3),MOD(4,3)
from dual;
1 .........2..........0........1
● 날짜처리함수
■ 날짜와 숫자의 계산
① 날짜와 날짜의 덧셈과 뺄셈
select ykiho,proc_dtime + sysdate
from tbjcc10 where rownum < 10;
ORA-00975: 날짜와 날짜의 가산은 할 수 없습니다
select ykiho,sysdate - proc_dtime
from tbjcc10 where rownum < 10; (뺄셈은 가능)
② 날짜와 숫자의 덧셈과 뺄셈
모두 가능함
③ date 형에서 시간 더하고 빼기
select
ykiho,
proc_dtime,
to_char(proc_dtime,'hh24:mi:ss') as proc_time,
to_char(proc_dtime + 14/24,'hh24:mi:ss') as time,
to_char(proc_dtime + 30/(24*60),'hh24:mi:ss') as min,
to_char(proc_dtime + 50/(24*60*60),'hh24:mi:ss') as sec
from tbjcc10 where rownum < 2
④ ADD_MONTHS,MONTHS_BETWEEN,LAST_DAY 사용 예시
select
ykiho,
to_char(proc_dtime,'yyyy/mm/dd')as 처리년월일,
to_char(add_months(proc_dtime,1),'yyyy/mm/dd') as proc_time_1개월,
to_char(add_months(proc_dtime,-13),'yyyy/mm/dd') as proc_time_13개월,
round(months_between(sysdate,proc_dtime))as month_월간격,
to_char(last_day(proc_dtime),'yyyy/mm/dd') as month_마지막일자,
SYSDATE - TO_DATE('20000101','YYYYMMDD')
from tbjcc10 where rownum < 2
● 그룹함수
■ COUNT,AVG,MIN,MAX,SUM
① select count( distinct recv_dt) from tbjfc00 where recv_dt > '20020100'
② 평균을 구할 때 null 은 포함시키면 안된다
왜냐하면 salary 가 0 일 경우와 null 일 경우 평균값이 서로 상이함
평균 = sum / count 에서 null 은 count에서 제외되기 때문이다
[CHAPTER 3] 사례 이해를 위한 기본사항
● union 과 union all 의 차이점
■ union -> 합집합을 보여주며 중복된 row 가 있을 경우 하나의 row만 포함
즉 DB 내부적으로는 SORT를 한다는 의미임
■ union all -> 합집합을 보여주며 중복된 row 가 있을 경우 모두 보여준다
즉 DB 내부적으로는 SORT를 하지 않는다는 의미임
■ minus -> 차집합을 구한다(sort함)
■ intersect -> 교집합을 구한다(sort함)
☞ Guidelines
두 문장간의 자료 유형이 일치해야 하고,
두 문장간의 컬럼 수가 일치해야 한다.
● join 의 종류를 나열하면
1. Join
하나 이상의 테이블로부터 자료를 검색하기 위하여 조인을 사용합니다.
일반적으로Primary Key(이후 PK로 사용)와 Foreign Key(이후 FK로 사용)을 사용하여
Join하는 경우가 대부분이지만 때로는 논리적인 값들의 연관으로 Join하는 경우도 있습니다.
1.1 Syntax
☞ Guidelines
1) WHERE 절에 조인 조건을 기술한다.
2) 테이블을 조인하는 SELECT 문장을 작성할 경우 명확성을 위하여 또는 데이터베이스의
Performance 향상을 위하여 열 이름 앞에 테이블 명을 붙인다.
3) 똑 같은 열 이름이 존재하는 테이블이 있을 경우는 반드시 열 이름 앞에 테이블 명을 붙인다.
4) n개의 테이블을 조인 하려면 최소한 n-1번의 조인 조건 문이 필요하다.
1.2 Join의 종류
1.3 Cartesian Product
모든 가능한 행들의 Join으로 다음과 같은 경우에 발생한다.
1) 조인 조건이 생략된 경우
2) 조인 조건이 잘못된 경우
3) 첫번째 테이블의 모든 행이 두번째 테이블의 모든 행과 두번째 테이블의 모든 행이
첫번째 테이블의 모든 행과 조인되는 경우.
4) 양쪽 ROW의 개수를 곱한 결과
♣ 참고
Cartesian Product는 만은 수의 행을 생성하는 경향이 있고 결과도 거의 유용하지 못하다.
그러므로 모든 테이블로부터 모든 행을 조합할 필요가 없을 경우 WHERE절에 조인 조건을
명확히 기술하여야 한다.
문제1) EMP 테이블과 DEPT 테이블을 Cartesian Product하여 사원번호,이름,업무,부서번호,
부서명,근무지를 출력하여라.
SQL> SELECt empno,ename,job,dept.deptno,dname,loc
2 FROM dept,emp
3 ORDER BY empno;
1.4 Equijoin
Equijoin 이란 조인 조건에서 “=”을 사용하여 값들이 정확하게 일치하는 경우에 사용하는
조인을 말합니다. 대부분 PK와 FK의 관계를 이용하여 조인 합니다.
Equijoin은 다른 말로 단순 조인 또는 내부 조인 이라고도 합니다.
1.4.1) Syntax
문제2) EMP 테이블에서 사원번호,이름,업무,EMP 테이블의 부서번호,DEPT 테이블의 부서번호,
부서명,근무지를 출력하여라
SQL> SELECT empno,ename,job,emp.deptno,dept.deptno,dname,loc
2 FROM dept,emp
3 WHERE dept.deptno = emp.deptno
4 ORDER BY dept.deptno;
1.4.2) Table에 Alias사용
1) 테이블 별칭을 사용하여 긴 테이블 명을 간단하게 사용한다.
2) 테이블 이름 대신에 Alias를 사용한다.
3) SQL 코드를 적게 사용하여 코딩 시간이 절약되고 메모리를 보다 적게 사용한다
SQL> SELECT e.empno,e.ename,e.job,e.deptno,
2 d.deptno,d.dname,d.loc
3 FROM dept d,emp e
4 WHERE d.deptno = e.deptno
5 ORDER BY d.deptno;
☞ Guidelines
1) 테이블 Alias는 30자까지 사용 가능하지만 짧을수록 더 좋다.
2) FROM절에서 Alias가 사용되면 SELECT문 전체에서 사용 가능하다.
3) 테이블의 Alias에 가급적 의미를 부여
4) 테이블은 현재 SELECT문장에서만 유용
1.5 Non-Equijoin
EMP와 SALGRADE 사이의 관련성은 EMP 테이블의 어떠한 column도 직접적으로 SALGRADE 테이블의
한 column에 상응하지 않기 때문에 Non-Equijoin이다.
두 테이블 사이의 관련성은 EMP 테이블의 SAL열이 SALGRADE 테이블의 LOSAL과 HISAL열 사이에
있다는 것이다. 조인 조건은 등등(=) 이외의 연산자(BETWEEN ~ AND ~)를 갖는다.
문제3) EMP 테이블에서 사원번호,이름,업무,급여,급여의 등급,하한값,상한값을 출력하여라.
♣ 참고
위 질의가 실행될 때 한번만 조인되는 것을 알 수 있다. 이에 대한 두가지 이유가 있다.
1) SALGRADE 테이블에서 중복되는 등급을 포함하는 행이 없다.
2) EMP 테이블에 있는 SAL의 값은 SALGRADE 테이블에서 제공하는 값 범위에 있다.
☞ Guidelines
<= 및 >= 같은 다른 연산자를 사용 가능하나 BETWEEN이 가장 단순하다.
또한 테이블에 Alias를 사용하였는데 이는 모호성 때문이 아니라 성능 때문에 사용하였다.
BETWEEN 사용시 하한값을 먼저 명시하고 상한값을 나중에 명시한다는 것을 명심하라.
1.6 Outer Join
행이 조인 조건을 만족하지 않으면, 행은 질의 결과에 나타나지 않을 것입니다.
예를 들어 EMP와DEPT테이블의 equijoin조건에서 부서OPERATIONS(40번 부서)는
해당 부서에 아무도 없기 때문에 나타나지 않습니다. 이런 경우 모든 행을 전부 출력하고자
할 경우 Outer Join을 사용한다. 즉 정상적으로 조인 조건을 만족하지 못하는 행들을 보기
위해 Outer join을 사용한다.
☞ Guidelines
1) 행인 조건을 만족하지 않을 시 해당 행은 질의 결과에 나타나지 않는다.
2) Outer join 연산자를 조인 조건에 사용시 조인 조건을 만족하지 않는 행들도 결과에
나타날 수 있다.
3) 연산자는 괄호로 묶인 플러스 기호(+)이며 조인 시킬 값이 없는 조인 측에 "(+)"를
위치 시킨다.
4) (+)연산자는 한 개 이상의 NULL 행을 생성하고 정보가 충분한 테이블의 한 개 이상의
행들이 이런 NULL 행에 조인된다.
5) Outer join 연산자는 표현식의 한 편에만 올 수 있다.
6) Outer join을 포함하는 조건은IN 연산자, OR 연산자를 사용하여 다른 하나의 조건에
연결될 수 없다.
1.6.1) Syntax
table1.column 테이블을 함께 조인(또는 관련)시키는 조건입니다.
table2.column(+) (+)는 outer join기호입니다. WHERE절 조건의 양쪽이 아니라
어느 한쪽에 둘 수 있습니다. 즉 양측 모두에는 올 수 없습니다.
일치하는 행이 없는 테이블의 열 이름 뒤에 outer join연산자
를 사용합니다.
1.6.2) Outer Join제약 사항
1) Outer Join연산자는 정보가 부재하는 쪽의 표현식 한 쪽에만 둡니다. 다른 테이블의
어떠한 열과도 직접적으로 일치하는 것이 없는 한 테이블의 행을 리턴합니다.
2) Outer Join을 포함하는 조건은 IN연산자를 사용할 수 없고, OR연산자에 의해
다른 조건과 연결될 수 없습니다.
[보충설명]
두개 이상의 table join시 한쪽의 table의 행에 대해 다른쪽 table에 일치하는 행이 없더라도
다른 쪽 table의 행을 null 로 하여 행을 return하는 것이 outer join이다.
from dept d , emp e
where d.deptno = e.deptno(+) 으로 예시를 들면
이 경우 기준이 되는 table은 dept 다. 물론 양쪽 모두에 존재하는 값은 모두 나온다.
단, dept 에는 없고 emp 에 존재하는 값이 있는 경우에는 return 되지 않는다.
outer join 연산자(+) 를 지정한 table에 대해서는 일시적으로 특별한 null 열이 작성되고
통상의 join에서는 return 되지 않는 다른 쪽 table의 행 모두와 join이 일어난다.
outer join 기호는 컬럼명 바로뒤에 붙는다.
null열이 작성되어야 하는 쪽에 기호가 붙는다.
그리고 하나의 table이 outer join 의 대상으로 삼을수 있는 table 의 수는 한 개 뿐이다
문제4) EMP 테이블과 DEPT 테이블에서 DEPT 테이블에 있는 모든 자료를 사원번호,이름,업무,
EMP 테이블의 부서번호,DEPT 테이블의 부서번호,부서명,근무지를 출력하여라
1.7 Self Join
때때로 자체적으로 테이블을 조인할 필요가 있습니다. 각 종업원의 관리자 명을 알기 위해서
자체적으로 EMP테이블을 조인하는 것이 필요합니다.
1) ENAME열을 검사하여 EMP테이블에서 Blake를 검색한다.
2) MGR열을 검사하여 Blake에 대한 관리자 번호를 검색한다.(Blake 관리자 번호:7839)
3) ENAME열을 검사하여 EMPNO가 7839인 관리자를 검색한다.
7839는 King이므로 Blake의 관리자는 King이다.
☞ Guidelines
1) Self join을 사용하여 한 테이블의 행들을 같은 테이블의 행들과 조인한다.
2) 같은 테이블에 대해 두 개의 alias를 작성(테이블 구분)함으로 FROM절에 두 개의
테이블을 사용하는 것과 같이 한다.
3) Column에 대해서도 어떤 테이블에서 왔는지 반드시 Alias명을 기술하여야 한다.
4) 테이블 하나를 두개 또는 그 이상으로 Self join할 수 있다.
문제5) EMP 테이블에서 Self join하여 관리자를 출력하여라.
[참고..문제1]
temp를 이용하여 자신보다 생일이 빠른 사람의 수를 순서로 정렬하는 QUERY 작성
select
a.emp_id,
to_char(a.birth_date,'yyyy/mm/dd') "일자",
count(b.birth_date) "자신보다 생일이 빠른 사람의 수"
from temp a, temp b
WHERE b.BIRTH_DATE(+) < a.BIRTH_DATE
GROUP BY a.emp_id,a.birth_date
order by count(b.birth_date);
19960303 1971/09/25 0
19966102 1972/07/05 1
19930402 1972/08/15 2
19960212 1972/12/15 3
19960101 1973/03/22 4
19950303 1973/06/15 5
19970101 1974/01/25 6
19970201 1975/04/15 7
19930331 1976/05/25 8
19970112 1976/11/05 9
[참고..문제2]
TEMP,TDEPT를 이용하여 상위부서가 'CA0001' 인 부서에 소속된 직원을 나열하시오
select
a.emp_id "사번",
a.emp_name "성명",
b.dept_code "부서",
b.dept_name "부서명",
c.dept_code "상위",
c.dept_name "상위명",
d.emp_id "부서장",
d.emp_name "부서장명"
from temp a, tdept b, tdept c, temp d
where b.dept_code = a.dept_code
and c.dept_code = b.parent_dept
and c.dept_code = 'CA0001'
and d.emp_id = c.boss_id;
사번 성명 부서 부서명 상위 상위명 부서장 부서장명
19930402 강감찬 CA0001 영업 CA0001 영업 19930402 강감찬
19960303 설까치 CB0001 영업기획 CA0001 영업 19930402 강감찬
19970112 연흥부 CC0001 영업1 CA0001 영업 19930402 강감찬
19960212 배뱅이 CD0001 영업2 CA0001 영업 19930402 강감찬
● subquery (서버 쿼리)
select한 결과를 조건 비교에 사용하거나 update,insert에 사용할 때 사용되는 query를 말한다
종류: single row, multi row, multi column, correlated 4가지가 있다
■ single row
select에서 결과로 나오는 행의 수가 1 row selected
[예시] select emp_id,emp_name
from temp
where salary = (select max(salary) from temp);
■ multi row
서버 query결과 나오는 행의 수 >= 1 일 때를 말한다.
이때 in , any , all , exists 등의 연산시에만 가능하다.
[예시] select emp_id,emp_name
from temp
where dept_code in ( select dept_code
from tdept
where area = '인천');
■ multi column 서버쿼리
서버 query결과 나오는 행들이 한 컬럼이 아닌 두개 이상을 가지는 경우다.
temp에서 부서별 최고 연봉금액을 읽어서 해당부서와 최고 연봉금액이 동시에
일치하는 사원의 사번,성명,연봉을 읽어라
[예시] select emp_id,emp_name,salary
from temp
where ( dept_code,salary) in (select dept_code,max(salary)
from temp
group by dept_code);
■ correlated 서버쿼리
innerQuery(sub query)에서 outerQuery(main query)의 어떤 컬럼값을 사용하는 경우를
말한다.
temp 에서 직원중 자신의 연봉이 자신과 같은 LEV(직급)에 해당하는 직원의
평균salary 보다 많은 경우에 이 사원의 사번과 성명을 읽어 오는 query를 작성
[예시] select emp_id,emp_name
from temp a
where salary > ( select avg(salary) from temp b
where b.lev = a.lev);
[세부해석]
① 사원table 에서 1 record 가 읽혀진다
② 해당사원의 직급정보가 innerQuery의 조건에 포함된다
③ innerQuery가 실행되어 해당직급의 평균연봉이 구해진다
④ 3번에서 구해진 평균연봉이 1번사원의 연봉과 비교된다
⑤ 조건을 만족하면 사원번호를 포함하고 아니면 사원번호를 제외한다
⑥ 다음사원을 읽어 동일한 일을 수행한다
만약 수행속도가 늦으면 correlated 서버쿼리는 반드시 하나의 record가 조건절에서
비교되기 때문에 innerQuery 가 한번씩 수행되어야 하기 때문이다.
즉 10,000건이면 하나의 SQL수행하기 위해 innerQuery가 10,000번 수행하기 때문.
● exists 사용
subquery 가 적어도 하나의 행을 돌려 주는지를 check하고자 할 때 사용한다.
[예시] select emp_id,emp_name,salary
from temp a
where exists ( select b.salary
from temp b
where b.lev = '과장'
and a.salary > b.salary );
[CHAPTER 4] 사례 이해를 위한 필수사항
● rownum 과 rowid
■ rownum -> where 까지를 만족시킨 자료에 row 단위로 붙는 순번을 말한다
[예시1] select rownum,recv_dt,recv_no,ykiho
from tbjfc00
where recv_no > 0 and recv_dt = '20020701' and rownum < 11;
[예시2] 요양기호중 제일 큰 요양기호를 추출하는 SQL 문장 작성하기.
select /*+ index_desc(TBJCC10 PK_TBJCC10) */ YKIHO
from TBJCC10
where YKIHO > 0 and rownum = 1;
[주의] where 에서 rownum 과 관련 =, > , >= 를 사용시 원하는 결과가 나오지 않는다
예외적으로는 rownum = 1 은 사용가능함
■ rowid -> database내의 모든 row 가 가지는 유일한 식별자를 말한다
[구성] oooooo fff bbbbbb sss
oooooo : 데이터 객체 번호
fff : 파일번호
bbbbbb : 블록번호
sss : 슬롯번호
[예시] 이중고객(DATA중복)을 rowid 를 이용해서 찾는 방법이 있다(많이 활용됨)
select rowid,cust_id
from customer a where a.rowid >
(select min(b.rowid) from customer b where a.cust_id = b.cust_id);
● decode 문 사용 예시
실제 업무를 하다 보면 DECODE 함수 하나만 사용되는 경우보다는 SUM,MAX,
MIN,COUNT 와 같이 GROUP 함수와 함께 사용되는 경우가 대부분이다
■ decode( A , B , 'T' , C , 'F' , 'X' )
A 컬럼(또는 변수) 과 B 컬럼(또는 변수) 이 같다면 'T' 를 RETURN 받고,
A 컬럼(또는 변수) 과 C 컬럼(또는 변수) 이 같다면 'F' 를 RETURN 받고,
그렇지 않으면 'X'를 RETURN 받는다.
■ decode에서 OR 비교 예시
decode( A , 0 , '지표' , 1 , '정밀' , 2, '전문','없음')
■ decode 에서 AND 비교 예시
decode( A, B , DECODE ( A , C , 'T' , 'F' ) , 'F' )
IF A = B then
IF A = C then
RETURN 'T';
else
RETURN 'F';
end if;
else
RETURN 'F';
end if; 를 의미한다.
■ decode 에서 중첩IF 예시
decode( A, B , DECODE ( C , D , 'T' , 'F' ) , 'F' )
IF A = B then
IF C = D then
RETURN 'T';
else
RETURN 'F';
end if;
else
RETURN 'F';
end if; 를 의미한다.
■ group 함수 내의 decode 사용 예시
temp 의 자료를 이용하여 한 행에 사번,성명을 3명씩 보여주는 SQL 작성
select ceil(rownum / 3) c0,
max ( decode ( mod ( rownum , 3 ) , 1 , emp_id , null ) ) c1,
max ( decode ( mod ( rownum , 3 ) , 1 , emp_name , null ) ) c2,
max ( decode ( mod ( rownum , 3 ) , 2 , emp_id , null ) ) c3,
max ( decode ( mod ( rownum , 3 ) , 2 , emp_name , null ) ) c4,
max ( decode ( mod ( rownum , 3 ) , 0 , emp_id , null ) ) c5,
max ( decode ( mod ( rownum , 3 ) , 0 , emp_name , null ) ) c6
from temp
where emp_id > 0
group by ceil(rownum / 3 );
[결과]
C0 C1 C2 C3 C4 C5 C6
1 19930331 정도령 19930402 강감찬 19950303 이순신
2 19960101 홍길동 19960212 배뱅이 19960303 설까치
3 19966102 지문덕 19970101 김길동 19970112 연흥부
4 19970201 박문수
● not in 사용에 있어 함정
■ where hobby in ( null, '등산')
hobby = null or hobby = '등산' 으로 해석되는데 hobby = null 은 모두 false return 되고
hobby = '등산' 값만 return 된다. null 은 항상 is null or is not null 로 비교해야 한다.
■ where hobby not in ( null, '등산')
hobby = null and hobby = '등산' 으로 해석되는데 hobby = null 가 모두 false return 되므로
not in 에 null 을 포함하면 한 건의 record 도 검색할 수 없다.
● inLine View
■ inLine View는 view를 이용하는데, create를 시키지 않고 바로 SQL 안에 기술하여 사용
하는 방법을 말한다.
[예시1] 사원table에서 사원을 골라내는데 해당사원의 연봉이 동일한 직급이 가진 사원
의 평균 연봉보다 많은 사원을 찾는 QUERY
select a.emp_id, a.emp_name, a.salary, b.avg_sal
from temp A ,
(select lev, avg(salary) avg_sal
from temp
group by lev ) B
where a.lev = b.lev
and a.salary > b.avg_sal;
EMP_ID EMP_NAME SALARY AVG_SAL
19960101 홍길동 72000000 51500000
19950303 이순신 56000000 50500000
19930331 정도령 70000000 67000000
[예시2] temp 자료에서 rownum 을 채취해 값이 5 와 10 사이에 있는 행의 rownum,
사번,성명을 조회 SQL 작성 ( inLine View 사용할것 )
select no, emp_id,emp_name
from ( select rownum no, emp_id, emp_name
from temp
where emp_id > 0 )
where no between 5 and 10;
● hints 란
optimizer는 수행하고자 하는 DML(select,delete,update,insert)을 가장 효율적으로
처리할 수 있는 최적의 경로를 찾아 주는 것이 주임무다
이러기 위해 필요한 내부요인을 보면
① 어떤table를 먼저 읽을 것인가
② table읽을시 index사용할 것인가
③ index사용시 어떤 index를 사용할 것인가
④ join 시 어떤 방법 join할 것인가(동등join,outer join, self join )
위와 같은 실행계획에 의거 수핼경로는 PLAN을 통해서 알 수 있다
따라서 hints는 이렇게 optimizer가 실행계획을 작성하는 단계에 인위적으로
개발자가 관여 하겠다는 의도를 가질때 사용된다
■ init parameter 중 optimizer_mode 지정하는 값
all_rows
first_rows(현 운영시스템 적용) vi /SYSTEM/ora81/admin/HIRAOPS2/pfile/initHIRAOPS2.ora
choose
rule
■ Access Methods 로써의 Hints
full
rowid
hash
hash_aj , hash_sj
index, index_asc, index_desc
index_ffs
merge_aj , merge_sj
■ Join 순서를 결정하는 Hints
ordered
■ Join operation을 결정하는 Hints
use_nl
use_merge
[ 세부설명은 tuning 과정에서 할 예정임]
[CHAPTER 5] oracle 8.1.6 이상에서 제공되는 분석용 함수
● RANKING
RANKING 함수들은 주어진 기준 값에 근거하여 DATASET 내의 다른 RECORD 와
비교되는 RECORD 의 순서를 계산해 낸다.
■ over -> 쿼리 result set 을 이용해 동작하는 함수라는 구분이다
■ partition by -> result set 을 value expression 에 지정된 값에 근거하여 분할하는 역할함
■ order by -> 각 partition 내에서 data가 어떤 값을 기준으로 정렬될 것인가를 지정
■ nulls first | nulls last -> null 이 포함된 row 가 순서상 제일 앞에 위치할 것인지 제일
뒤에 위치할 것인지를 지정
● 실습용 table 생성
CREATE TABLE SALE_HIST(
SALE_DATE DATE NOT NULL, // 매출일자
SALE_SITE VARCHAR2(10) NOT NULL, // 매출사업장
SALE_ITEM VARCHAR2(10) NOT NULL, // 매출품목
SALE_AMT NUMBER, // 매출금액
CONSTRAINT SALEHIST_PK PRIMARY KEY (SALE_DATE,SALE_SITE,SALE_ITEM)
);
INSERT INTO SALE_HIST VALUES(to_date('20010501','yyyymmdd'),'01','PENCIL',5000);
INSERT INTO SALE_HIST VALUES(to_date('20010501','yyyymmdd'),'01','NOTEBOOK',9000);
INSERT INTO SALE_HIST VALUES(to_date('20010501','yyyymmdd'),'01','ERASER',4500);
INSERT INTO SALE_HIST VALUES(to_date('20010501','yyyymmdd'),'02','PENCIL',2500);
INSERT INTO SALE_HIST VALUES(to_date('20010501','yyyymmdd'),'02','NOTEBOOK',7000);
INSERT INTO SALE_HIST VALUES(to_date('20010501','yyyymmdd'),'02','ERASER',3000);
INSERT INTO SALE_HIST VALUES(to_date('20010501','yyyymmdd'),'03','PENCIL',2500);
INSERT INTO SALE_HIST VALUES(to_date('20010501','yyyymmdd'),'03','NOTEBOOK',7000);
INSERT INTO SALE_HIST VALUES(to_date('20010501','yyyymmdd'),'03','ERASER',6000);
INSERT INTO SALE_HIST VALUES(to_date('20010502','yyyymmdd'),'01','PENCIL',6000);
INSERT INTO SALE_HIST VALUES(to_date('20010502','yyyymmdd'),'01','NOTEBOOK',5000);
INSERT INTO SALE_HIST VALUES(to_date('20010502','yyyymmdd'),'01','ERASER',5500);
INSERT INTO SALE_HIST VALUES(to_date('20010502','yyyymmdd'),'02','PENCIL',3500);
INSERT INTO SALE_HIST VALUES(to_date('20010502','yyyymmdd'),'02','NOTEBOOK',7000);
INSERT INTO SALE_HIST VALUES(to_date('20010502','yyyymmdd'),'02','ERASER',4000);
INSERT INTO SALE_HIST VALUES(to_date('20010502','yyyymmdd'),'03','PENCIL',5500);
INSERT INTO SALE_HIST VALUES(to_date('20010502','yyyymmdd'),'03','NOTEBOOK',4500);
INSERT INTO SALE_HIST VALUES(to_date('20010502','yyyymmdd'),'03','ERASER',5000);
INSERT INTO SALE_HIST VALUES(to_date('20010503','yyyymmdd'),'01','PENCIL',7000);
INSERT INTO SALE_HIST VALUES(to_date('20010503','yyyymmdd'),'01','NOTEBOOK',6000);
INSERT INTO SALE_HIST VALUES(to_date('20010503','yyyymmdd'),'01','ERASER',6500);
INSERT INTO SALE_HIST VALUES(to_date('20010503','yyyymmdd'),'02','PENCIL',3500);
INSERT INTO SALE_HIST VALUES(to_date('20010503','yyyymmdd'),'02','NOTEBOOK',5000);
INSERT INTO SALE_HIST VALUES(to_date('20010503','yyyymmdd'),'02','ERASER',5000);
INSERT INTO SALE_HIST VALUES(to_date('20010503','yyyymmdd'),'03','PENCIL',6500);
INSERT INTO SALE_HIST VALUES(to_date('20010503','yyyymmdd'),'03','NOTEBOOK',3500);
INSERT INTO SALE_HIST VALUES(to_date('20010503','yyyymmdd'),'03','ERASER',7000);
INSERT INTO SALE_HIST VALUES(to_date('20010504','yyyymmdd'),'01','PENCIL',5500);
INSERT INTO SALE_HIST VALUES(to_date('20010504','yyyymmdd'),'01','NOTEBOOK',6500);
INSERT INTO SALE_HIST VALUES(to_date('20010504','yyyymmdd'),'01','ERASER',3500);
INSERT INTO SALE_HIST VALUES(to_date('20010504','yyyymmdd'),'02','PENCIL',7500);
INSERT INTO SALE_HIST VALUES(to_date('20010504','yyyymmdd'),'02','NOTEBOOK',5000);
INSERT INTO SALE_HIST VALUES(to_date('20010504','yyyymmdd'),'02','ERASER',4000);
INSERT INTO SALE_HIST VALUES(to_date('20010504','yyyymmdd'),'03','PENCIL',3500);
INSERT INTO SALE_HIST VALUES(to_date('20010504','yyyymmdd'),'03','NOTEBOOK',5500);
INSERT INTO SALE_HIST VALUES(to_date('20010504','yyyymmdd'),'03','ERASER',3000);
COMMIT;
[실습1] temp 에서 salary 값으로 순위를 부여하는 sql 문 작성
select emp_id,emp_name,salary,
dense_rank() over (order by salary desc ) as c1,
rank() over (order by salary desc ) as c2,
rank() over (order by salary ) as c3
from temp
[결과1]
EMP_ID EMP_NAME SALARY C1 C2 C3
19960303 설까치 35000000 9 10 1
19960212 배뱅이 39000000 8 9 2
19966102 지문덕 45000000 7 7 3
19970112 연흥부 45000000 7 7 3
19970201 박문수 50000000 6 6 5
19950303 이순신 56000000 5 5 6
19930402 강감찬 64000000 4 4 7
19930331 정도령 70000000 3 3 8
19960101 홍길동 72000000 2 2 9
19970101 김길동 100000000 1 1 10
[설명1]
RANK 와 DENSE_RANK 의 차이점은 순위를 부여하는 간격에 있다
OVER : 해당함수가 QUERY의 RESULTSET을 이용하여 동작하는 함수라는 표시
ORDER BY : RESULTSET에 순위를 부여할 때의 정렬기준을 표시해 주는 부분
[실습2] temp 에서 직원들을 부서별로 급여기준 순위를 부여하는 sql 문 작성
select dept_code,emp_id,emp_name,sum(salary),
rank() over (partition by dept_code order by sum(salary) desc ) as c1
from temp
group by dept_code,emp_id,emp_name
[결과2]
DEPT_CODE EMP_ID EMP_NAME SUM(SALARY) C1
AA0001 19970101 김길동 100000000 1
AA0001 20000101 이태백 30000000 2
AB0001 19960101 홍길동 72000000 1
AB0001 20000102 김설악 30000000 2
AC0001 19970201 박문수 50000000 1
AC0001 20000203 최오대 30000000 2
BA0001 19930331 정도령 70000000 1
BA0001 20000334 박지리 30000000 2
BB0001 19950303 이순신 56000000 1
BB0001 20000305 정북악 30000000 2
BC0001 19966102 지문덕 45000000 1
BC0001 20006106 유도봉 30000000 2
CA0001 19930402 강감찬 64000000 1
CA0001 20000407 윤주왕 30000000 2
CB0001 19960303 설까치 35000000 1
CB0001 20000308 강월악 30000000 2
CC0001 19970112 연흥부 45000000 1
CC0001 20000119 장금강 30000000 2
CD0001 19960212 배뱅이 39000000 1
CD0001 20000210 나한라 30000000 2
[설명2]
partition by dept_code 라는 문장에 의해 부서별 급여 RANKING이 구해진다
PARTITION BY : 분석용 함수가 작용하는 단위를 표시해 주며 생략될 경우 전체
RESULTSET이 하나의 작용단위가 된다.또한 뒤에 기술된 컬럼에 의하여
분석용 함수가 작용할 단위가 나뉘어지며, 그 안에서 ORDER BY 에 의해
정렬이 이뤄지고 분석용 함수가 작용한다.
[실습3] temp 의 자료를 이용하여 사원별 RANKING을 구하고 부서별로 구한 소계에
까지 RANKING 을 부여해보자
여기서 ROLLUP 이나 CUBE 의 결과로 나오는 SUBTOTAL(소계)에까지 RANKING
이 적용되는지 확인한다
select dept_code,emp_id,sum(salary),
rank() over (partition by grouping (dept_code),
grouping (emp_id)
order by sum(salary) desc ) as c1
from temp
group by rollup (dept_code,emp_id);
[결과3]
DEPT_CODE EMP_ID SUM(SALARY) C1
AA0001 19970101 100000000 1
AB0001 19960101 72000000 2
BA0001 19930331 70000000 3
CA0001 19930402 64000000 4
BB0001 19950303 56000000 5
AC0001 19970201 50000000 6
BC0001 19966102 45000000 7
CC0001 19970112 45000000 7
CD0001 19960212 39000000 9
CB0001 19960303 35000000 10
AA0001 20000101 30000000 11
AC0001 20000203 30000000 11
AA0001 130000000 1
AB0001 102000000 2
BA0001 100000000 3
CA0001 94000000 4
BB0001 86000000 5
AC0001 80000000 6
BC0001 75000000 7
CC0001 75000000 7
CD0001 69000000 9
CB0001 65000000 10
876000000 1
※ rollup 과 cube 의미
[rollup 예제]
select substr(ykiho,1,2) ykiho12,substr(ykiho,3,1),sum(tot_dmd_cnt)
from tbjfc02
where recv_dt between '20020100' and '20020131'
group by rollup (substr(ykiho,1,2),substr(ykiho,3,1))
/
SUBSTR(YKIHO,1,2) SUBSTR(YKIHO,3,1) SUM(TOT_DMD_CNT)
-----------------
21 2 138155
21 3 1562874
21 5 216731
21 6 18
21 7 37682
21 8 1801765
21 9 186553
21 3943778
39 2 1488
39 3 242614
39 5 37928
39 7 18463
39 8 271618
39 9 39530
39 611641
4555419
-----------------
[cube 예제]
select substr(ykiho,1,2),substr(ykiho,3,1),sum(tot_dmd_cnt)
from tbjfc02
where recv_dt between '20020100' and '20020131'
group by cube (substr(ykiho,1,2),substr(ykiho,3,1))
/
SUBSTR(YKIHO,1,2) SUBSTR(YKIHO,3,1) SUM(TOT_DMD_CNT)
------------------
21 2 138155
21 3 1562874
21 5 216731
21 6 18
21 7 37682
21 8 1801765
21 9 186553
21 3943778
39 2 1488
39 3 242614
39 5 37928
39 7 18463
39 8 271618
39 9 39530
39 611641
2 139643
3 1805488
5 254659
6 18
7 56145
8 2073383
9 226083
4555419
[설명3]
emp_id 만이 null 로 나온 row는 부서별 subtotal 이며 dept_code 까지 null 로 나온것은
grandtotal(합계)이다.
rollup과 cube는 소계및 합계용 함수이고 rollup을 이용해 구해진 소계에도 따로 순위가
부여되는 모습을 보여 주고 있다. 또한 partition by 에 의해 함수 적용단위가 나뉘어지는
경우를 설명하는 좋은 예다
[실습4]
sale_hist table의 자료를 이용하여 일자별 매출 순위와 순위별 사업장,품목을 display 하시오
select sale_date,sale_site,sale_item,sale_amt,
rank() over ( partition by sale_date order by sale_amt desc ) as c1
from sale_hist;
[결과4]
SALE_DATE SALE_SITE SALE_ITEM SALE_AMT C1
05/01/2001 00:00:00 01 NOTEBOOK 9000 1
05/01/2001 00:00:00 02 NOTEBOOK 7000 2
05/01/2001 00:00:00 03 NOTEBOOK 7000 2
05/01/2001 00:00:00 03 ERASER 6000 4
05/02/2001 00:00:00 02 NOTEBOOK 7000 1
05/02/2001 00:00:00 01 PENCIL 6000 2
05/02/2001 00:00:00 01 ERASER 5500 3
05/02/2001 00:00:00 03 PENCIL 5500 3
05/02/2001 00:00:00 01 NOTEBOOK 5000 5
● CUME_DIST
관련 DATASET안에서 해당row의 위치를 0 에서 1 까지의 값으로 표시해 준다.
rank와 비슷하지만 cume_dist는 최대값 1을 기준으로 해당 row의 위치가 0 에서
1 까지의 값으로 표시된다.
● NTILE(n)
자신이 속한 partition 의 row 들을 지정한 숫자만큼으로 분류하고자 할 때 각
row의 위치를 표시한다
예를 들면 100개의 row 에서 ntile(5)하면 1-20(1),21-40(2)..81-100(5)로 분류된다.
● ROW_NUMBER()
partition내의 row 들에 순서대로 unique 한 일련번호를 부여한다.
[실습1]
temp table 내의 각 row를 salary 기준으로 CUME_DIST,NTILE,ROW_NUMBER의 값을 구하라
select emp_id,emp_name,salary,
rank() over (order by salary ) as c1,
cume_dist() over ( order by salary ) as c2,
ntile(5) over ( order by salary ) as c3,
row_number() over ( order by salary ) as c4
from temp
[결과1]
EMP_ID EMP_NAME SALARY C1 C2 C3 C4
20000101 이태백 30000000 1 .5 1 1
20000102 김설악 30000000 1 .5 1 2
20000334 박지리 30000000 1 .5 1 3
20000203 최오대 30000000 1 .5 1 4
20006106 유도봉 30000000 1 .5 2 5
20000308 강월악 30000000 1 .5 2 6
20000210 나한라 30000000 1 .5 2 7
20000119 장금강 30000000 1 .5 2 8
20000407 윤주왕 30000000 1 .5 3 9
20000305 정북악 30000000 1 .5 3 10
19960303 설까치 35000000 11 .55 3 11
19960212 배뱅이 39000000 12 .6 3 12
19966102 지문덕 45000000 13 .7 4 13
19970112 연흥부 45000000 13 .7 4 14
19970201 박문수 50000000 15 .75 4 15
19950303 이순신 56000000 16 .8 4 16
19930402 강감찬 64000000 17 .85 5 17
19930331 정도령 70000000 18 .9 5 18
19960101 홍길동 72000000 19 .95 5 19
19970101 김길동 100000000 20 1 5 20
● WINDOWING 함수
기존에 있던 모든 그룹 FUNCTION들에 대해서 Moving & Cumulative processing 을
지원하는 함수를 말한다
■ over -> 쿼리 result set 을 이용해 동작하는 함수라는 구분이다
■ partition by -> result set 을 value expression2 에 지정된 값에 근거하여 분할하는 역할함
■ order by -> 각 partition 내에서 data가 어떤 값을 기준으로 정렬될 것인가를 지정
■ nulls first | nulls last -> null 이 포함된 row 가 순서상 제일 앞에 위치할 것인지 제일
뒤에 위치할 것인지를 지정
■ rows | range -> 자료의 물리적 순서를 이용(rows)할 것인지 논리적(range)를 이용할 것인지
를 결정
■ between.and -> 자료의 범위를 결정한다
■ unbounded preceding -> 지정된 값 이후의 모든 row를 포함한다
■ current row -> 현재 row 를 시작값 또는 마지막 값으로 이용할 때 사용한다.
[실습1] sale_hist 의 자료를 이용하여 '01' 사업장의 품목별 당일 판매액과 당일까지의
누적 판매액을 구하는 SQL 작성( unbounded preceding 사용 )
select to_char(sale_date,'yyyymmdd') sale_date, sale_site, sale_item,sale_amt,
sum ( sale_amt) OVER
( PARTITION BY sale_item
ORDER BY sale_item
ROWS UNBOUNDED PRECEDING ) as C1
from sale_hist
where sale_site = '01';
[결과1]
SALE_DATE SALE_SITE SALE_ITEM SALE_AMT C1
20010501 01 ERASER 4500 4500
20010502 01 ERASER 5500 10000
20010503 01 ERASER 6500 16500
20010504 01 ERASER 3500 20000
20010501 01 NOTEBOOK 9000 9000
20010502 01 NOTEBOOK 5000 14000
20010503 01 NOTEBOOK 6000 20000
20010504 01 NOTEBOOK 6500 26500
20010501 01 PENCIL 5000 5000
20010502 01 PENCIL 6000 11000
20010504 01 PENCIL 5500 16500
20010503 01 PENCIL 7000 23500
[설명1] unbounded preceding 와 물리적인 이전 rows 참조(rows이용) 를 설명함.
동일 sale_item 에 대하여 정렬하고 동일 sale_item 내에서 자신보다 먼저 나타나는
record들을 이용해 누적sum을 보여주고, rows를 사용하여 물리적인 자료의 순서에 의해
이전 record들을 참조하고 있는 경우를 보여준다
[실습2] 일자별 사업장별 매출액과 사업장별 매출액의 3일 이동평균을 구해보자(range사용)
select to_char(sale_date,'yyyymmdd') sale_date, sale_site,
sum(sale_amt), avg(sum(sale_amt)) OVER
(PARTITION BY sale_site
ORDER BY sale_date
RANGE INTERVAL '2' DAY PRECEDING ) as S_SUM
from sale_hist
group by sale_date, sale_site;
[결과2]
SITE_DATE SALE_SITE SUM(SALE_AMT) S_SUM
20010501 01 18500 18500
20010502 01 16500 17500
20010503 01 19500 18166.66
20010504 01 15500 17166.66
20010501 02 12500 12500
20010502 02 14500 13500
20010503 02 13500 13500
20010504 02 16500 14833.33
20010501 03 15500 15500
20010502 03 15000 15250
20010503 03 17000 15833.33
20010504 03 12000 14666.66
[설명2] 논리적인 이전 record 참조의 예이다.
일자별,사업장별로 매출액을 sum한 결과를 이용해 분석함수를 적용시켜 avg 를 구한다
range의 지정범위는 자신의 일자 이전의 2일치 자료와 해당일자의 자료이다.
예를 들면 S_SUM : 17500 = (18500 + 16500) / 2
18166.66 = (18500 + 16500 + 19500 ) / 3
17166.66 = (16500 + 19500 + 15500 ) / 3
즉 20010504 면 이전 2일치를 포함된 평균값을 구한다는 의미임
[실습3] TEMP 자료를 이용하여 자신의 급여와 바로 이전 사번 3명의 급여를 이용하여
합계,COUNT,평균을 구해보자 ( rows N preceding 사용 )
select emp_id, salary,
sum( salary ) OVER
(ORDER BY emp_id ROWS 3 PRECEDING ) as sum_sal,
count(salary) OVER
(ORDER BY emp_id ROWS 3 PRECEDING ) as cnt,
avg(salary) OVER
(ORDER BY emp_id ROWS 3 PRECEDING ) as avg
from temp;
[결과3]
EMP_ID SALARY SUM_SAL CNT AVG
19930331 70000000 70000000 1 70000000
19930402 64000000 134000000 2 67000000
19950303 56000000 190000000 3 63333333.33
19960101 72000000 262000000 4 65500000
19960212 39000000 231000000 4 57750000
19960303 35000000 202000000 4 50500000
19966102 45000000 191000000 4 47750000
19970101 100000000 219000000 4 54750000
19970112 45000000 225000000 4 56250000
19970201 50000000 240000000 4 60000000
20000101 30000000 225000000 4 56250000
20000102 30000000 155000000 4 38750000
20000119 30000000 140000000 4 35000000
20000203 30000000 120000000 4 30000000
20000210 30000000 120000000 4 30000000
20000305 30000000 120000000 4 30000000
20000308 30000000 120000000 4 30000000
20000334 30000000 120000000 4 30000000
20000407 30000000 120000000 4 30000000
20006106 30000000 120000000 4 30000000
[설명3]
위의 예시에서 평균 57750000 = ( 640 + 560 + 720 + 390 ) / 4
5775 = 2310 / 4
[실습4] 각 row의 판매액,동일일자/동일품목의 최대판매액,최대판매액사업장,
해당사업장 최소판매액,최소판매액사업장을 구해보자( first_value, last_value 사용 )
select to_char(sale_date,'yyyymmdd') sale_date,sale_item,sale_site,sale_amt,
first_value(sale_amt) OVER
(PARTITION BY sale_date, sale_item
ORDER BY sale_amt
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
) as first_amt,
first_value(sale_site) OVER
(PARTITION BY sale_date, sale_item
ORDER BY sale_amt
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
) as first_site,
last_value(sale_amt) OVER
(PARTITION BY sale_date, sale_item
ORDER BY sale_amt
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
) as last_amt,
last_value(sale_site) OVER
(PARTITION BY sale_date, sale_item
ORDER BY sale_amt
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
) as last_site
from sale_hist;
[결과4]
SALE_DATE SALE_ITEM SALE_SITE SALE_AMT FIRST_AMT FIRST_SITE LAST_AMT LAST_SITE
20010501 ERASER 02 3000 3000 02 6000 03
20010501 ERASER 01 4500 3000 02 6000 03
20010501 ERASER 03 6000 3000 02 6000 03
20010501 NOTEBOOK 02 7000 7000 02 9000 01
20010501 NOTEBOOK 03 7000 7000 02 9000 01
20010501 NOTEBOOK 01 9000 7000 02 9000 01
20010501 PENCIL 02 2500 2500 02 5000 01
20010501 PENCIL 03 2500 2500 02 5000 01
20010501 PENCIL 01 5000 2500 02 5000 01
20010502 ERASER 02 4000 4000 02 5500 01
20010502 ERASER 03 5000 4000 02 5500 01
20010502 ERASER 01 5500 4000 02 5500 01
20010502 NOTEBOOK 03 4500 4500 03 7000 02
20010502 NOTEBOOK 01 5000 4500 03 7000 02
20010502 NOTEBOOK 02 7000 4500 03 7000 02
20010502 PENCIL 02 3500 3500 02 6000 01
20010502 PENCIL 03 5500 3500 02 6000 01
20010502 PENCIL 01 6000 3500 02 6000 01
[설명4]
first_value, last_value 의 의미를 이해하기 위한 예제이며,
first_value -> partition by 에 의해 분류된 범위내에서 order by 절에 정렬을 한 후 rows 또는
range에 의해 범위가 지정되면 그 중 제일 앞에 위치하는 row의 값들을 읽어온다
last_value -> 상동.이하생략...그 중 제일 뒤에 위치하는 row의 값들을 읽어온다.
즉 first_value와 last_value는 정렬된 row의 순서에 의해 값이 결정된다.
확인 방법은 order by 뒤에 desc 를 붙여서 결과를 보면된다.
[실습5] 일자별 매출액과 함께 각 일자의 매출액이 전체일자 매출액에서 차지하는 비율을 구한다
( ratio_to_report 사용 )
select to_char(sale_date,'yyyymmdd') sale_date,
sum(sale_amt) as sale_amt,
sum(sum(sale_amt)) OVER ( ) as sum_tot,
round( ratio_to_report ( sum(sale_amt)) OVER ( ) ,2) as c1
from sale_hist
group by sale_date;
[결과5]
SALE_DATE SALE_AMT SUM_TOT C1
20010501 46500 186500 .25
20010502 46000 186500 .25
20010503 50000 186500 .27
20010504 44000 186500 .24
[설명5]
ratio_to_report 라는 함수가 전체대비 해당 row의 값이 차지하는 비율을 구해준다
위에서 처럼 소숫점 2자리까지만 나타날때 round를 사용한다
[실습6] 사업장별 품목의 매출액과 함께 동일사업장 동일품목의 전일 매출액과 다음날 매출액을
구한다 ( LAG 와 LEAD 사용 )
select to_char(sale_date,'yyyymmdd') sale_date, sale_site,sale_item, sale_amt,
lag(sale_amt,1) OVER
(PARTITION BY sale_site, sale_item
ORDER BY sale_date, sale_site,sale_item) as LAG_AMT,
lead(sale_amt,1) OVER
(PARTITION BY sale_site, sale_item
ORDER BY sale_date, sale_site,sale_item) as LEAD_AMT
from sale_hist;
[결과6]
SALE_DATE SALE_SITE SALE_ITEM SALE_AMT LAG_AMT LEAD_AMT
20010501 01 ERASER 4500 5500
20010502 01 ERASER 5500 4500 6500
20010503 01 ERASER 6500 5500 3500
20010504 01 ERASER 3500 6500
20010501 01 NOTEBOOK 9000 5000
20010502 01 NOTEBOOK 5000 9000 6000
20010503 01 NOTEBOOK 6000 5000 6500
20010504 01 NOTEBOOK 6500 6000
20010501 01 PENCIL 5000 6000
20010502 01 PENCIL 6000 5000 7000
20010503 01 PENCIL 7000 6000 5500
20010504 01 PENCIL 5500 7000
[설명6]
lag는 바로 이전 값을 참조하고, lead 는 바로 이후 값을 참조한다
lag와 lead 함수 안에 지정되는 숫자가 각각 앞뒤로 몇번째 row를 참조할 것인지를 결정
[실습7] sale_hist 에서 '01'사업장, 'PENCIL' 품목의 일자별 누적 판매금액을 구하라
select to_char(sale_date,'yyyymmdd') sale_date, sale_amt,
sum(sale_amt) OVER
(ORDER BY sale_date
ROWS UNBOUNDED PRECEDING ) as c1
from sale_hist
where sale_site = '01'
and sale_item = 'PENCIL';
[결과6]
SALE_DATE SALE_AMT C1
20010501 5000 5000
20010502 6000 11000
20010503 7000 18000
20010504 5500 23500
[실습8] sale_hist 에서 품목별/일자별로 과거 판매액을 모두 이용하여 이동평균값을 구하라
select sale_item,to_char(sale_date,'yyyymmdd') sale_date, sum(sale_amt),
avg(sum(sale_amt)) OVER
( PARTITION BY sale_item
ORDER BY sale_date
ROWS UNBOUNDED PRECEDING ) as s_sum
from sale_hist
group by sale_item, sale_date;
[결과8]
SALE_ITEM SALE_DATE SUM(SALE_AMT) S_SUM
ERASER 20010501 13500 13500
ERASER 20010502 14500 14000
ERASER 20010503 18500 15500
ERASER 20010504 10500 14250
NOTEBOOK 20010501 23000 23000
NOTEBOOK 20010502 16500 19750
NOTEBOOK 20010503 14500 18000
NOTEBOOK 20010504 17000 17750
PENCIL 20010501 10000 10000
PENCIL 20010502 15000 12500
PENCIL 20010503 17000 14000
PENCIL 20010504 16500 14625
[실습9] sale_hist 에서 '01'사업장, 'PENCIL' 품목에 대해 일자별 매출액과 전일매출액,
당일과 전일매출액과의 차이를 구하시오
select to_char(sale_date,'yyyymmdd') sale_date, sale_amt,
lag(sale_amt,1) OVER
(ORDER BY sale_date ) as yest_amt,
sale_amt -
lag(sale_amt,1) OVER
(ORDER BY sale_date ) as diff_amt
from sale_hist
where sale_site = '01'
and sale_item = 'PENCIL';
[결과9]
SALE_DATE SALE_AMT YEST_AMT DIFF_AMT
20010501 5000
20010502 6000 5000 1000
20010503 7000 6000 1000
20010504 5500 7000 -1500
● CASE 함수
기존 DECODE 문을 CASE를 통해 보다 간편하게 처리될 수 있다
[실습1] temp 자료를 salary로 분류하여 30000000 이하면 'D'
30000000 초과 50000000 이하는 'C'
50000000 초과 70000000 이하는 'B'
70000000 초과는 'A' 라는 등급을 분류하여
인원수를 알고 싶다
select CASE WHEN salary <= 30000000 THEN 'D'
WHEN salary BETWEEN 30000001 AND 50000000 THEN 'C'
WHEN salary BETWEEN 50000001 AND 70000000 THEN 'B'
WHEN salary > 70000000 THEN 'A' END,
COUNT(*)
from temp
group by
CASE WHEN salary <= 30000000 THEN 'D'
WHEN salary BETWEEN 30000001 AND 50000000 THEN 'C'
WHEN salary BETWEEN 50000001 AND 70000000 THEN 'B'
WHEN salary > 70000000 THEN 'A' END;
[결과1]
CASEWHENSALARY<=30000000THEN'D COUNT(*)
A 2
B 3
C 5
D 10
[실습2] temp 자료를 salary로 분류하여 30000000 이하면 'D'
30000000 초과 50000000 이하는 'C'
50000000 초과 70000000 이하는 'B'
70000000 초과는 'A' 라는 등급을 분류하여
인원수를 알고 싶다 (단 행단위로 나오는 값을 컬럼단위로 표현한다)
select count ( CASE WHEN salary > 70000000 THEN 'A' END) as A,
count ( CASE WHEN salary BETWEEN 50000001 AND 70000000 THEN 'B' END) as B,
count ( CASE WHEN salary BETWEEN 30000001 AND 50000000 THEN 'C' END) as C,
count ( CASE WHEN salary <= 30000000 THEN 'D' END) as D
from temp;
[결과2]
A B C D
2 3 5 10
[실습3]sale_hist 에서 일자별 품목별로
'01','02' 사업장 판매 금액합,
'02','03' 사업장 판매 금액합을 구하시오
select to_char(sale_date,'yyyymmdd') sale_date, sale_item,
sum( CASE WHEN sale_site BETWEEN '01' AND '02' THEN sale_amt END) as S01,
sum( CASE WHEN sale_site BETWEEN '02' AND '03' THEN sale_amt END) as S02
from sale_hist
group by sale_date, sale_item;
[결과3]
SALE_DATE SALE_ITEM S01 S02
20010501 ERASER 7500 9000
20010501 NOTEBOOK 16000 14000
20010501 PENCIL 7500 5000
20010502 ERASER 9500 9000
20010502 NOTEBOOK 12000 11500
20010502 PENCIL 9500 9000
20010503 ERASER 11500 12000
20010503 NOTEBOOK 11000 8500
20010503 PENCIL 10500 10000
20010504 ERASER 7500 7000
20010504 NOTEBOOK 11500 10500
20010504 PENCIL 13000 11000
● 함수들 모음
■ 통계 함수
-> var_pop : 모집단의 분산을 구해준다
-> avr_samp : 표본집단의 분산을 구해준다
-> stddev_pop : 모집단의 표준편차를 구해준다
-> stddev_samp : 표본집단의 표준편차를 구해준다
-> corvar_pop : 모집단의 공 분산을 구해준다
-> corvar_samp : 표본집단의 공 분산을 구해준다
-> corr : 상관계수을 구해준다
■ 회귀 분석용 함수
-> regr_count : 회귀선상에 찍히는 값들의 숫자를 구해준다
-> regr_avgy, regr_avgx : 회귀선의 독립변수와 종속변수의 평균을 구해준다
-> regr_slope, regr_intercept : 회귀선의 기울기를 구해준다
-> regr_r2 : 회귀선의 적합도를 구해준다
-> regr_sxx, regr_syy, regr_sxy : 회귀분석용 함수
http://163.163.31.21:9001/common/web/mainframe.jsp
