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
)) theindependent
variable values- REGR_AVGY(
dependent
,independent
) Averages (as in AVG(
y
)) thedependent
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.” ...
Get SQL in a Nutshell, 3rd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.