SQL in a Nutshell, 2nd Edition

Name

The REGR Family of Functions

Synopsis

SQL2003 defines a family of functions, having names beginning with REGR_, that relate to different aspects of linear regression. The functions work in the context of a least-squares regression line.

SQL2003 Syntax

Following is the syntax and a brief description of each REGR_ function:

REGR_AVGX( dependent, independent )

Averages (as in AVG( x )) the independent variable values.

REGR_AVGY( dependent, independent )

Averages (as in AVG( y )) the dependent variable values.

REGR_COUNT( dependent, independent )

Counts the number of non-NULL number pairs.

REGR_INTERCEPT( dependent, independent )

Computes the y-intercept of the regression line.

REGR_R2( dependent, independent )

Computes the coefficient of determination.

REGR_SLOPE( dependent, independent )

Computes the slope of the regression line.

REGR_SXX( dependent, independent )

Sums the squares of the independent variable values.

REGR_SXY( dependent, independent )

Sums the products of each pair of values.

REGR_SYY( dependent, independent )

Sums the squares of the dependent variable values.

The REGR_ functions only work on number pairs containing two non-NULL values. Any number pair with one or more NULL values will be ignored.

DB2 and Oracle

DB2 and Oracle support the SQL2003 syntax for all REGR_ functions. In addition, DB2 allows the shortened name REGR_ICPT in place of REGR_INTERCEPT.

Oracle supports the following analytic syntax:

REGR_function ( dependent, independent ) OVER (window_clause)

For ...