DB2 UDB's High-Function Business Intelligence in e-business

100 High-Function Business Intelligence in e-business

3.1 DB2 UDB’s statistics, analytic, and OLAP functions

DB2 UDB’s query related functions are broadly classified into two categories:

򐂰 Statistics and analytic functions

򐂰 Online Analytical Processing (OLAP) functions

Appendix A, “Introduction to statistics and analytic concepts” on page 217

provides an introduction to some of the analytic concepts described here.

The following sections briefly describe these functions and provide examples to

explain their usage where appropriate.

3.2 Statistics and analytic functions

The various analytic functions supported are listed in Table 3-1.

Table 3-1 List of statistics and analytic functions

Statistics and analytic functions Description

AVG Returns the average of a set of numbers.

CORRELATION or CORR Returns the coefficient of correlation of a set of number pairs.

COUNT Returns the count of the number of rows in a set of rows or values.

COUNT_BIG Returns the number of rows or values in a set of rows or values.

Result can be greater than the maximum value of integer.

COVARIANCE or COVAR Returns the covariance of a set of number pairs.

MAX Returns the maximum value in a set of values.

MIN Returns the minimum value in a set of values.

RAND Returns a random floating point number between 0 and 1

STDDEV Returns the standard deviation of a set of numbers.

SUM Returns the returns the sum of a set of numbers

VARIANCE or VAR Returns the variance of a set of numbers.

Regression features:

REGR_AVGX Returns quantities used to compute regression diagnostic statistics

REGR_AVGY Returns quantities used to compute regression diagnostic statistics.