The following statistical aggregate functions are available in Google Standard SQL. To learn about the syntax for aggregate function calls, see Aggregate function calls.
CORR
CORR(
X1, X2
)
[ OVER over_clause ]
over_clause:
{ named_window | ( [ window_specification ] ) }
window_specification:
[ named_window ]
[ PARTITION BY partition_expression [, ...] ]
[ ORDER BY expression [ { ASC | DESC } ] [, ...] ]
[ window_frame_clause ]
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.
To learn more about the optional arguments in this function and how to use them, see Aggregate function calls.
To learn more about the OVER clause and how to use it, see
Window function calls.
Return Data Type
FLOAT64
COVAR_POP
COVAR_POP(
X1, X2
)
[ OVER over_clause ]
over_clause:
{ named_window | ( [ window_specification ] ) }
window_specification:
[ named_window ]
[ PARTITION BY partition_expression [, ...] ]
[ ORDER BY expression [ { ASC | DESC } ] [, ...] ]
[ window_frame_clause ]
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.
To learn more about the optional arguments in this function and how to use them, see Aggregate function calls.
To learn more about the OVER clause and how to use it, see
Window function calls.
Return Data Type
FLOAT64
COVAR_SAMP
COVAR_SAMP(
X1, X2
)
[ OVER over_clause ]
over_clause:
{ named_window | ( [ window_specification ] ) }
window_specification:
[ named_window ]
[ PARTITION BY partition_expression [, ...] ]
[ ORDER BY expression [ { ASC | DESC } ] [, ...] ]
[ window_frame_clause ]
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.
To learn more about the optional arguments in this function and how to use them, see Aggregate function calls.
To learn more about the OVER clause and how to use it, see
Window function calls.
Return Data Type
FLOAT64
STDDEV_POP
STDDEV_POP(
[ DISTINCT ]
expression
)
[ OVER over_clause ]
over_clause:
{ named_window | ( [ window_specification ] ) }
window_specification:
[ named_window ]
[ PARTITION BY partition_expression [, ...] ]
[ ORDER BY expression [ { ASC | DESC } ] [, ...] ]
[ window_frame_clause ]
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.
To learn more about the optional arguments in this function and how to use them, see Aggregate function calls.
To learn more about the OVER clause and how to use it, see
Window function calls.
Return Data Type
FLOAT64
STDDEV_SAMP
STDDEV_SAMP(
[ DISTINCT ]
expression
)
[ OVER over_clause ]
over_clause:
{ named_window | ( [ window_specification ] ) }
window_specification:
[ named_window ]
[ PARTITION BY partition_expression [, ...] ]
[ ORDER BY expression [ { ASC | DESC } ] [, ...] ]
[ window_frame_clause ]
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.
To learn more about the optional arguments in this function and how to use them, see Aggregate function calls.
To learn more about the OVER clause and how to use it, see
Window function calls.
Return Data Type
FLOAT64
STDDEV
STDDEV(
[ DISTINCT ]
expression
)
[ OVER over_clause ]
over_clause:
{ named_window | ( [ window_specification ] ) }
window_specification:
[ named_window ]
[ PARTITION BY partition_expression [, ...] ]
[ ORDER BY expression [ { ASC | DESC } ] [, ...] ]
[ window_frame_clause ]
Description
An alias of STDDEV_SAMP.
VAR_POP
VAR_POP(
[ DISTINCT ]
expression
)
[ OVER over_clause ]
over_clause:
{ named_window | ( [ window_specification ] ) }
window_specification:
[ named_window ]
[ PARTITION BY partition_expression [, ...] ]
[ ORDER BY expression [ { ASC | DESC } ] [, ...] ]
[ window_frame_clause ]
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.
To learn more about the OVER clause and how to use it, see
Window function calls.
Return Data Type
FLOAT64
VAR_SAMP
VAR_SAMP(
[ DISTINCT ]
expression
)
[ OVER over_clause ]
over_clause:
{ named_window | ( [ window_specification ] ) }
window_specification:
[ named_window ]
[ PARTITION BY partition_expression [, ...] ]
[ ORDER BY expression [ { ASC | DESC } ] [, ...] ]
[ window_frame_clause ]
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.
To learn more about the optional arguments in this function and how to use them, see Aggregate function calls.
To learn more about the OVER clause and how to use it, see
Window function calls.
Return Data Type
FLOAT64
VARIANCE
VARIANCE(
[ DISTINCT ]
expression
)
[ OVER over_clause ]
over_clause:
{ named_window | ( [ window_specification ] ) }
window_specification:
[ named_window ]
[ PARTITION BY partition_expression [, ...] ]
[ ORDER BY expression [ { ASC | DESC } ] [, ...] ]
[ window_frame_clause ]
Description
An alias of VAR_SAMP.