16 분석함수 분석 함수의 개념 분석 함수의 종류 계층적 질의문.

Presentation on theme: "16 분석함수 분석 함수의 개념 분석 함수의 종류 계층적 질의문."— Presentation transcript:

1 16 분석함수 분석 함수의 개념 분석 함수의 종류 계층적 질의문

2 분석 함수의 개념 분석함수란? 분석함수의 종류 대량의 데이터를 다차원적으로 분석하기 위한 함수
여러 SQL 명령문을 사용하여야 하는 작업을 함수를 사용하여 하나의 SQL 명령문으로 간결하게 표현 가능 예를 들면, 기업에서 장기간 축적한 상품 판매 데이터에 대한 월별 판매 효과 분석 분석함수의 종류 RANK, DENS_RANK, ROW_NUMBER, FIRST_VALUE, LAST_VALUE, RAG 16장. 분석 함수

3 조인, Where, Group by, Having 절
분석 함수의 실행 단계 분석 함수의 실행 단계 1) 일반 질의 처리(general query processing) 기존 질의문을 처리하는 단계로, SQL명령문을 실행하여 출력 결과를 검색하는 단계 2) 분석 함수 적용(analytic function applying) 일반 질의 처리 결과에 대해 다양한 분석함수를 적용하는 단계 3) 정렬(sorting) ORDER BY 절에 의해 결과 집합 정렬 조인, Where, Group by, Having 절 처리 일반 질의 처리 분할(Partition) 생성 분할별로 분석함수 적용 분석 함수 적용 최종적으로 ORDER BY 절에 의해 결과 집합 정렬 정렬 16장. 분석 함수

4 RANK 분석 함수 RANK 분석 함수 특정 칼럼 값에 의해 정렬된 결과에 순위를 부여하는 함수
예를 들면, 학생들의 학점을 성적순으로 검색하여 상위 성적부터 등수를 부여하는 경우 OVER : 순위를 부여하기 위한 대상 집합의 정렬 기준과 분할 기준 정의 PARTITION BY : value expression1을 기준으로 분할, 생략시 전체 집합 대상 ORDER BY : 각 분할내에서 데이터를 정렬하는 기준 칼럼 지정 NULL FIRST | NULL LAST : 정렬 결과에서 NULL 값의 위치 지정 16장. 분석 함수

5 RANK 분석 함수 예 16장. 분석 함수

6 Top-N 분석 Top-N 분석 전체 칼럼 값 중에서 큰 값이나 작은 값 순으로 상위 N개 출력
예를 들면, 서점의 매출분석을 위해 최근 한 달간 판매된 도서중 분야별로 10대 베스트셀러를 분석 하는 경우 16장. 분석 함수

7 Top-N 분석 예 16장. 분석 함수

8 Top-N 분석 – 학생들의 키 순서 16장. 분석 함수

9 NTILE 분석 함수 NTILE 분석 함수 출력 결과를 사용자가 지정한 그룹 수로 나누어 출력
예를 들면, 10개의 결과를 2그룹으로 나누어서 1부터 5까지는 1번 그룹, 6부터 10까지는 2번 그룹으로 출력 16장. 분석 함수

10 NTILE 분석 함수 예 16장. 분석 함수

11 ROW_NUMBER 분석 함수 ROW_NUMBER 분석 함수 분할 별로 정렬된 결과에 대해 순위를 부여하는 함수
분할은 전체 행을 특정 칼럼을 기준으로 분리하는 기능 16장. 분석 함수

12 ROW_NUMBER 분석 함수 예 16장. 분석 함수

13 윈도우 분석 함수 윈도우 분석 함수 윈도우 분석 함수의 종류
전체 결과 집합 중에서 연속선상에 있는 부분 집합을 대상으로 적용하는 함수의 집합 윈도우 분석 함수의 종류 SUM, AVG, COUNT, MAX, MIN, STDDEV, VARIANCE FIRST_VALUE, LAST_VALUE 부분 집합을 결정하기 위한 범위 CURRENTROW UNBOUNDED PRECEDING UNBOUNDED FOLLOWING 16장. 분석 함수

14 윈도우 분석 함수 사용법 16장. 분석 함수

