AVG OVER

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

​

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: