New aggregate functions have been added to support analysis of large amounts of time series data. These new functions provide correlation, linear regression, ranking, and other analysis capabilities. The new functions do not require a separate license. Most of these new aggregates can be used either as simple aggregate functions, or as OLAP-style aggregates. See Chapter 4, “SQL Functions,” in Reference: Building Blocks, Tables, and Procedures.
CORR Returns the correlation coefficient of a set of number pairs.
COVAR_POP Returns the population covariance of a set of number pairs.
COVAR_SAMP Returns the sample covariance of a set of number pairs.
CUME_DIST Calculates the relative position of one value among a group of rows. It returns a decimal value between 0 and 1.
EXP_WEIGHTED_AVG Calculates an exponential weighted moving average. Weightings determine the relative importance of each quantity composing the average.
FIRST_VALUE Returns the first value from a set of values.
LAST_VALUE Returns the last value from a set of values.
MEDIAN Returns the median of an expression.
REGR_AVGX Computes the average of the independent variable of the regression line.
REGR_AVGY Computes the average of the dependent variable of the regression line.
REGR_COUNT Returns an integer that represents the number of non-NULL number pairs used to fit the regression line.
REGR_INTERCEPT Computes the y-intercept of the linear regression line that best fits the dependent and independent variables.
REGR_R2 Computes the coefficient of determination (also referred to as R-squared or the goodness-of-fit statistic) for the regression line.
REGR_SLOPE Computes the slope of the linear regression line fitted to non-NULL pairs.
REGR_SXX Returns the sum of squares of the independent expressions used in a linear regression model. The REGR_SXX function can be used to evaluate the statistical validity of a regression model.
REGR_SXY Returns the sum of products of the dependent and independent variables. The REGR_SXY function can be used to evaluate the statistical validity of a regression model.
REGR_SYY Returns values that can evaluate the statistical validity of a regression model.
WEIGHTED_AVG Calculates an arithmetically (or linearly) weighted average. A weighted average is an average in which each quantity to be averaged is assigned a weight. Weightings determine the relative importance of each quantity composing the average.
For detailed information on each of the functions listed above, see “Alphabetical list of functions” in Chapter 4, “SQL Functions,” in Reference: Building Blocks, Tables, and Procedures.
For information on these new functions in the context of OLAP window functions, see “Statistical aggregate functions” in Chapter 2, “Using OLAP,” in System Administration Guide: Volume 2.
