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

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

Window functions

COUNT OVER

⌘I

Overview

Performance and Observability

Security

Self-Managed

Managed service

Guides

SQL reference

Release notes

API reference

Legal

Syntax

Parameters

Return Types

Example

​Syntax

​Parameters

​Return Types

​Example

Syntax

Parameters

Return Types

Example