티베로(Tibero) 제공 분석함수(analytic_function)정리 - 티멕스 제공

티베로 분석함수(analytic_function)

1.1. 단일 로우 함수

단일 로우 함수는 하나의 로우로부터 컬럼 값을 파라미터로 입력 받는 함수이다. 함수의 파라미터는 반드시 컬럼 값만 입력받는 것은 아니고 실제 데이터를 직접 입력으로 받을 수도 있다. 단일 로우 함수는 SQL 문장 내의 어떤 연산식에도 포함될 수 있다.

1.2. 집단 함수

집단 함수는 하나 이상의 로우로부터 컬럼 값을 파라미터로 입력받는 함수이다. 함수의 파라미터는 반드시 컬럼 값만 입력받는 것은 아니고 실제 데이터를 직접 입력으로 받을 수도 있다. 집단 함수는 SELECT 문의 SELECT 절, GROUP BY 절, HAVING 절에만 포함된다.
Tibero에서 제공하는 집단 함수에는 AVG, COUNT, MAX, MIN, SUM 등이 있다. 이러한 함수는 각각 파라미터로 주어진 컬럼에 대하여 평균, 개수, 최댓값, 최솟값, 합계 등을 구한다. 만약 파라미터로 실제 데이터 값이 주어지면 그 값을 그대로 반환한다.

AVG	그룹 내의 모든 로우에 대한 expr 값의 평균을 구하는 함수이다.
CORR	파라미터로 주어진 expr1가 expr2의 상관계수를 계산하는 함수이다.
COUNT	쿼리가 반환하는 로우의 개수를 세는 함수이다.
COVAR_POP	expr1, expr2의 모공분산을 계산하는 함수이다.
COVAR_SAMP	expr1, expr2의 표본공분산을 계산하는 함수이다.
DENSE_RANK	각 그룹별로 로우를 정렬한 다음 그룹 내의 각 로우에 대한 순위를 반환하는 함수이다.
FIRST	정렬된 로우에서 처음에 해당하는 로우를 뽑아내어 명시된 집단함수를 적용한 결과를 반환한다.
LAST	정렬된 로우에서 마지막에 해당하는 로우를 뽑아내어 명시된 집단함수를 적용한 결과를 반환한 다.
MAX	그룹 내의 모든 로우에 대한 expr 값 중의 최댓값을 구하는 함수이다.
MIN	그룹 내의 모든 로우에 대한 expr 값 중의 최솟값을 구하는 함수이다.
PERCENT_RANK	파라미터로 주어진 값의 그룹 내의 위치를 나타내 주는 함수이다.
PERCENTILE_CONT	연속 분포 모델에서 파라미터로 주어진 백분위 값에 해당하는 값을 계산하는 역 분포 함수이다.
PERCENTILE_DISC	이산 분포를 가정한 역분산 함수로 분석 함수로도 사용할 수 있다.
RANK	그룹별로 로우를 정렬한 후 그룹 내의 각 로우의 순위를 반환하는 함수이다.
REGR_SLOPE REGR_INTERCEPT REGR_COUNT REGR_R2 REGR_AVGX REGR_AVGY REGR_SXX REGR_SYY REGR_SXY	임의의 수치 데이터 쌍의 집합에 가장 맞는 선형 방정식을 구하기 위해 사용된다.
STDDEV	expr의 표본 표준편차를 반환하는 함수이다.
STDDEV_POP	expr의 모표준편차를 반환하는 함수이다.
STDDEV_SAMP	expr의 누적 표본 표준편차를 반환하는 함수이다.
SUM	그룹 내의 모든 로우에 대한 expr 값의 합계를 구하는 함수이다.
VARIANCE	expr의 분산을 반환한다.
VAR_POP	expr의 모분산을 반환한다.
VAR_SAMP	expr의 표본분산을 반환하는 함수이다.
XMLAGG	XML 조각을 받고, 이를 한데 모아 XML 문서로 만들어 반환하는 함수이다.

1.3. 분석 함수

분석 함수는 집단 함수와 마찬가지로 특정 로우 그룹에 대한 집계 값을 구하는 데 사용된다.
집단 함수와 다른 점은 하나의 로우 그룹에 속한 모든 로우가 하나의 집계 값을 공유하지 않는다는 것이다. 각각의 로우에 대해 로우 그룹이 별개로 정의되며, 때문에 모든 로우가 별개로 각각 자신의 로우 그룹에 대한 집계 값을 갖게 된다. 이 로우 그룹을 분석 함수에서는 윈도우라고 부르며, analytic_clause 안에 정의된다. 윈도우 영역은 물리적인 로우의 개수로 정의될 수도 있고, 논리적인 어떤 계산 값을 통해서 정의될 수도 있다.
하나의 쿼리 블록 안에서 분석 함수는 ORDER BY 절을 제외하고 가장 마지막에 수행되는 연산이다. WHERE 절, GROUP BY 절, HAVING 절 모두 분석 함수가 수행되기 전에 먼저 적용된다. 그러므로 분석 함수는 SELECT 절 또는 ORDER BY 절에만 나올 수 있다.

