분 석 함 수

download report

Transcript 분 석 함 수

분 석 함 수
8조
정지혜 김지은 한진아
목 차
1 • 분석함수
2 • 분석함수 종류 및 예제
분석함수 개요
집계함수와의 차이 및 장점
1. 분석함수 개요
• 분석함수란?
오라클 분석함수는 데이터를 분석하는 함수이다.
분석 함수를 사용하면 쿼리의 결과를 결과셋이라고
하는데, 결과셋을 대상으로 전체 그룹별이 아닌
소그룹별로 각 로우에 대한 계산값을 리턴 할 수 있다.
• 분석함수와 그룹함수의 차이
그룹함수와 그룹단위로 값을 계산하는 점이 유사하나,
그룹마다가 아닌 행마다 1개의 행을 반환하는 차이
즉, 그룹을 계산해서 각 행마다 결과를 반환하는 것
1. 분석함수 개요
• 분석함수 처리 순서
하나의 쿼리에서 ORDER BY절 직전에 수행된다.
즉, JOIN, WHERE, GROUP BY, HAVING이 처리된
결과에 대해 분석함수가 적용되고 마지막으로
ORDER BY가 수행된다.
결합처리
실행된 결과 세트
ORDER BY구가 존
WHERE, GROUP
를 그룹에 분할,
재하는 경우,
BY, HAVING구의
각 그룹의 각 행으
적절한 출력 순서
실행
로 계산 실행
처리를 실행
1. 분석함수 개요
• 분석함수 장점
1.질의문 속도 향상
분석을 실시하기 위해서 inner join과 같은 복잡한 처리
절차가 필요했으나, 분석함수로 간소한 SQL문으로
처리 퍼포먼스를 향상할 수 있다
2. 개발 작업의 효율화
명쾌하고 간결한 SQL문장으로
복잡한 분석 처리의 기술이 가능해진다.
1. 분석함수 개요
•사용법
Analytic_function
4부분으로 구성
1.
2.
3.
4.
Analytic_function – 함수명
Arguments – 파라미터, 0~3개의 파라미터를 가질 수 있다
Over – 키워드, 분석함수임을 나타낸다
Analytic_clause – 다음 페이지 설명
1. 분석함수 개요
•사용법
Analytic_function > analytic_clause::=
분석함수는 analytic_clause를 사용하며, select-list나 order by절에 나올 수 있다.
analytic_clause는 위와 같이 3부분으로 구성된다.
1. query_partition_clause
2. order_by_clause
3. windowing_clause
1. 분석함수 개요
•사용법
analytic_function > analytic_clause > query_partition_clause
query_partition_clause는 위와 같이 사용하며,
그룹함수를 사용할 때의 group by 절에 해당한다.
1. 분석함수 개요
•사용법
analytic_function > analytic_clause > order_by_clause
* 분석함수의 값을 계산하기 위해서 내부적으로 정렬을 해주는 것
(그룹함수의 order by와 같은 기능)
* partition by로 그룹을 만들고 order by로 그 그룹안의 해당값으로 정렬.
partition by절이 없을 경우 전체 레코드를 하나의 그룹으로 잡고 정렬한다.
1. 분석함수 개요
•사용법
analytic_function > analytic_clause > windowing_clause
* Window : 분석함수에서의 그룹, 조건에 따른 행들의 집합
* 분석함수를 내부적으로 그룹으로 분리하고 정렬한 후
(order by절은 필수), 현재 행을 기준으로, 그룹에서 어떤 행까지를
함수의 계산에 포함할지를 필터링하는 부분
* 이 행을 사용하는 함수를 윈도우함수로 분류
분석함수 종류
분석함수의 종류 및 간략한 설명
2. 분석함수 종류
함수군
사용
종류
(1)
순위
(Ranking)
레코드 순위와 다른 레코드 순위와
의 비교 계산
RANK
DENSE_RANK
PERCENT_RANK
CUME_DIST
NTILE, ROW_NUMBER…
(2)
윈도우
(Window)
-윈도우절 사용하는 함수군
-조건에 따른 행들의 집합(Window)
에 대한 연산을 수행하고 각 행에 대
한 값 반환(누적, 이동, 중심 집합 계
산)
SUM, AVG, MIN, MAX,
COUNT, VARIANCE,
STDDEV, FIRST_VALUE,
LAST_VALUE, ROWS,
RANGE…
(3)
리포팅
(Reporting)
-공유 계산(계산된 결과에서 다시
계산)
RATIO_TO_REPORT
(4)
비교
(LAG/LEAD)
현재 행으로부터 지정한 수만큼
다른 행에 접근하여 값 검색
LAG, LEAD
(5)
선형회귀
(Linear
Regression)
선형 회귀식과 다른 통계량들(기울
기, 절편 등) 계산
REGR_SLOPE(Y,X)
REGR_INTERCEPT
REGR_COUNT, REGR_R2,
REGR_AVGX,
REGR_AVGY…
2. 분석함수 종류
(1). 순위(RANKING)
레코드의 순위와 다른 레코드들의 순위와의 비교연산
(순위, 백분위수, n-분위수 계산)
종류
예제
RANK
An ordered ranking of rows
starting with a rank of one
2000년 9월과 10월 미국에서의 채널별 판
매액 순위
DENSE_RANK
It handles tie values Next rank
after a tie, using
DENSE_RANK=tied rank +1
채널별, 월별(2000년 9월과 10월) 판매액
순위(일반순위, 공백수가 없는 순위)
PERCENT_RANK
그룹 수에 대한 값의 순위 퍼센트를
계산 (범위는 0에서 1 사이)
2000년 7,8,9월의 월별 결과 중 판매액의
순위 퍼센트
CUME_DIST
누적 분포(전체 중 특정한 값의 위치
를 계산, 범위는 0에서 1 사이)
2000년 7,8,9월의 월별 결과 중 판매액의
위치
NTILE
분위수 계산
1999년도 남성의 월별 판매액 사분위수
(N=4)
ROW_NUMBER
행의 수 계산(파티션 내에서 각 행에
대해 1로 시작하여 정렬되어 정의되
는 유일한 수를 할당)
채널별, 2000년 9월과 10월의 월별 내림차
순 판매액의 행수
예제 - 순위
급료가 높은 순서로 순위를 매기는 분석
SELECT employee_id
, salary
, department_id
, RANK() OVER(ORDER BY salary DESC) AS RNK
, DENSE_RANK() OVER(ORDER BY salary DESC) AS
DENSE_RNK
FROM employees;
예제 - 순위
부서 별 그룹 내에서 급료가 높은 순으로 정렬하는 분석
SELECT employee_id
, salary
, department_id
, RANK() OVER(PARTITION BY department_id ORDER BY
salary DESC) AS RNK
FROM employees;
2. 분석함수 종류
(2). 윈도우(WINDOW)
•
•
•
분석함수 중 윈도우절(WINDOWING절)을 사용하는 함수
Window에 대한 연산을 수행하고 각 행에 대한 값 반환
윈도우절 사용하면?
PARTITION BY절에 의해 명시된 그룹을 더 디테일하게 그룹핑할 수 있다.
•
누적집계, 이동집계, 집중집계를 계산할 수 있다.
주요 분석함수
AVG
CORR, COVAR_POP,
COVAR_SAMP
MIN, MAX
COUNT
VARIANCE, VAR_POP,
VAR_SAMP
STDDEV, STDDEV_POP…
FIRST_VALUE, LAST_VALUE
구문형식
예제 – 윈도우
SELECT employee_id
급료의 누적 값을 분석
, salary
, department_id
, SUM(salary) OVER(ORDER BY department_id, employee_id) AS
SUM
, SUM(salary) OVER(PARTITION BY department_id ORDER BY employee_id
rows between unbounded preceding and current row)
AS SUM2
FROM employees;
2. 분석함수 종류
(3). 리포팅(REPORTING)
RATIO_TO_REPORT(expr) OVER ([query_partition_clause ])
RATIO_TO_REPORT 함수를 사용하면
다양한 집합에 대한 각 행의 비율을 구할수 있다.
2. 분석함수 종류
(4). 비교(LAG/LEAD)
LAG ( value_expr [, offset] [, default] )
OVER ( [query_partition_clause] order_by_clause )
- 현재의 행으로부터의 오프셋을 지정해
그 위치보다 앞에 있는 지정된 행에 액세스할 수 있다.
* value_expr : 분석하는 대상행명을 지정
* Offset : 몇행전의 데이터를 표시하는지를 지정합니다.
* Default : offset로 지정된 데이터가 대상테이블 또는 그룹에 존재하지 않게 되었을
경우 여기서 지정한 값을 되돌립니다.
예제 – 리포팅
SELECT employee_id
각 부서별로 급료의 비율
, salary
, department_id
, SUM(salary) over(PARTITION BY department_id) AS
TOTAL_SAL
, TO_CHAR((RATIO_TO_REPORT(salary)
over(PARTITION BY
department_id)),'9.999') AS RATIO_SAL
FROM employees;
2. 분석함수 종류
(4). 비교(LAG/LEAD)
LEAD ( value_expr [, offset] [, default] )
OVER ( [query_partition_clause] order_by_clause )
- 현재의 행에 오프셋을 지정해서
그 위치보다 뒤에 있는 지정된 행에 액세스 할 수 있다.
- 리드 함수를 사용하면 대상테이블에 한번 액세스 해서
- 간단하게 매상 비교등을 실시하는 것이 성과 SQL문의 퍼포먼스가 큰폭으로 향상
예제 – 비교(LAG/LEAD)
동일한 테이블에 있는 다른 행의 값을 참조하기 위한 함수
SELECT employee_id
, salary
, department_id
, LAG(salary, 1) OVER(ORDER BY salary) AS PREV_SAL
, salary AS NOW_SAL
, LEAD(salary, 1) OVER(ORDER BY salary) AS NEXT_SAL
FROM employees;
2. 분석함수 종류
(5). 선형회귀분석
- 선형 회귀식과 다른 통계량들(기울기, 절편 등) 계산
Ex> 근무년수와 월급의 상관관계 등
회귀직선의 기울기 계산
회귀직선의 절편 계산
회귀선 적합에 사용되는 수
결정계수(R-Square)
X의 평균
Y의 평균
Sxx, Sxy, Syy
예제 – 선형회귀분석
예시는 근무 달수와 월급의 상관관계를 분석
SELECT TRUNC((REGR_SLOPE((CEIL(MONTHS_BETWEEN(SYSDATE, hire_date))), salary)), 5) AS SLOPE
, TRUNC((REGR_INTERCEPT((CEIL(MONTHS_BETWEEN(SYSDATE, hire_date))), salary)), 5) AS ICPT
, TRUNC((REGR_R2((CEIL(MONTHS_BETWEEN(SYSDATE, hire_date))), salary)), 5) AS RSQR
, TRUNC((REGR_COUNT((CEIL(MONTHS_BETWEEN(SYSDATE, hire_date))), salary)), 5) AS COUNT
, TRUNC((REGR_AVGX((CEIL(MONTHS_BETWEEN(SYSDATE, hire_date))), salary)), 5) AS AVG_SAL
, TRUNC((REGR_AVGY((CEIL(MONTHS_BETWEEN(SYSDATE, hire_date))), salary)), 5) AS
AVG_W_MONTH
, TRUNC((REGR_SXX((CEIL(MONTHS_BETWEEN(SYSDATE, hire_date))), salary)), 5) AS SXX
, TRUNC((REGR_SXY((CEIL(MONTHS_BETWEEN(SYSDATE, hire_date))), salary)), 5) AS SXY
, TRUNC((REGR_SYY((CEIL(MONTHS_BETWEEN(SYSDATE, hire_date))), salary)), 5) AS SYY
FROM employees;