15 윈도우 분석 함수 예 16장. 분석 함수

16 윈도우 분석 함수 16장. 분석 함수

17 FIRST_VALUE와 LAST_VALUE 분석 함수
윈도우에서 정렬된 값 중에서 첫 번째 값을 반환 LAST_VALUE 윈도우에서 정렬된 값 중에서 마지막 값을 반환 16장. 분석 함수

18 16장. 분석 함수

19 FIRST_VALUE와 LAST_VALUE 분석 함수
16장. 분석 함수

20 LAG와 LEAD 분석 함수 LAG 분석 함수 LEAD 분석 함수 음수 사용 불가
현재 행을 기준으로 이전 값을 참조하는 함수 LEAD 분석 함수 현재 행을 기준으로 이후 값을 참조하기 위함 함수 음수 사용 불가 일별 매출추이 등 연속된 데이터 값을 분석때 유용 16장. 분석 함수

21 LAG와 LEAD 분석 함수 16장. 분석 함수

22 계층적 질의문 계층적 질의문 관계형 데이터 베이스 모델은 평면적인 2차원 테이블 구조
관계형 데이터 베이스에서 데이터간이 부모 관계를 표현할 수 있는 칼럼을 지정하여 계층적인 관계를 표현 하나의 테이블에서 계층적인 구조를 표현하는 관계를 순환관계(recursive relationship) 계층적인 데이터를 저장한 칼럼으로부터 데이터를 검색하여 계층적으로 출력 기능 제공 16장. 분석 함수

23 계층적 질의문 개념도 공과대학 정보미디어학부 메카트로닉스학부 컴퓨터공학과 멀티미디어학과 전자공학과 기계공학과 부서 테이블 부서
(DEPTNO : 10) 정보미디어학부 (DEPTNO : 100) 메카트로닉스학부 (DEPTNO : 200) 컴퓨터공학과 (DEPTNO : 101) 멀티미디어학과 (DEPTNO : 102) 전자공학과 (DEPTNO : 201) 기계공학과 (DEPTNO : 202) 10 100 4호관 3호관 2호관 1호관 LOC 200 202 201 102 101 COL DNAME DEPTNO 부서 테이블 부서 #* 부서번호 * 부서이름 o 위치 소속되어 16장. 분석 함수

24 계층적 질의문 사용법 사용법 SELECT 명령문에서 START WITH와 CONNECT BY 절을 이용
계층적 질의문에서는 계층적인 출력 형식과 시작 위치 제어 출력 형식은 top-down 또는 bottom-up 16장. 분석 함수

25 계층 구조의 출력 형태 16장. 분석 함수

26 계층적 질의문 예 – top down 방식 공과대학 정보미디어학부 메카트로닉스학부 컴퓨터공학과 멀티미디어학과 전자공학과
기계공학과 16장. 분석 함수

27 계층적 질의문 예 – bottom up 방식 공과대학 정보미디어학부 메카트로닉스학부 컴퓨터공학과 멀티미디어학과 전자공학과
기계공학과 16장. 분석 함수

28 계층적 질의문 예 – 레벨별 구분 16장. 분석 함수

29 계층 구조에서 가지 제거 방법 계층 구조에서 가지 제거 방법
계층적 질의문에서 WHERE 절이나 CONNECT BY 절을 이용하여 계층 구조의 일부를 제거하고 나머지 출력 WHERE절은 임의의 가지를 삭제 CONNECT BY절은 임의의 가지와 자식 노드까지 동시 삭제 16장. 분석 함수

30 계층구조에서 가지 제거 방법 공과대학 정보미디어학부 메카트로닉스학부 컴퓨터공학과 멀티미디어학과 전자공학과 기계공학과
WHERE dname != ‘정보미디어학부’ START WITH …. CONNECT BY …. START WITH college is null CONNECT BY PRIOR deptno = college AND dname != ‘정보미디어학부’ ‘정보미디어학부’만 제외하고 출력 ‘정보미디어학부’이하를 제외하고 출력 16장. 분석 함수

31 계층구조에서 가지 제거 방법 예 1 16장. 분석 함수

32 계층구조에서 가지 제거 방법 예 2 16장. 분석 함수