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.
REGR_AVGX Returns quantities used to compute regression diagnostic statistics
REGR_AVGY Returns quantities used to compute regression diagnostic statistics.