Statistical aggregate functions

GoogleSQL for SecOps supports statistical aggregate functions. To learn about the syntax for aggregate function calls, see Aggregate function calls.

Function list

Name	Summary
STDDEV	An alias of the STDDEV_SAMP function.
STDDEV_POP	Computes the population (biased) standard deviation of the values.
STDDEV_SAMP	Computes the sample (unbiased) standard deviation of the values.
VAR_POP	Computes the population (biased) variance of the values.
VAR_SAMP	Computes the sample (unbiased) variance of the values.
VARIANCE	An alias of VAR_SAMP.

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.

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

NaN is produced if:

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

To learn more about the optional aggregate clauses that you can pass into this function, see Aggregate function calls.

If this function is used with the OVER clause, it's part of a window function call. In a window function call, aggregate function clauses can't be used. To learn more about the OVER clause and how to use it, see Window function calls.

Return Data Type

FLOAT64

Examples

SELECT STDDEV_POP(x) AS results FROM UNNEST([10, 14, 18]) AS x

/*-------------------+
 | results           |
 +-------------------+
 | 3.265986323710904 |
 +-------------------*/

SELECT STDDEV_POP(x) AS results FROM UNNEST([10, 14, NULL]) AS x

/*---------+
 | results |
 +---------+
 | 2       |
 +---------*/

SELECT STDDEV_POP(x) AS results FROM UNNEST([10, NULL]) AS x

/*---------+
 | results |
 +---------+
 | 0       |
 +---------*/

SELECT STDDEV_POP(x) AS results FROM UNNEST([NULL]) AS x

/*---------+
 | results |
 +---------+
 | NULL    |
 +---------*/

SELECT STDDEV_POP(x) AS results FROM UNNEST([10, 14, CAST('Infinity' as FLOAT64)]) AS x

/*---------+
 | results |
 +---------+
 | NaN     |
 +---------*/

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

NaN is produced if:

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

To learn more about the optional aggregate clauses that you can pass into this function, see Aggregate function calls.

If this function is used with the OVER clause, it's part of a window function call. In a window function call, aggregate function clauses can't be used. To learn more about the OVER clause and how to use it, see Window function calls.

Return Data Type

FLOAT64

Examples

SELECT STDDEV_SAMP(x) AS results FROM UNNEST([10, 14, 18]) AS x

/*---------+
 | results |
 +---------+
 | 4       |
 +---------*/

SELECT STDDEV_SAMP(x) AS results FROM UNNEST([10, 14, NULL]) AS x

/*--------------------+
 | results            |
 +--------------------+
 | 2.8284271247461903 |
 +--------------------*/

SELECT STDDEV_SAMP(x) AS results FROM UNNEST([10, NULL]) AS x

/*---------+
 | results |
 +---------+
 | NULL    |
 +---------*/

SELECT STDDEV_SAMP(x) AS results FROM UNNEST([NULL]) AS x

/*---------+
 | results |
 +---------+
 | NULL    |
 +---------*/

SELECT STDDEV_SAMP(x) AS results FROM UNNEST([10, 14, CAST('Infinity' as FLOAT64)]) AS x

/*---------+
 | results |
 +---------+
 | NaN     |
 +---------*/

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

NaN is produced if:

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

If this function is used with the OVER clause, it's part of a window function call. In a window function call, aggregate function clauses can't be used. To learn more about the OVER clause and how to use it, see Window function calls.

Return Data Type

FLOAT64

Examples

SELECT VAR_POP(x) AS results FROM UNNEST([10, 14, 18]) AS x

/*--------------------+
 | results            |
 +--------------------+
 | 10.666666666666666 |
 +--------------------*/

SELECT VAR_POP(x) AS results FROM UNNEST([10, 14, NULL]) AS x

/*----------+
 | results |
 +---------+
 | 4       |
 +---------*/

SELECT VAR_POP(x) AS results FROM UNNEST([10, NULL]) AS x

/*----------+
 | results |
 +---------+
 | 0       |
 +---------*/

SELECT VAR_POP(x) AS results FROM UNNEST([NULL]) AS x

/*---------+
 | results |
 +---------+
 | NULL    |
 +---------*/

SELECT VAR_POP(x) AS results FROM UNNEST([10, 14, CAST('Infinity' as FLOAT64)]) AS x

/*---------+
 | results |
 +---------+
 | NaN     |
 +---------*/

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

NaN is produced if:

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

To learn more about the optional aggregate clauses that you can pass into this function, see Aggregate function calls.

If this function is used with the OVER clause, it's part of a window function call. In a window function call, aggregate function clauses can't be used. To learn more about the OVER clause and how to use it, see Window function calls.

Return Data Type

FLOAT64

Examples

SELECT VAR_SAMP(x) AS results FROM UNNEST([10, 14, 18]) AS x

/*---------+
 | results |
 +---------+
 | 16      |
 +---------*/

SELECT VAR_SAMP(x) AS results FROM UNNEST([10, 14, NULL]) AS x

/*---------+
 | results |
 +---------+
 | 8       |
 +---------*/

SELECT VAR_SAMP(x) AS results FROM UNNEST([10, NULL]) AS x

/*---------+
 | results |
 +---------+
 | NULL    |
 +---------*/

SELECT VAR_SAMP(x) AS results FROM UNNEST([NULL]) AS x

/*---------+
 | results |
 +---------+
 | NULL    |
 +---------*/

SELECT VAR_SAMP(x) AS results FROM UNNEST([10, 14, CAST('Infinity' as FLOAT64)]) AS x

/*---------+
 | results |
 +---------+
 | NaN     |
 +---------*/

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.