FirebirdSQL logo
  • Paragraphs
  • Anglaiskeyboard_arrow_down
 Built-in Scalar FunctionsWindow (Analytical) Functions 

REGR_AVGX()

Average of the independent variable of the regression line

Result type

DOUBLE PRECISION

Syntax
REGR_AVGX ( <y>, <x> )
Table 1. REGR_AVGX Function Parameters
Parameter Description

y

Dependent variable of the regression line.It may contain a table column, a constant, a variable, an expression, a non-aggregate function or a UDF.Aggregate functions are not allowed as expressions.

x

Independent variable of the regression line.It may contain a table column, a constant, a variable, an expression, a non-aggregate function or a UDF.Aggregate functions are not allowed as expressions.

The function REGR_AVGX calculates the average of the independent variable (x) of the regression line.

The function REGR_AVGX(<y>, <x>) is equivalent to

SUM(<exprX>) / REGR_COUNT(<y>, <x>)

<exprX> :==
  CASE WHEN <x> IS NOT NULL AND <y> IS NOT NULL THEN <x> END
See also

[fblangref50-aggfuncs-regr-avgy], [fblangref50-aggfuncs-regr-count], [fblangref50-aggfuncs-sum]

REGR_AVGY()

Average of the dependent variable of the regression line

Result type

DOUBLE PRECISION

Syntax
REGR_AVGY ( <y>, <x> )
Table 1. REGR_AVGY Function Parameters
Parameter Description

y

Dependent variable of the regression line.It may contain a table column, a constant, a variable, an expression, a non-aggregate function or a UDF.Aggregate functions are not allowed as expressions.

x

Independent variable of the regression line.It may contain a table column, a constant, a variable, an expression, a non-aggregate function or a UDF.Aggregate functions are not allowed as expressions.

The function REGR_AVGY calculates the average of the dependent variable (y) of the regression line.

The function REGR_AVGY(<y>, <x>) is equivalent to

SUM(<exprY>) / REGR_COUNT(<y>, <x>)

<exprY> :==
  CASE WHEN <x> IS NOT NULL AND <y> IS NOT NULL THEN <y> END
See also

[fblangref50-aggfuncs-regr-avgx], [fblangref50-aggfuncs-regr-count], [fblangref50-aggfuncs-sum]

docnext count = 8

REGR_COUNT()

Number of non-empty pairs of the regression line

Result type

DOUBLE PRECISION

Syntax
REGR_COUNT ( <y>, <x> )
Table 1. REGR_COUNT Function Parameters
Parameter Description

y

Dependent variable of the regression line.It may contain a table column, a constant, a variable, an expression, a non-aggregate function or a UDF.Aggregate functions are not allowed as expressions.

x

Independent variable of the regression line.It may contain a table column, a constant, a variable, an expression, a non-aggregate function or a UDF.Aggregate functions are not allowed as expressions.

The function REGR_COUNT counts the number of non-empty pairs of the regression line.

The function REGR_COUNT(<y>, <x>) is equivalent to

COUNT(*) FILTER (WHERE <x> IS NOT NULL AND <y> IS NOT NULL)
See also

[fblangref50-aggfuncs-count]

REGR_INTERCEPT()

Point of intersection of the regression line with the y-axis

Result type

DOUBLE PRECISION

Syntax
REGR_INTERCEPT ( <y>, <x> )
Table 1. REGR_INTERCEPT Function Parameters
Parameter Description

y

Dependent variable of the regression line.It may contain a table column, a constant, a variable, an expression, a non-aggregate function or a UDF.Aggregate functions are not allowed as expressions.

x

Independent variable of the regression line.It may contain a table column, a constant, a variable, an expression, a non-aggregate function or a UDF.Aggregate functions are not allowed as expressions.

The function REGR_INTERCEPT calculates the point of intersection of the regression line with the y-axis.

The function REGR_INTERCEPT(<y>, <x>) is equivalent to

REGR_AVGY(<y>, <x>) - REGR_SLOPE(<y>, <x>) * REGR_AVGX(<y>, <x>)

REGR_INTERCEPT Examples

Forecasting sales volume

with recursive years (byyear) as (
  select 1991
  from rdb$database
  union all
  select byyear + 1
  from years
  where byyear < 2020
),
s as (
  select
    extract(year from order_date) as byyear,
    sum(total_value) as total_value
  from sales
  group by 1
),
regr as (
  select
    regr_intercept(total_value, byyear) as intercept,
    regr_slope(total_value, byyear) as slope
  from s
)
select
  years.byyear as byyear,
  intercept + (slope * years.byyear) as total_value
from years
cross join regr
BYYEAR TOTAL_VALUE
------ ------------
  1991    118377.35
  1992    414557.62
  1993    710737.89
  1994   1006918.16
  1995   1303098.43
  1996   1599278.69
  1997   1895458.96
  1998   2191639.23
  1999   2487819.50
  2000   2783999.77
