분 석 함 수 8조 정지혜 김지은 한진아

목 차 분석함수 1 분석함수 종류 및 예제 2

분 석 함 수 개 요 집계함수와의 차이 및 장점

1. 분석함수 개요 분석함수란? 분석함수와 그룹함수의 차이 오라클 분석함수는 데이터를 분석하는 함수이다. 분석 함수를 사용하면 쿼리의 결과를 결과셋이라고 하는데, 결과셋을 대상으로 전체 그룹별이 아닌 소그룹별로 각 로우에 대한 계산값을 리턴 할 수 있다. 분석함수와 그룹함수의 차이 그룹함수와 그룹단위로 값을 계산하는 점이 유사하나, 그룹마다가 아닌 행마다 1개의 행을 반환하는 차이 즉, 그룹을 계산해서 각 행마다 결과를 반환하는 것 분석함수는 그룹단위로 값을 계산한다는 점에서 그룹함수와 유사하지만,   그룹마다가 아니라 행마다 1개의 행을 반환한다는 점에서 그룹함수와 상당한 차이가 있다.   (분석함수는 쉽게 생각해서, 그룹을 계산해서 각 행마다 결과를 반환하는 것이다.)   분석함수에서의 그룹을 윈도우(window)라고 부르며,   analytic_clause에서 정의한다.(analytic_clause는 아래에서 설명)   조회되는 각 행마다, 분석함수 값을 계산하기 위한 윈도우를 정의한다. 윈도우의 크기는 행의 갯수나   시간간격을 계산하여 정의할 수 있다. 분석함수는 order by절을 제외하고는 쿼리에서 가장 나중에   실행된다. 따라서 select-list나 order by절에만 사용할 수 있다. 분석용 함수는 하나의 쿼리에서 ORDER BY절 직전에 수행된다. 즉, JOIN, WHERE, GROUP BY, HAVING이 처리된 결과에 대해 분석용 함수 적용 결합처리(테이블을 결합할 필요가 있는 경우) WHERE, GROUP BY, HAVING구의 실행 실행된 결과 세트를 그룹에 분할, 각 그룹의 각 행으로 계산 실행 ORDER BY구가 존재하는 경우, 적절한 출력 순서 처리를 실행

1. 분석함수 개요 분석함수 처리 순서 하나의 쿼리에서 ORDER BY절 직전에 수행된다. 즉, JOIN, WHERE, GROUP BY, HAVING이 처리된 결과에 대해 분석함수가 적용되고 마지막으로 ORDER BY가 수행된다. 결합처리 WHERE, GROUP BY, HAVING구의 실행 실행된 결과 세트를 그룹에 분할, 각 그룹의 각 행으로 계산 실행 ORDER BY구가 존재하는 경우, 적절한 출력 순서 처리를 실행

1. 분석함수 개요 분석함수 장점 2. 개발 작업의 효율화 질의문 속도 향상 분석을 실시하기 위해서 inner join과 같은 복잡한 처리 절차가 필요했으나, 분석함수로 간소한 SQL문으로 처리 퍼포먼스를 향상할 수 있다 2. 개발 작업의 효율화 명쾌하고 간결한 SQL문장으로 복잡한 분석 처리의 기술이 가능해진다.

1. 분석함수 개요 사용법 Analytic_function 4부분으로 구성 Analytic_function – 함수명 Arguments – 파라미터, 0~3개의 파라미터를 가질 수 있다 Over – 키워드, 분석함수임을 나타낸다 Analytic_clause – 다음 페이지 설명 http://www.soqool.com/servlet/board?cmd=view&cat=100&subcat=1030&seq=2 >> 구문설명 블로그

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절은 그룹함수의 order by와 기능이 같다.   즉, 분석함수의 값을 계산하기 위해서 내부적으로 정렬을 해주는 것이다.   partition by로 그룹을 만들고 order by로 그 그룹안의 해당값으로 정렬을 하는 것이다.   partition by절이 없을 경우 전체 레코드를 하나의 그룹으로 잡고 정렬한다.   분석함수에는 order by절을 필요로 하지 않는 것도 있다.   예를 들어, sum을 할 때, 정렬을 할 필요는 없는 것이다. * 분석함수의 값을 계산하기 위해서 내부적으로 정렬을 해주는 것 (그룹함수의 order by와 같은 기능) * partition by로 그룹을 만들고 order by로 그 그룹안의 해당값으로 정렬. partition by절이 없을 경우 전체 레코드를 하나의 그룹으로 잡고 정렬한다. 10

