Product Documentation

Appearance

7.1.2
7.0.2
6.9.3
5.4.17
5.2.31

AVG (window function) ​

Compute the average for an expression based on a window definition that contains an optional frame clause.

AVG | AVERAGE ([ expression ]) OVER { window_name | ( [ window_definition ] ) }

AVG and AVERAGE are synonyms for the same function. For the full window_definition syntax, see Syntax for Window Functions.

For example:

premdb=# select name, city, capacity, avg(capacity) 
over(partition by city order by name)::dec(6,1) as avg_cap 
from team where city in('London','Birmingham','Liverpool') order by 2,1;
        name         |    city    | capacity | avg_cap 
----------------------+------------+----------+---------
 Aston Villa          | Birmingham |    42785 | 42785.0
 Birmingham City      | Birmingham |    30016 | 36400.5
 West Bromwich Albion | Birmingham |    27000 | 33267.0
 Everton              | Liverpool  |    40221 | 40221.0
 Liverpool            | Liverpool  |    44742 | 42481.5
 Arsenal              | London     |    60260 | 60260.0
 Charlton Athletic    | London     |    27111 | 43685.5
 Chelsea              | London     |    41663 | 43011.3
 Crystal Palace       | London     |    26255 | 38822.3
 Fulham               | London     |    25700 | 36197.8
 Queens Park Rangers  | London     |    18439 | 33238.0
 Tottenham Hotspur    | London     |    36284 | 33673.1
 West Ham United      | London     |    35016 | 33841.0
 Wimbledon            | London     |    26255 | 32998.1
(14 rows)

Add an explicit window frame to this example:

premdb=# select name, city, capacity, avg(capacity) 
over(partition by city order by name rows between 2 preceding and 2 following)::dec(6,1) as avg_cap 
from team where city in('London','Birmingham','Liverpool') order by 2,1;
        name         |    city    | capacity | avg_cap 
----------------------+------------+----------+---------
 Aston Villa          | Birmingham |    42785 | 33267.0
 Birmingham City      | Birmingham |    30016 | 33267.0
 West Bromwich Albion | Birmingham |    27000 | 33267.0
 Everton              | Liverpool  |    40221 | 42481.5
 Liverpool            | Liverpool  |    44742 | 42481.5
 Arsenal              | London     |    60260 | 43011.3
 Charlton Athletic    | London     |    27111 | 38822.3
 Chelsea              | London     |    41663 | 36197.8
 Crystal Palace       | London     |    26255 | 27833.6
 Fulham               | London     |    25700 | 29668.2
 Queens Park Rangers  | London     |    18439 | 28338.8
 Tottenham Hotspur    | London     |    36284 | 28338.8
 West Ham United      | London     |    35016 | 28998.5
 Wimbledon            | London     |    26255 | 32518.3
(14 rows)

Parent topic:Window Functions