analytic_function

분석 함수는 크게 analytic_function, argument, analytic_clause로 구성된다.
analytic_function의 세부 내용은 다음과 같다.

• 문법

• 

• 구성요소
• 구성요소설명

  analytic_function	분석 함수의 이름을 명시한다.
  argument	분석 함수의 파라미터를 명시한다. 함수에 따라 파라미터의 타입이 결정된다.
  OVER analytic_clause	OVER analytic_clause를 사용해 함수를 분석 함수로 수행할 수 있다.

 

analytic_clause

OVER analytic_clause를 사용하여 함수를 분석 함수로 수행할 수 있다. 분석 함수는 ORDER BY 절을 제외한 다른 모든 절의 내용이 처리된 다음에 적용된다. 그러므로 분석 함수가 계산한 결과의 일부만 선택하고자 하면, 분석 함수를 수행한 쿼리를 뷰로 둘러싸고, 그 쿼리를 둘러싼 뷰에 WHERE 절을 적용하면 된다. analytic_clause 안에 분석 함수를 사용할 수는 없다. 그러나 부질의 내에 분석 함수를 사용하는 것은 가능하다.

 

analytic_clause의 세부 내용은 다음과 같다.

• 문법

• 

• 구성요소
• 구성요소설명

  partition_by	분석 함수를 계산하기 전에 현재 질의 블록의 결과 집합을 분할한다.
  order_by_clause	partition_by에 의해 분할된 하나의 파티션 내에서 로우를 어떻게 정렬할지를 명시한다.
  window_clause	window_clause를 가질 수 있는 경우가 있다. 분석 함수의 order_by_clause를 명시할 경우에만 이 window_clause를 명시할 수 있다.

 

partition_by

분석 함수를 계산하기 전에 현재 질의 블록의 결과 집합을 expr 또는 expr의 리스트를 기준으로 분할한다. 이 절을 명시하지 않으면, 분석 함수의 윈도우는 전체 로우 집합 내에서 움직이게 된다.
하나의 질의 블록의 SELECT 절 또는 ORDER BY 절에 여러 개의 분석 함수를 명시할 수 있으며, 각각이 서로 다른 PARTITION BY 키를 갖는 것도 가능하다.

 

partition_by의 세부 내용은 다음과 같다.

• 문법

• 

• 구성요소
• 구성요소설명

  expr	expr이 취할 수 있는 값은 상수, 컬럼, 분석 함수가 아닌 함수로 구성된 연산식이다.

 

order_by_clause

partition_by에 의해 분할된 하나의 파티션 내에서 로우를 어떻게 정렬할지를 명시한다. 정렬에 사용되는 키 값은 여러 개를 명시할 수 있다.
분석 함수에서 사용되는 order_by_clause 내에서는 위치 상수(ORDER BY 1과 같은)를 사용할 수 없다. SIBLINGS 역시 사용할 수 없다. SELECT 리스트의 컬럼의 별칭도 사용할 수 없다. 그 이외에는 보통의 ORDER BY 절과 사용 방식이 같다.
분석 함수에 사용된 order_by_clause는 파티션 내의 로우의 순서를 결정할 뿐이지 분석 함수를 적용하고 난 쿼리 블록의 최종 결과 집합의 로우의 순서를 결정해 주는 것은 아니다. 이를 위해서는 쿼리 블록을 위한 별도의 ORDER BY 절을 추가로 명시해야만 한다.

 

order_by_clause의 세부 내용은 다음과 같다.

• 문법

• 

• 구성요소
• 구성요소설명

  SIBLINGS	order_by_clause는 계층 질의의 형제 노드 내에서 정렬 순서를 정의한다. 분석 함수에서는 사용할 수 없다.
  expr	정렬의 키로 사용되는 연산식이다.
  position	select_list에 명시된 expr의 위치를 지정한다. 분석 함수에서는 사용할 수 없다.
  ASC	디폴트로, 정렬 순서를 명시한다. ASC는 오름차순으로 정렬한다.
  DESC	정렬 순서를 명시한다. DESC는 내림차순으로 정렬한다.
  NULLS FIRST	NULL 값의 정렬 순서를 명시한다. NULLS FIRST는 오름차순 정렬의 디폴트로 사용된다.
  NULLS LAST	NULL 값의 정렬 순서를 명시한다. NULLS LAST는 내림차순 정렬의 디폴트로 사용된다.

 

window_clause

분석 함수에 따라 window_clause를 가질 수 있는 경우가 있다. 분석 함수의 order_by_clause를 명시할 경우에만 window_clause를 명시할 수 있다. window_clause를 명시하지 않았을 때는 필요한 경우에 디폴트 윈도우로 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW가 지정된다.

 

window_clause의 세부 내용은 다음과 같다.

• 문법

• 

