Returns the average value within the requested window. For more information on usage, please refer to Window Functions.

​
Syntax

AVG( <value> ) OVER ( [ PARTITION BY <partition_by> ] )

​
Parameters

ParameterDescriptionSupported input types
<value>A value used for the AVG() functionAny numeric type
<partition_byAn expression used for the PARTITION BY clauseAny

​
Return Types

  • NUMERIC if the input is type INTEGER, BIGINT or NUMERIC
  • DOUBLE PRECISION if the input is type REAL or DOUBLE PRECISION

​
Example

The example below is querying test scores for players in various game levels. Unlike a regular AVG() aggregation, the window function allows us to see how each student individually compares to the average test score for their game level. 
SELECT
	nickname,
	level,
	currentscore,
	AVG(game_score) OVER (PARTITION BY level) AS score_average
FROM
	class_test;
Returns:
nicknamelevelcurrentscorescore_average
kennethpark97675.77777
sabrina2179081.33333
burchdenise87979.55555
ymatthews68593.88888
rileyjon88084.99999