SQL in a Nutshell, 3rd Edition

Name

The REGR Family of Functions

ANSI SQL 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.

ANSI SQL Standard 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.

Oracle and PostgreSQL

Oracle and PostgreSQL support the ANSI SQL syntax for all REGR_ functions. Oracle also supports the following analytic syntax:

REGR_function(dependent, independent) OVER (window_clause)

For an explanation of the window_clause, see the section later in this chapter titled “ANSI SQL Window Functions.” ...