GoogleSQL for Spanner supports statistical aggregate functions.To learn about the syntax for aggregate function calls, seeAggregate function calls.

Function list

STDDEV

STDDEV([DISTINCT]expression[HAVING{MAX|MIN}having_expression])

Description

An alias ofSTDDEV_SAMP.

STDDEV_SAMP

STDDEV_SAMP([DISTINCT]expression[HAVING{MAX|MIN}having_expression])

Description

Returns the sample (unbiased) standard deviation of the values. The returnresult is between0 and+Inf.

All numeric types are supported. If theinput isNUMERIC then the internal aggregation isstable with the final output converted to aFLOAT64.Otherwise the input is converted to aFLOAT64before aggregation, resulting in a potentially unstable result.

This function ignores anyNULL inputs. If there are fewer than two non-NULLinputs, this function returnsNULL.

NaN is produced if:

• Any input value isNaN
• Any input value is positive infinity or negative infinity.

To learn more about the optional aggregate clauses that you can passinto this function, seeAggregate function calls.

Return Data Type

FLOAT64

Examples

SELECTSTDDEV_SAMP(x)ASresultsFROMUNNEST([10,14,18])ASx/*---------+ | results | +---------+ | 4       | +---------*/

SELECTSTDDEV_SAMP(x)ASresultsFROMUNNEST([10,14,NULL])ASx/*--------------------+ | results            | +--------------------+ | 2.8284271247461903 | +--------------------*/

SELECTSTDDEV_SAMP(x)ASresultsFROMUNNEST([10,NULL])ASx/*---------+ | results | +---------+ | NULL    | +---------*/

SELECTSTDDEV_SAMP(x)ASresultsFROMUNNEST([NULL])ASx/*---------+ | results | +---------+ | NULL    | +---------*/

SELECTSTDDEV_SAMP(x)ASresultsFROMUNNEST([10,14,CAST('Infinity'asFLOAT64)])ASx/*---------+ | results | +---------+ | NaN     | +---------*/

VAR_SAMP

VAR_SAMP([DISTINCT]expression[HAVING{MAX|MIN}having_expression])

Description

Returns the sample (unbiased) variance of the values. The return result isbetween0 and+Inf.

All numeric types are supported. If theinput isNUMERIC then the internal aggregation isstable with the final output converted to aFLOAT64.Otherwise the input is converted to aFLOAT64before aggregation, resulting in a potentially unstable result.

This function ignores anyNULL inputs. If there are fewer than two non-NULLinputs, this function returnsNULL.

NaN is produced if:

• Any input value isNaN
• Any input value is positive infinity or negative infinity.

To learn more about the optional aggregate clauses that you can passinto this function, seeAggregate function calls.

Return Data Type

FLOAT64

Examples

SELECTVAR_SAMP(x)ASresultsFROMUNNEST([10,14,18])ASx/*---------+ | results | +---------+ | 16      | +---------*/

SELECTVAR_SAMP(x)ASresultsFROMUNNEST([10,14,NULL])ASx/*---------+ | results | +---------+ | 8       | +---------*/

SELECTVAR_SAMP(x)ASresultsFROMUNNEST([10,NULL])ASx/*---------+ | results | +---------+ | NULL    | +---------*/

SELECTVAR_SAMP(x)ASresultsFROMUNNEST([NULL])ASx/*---------+ | results | +---------+ | NULL    | +---------*/

SELECTVAR_SAMP(x)ASresultsFROMUNNEST([10,14,CAST('Infinity'asFLOAT64)])ASx/*---------+ | results | +---------+ | NaN     | +---------*/

VARIANCE

VARIANCE([DISTINCT]expression[HAVING{MAX|MIN}having_expression])

Description

An alias ofVAR_SAMP.