• 구성요소
• 구성요소설명

  ROWS	윈도우 타입을 ROW로 지정한다. 분석 함수는 현재 로우가 정의하는 윈도우 내의 로우에 대해서 계산이 된다. ROWS는 윈도우를 물리적인 로우 단위로 정의한다. RANGE와는 달리 ROW로 지정된 윈도우의 로우는 order_by_clause를 통해 동률이 나왔을 경우 상이한 로우가 결과로 반환된다.
  RANGE	윈도우 타입을 RANGE로 지정한다. RANGE는 현재 로우를 기준으로 논리적인 오프셋을 명시하여 윈도우를 정의한다. ROW와는 달리 RANGE로 지정된 윈도우의 로우는 언제나 항상 똑같은 로우가 반환된다. RANGE를 명시할 경우는 order_by_clause에 하나의 키만 명시할 수 있다. RANGE로 정의된 윈도우의 경우 두 개의 로우가 order_by_clause로 인해 정렬한 결과가 동률일 때는 분석 함수의 결과 값은 항상 동일하다. “4.2.143. SUM”의 분석 함수 예제에서 이를 확인해 볼 수 있다.
  BETWEEN ... AND	윈도우의 시작점과 끝점을 명시한다. AND 이전에 오는 것이 시작점, AND 이후에 오는 것이 끝점이다. BETWEEN ... AND를 명시하지 않고 하나의 시점만 명시할 경우에는, 그 시점이 시작점이 되며 끝점은 현재 로우가 된다.

 

다음의 표는 Tibero에서 제공하는 분석 함수와 함수별 window_clause의 명시 가능 여부를 나타낸다.

분석 함수window_clause 명시 가능 여부

AVG	예
CORR	예
COUNT	예
COVAR_POP	예
COVAR_SAMP	예
DENSE_RANK	아니오
FIRST	아니오
FIRST_VALUE	예
LAG	아니오
LAST	아니오
LAST_VALUE	예
LEAD	아니오
MAX	예
MIN	예
NTILE	아니오
PERCENT_RANK	아니오
PERCENTILE_CONT	아니오
PERCENTILE_DISC	아니오
RANK	아니오
RATIO_TO_REPORT	아니오
REGR_SLOPE REGR_INTERCEPT REGR_COUNT REGR_R2 REGR_AVGX REGR_AVGY REGR_SXX REGR_SYY REGR_SXY	예
ROW_NUMBER	아니오
STDDEV	예
STDDEV_POP	예
STDDEV_SAMP	예
SUM	예
VARIANCE	예
VAR_POP	예
VAR_SAMP	예

 

window_value

window_value의 세부 내용은 다음과 같다.

• 문법

• 

• 구성요소윈도우 타입별로 expr은 다음과 같이 달라진다.
• • 윈도우 타입이 ROW일 경우에는 다음과 같다.
  • • expr은 물리적인 오프셋을 지정한다. 이것은 양수이거나 양수로 계산되는 연산식이다.
    • 시작점의 로우는 끝점의 로우보다 먼저 나와야 한다.
  • 윈도우 타입이 RANGE일 경우에는 다음과 같다.
  • • expr은 논리적인 오프셋을 지정한다. 이것은 0 또는 양수로 계산되는 연산식 또는 간격 리터럴이다. 간격 리터럴에 대해서는 “2.3. 리터럴”을 참고한다.
    • expr에 수치 값을 사용할 수 있는 경우는 order_by_clause의 expr의 데이터 타입이 NUMBER 또는 DATE 타입일 때이다.
    • expr에 간격 값을 사용할 수 있는 경우는 order_by_clause의 expr의 데이터 타입이 DATE 타입일 때이다.
• 구성요소설명

  UNBOUNDED PRECEDING	시작점을 명시할 때 사용하며, 파티션의 첫 번째 로우를 지정한다. 끝점으로 사용할 수는 없다.
  UNBOUNDED FOLLOWING	끝점을 명시할 때 사용하며, 파티션의 맨 마지막 로우를 지정한다. 시작점으로 사용할 수는 없다.
  CURRENT ROW	시작점 또는 끝점으로 사용할 수 있다. ROW 또는 RANGE를 명시했을 경우 현재 로우 또는 현재 로우에서 계산된 값을 의미한다. 시작점으로 명시했을 때는 끝점으로 expr PRECEDING을 사용할 수 없으며, 끝점으로 명시했을 경우는 시작점으로 expr FOLLOWING을 사용할 수 없다.
  expr PRECEDING	expr PRECEDING을 끝점에 명시했으면, 시작점은 항상 expr PRECEDING이 되어야 한다.
  expr FOLLOWING	expr FOLLOWING을 시작점에 명시했으면, 끝점은 항상 expr FOLLOWING이 되어야 한다.

[출처] tmaxsoft.com
https://technet.tmaxsoft.com/upload/download/online/tibero/pver-20150504-000001/sql-reference/ch_SQL_functions.html