AVG

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

Parameter	Description	Supported input types
<value>	A value used for the AVG() function	Any numeric type
<partition_by	An expression used for the PARTITION BY clause	Any

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:

nickname	level	currentscore	score_average
kennethpark	9	76	75.77777
sabrina21	7	90	81.33333
burchdenise	8	79	79.55555
ymatthews	6	85	93.88888
rileyjon	8	80	84.99999