1. 분석함수 개요 사용법 analytic_function > analytic_clause > windowing_clause  windowing clause는 분석함수를 내부적으로 그룹으로 분리하고 정렬한 후에(order by절은 필수),   현재 행을 기준으로, 그룹에서 어떤 행까지를 함수의 계산에 포함할지를 필터링하는 부분이다.   크게 두가지 rows, range를 사용할 수 있는데,   rows는 현재 행을 기준으로 몇개의 행을 포함하는지를,   range는 현재 행을 기준으로 어떤 값의 범위를 포함하는지를 명시한다. * Window : 분석함수에서의 그룹, 조건에 따른 행들의 집합 * 분석함수를 내부적으로 그룹으로 분리하고 정렬한 후 (order by절은 필수), 현재 행을 기준으로, 그룹에서 어떤 행까지를 함수의 계산에 포함할지를 필터링하는 부분 * 이 행을 사용하는 함수를 윈도우함수로 분류 11

분 석 함 수 종 류 분석함수의 종류 및 간략한 설명

2. 분석함수 종류 함 수 군 사 용 종 류 (1) 순위 (Ranking) (2) 윈도우 (Window) (3) 리포팅 레코드 순위와 다른 레코드 순위와의 비교 계산 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… 13

2. 분석함수 종류 (1). 순위(RANKING) RANK 레코드의 순위와 다른 레코드들의 순위와의 비교연산 종 류 예 제 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; 급료가 높은 순서로 순위를 매기는 분석 * 예시는 급료가 높은 순서로 순위를 매기는 분석을 실시하였습니다. 순위를 보시면 급료가 동일한 2번째 레코드와 3번째 레코드의 순위가 각각 2위이고 그 다음 순위는 3위가 아닌 4위입니다. 이처럼 동일 순위를 무시한 연속 순위를 매기려면 DENSE_RANK함수를 씁니다. * RANK() – 상위 순으로 등수를 부여하는 경우, 정렬 결과를 기준으로 전체 순위 출력 * OVER – 순위를 부여하기 위한 대상 집합의 정렬 기준과 분할 기준 정의 15

예제 - 순위 SELECT employee_id , salary , department_id , RANK() OVER(PARTITION BY department_id ORDER BY salary DESC) AS RNK FROM employees; 부서 별 그룹 내에서 급료가 높은 순으로 정렬하는 분석 그리고 partition by로 그룹을 만들고 order by로 그 그룹 안의 해당 값으로 정렬을 합니다. partition by절이 없을 경우 전체 레코드를 하나의 그룹으로 잡고 정렬합니다. 예제는 partition by로 부서 별로 그룹을 만들어 부서 별 그룹 내에서 급료가 높은 순으로 정렬하는 분석을 실시하고 있습니다. 16

2. 분석함수 종류 (2). 윈도우(WINDOW) 분석함수 중 윈도우절(WINDOWING절)을 사용하는 함수 윈도우절 사용하면? PARTITION BY절에 의해 명시된 그룹을 더 디테일하게 그룹핑할 수 있다. 누적집계, 이동집계, 집중집계를 계산할 수 있다. 구문형식 주요 분석함수 AVG CORR, COVAR_POP, COVAR_SAMP MIN, MAX COUNT VARIANCE, VAR_POP, VAR_SAMP STDDEV, STDDEV_POP… FIRST_VALUE, LAST_VALUE 윈도우절은 PARTITION BY절에 명시된 전체 그룹에서 그 부분집합인 윈도우를 지정하는데 ‘BETWEEN 시작윙치 AND 끝위치’ 형태로 지정한다. http://blog.naver.com/minis24?Redirect=Log&logNo=80100582246 윈도우함수 설명 참고 http://blog.naver.com/PostView.nhn?blogId=minis24&logNo=80100582246 윈도우 예제 17

