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 
  
 [ 
  
 { 
  
 IGNORE 
  
 | 
  
 RESPECT 
  
 } 
  
 NULLS 
  
 ] 
 ) 
 [ 
  
 OVER 
  
 over_clause 
  
 ] 
 

Description

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

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

• IGNORE NULLS or RESPECT NULLS : If IGNORE NULLS is specified, the NULL values are excluded from the result. If RESPECT NULLS is specified, both NULL and non- NULL values can be included in the result.

  If neither IGNORE NULLS nor RESPECT NULLS is specified, most functions default to IGNORE NULLS behavior but in a few cases NULL values are respected.

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

• 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, such as IGNORE_NULLS , 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
• IGNORE NULLS or RESPECT NULLS
• DISTINCT

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  | 
 +------+------*/