BigQuery supports the following statistical aggregate functions.
CORR
CORR(X1, X2) [OVER (...)]
Description
Returns the Pearson coefficient
of correlation of a set of number pairs. For each number pair, the first number
is the dependent variable and the second number is the independent variable.
The return result is between -1
and 1
. A result of 0
indicates no
correlation.
All numeric types are supported. If the
input is NUMERIC
or BIGNUMERIC
then the internal aggregation is
stable with the final output converted to a FLOAT64
.
Otherwise the input is converted to a FLOAT64
before aggregation, resulting in a potentially unstable result.
This function ignores any input pairs that contain one or more NULL values. If there are fewer than two input pairs without NULL values, this function returns NULL.
Optional Clause
OVER
: Specifies a window. See
Analytic Functions.
Return Data Type
FLOAT64
COVAR_POP
COVAR_POP(X1, X2) [OVER (...)]
Description
Returns the population covariance of
a set of number pairs. The first number is the dependent variable; the second
number is the independent variable. The return result is between -Inf
and
+Inf
.
All numeric types are supported. If the
input is NUMERIC
or BIGNUMERIC
then the internal aggregation is
stable with the final output converted to a FLOAT64
.
Otherwise the input is converted to a FLOAT64
before aggregation, resulting in a potentially unstable result.
This function ignores any input pairs that contain one or more NULL values. If there is no input pair without NULL values, this function returns NULL. If there is exactly one input pair without NULL values, this function returns 0.
Optional Clause
OVER
: Specifies a window. See
Analytic Functions.
Return Data Type
FLOAT64
COVAR_SAMP
COVAR_SAMP(X1, X2) [OVER (...)]
Description
Returns the sample covariance of a
set of number pairs. The first number is the dependent variable; the second
number is the independent variable. The return result is between -Inf
and
+Inf
.
All numeric types are supported. If the
input is NUMERIC
or BIGNUMERIC
then the internal aggregation is
stable with the final output converted to a FLOAT64
.
Otherwise the input is converted to a FLOAT64
before aggregation, resulting in a potentially unstable result.
This function ignores any input pairs that contain one or more NULL values. If there are fewer than two input pairs without NULL values, this function returns NULL.
Optional Clause
OVER
: Specifies a window. See
Analytic Functions.
Return Data Type
FLOAT64
STDDEV_POP
STDDEV_POP([DISTINCT] expression) [OVER (...)]
Description
Returns the population (biased) standard deviation of the values. The return
result is between 0
and +Inf
.
All numeric types are supported. If the
input is NUMERIC
or BIGNUMERIC
then the internal aggregation is
stable with the final output converted to a FLOAT64
.
Otherwise the input is converted to a FLOAT64
before aggregation, resulting in a potentially unstable result.
This function ignores any NULL inputs. If all inputs are ignored, this function returns NULL.
If this function receives a single non-NULL input, it returns 0
.
Optional Clauses
The clauses are applied in the following order:
OVER
: Specifies a window. See Analytic Functions. This clause is currently incompatible with all other clauses withinSTDDEV_POP()
.DISTINCT
: Each distinct value ofexpression
is aggregated only once into the result.
Return Data Type
FLOAT64
STDDEV_SAMP
STDDEV_SAMP([DISTINCT] expression) [OVER (...)]
Description
Returns the sample (unbiased) standard deviation of the values. The return
result is between 0
and +Inf
.
All numeric types are supported. If the
input is NUMERIC
or BIGNUMERIC
then the internal aggregation is
stable with the final output converted to a FLOAT64
.
Otherwise the input is converted to a FLOAT64
before aggregation, resulting in a potentially unstable result.
This function ignores any NULL inputs. If there are fewer than two non-NULL inputs, this function returns NULL.
Optional Clauses
The clauses are applied in the following order:
OVER
: Specifies a window. See Analytic Functions. This clause is currently incompatible with all other clauses withinSTDDEV_SAMP()
.DISTINCT
: Each distinct value ofexpression
is aggregated only once into the result.
Return Data Type
FLOAT64
STDDEV
STDDEV([DISTINCT] expression) [OVER (...)]
Description
An alias of STDDEV_SAMP.
VAR_POP
VAR_POP([DISTINCT] expression) [OVER (...)]
Description
Returns the population (biased) variance of the values. The return result is
between 0
and +Inf
.
All numeric types are supported. If the
input is NUMERIC
or BIGNUMERIC
then the internal aggregation is
stable with the final output converted to a FLOAT64
.
Otherwise the input is converted to a FLOAT64
before aggregation, resulting in a potentially unstable result.
This function ignores any NULL inputs. If all inputs are ignored, this function returns NULL.
If this function receives a single non-NULL input, it returns 0
.
Optional Clauses
The clauses are applied in the following order:
OVER
: Specifies a window. See Analytic Functions. This clause is currently incompatible with all other clauses withinVAR_POP()
.DISTINCT
: Each distinct value ofexpression
is aggregated only once into the result.
Return Data Type
FLOAT64
VAR_SAMP
VAR_SAMP([DISTINCT] expression) [OVER (...)]
Description
Returns the sample (unbiased) variance of the values. The return result is
between 0
and +Inf
.
All numeric types are supported. If the
input is NUMERIC
or BIGNUMERIC
then the internal aggregation is
stable with the final output converted to a FLOAT64
.
Otherwise the input is converted to a FLOAT64
before aggregation, resulting in a potentially unstable result.
This function ignores any NULL inputs. If there are fewer than two non-NULL inputs, this function returns NULL.
Optional Clauses
The clauses are applied in the following order:
OVER
: Specifies a window. See Analytic Functions. This clause is currently incompatible with all other clauses withinVAR_SAMP()
.DISTINCT
: Each distinct value ofexpression
is aggregated only once into the result.
Return Data Type
FLOAT64
VARIANCE
VARIANCE([DISTINCT] expression) [OVER (...)]
Description
An alias of VAR_SAMP.