예제 – 윈도우 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; 급료의 누적 값을 분석 * Window 함수는 조건에 따른 행들의 집합(Window)에 대한 연산을 수행하고 각 행에 대한 값 반환합니다. Window함수의 한 종류인 SUM함수는 조건을 만족하는 행의 합을 반환합니다. * 예시는 급료의 누적 값을 분석하는 것을 실시하고 있습니다. SUM은 department_id와 employee_id로 정렬된 급료를 누적하여 더합니다. 1번째 레코드의 SUM은 1번째 레코드의 급료인 4400이고 2번째 레코드는 1번째 레코드의 SUM과 자신의 급료인 13000을 더하여 17400입니다. SUM2는 부서별로 누적하여 더하고 있는 것을 보여줍니다. 부서의 id가 20인 2, 3번째 레코드를 보시면 2번째 레코드의 급료는 13000이며 SUM2는 13000입니다. 3번째 레코드의 급료값은 6000이며 SUM2는 13000과 더하여 19000이 됩니다. 4번째 레코드는 부서의 id가 30이므로 합하지 않습니다. * 윈도우 지정은 rows between 윈도우 개시점 and 윈도우 종료점 입니다. rows between unbounded preceding and current row 를 보시면  unbounded preceding는 그룹의 최초의 행을 윈도우의 개시점으로 한다 라고 의미하고 있습니다. SUM2를 보시면 부서별로 그룹화했으므로 부서의 id가 각각 10, 20, 30, 40, 50을 윈도우 개시점으로 지정되었습니다. 윈도우 종료점으로 지정한 current row는 윈도우의 종료점을 항상 현재행으로 한다란 의미로 현재행이 이동하면 윈도우 종료점도 이동합니다. 윈도우 종료점이 그룹의 마지막에 도착한 시점에서 누적 계산이 종료하고 있음을 위의 결과로 확인할 수 있습니다. 이것은 디폴트값이라 적어주지 않아도 무방합니다. 18

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 ) 래그·리드 함수를 이용해 분석해 봅시다.우선은, 래그 함수가 어떤것인가를 보고 갑니다.   래그 함수는 현재의 행으로부터의 오프셋을 지정해 그 위치보다 앞에 있는 지정된 행에 액세스 할 수 있습니다. 래그 함수를 사용하지 않고 동등의 결과를 취득하려면  내부 결합이나 펑션을 사용해 데이터를 취득할 필요가 있습니다. 래그 함수를 사용하면 대상테이블에 한번 액세스 해서 간단하게 지난 달과의 매상 비교등을 실시할 수 있어 SQL문의 퍼포먼스가 큰폭으로 향상합니다. 래그 함수의 인수에 대해서 설명을 하자면 value_expr는 분석하는 대상렬명을 지정합니다. offset는 몇행전의 데이터를 표시하는지를 지정합니다. default는 offset로 지정된 데이터가 대상테이블 또는 그룹에 존재하지 않게 되었을 경우에 여기서 지정한 값을 되돌립니다. 예를 들면 대상테이블의 1행째에는 「offset로 지정된 전의 데이터」가 존재하지 않는 경우입니다.default를 지정하지 않는 경우는 NULL가 돌아갑니다. - 현재의 행으로부터의 오프셋을 지정해 그 위치보다 앞에 있는 지정된 행에 액세스할 수 있다. * value_expr : 분석하는 대상행명을 지정 * Offset : 몇행전의 데이터를 표시하는지를 지정합니다. * Default : offset로 지정된 데이터가 대상테이블 또는 그룹에 존재하지 않게 되었을 경우 여기서 지정한 값을 되돌립니다. 20

예제 – 리포팅 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; 각 부서별로 급료의 비율 *리포팅함수는 공유 계산으로 계산된 결과에서 다시 계산합니다. (단, 전체 Window에 대한 연산이나 현재 행을 제외한 연산 수행) 전체 Window에 대한 연산이나 현재 행을 제외한 연산을 할 수 있게 하므로 Join처리비용을 절감합니다. 보통 전국 매출 대비 특정 지역 매출의 비중 등에 사용됩니다. *예시는 각 부서별로 급료의 비율을 분석한 것입니다. 부서의 id가 10인 1번째 레코드는 그룹화된 부서 내에서 1명만 있으므로 비율은 1.000입니다. 부서의 id가 20인 2,3번째 레코드는 각각 급료의 비율을 나타낸 것을 보실 수 있습니다. 2,3번째 레코드의 RATIO_SAL을 합하면 1.000이 됩니다. 21

