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;