This page documents the ZetaSQL aggregate functions supported by Beam ZetaSQL.
Operator syntax | Description |
---|---|
COUNT(*) | Returns the number of input rows |
AVG(FLOAT64) | Returns the average of non-NULL input values |
SUM(numeric) | Returns the sum of non-NULL values |
MAX(value) | Returns the maximum non-NULL value |
MIN(value) | Returns the minimum non-NULL value |
{:.table} |
AVG(expression)
Description
Returns the average of non-NULL
input values.
Supported Argument Types
FLOAT64. Note that, for floating point input types, the return result is non-deterministic, which means you might receive a different result each time you use this function.
Returned Data Types
Examples
SELECT AVG(x) as avg FROM UNNEST([0, 2, NULL, 4, 4, 5]) as x; +-----+ | avg | +-----+ | 3 | +-----+
COUNT(*)
COUNT(expression)
Description
expression
evaluated to any value other than NULL
.Supported Argument Types
expression
can be any data type.
Return Data Types
INT64
Examples
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 | +------------+---------+
MAX(expression)
Description
Returns the maximum value of non-NULL
expressions. Returns NULL
if there are zero input rows or expression
evaluates to NULL
for all rows.
Supported Argument Types
Any data type except:
ARRAY
STRUCT
Return Data Types
Same as the data type used as the input values.
Examples
SELECT MAX(x) AS max FROM UNNEST([8, NULL, 37, 4, NULL, 55]) AS x; +-----+ | max | +-----+ | 55 | +-----+
MIN(expression)
Description
Returns the minimum value of non-NULL
expressions. Returns NULL
if there are zero input rows or expression
evaluates to NULL
for all rows.
Supported Argument Types
Any data type except:
ARRAY
STRUCT
Return Data Types
Same as the data type used as the input values.
Examples
SELECT MIN(x) AS min FROM UNNEST([8, NULL, 37, 4, NULL, 55]) AS x; +-----+ | min | +-----+ | 4 | +-----+
SUM(expression)
Description
Returns the sum of non-null values.
If the expression is a floating point value, the sum is non-deterministic, which means you might receive a different result each time you use this function.
Supported Argument Types
Any supported numeric data types.
Return Data Types
Returns NULL
if the input contains only NULL
s.
Examples
SELECT SUM(x) AS sum FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x; +-----+ | sum | +-----+ | 25 | +-----+