9.2 Statistical Aggregate Functions
9.2.1 CORR
Available inDSQL, PSQL
Result typeDOUBLE PRECISION
Syntax
CORR ( <expr1>, <expr2> )
CORR
Function ParametersParameter | Description |
---|---|
exprN | Numeric expression. 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 CORR
function return the correlation coefficient for a pair of numerical expressions.
The function CORR(<expr1>, <expr2>)
is equivalent to
COVAR_POP(<expr1>, <expr2>) / (STDDEV_POP(<expr2>) * STDDEV_POP(<expr1>))
This is also known as the Pearson correlation coefficient.
In a statistical sense, correlation is the degree of to which a pair of variables are linearly related. A linear relation between variables means that the value of one variable can to a certain extent predict the value of the other. The correlation coefficient represents the degree of correlation as a number ranging from -1 (high inverse correlation) to 1 (high correlation). A value of 0 corresponds to no correlation.
If the group or window is empty, or contains only NULL
values, the result will be NULL
.
9.2.1.1 CORR
Examples
select
corr(alength, aheight) AS c_corr
from measure
9.2.2 COVAR_POP
Available inDSQL, PSQL
Result typeDOUBLE PRECISION
Syntax
COVAR_POP ( <expr1>, <expr2> )
COVAR_POP
Function ParametersParameter | Description |
---|---|
exprN | Numeric expression. 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 COVAR_POP
returns the population covariance for a pair of numerical expressions.
The function COVAR_POP(<expr1>, <expr2>)
is equivalent to
(SUM(<expr1> * <expr2>) - SUM(<expr1>) * SUM(<expr2>) / COUNT(*)) / COUNT(*)
If the group or window is empty, or contains only NULL
values, the result will be NULL
.
9.2.2.1 COVAR_POP
Examples
select
covar_pop(alength, aheight) AS c_covar_pop
from measure
See alsoSection 9.2.3, COVAR_SAMP
, Section 9.1.6, SUM()
, Section 9.1.2, COUNT()
9.2.3 COVAR_SAMP
Available inDSQL, PSQL
Result typeDOUBLE PRECISION
Syntax
COVAR_SAMP ( <expr1>, <expr2> )
COVAR_SAMP
Function ParametersParameter | Description |
---|---|
exprN | Numeric expression. 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 COVAR_SAMP
returns the sample covariance for a pair of numerical expressions.
The function COVAR_SAMP(<expr1>, <expr2>)
is equivalent to
(SUM(<expr1> * <expr2>) - SUM(<expr1>) * SUM(<expr2>) / COUNT(*)) / (COUNT(*) - 1)
If the group or window is empty, contains only 1 row, or contains only NULL
values, the result will be NULL
.
9.2.3.1 COVAR_SAMP
Examples
select
covar_samp(alength, aheight) AS c_covar_samp
from measure
See alsoSection 9.2.2, COVAR_POP
, Section 9.1.6, SUM()
, Section 9.1.2, COUNT()
9.2.4 STDDEV_POP
Available inDSQL, PSQL
Result typeDOUBLE PRECISION
or NUMERIC
depending on the type of expr
Syntax
STDDEV_POP ( <expr> )
STDDEV_POP
Function ParametersParameter | Description |
---|---|
expr | Numeric expression. 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 STDDEV_POP
returns the population standard deviation for a group or window.
NULL
values are skipped.
The function STDDEV_POP(<expr>)
is equivalent to
SQRT(VAR_POP(<expr>))
If the group or window is empty, or contains only NULL
values, the result will be NULL
.
9.2.4.1 STDDEV_POP
Examples
select
dept_no
stddev_pop(salary)
from employee
group by dept_no
See alsoSection 9.2.5, STDDEV_SAMP
, Section 9.2.6, VAR_POP
, SQRT
9.2.5 STDDEV_SAMP
Available inDSQL, PSQL
Result typeDOUBLE PRECISION
or NUMERIC
depending on the type of expr
Syntax
STDDEV_POP ( <expr> )
STDDEV_SAMP
Function ParametersParameter | Description |
---|---|
expr | Numeric expression. 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 STDDEV_SAMP
returns the sample standard deviation for a group or window.
NULL
values are skipped.
The function STDDEV_SAMP(<expr>)
is equivalent to
SQRT(VAR_SAMP(<expr>))
If the group or window is empty, contains only 1 row, or contains only NULL
values, the result will be NULL
.
9.2.5.1 STDDEV_SAMP
Examples
select
dept_no
stddev_samp(salary)
from employee
group by dept_no
See alsoSection 9.2.4, STDDEV_POP
, Section 9.2.7, VAR_SAMP
, SQRT
9.2.6 VAR_POP
Available inDSQL, PSQL
Result typeDOUBLE PRECISION
or NUMERIC
depending on the type of expr
Syntax
VAR_POP ( <expr> )
VAR_POP
Function ParametersParameter | Description |
---|---|
expr | Numeric expression. 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 VAR_POP
returns the population variance for a group or window.
NULL
values are skipped.
The function VAR_POP(<expr>)
is equivalent to
(SUM(<expr> * <expr>) - SUM (<expr>) * SUM (<expr>) / COUNT(<expr>))
/ COUNT (<expr>)
If the group or window is empty, or contains only NULL
values, the result will be NULL
.
9.2.6.1 VAR_POP
Examples
select
dept_no
var_pop(salary)
from employee
group by dept_no
See alsoSection 9.2.7, VAR_SAMP
, Section 9.1.6, SUM()
, Section 9.1.2, COUNT()
9.2.7 VAR_SAMP
Available inDSQL, PSQL
Result typeDOUBLE PRECISION
or NUMERIC
depending on the type of expr
Syntax
VAR_SAMP ( <expr> )
VAR_SAMP
Function ParametersParameter | Description |
---|---|
expr | Numeric expression. 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 VAR_POP
returns the sample variance for a group or window.
NULL
values are skipped.
The function VAR_SAMP(<expr>)
is equivalent to
(SUM(<expr> * <expr>) - SUM(<expr>) * SUM (<expr>) / COUNT (<expr>))
/ (COUNT(<expr>) - 1)
If the group or window is empty, contains only 1 row, or contains only NULL
values, the result will be NULL
.
9.2.7.1 VAR_SAMP
Examples
select
dept_no
var_samp(salary)
from employee
group by dept_no
See alsoSection 9.2.6, VAR_POP
, Section 9.1.6, SUM()
, Section 9.1.2, COUNT()