GoogleSQL for BigQuery 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. |
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. |
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 count of TRUE values for an expression.
|
GROUPING
|
Checks if a groupable value in the GROUP BY clause is
aggregated.
|
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.
|
MAX_BY
|
Synonym for ANY_VALUE(x HAVING MAX y) .
|
MIN
|
Gets the minimum non-NULL value.
|
MIN_BY
|
Synonym for ANY_VALUE(x HAVING MIN y) .
|
STRING_AGG
|
Concatenates non-NULL STRING or
BYTES values.
|
SUM
|
Gets the sum of non-NULL values.
|
ANY_VALUE
ANY_VALUE(
expression
[ HAVING { MAX | MIN } expression2 ]
)
[ 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 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
or expression2
is
NULL
for all rows in the group.
ANY_VALUE
behaves as if IGNORE NULLS
is specified;
rows for which expression
is NULL
are not considered and won't be
selected.
If the HAVING
clause is included in the ANY_VALUE
function, the
OVER
clause can't be used with this function.
To learn more about the optional aggregate clauses that you can pass into this function, see Aggregate function calls.
To learn more about the OVER
clause and how to use it, see
Window 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 |
*-----------*/
SELECT
fruit,
ANY_VALUE(fruit) OVER (ORDER BY LENGTH(fruit) ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS any_value
FROM UNNEST(["apple", "banana", "pear"]) as fruit;
/*--------+-----------*
| fruit | any_value |
+--------+-----------+
| pear | pear |
| apple | pear |
| banana | apple |
*--------+-----------*/
WITH
Store AS (
SELECT 20 AS sold, "apples" AS fruit
UNION ALL
SELECT 30 AS sold, "pears" AS fruit
UNION ALL
SELECT 30 AS sold, "bananas" AS fruit
UNION ALL
SELECT 10 AS sold, "oranges" AS fruit
)
SELECT ANY_VALUE(fruit HAVING MAX sold) AS a_highest_selling_fruit FROM Store;
/*-------------------------*
| a_highest_selling_fruit |
+-------------------------+
| pears |
*-------------------------*/
WITH
Store AS (
SELECT 20 AS sold, "apples" AS fruit
UNION ALL
SELECT 30 AS sold, "pears" AS fruit
UNION ALL
SELECT 30 AS sold, "bananas" AS fruit
UNION ALL
SELECT 10 AS sold, "oranges" AS fruit
)
SELECT ANY_VALUE(fruit HAVING MIN sold) AS a_lowest_selling_fruit FROM Store;
/*-------------------------*
| a_lowest_selling_fruit |
+-------------------------+
| oranges |
*-------------------------*/
ARRAY_AGG
ARRAY_AGG(
[ DISTINCT ]
expression
[ { IGNORE | RESPECT } NULLS ]
[ ORDER BY key [ { ASC | DESC } ] [, ... ] ]
[ LIMIT n ]
)
[ 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 an ARRAY of expression
values.
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.
An error is raised if an array in the final query result contains a NULL
element.
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 ARRAY_AGG(x IGNORE NULLS) AS array_agg
FROM UNNEST([NULL, 1, -2, 3, -2, 1, NULL]) AS x;
/*-------------------*
| array_agg |
+-------------------+
| [1, -2, 3, -2, 1] |
*-------------------*/
SELECT ARRAY_AGG(x ORDER BY ABS(x)) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;
/*-------------------------*
| array_agg |
+-------------------------+
| [1, 1, 2, -2, -2, 2, 3] |
*-------------------------*/
SELECT ARRAY_AGG(x LIMIT 5) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;
/*-------------------*
| array_agg |
+-------------------+
| [2, 1, -2, 3, -2] |
*-------------------*/
WITH vals AS
(
SELECT 1 x UNION ALL
SELECT -2 x UNION ALL
SELECT 3 x UNION ALL
SELECT -2 x UNION ALL
SELECT 1 x
)
SELECT ARRAY_AGG(DISTINCT x ORDER BY x) as array_agg
FROM vals;
/*------------*
| array_agg |
+------------+
| [-2, 1, 3] |
*------------*/
WITH vals AS
(
SELECT 1 x, 'a' y UNION ALL
SELECT 1 x, 'b' y UNION ALL
SELECT 2 x, 'a' y UNION ALL
SELECT 2 x, 'c' y
)
SELECT x, ARRAY_AGG(y) as array_agg
FROM vals
GROUP BY x;
/*---------------*
| x | array_agg |
+---------------+
| 1 | [a, b] |
| 2 | [a, c] |
*---------------*/
SELECT
x,
ARRAY_AGG(x) OVER (ORDER BY ABS(x)) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;
/*----+-------------------------*
| x | array_agg |
+----+-------------------------+
| 1 | [1, 1] |
| 1 | [1, 1] |
| 2 | [1, 1, 2, -2, -2, 2] |
| -2 | [1, 1, 2, -2, -2, 2] |
| -2 | [1, 1, 2, -2, -2, 2] |
| 2 | [1, 1, 2, -2, -2, 2] |
| 3 | [1, 1, 2, -2, -2, 2, 3] |
*----+-------------------------*/
ARRAY_CONCAT_AGG
ARRAY_CONCAT_AGG(
expression
[ ORDER BY key [ { ASC | DESC } ] [, ... ] ]
[ LIMIT n ]
)
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. An
error is raised, however, if an array in the final query result contains a
NULL
element. 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] |
*-----------------------------------*/
SELECT FORMAT("%T", ARRAY_CONCAT_AGG(x ORDER BY ARRAY_LENGTH(x))) AS array_concat_agg FROM (
SELECT [1, 2, 3, 4] AS x
UNION ALL SELECT [5, 6]
UNION ALL SELECT [7, 8, 9]
);
/*-----------------------------------*
| array_concat_agg |
+-----------------------------------+
| [5, 6, 7, 8, 9, 1, 2, 3, 4] |
*-----------------------------------*/
SELECT FORMAT("%T", ARRAY_CONCAT_AGG(x LIMIT 2)) AS array_concat_agg FROM (
SELECT [1, 2, 3, 4] AS x
UNION ALL SELECT [5, 6]
UNION ALL SELECT [7, 8, 9]
);
/*--------------------------*
| array_concat_agg |
+--------------------------+
| [1, 2, 3, 4, 5, 6] |
*--------------------------*/
SELECT FORMAT("%T", ARRAY_CONCAT_AGG(x ORDER BY ARRAY_LENGTH(x) LIMIT 2)) AS array_concat_agg FROM (
SELECT [1, 2, 3, 4] AS x
UNION ALL SELECT [5, 6]
UNION ALL SELECT [7, 8, 9]
);
/*------------------*
| array_concat_agg |
+------------------+
| [5, 6, 7, 8, 9] |
*------------------*/
AVG
AVG(
[ 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 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.
This function can be used with the
AGGREGATION_THRESHOLD
clause.
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.
AVG
can be used with differential privacy. For more information, see
Differentially private aggregate functions.
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
INTERVAL
Returned Data Types
INPUT | INT64 | NUMERIC | BIGNUMERIC | FLOAT64 | INTERVAL |
---|---|---|---|---|---|
OUTPUT | FLOAT64 | NUMERIC | BIGNUMERIC | FLOAT64 | INTERVAL |
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 |
*------*/
SELECT
x,
AVG(x) OVER (ORDER BY x ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS avg
FROM UNNEST([0, 2, NULL, 4, 4, 5]) AS x;
/*------+------*
| x | avg |
+------+------+
| NULL | NULL |
| 0 | 0 |
| 2 | 1 |
| 4 | 3 |
| 4 | 4 |
| 5 | 4.5 |
*------+------*/
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
1.
COUNT(*)
[OVER over_clause]
2.
COUNT(
[ 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 number of rows in the input.
- Returns the number of rows with
expression
evaluated to any value other thanNULL
.
To learn more about the optional aggregate clauses that you can pass into this function, see Aggregate function calls.
This function can be used with the
AGGREGATION_THRESHOLD
clause.
To learn more about the OVER
clause and how to use it, see
Window function calls.
This function with DISTINCT supports specifying collation.
COUNT
can be used with differential privacy. For more information, see
Differentially private aggregate functions.
Supported Argument Types
expression
can be any data type. If
DISTINCT
is present, expression
can only be a data type that is
groupable.
Return Data Types
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
x,
COUNT(*) OVER (PARTITION BY MOD(x, 3)) AS count_star,
COUNT(DISTINCT x) OVER (PARTITION BY MOD(x, 3)) AS count_dist_x
FROM UNNEST([1, 4, 4, 5]) AS x;
/*------+------------+--------------*
| x | count_star | count_dist_x |
+------+------------+--------------+
| 1 | 3 | 2 |
| 4 | 3 | 2 |
| 4 | 3 | 2 |
| 5 | 1 | 1 |
*------+------------+--------------*/
SELECT
x,
COUNT(*) OVER (PARTITION BY MOD(x, 3)) AS count_star,
COUNT(x) OVER (PARTITION BY MOD(x, 3)) AS count_x
FROM UNNEST([1, 4, NULL, 4, 5]) AS x;
/*------+------------+---------*
| x | count_star | count_x |
+------+------------+---------+
| NULL | 1 | 0 |
| 1 | 3 | 3 |
| 4 | 3 | 3 |
| 4 | 3 | 3 |
| 5 | 1 | 1 |
*------+------------+---------*/
If you want to count the number of distinct values of an expression for which a certain condition is satisfied, this is one recipe that you can use:
COUNT(DISTINCT IF(condition, expression, NULL))
Here, IF
will return the value of expression
if condition
is TRUE
, or
NULL
otherwise. The surrounding COUNT(DISTINCT ...)
will ignore the NULL
values, so it will count only the distinct values of expression
for which
condition
is TRUE
.
For example, to count 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 |
*-------------------*/
Or to count the number of distinct dates on which a certain kind of event occurred:
WITH Events AS (
SELECT DATE '2021-01-01' AS event_date, 'SUCCESS' AS event_type
UNION ALL
SELECT DATE '2021-01-02' AS event_date, 'SUCCESS' AS event_type
UNION ALL
SELECT DATE '2021-01-02' AS event_date, 'FAILURE' AS event_type
UNION ALL
SELECT DATE '2021-01-03' AS event_date, 'SUCCESS' AS event_type
UNION ALL
SELECT DATE '2021-01-04' AS event_date, 'FAILURE' AS event_type
UNION ALL
SELECT DATE '2021-01-04' AS event_date, 'FAILURE' AS event_type
)
SELECT
COUNT(DISTINCT IF(event_type = 'FAILURE', event_date, NULL))
AS distinct_dates_with_failures
FROM Events;
/*------------------------------*
| distinct_dates_with_failures |
+------------------------------+
| 2 |
*------------------------------*/
COUNTIF
COUNTIF(
[ 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 count of TRUE
values for expression
. Returns 0
if there are
zero input rows, or if expression
evaluates to FALSE
or NULL
for all rows.
Since expression
must be a BOOL
, the form COUNTIF(DISTINCT ...)
is
generally not useful: there is only one distinct value of TRUE
. So
COUNTIF(DISTINCT ...)
will return 1 if expression
evaluates to TRUE
for
one or more input rows, or 0 otherwise.
Usually when someone wants to combine COUNTIF
and DISTINCT
, they
want to count the number of distinct values of an expression for which a certain
condition is satisfied. One recipe to achieve this is the following:
COUNT(DISTINCT IF(condition, expression, NULL))
Note that this uses COUNT
, not COUNTIF
; the IF
part has been moved inside.
To learn more, see the examples for COUNT
.
To learn more about the optional aggregate clauses that you can pass into this function, see Aggregate function calls.
This function can be used with the
AGGREGATION_THRESHOLD
clause.
To learn more about the OVER
clause and how to use it, see
Window function calls.
Supported Argument Types
BOOL
Return Data Types
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 |
*--------------+--------------*/
SELECT
x,
COUNTIF(x<0) OVER (ORDER BY ABS(x) ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS num_negative
FROM UNNEST([5, -2, 3, 6, -10, NULL, -7, 4, 0]) AS x;
/*------+--------------*
| x | num_negative |
+------+--------------+
| NULL | 0 |
| 0 | 1 |
| -2 | 1 |
| 3 | 1 |
| 4 | 0 |
| 5 | 0 |
| 6 | 1 |
| -7 | 2 |
| -10 | 2 |
*------+--------------*/
GROUPING
GROUPING(groupable_value)
Description
If a groupable item in the GROUP BY
clause is aggregated
(and thus not grouped), this function returns 1
. Otherwise,
this function returns 0
.
Definitions:
groupable_value
: An expression that represents a value that can be grouped in theGROUP BY
clause.
Details:
The GROUPING
function is helpful if you need to determine which rows are
produced by which grouping sets. A grouping set is a group of columns by which
rows can be grouped together. So, if you need to filter rows by
a few specific grouping sets, you can use the GROUPING
function to identify
which grouping sets grouped which rows by creating a matrix of the results.
In addition, you can use the GROUPING
function to determine the type of
NULL
produced by the GROUP BY
clause. In some cases, the GROUP BY
clause
produces a NULL
placeholder. This placeholder represents all groupable items
that are aggregated (not grouped) in the current grouping set. This is different
from a standard NULL
, which can also be produced by a query.
For more information, see the following examples.
Returned Data Type
INT64
Examples
In the following example, it's difficult to determine which rows are grouped by
the grouping value product_type
or product_name
. The GROUPING
function
makes this easier to determine.
Pay close attention to what's in the product_type_agg
and
product_name_agg
column matrix. This determines how the rows are grouped.
product_type_agg |
product_name_agg |
Notes |
---|---|---|
1 | 0 | Rows are grouped by product_name . |
0 | 1 | Rows are grouped by product_type . |
0 | 0 | Rows are grouped by product_type and product_name . |
1 | 1 | Grand total row. |
WITH
Products AS (
SELECT 'shirt' AS product_type, 't-shirt' AS product_name, 3 AS product_count UNION ALL
SELECT 'shirt', 't-shirt', 8 UNION ALL
SELECT 'shirt', 'polo', 25 UNION ALL
SELECT 'pants', 'jeans', 6
)
SELECT
product_type,
product_name,
SUM(product_count) AS product_sum,
GROUPING(product_type) AS product_type_agg,
GROUPING(product_name) AS product_name_agg,
FROM Products
GROUP BY GROUPING SETS(product_type, product_name, ())
ORDER BY product_name;
/*--------------+--------------+-------------+------------------+------------------+
| product_type | product_name | product_sum | product_type_agg | product_name_agg |
+--------------+--------------+-------------+------------------+------------------+
| NULL | NULL | 42 | 1 | 1 |
| shirt | NULL | 36 | 0 | 1 |
| pants | NULL | 6 | 0 | 1 |
| NULL | jeans | 6 | 1 | 0 |
| NULL | polo | 25 | 1 | 0 |
| NULL | t-shirt | 11 | 1 | 0 |
+--------------+--------------+-------------+------------------+------------------*/
In the following example, it's difficult to determine
if NULL
represents a NULL
placeholder or a standard NULL
value in the
product_type
column. The GROUPING
function makes it easier to
determine what type of NULL
is being produced. If
product_type_is_aggregated
is 1
, the NULL
value for
the product_type
column is a NULL
placeholder.
WITH
Products AS (
SELECT 'shirt' AS product_type, 't-shirt' AS product_name, 3 AS product_count UNION ALL
SELECT 'shirt', 't-shirt', 8 UNION ALL
SELECT NULL, 'polo', 25 UNION ALL
SELECT 'pants', 'jeans', 6
)
SELECT
product_type,
product_name,
SUM(product_count) AS product_sum,
GROUPING(product_type) AS product_type_is_aggregated
FROM Products
GROUP BY GROUPING SETS(product_type, product_name)
ORDER BY product_name;
/*--------------+--------------+-------------+----------------------------+
| product_type | product_name | product_sum | product_type_is_aggregated |
+--------------+--------------+-------------+----------------------------+
| shirt | NULL | 11 | 0 |
| NULL | NULL | 25 | 0 |
| pants | NULL | 6 | 0 |
| NULL | jeans | 6 | 1 |
| NULL | polo | 25 | 1 |
| NULL | t-shirt | 11 | 1 |
+--------------+--------------+-------------+----------------------------*/
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.
This function can be used with the
AGGREGATION_THRESHOLD
clause.
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.
This function can be used with the
AGGREGATION_THRESHOLD
clause.
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
)
[ 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 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.
To learn more about the OVER
clause and how to use it, see
Window 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 |
*-----*/
SELECT x, MAX(x) OVER (PARTITION BY MOD(x, 2)) AS max
FROM UNNEST([8, NULL, 37, 55, NULL, 4]) AS x;
/*------+------*
| x | max |
+------+------+
| NULL | NULL |
| NULL | NULL |
| 8 | 8 |
| 4 | 8 |
| 37 | 55 |
| 55 | 55 |
*------+------*/
MAX_BY
MAX_BY(
x, y
)
Description
Synonym for ANY_VALUE(x HAVING MAX y)
.
Return Data Types
Matches the input x
data type.
Examples
WITH fruits AS (
SELECT "apple" fruit, 3.55 price UNION ALL
SELECT "banana" fruit, 2.10 price UNION ALL
SELECT "pear" fruit, 4.30 price
)
SELECT MAX_BY(fruit, price) as fruit
FROM fruits;
/*-------*
| fruit |
+-------+
| pear |
*-------*/
MIN
MIN(
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 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.
To learn more about the OVER
clause and how to use it, see
Window 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 |
*-----*/
SELECT x, MIN(x) OVER (PARTITION BY MOD(x, 2)) AS min
FROM UNNEST([8, NULL, 37, 4, NULL, 55]) AS x;
/*------+------*
| x | min |
+------+------+
| NULL | NULL |
| NULL | NULL |
| 8 | 4 |
| 4 | 4 |
| 37 | 37 |
| 55 | 37 |
*------+------*/
MIN_BY
MIN_BY(
x, y
)
Description
Synonym for ANY_VALUE(x HAVING MIN y)
.
Return Data Types
Matches the input x
data type.
Examples
WITH fruits AS (
SELECT "apple" fruit, 3.55 price UNION ALL
SELECT "banana" fruit, 2.10 price UNION ALL
SELECT "pear" fruit, 4.30 price
)
SELECT MIN_BY(fruit, price) as fruit
FROM fruits;
/*--------*
| fruit |
+--------+
| banana |
*--------*/
STRING_AGG
STRING_AGG(
[ DISTINCT ]
expression [, delimiter]
[ ORDER BY key [ { ASC | DESC } ] [, ... ] ]
[ LIMIT n ]
)
[ 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 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.
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.
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 |
*-----------------------*/
SELECT STRING_AGG(fruit, " & " ORDER BY LENGTH(fruit)) AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;
/*------------------------------*
| string_agg |
+------------------------------+
| pear & pear & apple & banana |
*------------------------------*/
SELECT STRING_AGG(fruit, " & " LIMIT 2) AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;
/*--------------*
| string_agg |
+--------------+
| apple & pear |
*--------------*/
SELECT STRING_AGG(DISTINCT fruit, " & " ORDER BY fruit DESC LIMIT 2) AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;
/*---------------*
| string_agg |
+---------------+
| pear & banana |
*---------------*/
SELECT
fruit,
STRING_AGG(fruit, " & ") OVER (ORDER BY LENGTH(fruit)) AS string_agg
FROM UNNEST(["apple", NULL, "pear", "banana", "pear"]) AS fruit;
/*--------+------------------------------*
| fruit | string_agg |
+--------+------------------------------+
| NULL | NULL |
| pear | pear & pear |
| pear | pear & pear |
| apple | pear & pear & apple |
| banana | pear & pear & apple & banana |
*--------+------------------------------*/
SUM
SUM(
[ 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 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.
This function can be used with the
AGGREGATION_THRESHOLD
clause.
To learn more about the OVER
clause and how to use it, see
Window function calls.
SUM
can be used with differential privacy. For more information, see
Differentially private aggregate functions.
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
INTERVAL
Return Data Types
INPUT | INT64 | NUMERIC | BIGNUMERIC | FLOAT64 | INTERVAL |
---|---|---|---|---|---|
OUTPUT | INT64 | NUMERIC | BIGNUMERIC | FLOAT64 | INTERVAL |
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
x,
SUM(x) OVER (PARTITION BY MOD(x, 3)) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;
/*---+-----*
| x | sum |
+---+-----+
| 3 | 6 |
| 3 | 6 |
| 1 | 10 |
| 4 | 10 |
| 4 | 10 |
| 1 | 10 |
| 2 | 9 |
| 5 | 9 |
| 2 | 9 |
*---+-----*/
SELECT
x,
SUM(DISTINCT x) OVER (PARTITION BY MOD(x, 3)) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;
/*---+-----*
| x | sum |
+---+-----+
| 3 | 3 |
| 3 | 3 |
| 1 | 5 |
| 4 | 5 |
| 4 | 5 |
| 1 | 5 |
| 2 | 7 |
| 5 | 7 |
| 2 | 7 |
*---+-----*/
SELECT SUM(x) AS sum
FROM UNNEST([]) AS x;
/*------*
| sum |
+------+
| NULL |
*------*/