Statistical aggregate functions

Cloud Spanner SQL supports the following statistical aggregate functions.

STDDEV_SAMP

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

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 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:

  1. DISTINCT: Each distinct value of expression is aggregated only once into the result.
  2. HAVING MAX or HAVING MIN: Restricts the set of rows that the function aggregates by a maximum or minimum value. See HAVING MAX and HAVING MIN clause for details.

Return Data Type

FLOAT64

STDDEV

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

Description

An alias of STDDEV_SAMP.

VAR_SAMP

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

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 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:

  1. DISTINCT: Each distinct value of expression is aggregated only once into the result.
  2. HAVING MAX or HAVING MIN: Restricts the set of rows that the function aggregates by a maximum or minimum value. See HAVING MAX and HAVING MIN clause for details.

Return Data Type

FLOAT64

VARIANCE

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

Description

An alias of VAR_SAMP.