...
See also

[fblangref50-aggfuncs-regr-avgx], [fblangref50-aggfuncs-regr-avgy], [fblangref50-aggfuncs-regr-slope]

REGR_R2()

Coefficient of determination of the regression line

Result type

DOUBLE PRECISION

Syntax
REGR_R2 ( <y>, <x> )
Table 1. REGR_R2 Function Parameters
Parameter Description

y

Dependent variable of the regression line.It may contain a table column, a constant, a variable, an expression, a non-aggregate function or a UDF.Aggregate functions are not allowed as expressions.

x

Independent variable of the regression line.It may contain a table column, a constant, a variable, an expression, a non-aggregate function or a UDF.Aggregate functions are not allowed as expressions.

The REGR_R2 function calculates the coefficient of determination, or R-squared, of the regression line.

The function REGR_R2(<y>, <x>) is equivalent to

POWER(CORR(<y>, <x>), 2)
See also

[fblangref50-aggfuncs-corr], POWER

REGR_SLOPE()

Slope of the regression line

Result type

DOUBLE PRECISION

Syntax
REGR_SLOPE ( <y>, <x> )
Table 1. REGR_SLOPE Function Parameters
Parameter Description

y

Dependent variable of the regression line.It may contain a table column, a constant, a variable, an expression, a non-aggregate function or a UDF.Aggregate functions are not allowed as expressions.

x

Independent variable of the regression line.It may contain a table column, a constant, a variable, an expression, a non-aggregate function or a UDF.Aggregate functions are not allowed as expressions.

The function REGR_SLOPE calculates the slope of the regression line.

The function REGR_SLOPE(<y>, <x>) is equivalent to

COVAR_POP(<y>, <x>) / VAR_POP(<exprX>)

<exprX> :==
  CASE WHEN <x> IS NOT NULL AND <y> IS NOT NULL THEN <x> END
See also

[fblangref50-aggfuncs-covar-pop], [fblangref50-aggfuncs-var-pop]

REGR_SXX()

Sum of squares of the independent variable

Result type

DOUBLE PRECISION

Syntax
REGR_SXX ( <y>, <x> )
Table 1. REGR_SXX Function Parameters
Parameter Description

y

Dependent variable of the regression line.It may contain a table column, a constant, a variable, an expression, a non-aggregate function or a UDF.Aggregate functions are not allowed as expressions.

x

Independent variable of the regression line.It may contain a table column, a constant, a variable, an expression, a non-aggregate function or a UDF.Aggregate functions are not allowed as expressions.

The function REGR_SXX calculates the sum of squares of the independent expression variable (x).

The function REGR_SXX(<y>, <x>) is equivalent to

REGR_COUNT(<y>, <x>) * VAR_POP(<exprX>)

<exprX> :==
  CASE WHEN <x> IS NOT NULL AND <y> IS NOT NULL THEN <x> END
See also

[fblangref50-aggfuncs-regr-count], [fblangref50-aggfuncs-var-pop]

REGR_SXY()

Sum of products of the independent variable and the dependent variable

Result type

DOUBLE PRECISION

Syntax
REGR_SXY ( <y>, <x> )
Table 1. REGR_SXY Function Parameters
Parameter Description

y

Dependent variable of the regression line.It may contain a table column, a constant, a variable, an expression, a non-aggregate function or a UDF.Aggregate functions are not allowed as expressions.

x

Independent variable of the regression line.It may contain a table column, a constant, a variable, an expression, a non-aggregate function or a UDF.Aggregate functions are not allowed as expressions.

The function REGR_SXY calculates the sum of products of independent variable expression (x) times dependent variable expression (y).

The function REGR_SXY(<y>, <x>) is equivalent to

REGR_COUNT(<y>, <x>) * COVAR_POP(<y>, <x>)
See also

[fblangref50-aggfuncs-covar-pop], [fblangref50-aggfuncs-regr-count]

REGR_SYY()

Sum of squares of the dependent variable

Result type

DOUBLE PRECISION

Syntax
REGR_SYY ( <y>, <x> )
Table 1. REGR_SYY Function Parameters
Parameter Description

y

Dependent variable of the regression line.It may contain a table column, a constant, a variable, an expression, a non-aggregate function or a UDF.Aggregate functions are not allowed as expressions.

x

Independent variable of the regression line.It may contain a table column, a constant, a variable, an expression, a non-aggregate function or a UDF.Aggregate functions are not allowed as expressions.

The function REGR_SYY calculates the sum of squares of the dependent variable (y).

The function REGR_SYY(<y>, <x>) is equivalent to

REGR_COUNT(<y>, <x>) * VAR_POP(<exprY>)

<exprY> :==
  CASE WHEN <x> IS NOT NULL AND <y> IS NOT NULL THEN <y> END
See also

[fblangref50-aggfuncs-regr-count], [fblangref50-aggfuncs-var-pop]