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

Aggregate function call syntax

function_name([DISTINCT]function_arguments[{IGNORE|RESPECT}NULLS][HAVING{MAX|MIN}having_expression][ORDERBYkey[{ASC|DESC}][,...]][LIMITn])[OVERover_clause]

Description

Each aggregate function supports all or a subset of theaggregate function call syntax. You can use the following syntax to build anaggregate function:

• DISTINCT: Each distinct value ofexpression is aggregated only once into the result.

• IGNORE NULLS orRESPECT NULLS: IfIGNORE NULLS isspecified, theNULL values are excluded from the result. IfRESPECT NULLS is specified, bothNULL and non-NULL values can beincluded in the result.

  If neitherIGNORE NULLS norRESPECT NULLS is specified, most functionsdefault toIGNORE NULLS behavior but in a few casesNULL values arerespected.

• HAVING MAX orHAVING MIN: Restricts the set of rows that thefunction aggregates by a maximum or minimum value.For details, seeHAVING MAX and HAVING MIN clause.

• ORDER BY: Specifies the order of the values.

  • For each sort key, the default sort direction isASC.

  • NULL is the minimum possible value, soNULLs appear firstinASC sorts and last inDESC sorts.

  • If you're using floating point data types, seeFloating point semanticson ordering and grouping.

  • TheORDER BY clause is supported only for aggregate functions thatdepend on the order of their input. For those functions, if theORDER BY clause is omitted, the output is nondeterministic.

  • ThisORDER BY clause can't be used if theOVER clause is used.

  • IfDISTINCT is also specified, thenthe sort key must be the same asexpression.

• LIMIT: Specifies the maximum number ofexpression inputs in theresult.

  • If the input is anARRAY value, the limit applies to the number of inputarrays, not the number of elements in the arrays. An empty array countsas1. ANULL array isn't counted.

  • If the input is aSTRING value, the limit applies to the number of inputstrings, not the number of characters or bytes in the inputs. An emptystring counts as1. ANULL string isn't counted.

  • The limitn must be a constantINT64.

• OVER: If the aggregate function is also a window function, use this clauseto 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 asinput. If this clause is used, aggregate functionclauses (i.e.DISTINCT) can't be used. To learn more about theOVER clause,seeWindow function calls.

Details

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

• OVER
• HAVING MAX/HAVING MIN
• IGNORE NULLS orRESPECT NULLS
• DISTINCT
• ORDER BY
• LIMIT

When used in conjunction with aGROUP BY clause, the groups summarizedtypically have at least one row. When the associatedSELECT statement hasnoGROUP BY clause or when certain aggregate function modifiers filter rowsfrom the group to be summarized, it's possible that the aggregate functionneeds to summarize an empty group.

Restrict aggregation by a maximum or minimum value

Some aggregate functions support two optional clauses that are calledHAVING MAX andHAVING MIN. These clauses restrict the set of rows that afunction aggregates to rows that have a maximum or minimum value in a particularcolumn.

HAVING MAX clause

HAVINGMAXhaving_expression

HAVING MAX restricts the set of input rows that the function aggregates toonly those with the maximumhaving_expression value. The maximum value iscomputed as the result ofMAX(having_expression) across rows in the group.Only rows whosehaving_expression value is equal to this maximum value (usingSQL equality semantics) are included in the aggregation. All other rows areignored in the aggregation.

This clause supports allorderable data types,except forARRAY.

Examples

In the following query, rows with the most inches of precipitation,4, areadded to a group, and then theyear for one of these rows is produced.Which row is produced is nondeterministic, not random.

WITHPrecipitationAS(SELECT2009ASyear,'spring'ASseason,3ASinchesUNIONALLSELECT2001,'winter',4UNIONALLSELECT2003,'fall',1UNIONALLSELECT2002,'spring',4UNIONALLSELECT2005,'summer',1)SELECTANY_VALUE(yearHAVINGMAXinches)ASany_year_with_max_inchesFROMPrecipitation;/*--------------------------+ | any_year_with_max_inches | +--------------------------+ | 2001                     | +--------------------------*/

HAVING MIN clause

HAVINGMINhaving_expression

HAVING MIN restricts the set of input rows that the function aggregates toonly those with the minimumhaving_expression value. The minimum value iscomputed as the result ofMIN(having_expression) across rows in the group.Only rows whosehaving_expression value is equal to this minimum value (usingSQL equality semantics) are included in the aggregation. All other rows areignored in the aggregation.

This clause supports allorderable data types,except forARRAY.

Examples

In the following query, rows with the fewest inches of precipitation,1,are added to a group, and then theyear for one of these rows is produced.Which row is produced is nondeterministic, not random.

WITHPrecipitationAS(SELECT2009ASyear,'spring'ASseason,3ASinchesUNIONALLSELECT2001,'winter',4UNIONALLSELECT2003,'fall',1UNIONALLSELECT2002,'spring',4UNIONALLSELECT2005,'summer',1)SELECTANY_VALUE(yearHAVINGMINinches)ASany_year_with_min_inchesFROMPrecipitation;/*--------------------------+ | any_year_with_min_inches | +--------------------------+ | 2003                     | +--------------------------*/

Aggregate function examples

A simple aggregate function call forCOUNT,MIN, andMAX looks like this:

SELECTCOUNT(*)AStotal_count,COUNT(fruit)ASnon_null_count,MIN(fruit)ASmin,MAX(fruit)ASmaxFROM(SELECTNULLASfruitUNIONALLSELECT'apple'ASfruitUNIONALLSELECT'pear'ASfruitUNIONALLSELECT'orange'ASfruit)/*-------------+----------------+-------+------+ | total_count | non_null_count | min   | max  | +-------------+----------------+-------+------+ | 4           | 3              | apple | pear | +-------------+----------------+-------+------*/

In the following example, the average ofx over a specified window is returnedfor each row. To learn more about windows and how to use them, seeWindow function calls.

SELECTx,AVG(x)OVER(ORDERBYxROWSBETWEEN1PRECEDINGANDCURRENTROW)ASavgFROMUNNEST([0,2,4,4,5])ASx;/*------+------+ | x    | avg  | +------+------+ | 0    | 0    | | 2    | 1    | | 4    | 3    | | 4    | 4    | | 5    | 4.5  | +------+------*/