GoogleSQL for Bigtable supports the following general aggregate functions. To learn about the syntax for aggregate function calls, see Aggregate function calls.
Function list
Name | Summary |
---|---|
ANY_VALUE
|
Gets an expression for some row. |
APPROX_COUNT_DISTINCT
|
Gets the approximate result for COUNT(DISTINCT expression) .
For more information, see Approximate aggregate functions. |
APPROX_QUANTILES
|
Gets the approximate quantile boundaries.
For more information, see Approximate aggregate functions. |
APPROX_TOP_COUNT
|
Gets the approximate top elements and their approximate count.
For more information, see Approximate aggregate functions. |
APPROX_TOP_SUM
|
Gets the approximate top elements and sum, based on the approximate sum
of an assigned weight.
For more information, see Approximate aggregate functions. |
ARRAY_AGG
|
Gets an array of values. |
ARRAY_CONCAT_AGG
|
Concatenates arrays and returns a single array as a result. |
AVG
|
Gets the average of non-NULL values.
|
BIT_AND
|
Performs a bitwise AND operation on an expression. |
BIT_OR
|
Performs a bitwise OR operation on an expression. |
BIT_XOR
|
Performs a bitwise XOR operation on an expression. |
CORR
|
Computes the Pearson coefficient of correlation of a set of number pairs.
For more information, see Statistical aggregate functions. |
COUNT
|
Gets the number of rows in the input, or the number of rows with an
expression evaluated to any value other than NULL .
|
COUNTIF
|
Gets the number of TRUE values for an expression.
|
COVAR_POP
|
Computes the population covariance of a set of number pairs.
For more information, see Statistical aggregate functions. |
COVAR_SAMP
|
Computes the sample covariance of a set of number pairs.
For more information, see Statistical aggregate functions. |
LOGICAL_AND
|
Gets the logical AND of all non-NULL expressions.
|
LOGICAL_OR
|
Gets the logical OR of all non-NULL expressions.
|
MAX
|
Gets the maximum non-NULL value.
|
MIN
|
Gets the minimum non-NULL value.
|
STDDEV
|
An alias of the STDDEV_SAMP function.
For more information, see Statistical aggregate functions. |
STDDEV_POP
|
Computes the population (biased) standard deviation of the values.
For more information, see Statistical aggregate functions. |
STDDEV_SAMP
|
Computes the sample (unbiased) standard deviation of the values.
For more information, see Statistical aggregate functions. |
STRING_AGG
|
Concatenates non-NULL STRING or
BYTES values.
|
SUM
|
Gets the sum of non-NULL values.
|
VAR_POP
|
Computes the population (biased) variance of the values.
For more information, see Statistical aggregate functions. |
VAR_SAMP
|
Computes the sample (unbiased) variance of the values.
For more information, see Statistical aggregate functions. |
VARIANCE
|
An alias of VAR_SAMP .
For more information, see Statistical aggregate functions. |
ANY_VALUE
ANY_VALUE(
expression
)
Description
Returns expression
for some row chosen from the group. Which row is chosen is
nondeterministic, not random. Returns NULL
when the input produces no
rows. Returns NULL
when expression
is
NULL
for all rows in the group.
ANY_VALUE
behaves as if RESPECT NULLS
is specified;
rows for which expression
is NULL
are considered and may be selected.
Some engines make a best effort to select non-NULL
values whenever possible.
To learn more about the optional aggregate clauses that you can pass into this function, see Aggregate function calls.
Supported Argument Types
Any
Returned Data Types
Matches the input data type.
Examples
SELECT ANY_VALUE(fruit) as any_value
FROM UNNEST(["apple", "banana", "pear"]) as fruit;
/*-----------*
| any_value |
+-----------+
| apple |
*-----------*/
ARRAY_AGG
ARRAY_AGG(
[ DISTINCT ]
expression
)
Description
Returns an ARRAY of expression
values.
To learn more about the optional aggregate clauses that you can pass into this function, see Aggregate function calls.
Supported Argument Types
All data types except ARRAY.
Returned Data Types
ARRAY
If there are zero input rows, this function returns NULL
.
Examples
SELECT ARRAY_AGG(x) AS array_agg FROM UNNEST([2, 1,-2, 3, -2, 1, 2]) AS x;
/*-------------------------*
| array_agg |
+-------------------------+
| [2, 1, -2, 3, -2, 1, 2] |
*-------------------------*/
SELECT ARRAY_AGG(DISTINCT x) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;
/*---------------*
| array_agg |
+---------------+
| [2, 1, -2, 3] |
*---------------*/
SELECT x, ARRAY_AGG(y) AS array_agg
FROM
UNNEST(
ARRAY<STRUCT<x INT64, y STRING>>[(1, 'a'), (1, 'b'), (2, 'a'), (2, 'c')])
GROUP BY x;
/*---------------*
| x | array_agg |
+---------------+
| 1 | [a, b] |
| 2 | [a, c] |
*---------------*/
ARRAY_CONCAT_AGG
ARRAY_CONCAT_AGG(
expression
)
Description
Concatenates elements from expression
of type ARRAY
, returning a single
array as a result.
This function ignores NULL
input arrays, but respects the NULL
elements in
non-NULL
input arrays. Returns NULL
if there are zero input rows or
expression
evaluates to NULL
for all rows.
To learn more about the optional aggregate clauses that you can pass into this function, see Aggregate function calls.
Supported Argument Types
ARRAY
Returned Data Types
ARRAY
Examples
SELECT FORMAT("%T", ARRAY_CONCAT_AGG(x)) AS array_concat_agg FROM (
SELECT [NULL, 1, 2, 3, 4] AS x
UNION ALL SELECT NULL
UNION ALL SELECT [5, 6]
UNION ALL SELECT [7, 8, 9]
);
/*-----------------------------------*
| array_concat_agg |
+-----------------------------------+
| [NULL, 1, 2, 3, 4, 5, 6, 7, 8, 9] |
*-----------------------------------*/
AVG
AVG(
[ DISTINCT ]
expression
)
Description
Returns the average of non-NULL
values in an aggregated group.
To learn more about the optional aggregate clauses that you can pass into this function, see Aggregate function calls.
Caveats:
- If the aggregated group is empty or the argument is
NULL
for all rows in the group, returnsNULL
. - If the argument is
NaN
for any row in the group, returnsNaN
. - If the argument is
[+|-]Infinity
for any row in the group, returns either[+|-]Infinity
orNaN
. - If there is numeric overflow, produces an error.
- If a floating-point type is returned, the result is non-deterministic, which means you might receive a different result each time you use this function.
Supported Argument Types
- Any numeric input type
Returned Data Types
INPUT | INT64 | FLOAT32 | FLOAT64 |
---|---|---|---|
OUTPUT | FLOAT64 | FLOAT64 | FLOAT64 |
Examples
SELECT AVG(x) as avg
FROM UNNEST([0, 2, 4, 4, 5]) as x;
/*-----*
| avg |
+-----+
| 3 |
*-----*/
SELECT AVG(DISTINCT x) AS avg
FROM UNNEST([0, 2, 4, 4, 5]) AS x;
/*------*
| avg |
+------+
| 2.75 |
*------*/
BIT_AND
BIT_AND(
expression
)
Description
Performs a bitwise AND operation on expression
and returns the result.
To learn more about the optional aggregate clauses that you can pass into this function, see Aggregate function calls.
Supported Argument Types
- INT64
Returned Data Types
INT64
Examples
SELECT BIT_AND(x) as bit_and FROM UNNEST([0xF001, 0x00A1]) as x;
/*---------*
| bit_and |
+---------+
| 1 |
*---------*/
BIT_OR
BIT_OR(
expression
)
Description
Performs a bitwise OR operation on expression
and returns the result.
To learn more about the optional aggregate clauses that you can pass into this function, see Aggregate function calls.
Supported Argument Types
- INT64
Returned Data Types
INT64
Examples
SELECT BIT_OR(x) as bit_or FROM UNNEST([0xF001, 0x00A1]) as x;
/*--------*
| bit_or |
+--------+
| 61601 |
*--------*/
BIT_XOR
BIT_XOR(
[ DISTINCT ]
expression
)
Description
Performs a bitwise XOR operation on expression
and returns the result.
To learn more about the optional aggregate clauses that you can pass into this function, see Aggregate function calls.
Supported Argument Types
- INT64
Returned Data Types
INT64
Examples
SELECT BIT_XOR(x) AS bit_xor FROM UNNEST([5678, 1234]) AS x;
/*---------*
| bit_xor |
+---------+
| 4860 |
*---------*/
SELECT BIT_XOR(x) AS bit_xor FROM UNNEST([1234, 5678, 1234]) AS x;
/*---------*
| bit_xor |
+---------+
| 5678 |
*---------*/
SELECT BIT_XOR(DISTINCT x) AS bit_xor FROM UNNEST([1234, 5678, 1234]) AS x;
/*---------*
| bit_xor |
+---------+
| 4860 |
*---------*/
COUNT
COUNT(*)
COUNT(
[ DISTINCT ]
expression
)
Description
Gets the number of rows in the input or the number of rows with an
expression evaluated to any value other than NULL
.
Definitions
*
: Use this value to get the number of all rows in the input.expression
: A value of any data type that represents the expression to evaluate. IfDISTINCT
is present,expression
can only be a data type that is groupable.DISTINCT
: To learn more, see Aggregate function calls.
Details
To count the number of distinct values of an expression for which a certain condition is satisfied, you can use the following recipe:
COUNT(DISTINCT IF(condition, expression, NULL))
IF
returns the value of expression
if condition
is TRUE
, or
NULL
otherwise. The surrounding COUNT(DISTINCT ...)
ignores the NULL
values, so it counts only the distinct values of expression
for which
condition
is TRUE
.
To count the number of non-distinct values of an expression for which a
certain condition is satisfied, consider using the
COUNTIF
function.
This function with DISTINCT
supports specifying collation.
Return type
INT64
Examples
You can use the COUNT
function to return the number of rows in a table or the
number of distinct values of an expression. For example:
SELECT
COUNT(*) AS count_star,
COUNT(DISTINCT x) AS count_dist_x
FROM UNNEST([1, 4, 4, 5]) AS x;
/*------------+--------------*
| count_star | count_dist_x |
+------------+--------------+
| 4 | 3 |
*------------+--------------*/
SELECT COUNT(*) AS count_star, COUNT(x) AS count_x
FROM UNNEST([1, 4, NULL, 4, 5]) AS x;
/*------------+---------*
| count_star | count_x |
+------------+---------+
| 5 | 4 |
*------------+---------*/
The following query counts the number of distinct positive values of x
:
SELECT COUNT(DISTINCT IF(x > 0, x, NULL)) AS distinct_positive
FROM UNNEST([1, -2, 4, 1, -5, 4, 1, 3, -6, 1]) AS x;
/*-------------------*
| distinct_positive |
+-------------------+
| 3 |
*-------------------*/
The following query counts the number of distinct dates on which a certain kind of event occurred:
SELECT
COUNT(DISTINCT IF(event_type = 'FAILURE', event_date, NULL))
AS distinct_dates_with_failures
FROM
UNNEST(
ARRAY<STRUCT<event_date DATE, event_type STRING>>[
(DATE '2021-01-01', 'SUCCESS'),
(DATE '2021-01-02', 'SUCCESS'),
(DATE '2021-01-02', 'FAILURE'),
(DATE '2021-01-03', 'SUCCESS'),
(DATE '2021-01-04', 'FAILURE'),
(DATE '2021-01-04', 'FAILURE')]) AS Events;
/*------------------------------*
| distinct_dates_with_failures |
+------------------------------+
| 2 |
*------------------------------*/
The following query counts the number of distinct id
s that exist in both
the customers
and vendor
tables:
SELECT
COUNT(
DISTINCT
IF(
id IN (
SELECT id
FROM
UNNEST(
ARRAY<STRUCT<id INT64, team STRING>>[
(1934, 'a'),
(2991, 'b'),
(3988, 'c')]) AS customers
),
id,
NULL)) AS result
FROM
UNNEST(
ARRAY<STRUCT<id INT64, team STRING>>[
(1934, 'd'),
(2991, 'e'),
(4366, 'f')]) AS vendors
/*--------*
| result |
+--------+
| 2 |
*--------*/
COUNTIF
COUNTIF(
[ DISTINCT ]
expression
)
Description
Gets the number of TRUE
values for an expression.
Definitions
expression
: ABOOL
value that represents the expression to evaluate.DISTINCT
: To learn more, see Aggregate function calls.
Details
The function signature COUNTIF(DISTINCT ...)
is generally not useful. If you
would like to use DISTINCT
, use COUNT
with DISTINCT IF
. For more
information, see the COUNT
function.
Return type
INT64
Examples
SELECT COUNTIF(x<0) AS num_negative, COUNTIF(x>0) AS num_positive
FROM UNNEST([5, -2, 3, 6, -10, -7, 4, 0]) AS x;
/*--------------+--------------*
| num_negative | num_positive |
+--------------+--------------+
| 3 | 4 |
*--------------+--------------*/
LOGICAL_AND
LOGICAL_AND(
expression
)
Description
Returns the logical AND of all non-NULL
expressions. Returns NULL
if there
are zero input rows or expression
evaluates to NULL
for all rows.
To learn more about the optional aggregate clauses that you can pass into this function, see Aggregate function calls.
Supported Argument Types
BOOL
Return Data Types
BOOL
Examples
LOGICAL_AND
returns FALSE
because not all of the values in the array are
less than 3.
SELECT LOGICAL_AND(x < 3) AS logical_and FROM UNNEST([1, 2, 4]) AS x;
/*-------------*
| logical_and |
+-------------+
| FALSE |
*-------------*/
LOGICAL_OR
LOGICAL_OR(
expression
)
Description
Returns the logical OR of all non-NULL
expressions. Returns NULL
if there
are zero input rows or expression
evaluates to NULL
for all rows.
To learn more about the optional aggregate clauses that you can pass into this function, see Aggregate function calls.
Supported Argument Types
BOOL
Return Data Types
BOOL
Examples
LOGICAL_OR
returns TRUE
because at least one of the values in the array is
less than 3.
SELECT LOGICAL_OR(x < 3) AS logical_or FROM UNNEST([1, 2, 4]) AS x;
/*------------*
| logical_or |
+------------+
| TRUE |
*------------*/
MAX
MAX(
expression
)
Description
Returns the maximum non-NULL
value in an aggregated group.
Caveats:
- If the aggregated group is empty or the argument is
NULL
for all rows in the group, returnsNULL
. - If the argument is
NaN
for any row in the group, returnsNaN
.
To learn more about the optional aggregate clauses that you can pass into this function, see Aggregate function calls.
This function supports specifying collation.
Supported Argument Types
Any orderable data type except for ARRAY
.
Return Data Types
The data type of the input values.
Examples
SELECT MAX(x) AS max
FROM UNNEST([8, 37, 55, 4]) AS x;
/*-----*
| max |
+-----+
| 55 |
*-----*/
MIN
MIN(
expression
)
Description
Returns the minimum non-NULL
value in an aggregated group.
Caveats:
- If the aggregated group is empty or the argument is
NULL
for all rows in the group, returnsNULL
. - If the argument is
NaN
for any row in the group, returnsNaN
.
To learn more about the optional aggregate clauses that you can pass into this function, see Aggregate function calls.
This function supports specifying collation.
Supported Argument Types
Any orderable data type except for ARRAY
.
Return Data Types
The data type of the input values.
Examples
SELECT MIN(x) AS min
FROM UNNEST([8, 37, 4, 55]) AS x;
/*-----*
| min |
+-----+
| 4 |
*-----*/
STRING_AGG
STRING_AGG(
[ DISTINCT ]
expression [, delimiter]
)
Description
Returns a value (either STRING
or BYTES
) obtained by concatenating
non-NULL
values. Returns NULL
if there are zero input rows or expression
evaluates to NULL
for all rows.
If a delimiter
is specified, concatenated values are separated by that
delimiter; otherwise, a comma is used as a delimiter.
To learn more about the optional aggregate clauses that you can pass into this function, see Aggregate function calls.
Supported Argument Types
Either STRING
or BYTES
.
Return Data Types
Either STRING
or BYTES
.
Examples
SELECT STRING_AGG(fruit) AS string_agg
FROM UNNEST(["apple", NULL, "pear", "banana", "pear"]) AS fruit;
/*------------------------*
| string_agg |
+------------------------+
| apple,pear,banana,pear |
*------------------------*/
SELECT STRING_AGG(fruit, " & ") AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;
/*------------------------------*
| string_agg |
+------------------------------+
| apple & pear & banana & pear |
*------------------------------*/
SELECT STRING_AGG(DISTINCT fruit, " & ") AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;
/*-----------------------*
| string_agg |
+-----------------------+
| apple & pear & banana |
*-----------------------*/
SUM
SUM(
[ DISTINCT ]
expression
)
Description
Returns the sum of non-NULL
values in an aggregated group.
To learn more about the optional aggregate clauses that you can pass into this function, see Aggregate function calls.
Caveats:
- If the aggregated group is empty or the argument is
NULL
for all rows in the group, returnsNULL
. - If the argument is
NaN
for any row in the group, returnsNaN
. - If the argument is
[+|-]Infinity
for any row in the group, returns either[+|-]Infinity
orNaN
. - If there is numeric overflow, produces an error.
- If a floating-point type is returned, the result is non-deterministic, which means you might receive a different result each time you use this function.
Supported Argument Types
- Any supported numeric data type
Return Data Types
INPUT | INT64 | FLOAT32 | FLOAT64 |
---|---|---|---|
OUTPUT | INT64 | FLOAT64 | FLOAT64 |
Examples
SELECT SUM(x) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;
/*-----*
| sum |
+-----+
| 25 |
*-----*/
SELECT SUM(DISTINCT x) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;
/*-----*
| sum |
+-----+
| 15 |
*-----*/
SELECT SUM(x) AS sum
FROM UNNEST([]) AS x;
/*------*
| sum |
+------+
| NULL |
*------*/