2. 분석함수 종류 (4). 비교(LAG/LEAD) LEAD ( value_expr [, offset] [, default] )   OVER ( [query_partition_clause] order_by_clause ) 리드 함수는 현재의 행에 오프셋을 지정해서 그 위치보다  뒤에 있는 지정된 행에 액세스 할 수 있습니다. 리드 함수는 래그 함수가 전에 있는 데이터에 액세스 할 수 있던 것과 반대 개념입니다.     리드 함수도 래그 함수와 같이 리드 함수를 사용하지 않고 동등의 결과를 취득하려면 내부 결합이나 펑션을 사용해 데이터를 취득해야 합니다. 리드 함수를 사용하면 대상테이블에 한번 액세스 해서 간단하게 매상 비교등을 실시하는 것이 성과 SQL문의 퍼포먼스가 큰폭으로 향상합니다.   리드 함수는 액세스 할 수 있는 데이터가 전, 후의 차이뿐이므로 래그 함수와의 차이만 알면 간단하게 이해할 수 있다고 생각합니다. 실제로 리드 함수를 사용한 예를 보고 갑시다.리드 함수의 구문은 이하대로입니다. 래그 함수와 같으므로 인수의 상세 등은 앞의 래그 함수를 참조하삼! - 현재의 행에 오프셋을 지정해서 그 위치보다  뒤에 있는 지정된 행에 액세스 할 수 있다.  리드 함수를 사용하면 대상테이블에 한번 액세스 해서 간단하게 매상 비교등을 실시하는 것이 성과 SQL문의 퍼포먼스가 큰폭으로 향상 22

예제 – 비교(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; 동일한 테이블에 있는 다른 행의 값을 참조하기 위한 함수 *비교함수는 동일한 테이블에 있는 다른 행의 값을 참조하기 위한 함수로 LAG : 현재 행을 기준으로 이전 값을 참조 LEAD : 현재 행을 기준으로 이후 값을 참조한다. 비교함수에서 지정하는 인수는 현재형을 기준으로 몇 번째 행을 참조할 것인지를 지정하며 음수는 사용할 수 없다. *예시는 급료의 1행 이전 값과 1행 이후 값을 분석하고 있습니다. 2번째 레코드의 현재 급료는 2200이며 LAG함수를 써서 1번째 레코드의 급료를 참조하고, LEAD함수를 써서 3번째 레코드의 급료를 참조하고 있는 것을 보실 수 있습니다. LAG, LEAD(salary, 1)에서 1은 1만큼의 위치에 있는 값을 참조합니다. 예를 들어, 3이라고 쓰면, 3번째 이전과 이후의 레코드의 급료를 참조하는 것입니다. 즉 이 그림에서 5번째 레코드의 이전 급료는 2번째 레코드값을 참조하여 2100가 들어가고 5번째 레코드의 이후 급료는 8번째 레코드의 급료인 2500가 들어가게 됩니다. 23

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; * 예시는 근무 달수와 월급의 상관관계를 분석하고 있습니다. 각 함수의 괄호안의 인자는 각각 X, Y값을 나타내는 것입니다. 따라서 이 예시에서는 X값은 근무 달수이고 Y값은 월급을 나타내고 있습니다. SLPOL는 이 회귀직선의 기울기이며, ICPT는 절편 계산입니다. RSQR는 결정계수를 나타냅니다. COUNT는 회귀선 적합에 사용되는 개수를 나타내고, AVG_SAL은 X값 즉, salary들의 평균을 나타냅니다. AVG_W_MONTH는 Y값 즉, 근무달 수의 평균을 나타냅니다. SXX는 의존변수의 제곱화이고 SXY는 의존변수와 독립변수를 제곱한 값이며 SYY는 독립변수의 제곱화를 나타냅니다. 이것들은 회귀분석을 진단하는 통계로 사용됩니다. 25