AVG OVER

Reference material for AVG function

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 shows each player’s score alongside the average score for their level. Unlike a regular AVG() aggregation, the window function keeps one row per player while also computing the per-level average.

SELECT
    nickname,
    level,
    currentscore,
    AVG(currentscore) OVER (PARTITION BY level) AS score_average
FROM
    (VALUES
        ('kennethpark', 9, 76),
        ('sabrina21', 9, 90),
        ('burchdenise', 10, 79),
        ('ymatthews', 10, 85),
        ('rileyjon', 10, 80)
    ) AS t(nickname, level, currentscore)
ORDER BY level, nickname;

nickname text	level int	currentscore int	score_average double null
kennethpark	9	76	83
sabrina21	9	90	83
burchdenise	10	79	81.33333333333333
rileyjon	10	80	81.33333333333333
ymatthews	10	85	81.33333333333333

Rows: 5Execution time: 2.78ms