Aggregate function calls

An aggregate function summarizes the rows of a group into a single value. When an aggregate function is used with the OVER clause, it becomes a window function, which computes values over a group of rows and then returns a single result for each row.

Aggregate function call syntax

function_name(
  [ DISTINCT ]
  function_arguments
  [ LIMIT n ]
)
[ OVER over_clause ]

Description

Each aggregate function supports all or a subset of the aggregate function call syntax. To build an aggregate function, use the following syntax:

• DISTINCT: Aggregate each distinct value of an expression only once into the result.

• function_arguments: Specify the input values, columns, or expressions that the aggregate function evaluates and summarizes across the rows of a group.

  • If DISTINCT is also specified, then the sort key must be the same as expression.

• LIMIT: Specifies the maximum number of expression inputs in the result.

  • If the input is an ARRAY value, the limit applies to the number of input arrays, not the number of elements in the arrays. An empty array counts as 1. A NULL array isn't counted.

  • If the input is a STRING value, the limit applies to the number of input strings, not the number of characters or bytes in the inputs. An empty string counts as 1. A NULL string isn't counted.

  • The limit n must be a constant INT64.

• OVER: If the aggregate function is also a window function, use this clause to define a window of rows around the row being evaluated. For each row, the aggregate function result is computed using the selected window of rows as input. If the OVER clause is used, aggregate function clauses, such as DISTINCT, aren't supported, but function call modifiers are still supported. To learn more about the OVER clause, see Window function calls.

Details

The clauses in an aggregate function call are applied in the following order:

• OVER
• DISTINCT
• LIMIT

When used in conjunction with a GROUP BY clause, the groups summarized typically have at least one row. When the associated SELECT statement has no GROUP BY clause or when certain aggregate function modifiers filter rows from the group to be summarized, it's possible that the aggregate function needs to summarize an empty group.

Aggregate function examples

A simple aggregate function call for COUNT, MIN, and MAX looks like this:

SELECT
  COUNT(*) AS total_count,
  COUNT(fruit) AS non_null_count,
  MIN(fruit) AS min,
  MAX(fruit) AS max
FROM
  (
    SELECT NULL AS fruit
    UNION ALL
    SELECT 'apple' AS fruit
    UNION ALL
    SELECT 'pear' AS fruit
    UNION ALL
    SELECT 'orange' AS fruit
  )

/*-------------+----------------+-------+------+
 | total_count | non_null_count | min   | max  |
 +-------------+----------------+-------+------+
 | 4           | 3              | apple | pear |
 +-------------+----------------+-------+------*/

In the following example, the average of x over a specified window is returned for each row. To learn more about windows and how to use them, see Window function calls.

SELECT
  x,
  AVG(x) OVER (ORDER BY x ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS avg
FROM UNNEST([0, 2, 4, 4, 5]) AS x;

/*------+------+
 | x    | avg  |
 +------+------+
 | 0    | 0    |
 | 2    | 1    |
 | 4    | 3    |
 | 4    | 4    |
 | 5    | 4.5  |
 +------+------*/