Statistical aggregate functions
GoogleSQL for BigQuery supports statistical aggregate functions.To learn about the syntax for aggregate function calls, seeAggregate function calls.
Function list
| Name | Summary |
|---|---|
CORR | Computes the Pearson coefficient of correlation of a set of number pairs. |
COVAR_POP | Computes the population covariance of a set of number pairs. |
COVAR_SAMP | Computes the sample covariance of a set of number pairs. |
STDDEV | An alias of theSTDDEV_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 ofVAR_SAMP. |
CORR
CORR(X1,X2)[OVERover_clause]over_clause:{named_window|([window_specification])}window_specification:[named_window][PARTITIONBYpartition_expression[,...]][ORDERBYexpression[{ASC|DESC}][,...]][window_frame_clause]Description
Returns thePearson coefficientof correlation of a set of number pairs. For each number pair, the first numberis the dependent variable and the second number is the independent variable.The return result is between-1 and1. A result of0 indicates nocorrelation.
All numeric types are supported. If theinput isNUMERIC orBIGNUMERIC 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 any input pairs that contain one or moreNULL values. Ifthere are fewer than two input pairs withoutNULL values, this functionreturnsNULL.
NaN is produced if:
- Any input value is
NaN - Any input value is positive infinity or negative infinity.
- The variance of
X1orX2is0. - The covariance of
X1andX2is0.
To learn more about the optional aggregate clauses that you can passinto this function, seeAggregate function calls.
To learn more about theOVER clause and how to use it, seeWindow function calls.
Return Data Type
FLOAT64
Examples
SELECTCORR(y,x)ASresultsFROMUNNEST([STRUCT(1.0ASy,5.0ASx),(3.0,9.0),(4.0,7.0)]);/*--------------------+ | results | +--------------------+ | 0.6546536707079772 | +--------------------*/SELECTCORR(y,x)ASresultsFROMUNNEST([STRUCT(1.0ASy,5.0ASx),(3.0,9.0),(4.0,NULL)]);/*---------+ | results | +---------+ | 1 | +---------*/SELECTCORR(y,x)ASresultsFROMUNNEST([STRUCT(1.0ASy,NULLASx),(9.0,3.0)])/*---------+ | results | +---------+ | NULL | +---------*/SELECTCORR(y,x)ASresultsFROMUNNEST([STRUCT(1.0ASy,NULLASx),(9.0,NULL)])/*---------+ | results | +---------+ | NULL | +---------*/SELECTCORR(y,x)ASresultsFROMUNNEST([STRUCT(1.0ASy,5.0ASx),(3.0,9.0),(4.0,7.0),(5.0,1.0),(7.0,CAST('Infinity'asFLOAT64))])/*---------+ | results | +---------+ | NaN | +---------*/SELECTCORR(x,y)ASresultsFROM(SELECT0ASx,0ASyUNIONALLSELECT0ASx,0ASy)/*---------+ | results | +---------+ | NaN | +---------*/COVAR_POP
COVAR_POP(X1,X2)[OVERover_clause]over_clause:{named_window|([window_specification])}window_specification:[named_window][PARTITIONBYpartition_expression[,...]][ORDERBYexpression[{ASC|DESC}][,...]][window_frame_clause]Description
Returns the populationcovariance ofa set of number pairs. The first number is the dependent variable; the secondnumber is the independent variable. The return result is between-Inf and+Inf.
All numeric types are supported. If theinput isNUMERIC orBIGNUMERIC 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 any input pairs that contain one or moreNULL values. Ifthere is no input pair withoutNULL values, this function returnsNULL.If there is exactly one input pair withoutNULL values, this function returns0.
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 passinto this function, seeAggregate function calls.
This function can be used with theAGGREGATION_THRESHOLD clause.
To learn more about theOVER clause and how to use it, seeWindow function calls.
Return Data Type
FLOAT64
Examples
SELECTCOVAR_POP(y,x)ASresultsFROMUNNEST([STRUCT(1.0ASy,1.0ASx),(2.0,6.0),(9.0,3.0),(2.0,6.0),(9.0,3.0)])/*---------------------+ | results | +---------------------+ | -1.6800000000000002 | +---------------------*/SELECTCOVAR_POP(y,x)ASresultsFROMUNNEST([STRUCT(1.0ASy,NULLASx),(9.0,3.0)])/*---------+ | results | +---------+ | 0 | +---------*/SELECTCOVAR_POP(y,x)ASresultsFROMUNNEST([STRUCT(1.0ASy,NULLASx),(9.0,NULL)])/*---------+ | results | +---------+ | NULL | +---------*/SELECTCOVAR_POP(y,x)ASresultsFROMUNNEST([STRUCT(1.0ASy,1.0ASx),(2.0,6.0),(9.0,3.0),(2.0,6.0),(NULL,3.0)])/*---------+ | results | +---------+ | -1 | +---------*/SELECTCOVAR_POP(y,x)ASresultsFROMUNNEST([STRUCT(1.0ASy,1.0ASx),(2.0,6.0),(9.0,3.0),(2.0,6.0),(CAST('Infinity'asFLOAT64),3.0)])/*---------+ | results | +---------+ | NaN | +---------*/COVAR_SAMP
COVAR_SAMP(X1,X2)[OVERover_clause]over_clause:{named_window|([window_specification])}window_specification:[named_window][PARTITIONBYpartition_expression[,...]][ORDERBYexpression[{ASC|DESC}][,...]][window_frame_clause]Description
Returns the samplecovariance of aset of number pairs. The first number is the dependent variable; the secondnumber is the independent variable. The return result is between-Inf and+Inf.
All numeric types are supported. If theinput isNUMERIC orBIGNUMERIC 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 any input pairs that contain one or moreNULL values. Ifthere are fewer than two input pairs withoutNULL values, this functionreturnsNULL.
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 passinto this function, seeAggregate function calls.
This function can be used with theAGGREGATION_THRESHOLD clause.
To learn more about theOVER clause and how to use it, seeWindow function calls.
Return Data Type
FLOAT64
Examples
SELECTCOVAR_SAMP(y,x)ASresultsFROMUNNEST([STRUCT(1.0ASy,1.0ASx),(2.0,6.0),(9.0,3.0),(2.0,6.0),(9.0,3.0)])/*---------+ | results | +---------+ | -2.1 | +---------*/SELECTCOVAR_SAMP(y,x)ASresultsFROMUNNEST([STRUCT(1.0ASy,1.0ASx),(2.0,6.0),(9.0,3.0),(2.0,6.0),(NULL,3.0)])/*----------------------+ | results | +----------------------+ | --1.3333333333333333 | +----------------------*/SELECTCOVAR_SAMP(y,x)ASresultsFROMUNNEST([STRUCT(1.0ASy,NULLASx),(9.0,3.0)])/*---------+ | results | +---------+ | NULL | +---------*/SELECTCOVAR_SAMP(y,x)ASresultsFROMUNNEST([STRUCT(1.0ASy,NULLASx),(9.0,NULL)])/*---------+ | results | +---------+ | NULL | +---------*/SELECTCOVAR_SAMP(y,x)ASresultsFROMUNNEST([STRUCT(1.0ASy,1.0ASx),(2.0,6.0),(9.0,3.0),(2.0,6.0),(CAST('Infinity'asFLOAT64),3.0)])/*---------+ | results | +---------+ | NaN | +---------*/STDDEV
STDDEV([DISTINCT]expression)[OVERover_clause]over_clause:{named_window|([window_specification])}window_specification:[named_window][PARTITIONBYpartition_expression[,...]][ORDERBYexpression[{ASC|DESC}][,...]][window_frame_clause]Description
An alias ofSTDDEV_SAMP.
STDDEV_POP
STDDEV_POP([DISTINCT]expression)[OVERover_clause]over_clause:{named_window|([window_specification])}window_specification:[named_window][PARTITIONBYpartition_expression[,...]][ORDERBYexpression[{ASC|DESC}][,...]][window_frame_clause]Description
Returns the population (biased) standard deviation of the values. The returnresult is between0 and+Inf.
All numeric types are supported. If theinput isNUMERIC orBIGNUMERIC 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 all inputs are ignored, thisfunction returnsNULL. If this function receives a single non-NULL input,it returns0.
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 passinto this function, seeAggregate function calls.
This function can be used with theAGGREGATION_THRESHOLD clause.
If this function is used with theOVER clause, it's part of awindow function call. In a window function call,aggregate function clauses can't be used.To learn more about theOVER clause and how to use it, seeWindow function calls.
Return Data Type
FLOAT64
Examples
SELECTSTDDEV_POP(x)ASresultsFROMUNNEST([10,14,18])ASx/*-------------------+ | results | +-------------------+ | 3.265986323710904 | +-------------------*/SELECTSTDDEV_POP(x)ASresultsFROMUNNEST([10,14,NULL])ASx/*---------+ | results | +---------+ | 2 | +---------*/SELECTSTDDEV_POP(x)ASresultsFROMUNNEST([10,NULL])ASx/*---------+ | results | +---------+ | 0 | +---------*/SELECTSTDDEV_POP(x)ASresultsFROMUNNEST([NULL])ASx/*---------+ | results | +---------+ | NULL | +---------*/SELECTSTDDEV_POP(x)ASresultsFROMUNNEST([10,14,CAST('Infinity'asFLOAT64)])ASx/*---------+ | results | +---------+ | NaN | +---------*/STDDEV_SAMP
STDDEV_SAMP([DISTINCT]expression)[OVERover_clause]over_clause:{named_window|([window_specification])}window_specification:[named_window][PARTITIONBYpartition_expression[,...]][ORDERBYexpression[{ASC|DESC}][,...]][window_frame_clause]Description
Returns the sample (unbiased) standard deviation of the values. The returnresult is between0 and+Inf.
All numeric types are supported. If theinput isNUMERIC orBIGNUMERIC 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 is
NaN - 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.
This function can be used with theAGGREGATION_THRESHOLD clause.
If this function is used with theOVER clause, it's part of awindow function call. In a window function call,aggregate function clauses can't be used.To learn more about theOVER clause and how to use it, seeWindow 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_POP
VAR_POP([DISTINCT]expression)[OVERover_clause]over_clause:{named_window|([window_specification])}window_specification:[named_window][PARTITIONBYpartition_expression[,...]][ORDERBYexpression[{ASC|DESC}][,...]][window_frame_clause]Description
Returns the population (biased) variance of the values. The return result isbetween0 and+Inf.
All numeric types are supported. If theinput isNUMERIC orBIGNUMERIC 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 all inputs are ignored, thisfunction returnsNULL. If this function receives a single non-NULL input,it returns0.
NaN is produced if:
- Any input value is
NaN - Any input value is positive infinity or negative infinity.
If this function is used with theOVER clause, it's part of awindow function call. In a window function call,aggregate function clauses can't be used.To learn more about theOVER clause and how to use it, seeWindow function calls.
Return Data Type
FLOAT64
Examples
SELECTVAR_POP(x)ASresultsFROMUNNEST([10,14,18])ASx/*--------------------+ | results | +--------------------+ | 10.666666666666666 | +--------------------*/SELECTVAR_POP(x)ASresultsFROMUNNEST([10,14,NULL])ASx/*----------+ | results | +---------+ | 4 | +---------*/SELECTVAR_POP(x)ASresultsFROMUNNEST([10,NULL])ASx/*----------+ | results | +---------+ | 0 | +---------*/SELECTVAR_POP(x)ASresultsFROMUNNEST([NULL])ASx/*---------+ | results | +---------+ | NULL | +---------*/SELECTVAR_POP(x)ASresultsFROMUNNEST([10,14,CAST('Infinity'asFLOAT64)])ASx/*---------+ | results | +---------+ | NaN | +---------*/VAR_SAMP
VAR_SAMP([DISTINCT]expression)[OVERover_clause]over_clause:{named_window|([window_specification])}window_specification:[named_window][PARTITIONBYpartition_expression[,...]][ORDERBYexpression[{ASC|DESC}][,...]][window_frame_clause]Description
Returns the sample (unbiased) variance of the values. The return result isbetween0 and+Inf.
All numeric types are supported. If theinput isNUMERIC orBIGNUMERIC 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 is
NaN - 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.
This function can be used with theAGGREGATION_THRESHOLD clause.
If this function is used with theOVER clause, it's part of awindow function call. In a window function call,aggregate function clauses can't be used.To learn more about theOVER clause and how to use it, seeWindow 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)[OVERover_clause]over_clause:{named_window|([window_specification])}window_specification:[named_window][PARTITIONBYpartition_expression[,...]][ORDERBYexpression[{ASC|DESC}][,...]][window_frame_clause]Description
An alias ofVAR_SAMP.
Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.
Last updated 2025-11-24 UTC.