AVG
Returns the average value within the requested window.
For additional window functions, see Window Functions.
Syntax
AVG( <val> ) OVER ( [ PARTITION BY <exp> ] )
| Parameter | Description |
<val> | An expression used for the AVG() function. |
<expr> | An expression used for the partition by clause. |
Example
The example below is querying test scores for students in various grade 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 grade level.
SELECT
first_name,
grade_level,
test_score,
AVG(test_score) OVER (PARTITION BY grade_level) AS average_for_grade
FROM
class_test;
Returns:
' +------------+-------------+------------+-------------------------+
' | first_name | grade_level | test_score | average_for_grade |
' +------------+-------------+------------+-------------------------+
' | Frank | 9 | 76 | 81.33333333333333 |
' | Humphrey | 9 | 90 | 81.33333333333333 |
' | Iris | 9 | 79 | 81.33333333333333 |
' | Sammy | 9 | 85 | 81.33333333333333 |
' | Peter | 9 | 80 | 81.33333333333333 |
' | Jojo | 9 | 78 | 81.33333333333333 |
' | Brunhilda | 12 | 92 | 89 |
' | Franco | 12 | 94 | 89 |
' | Thomas | 12 | 66 | 89 |
' | Gary | 12 | 100 | 89 |
' | Charles | 12 | 93 | 89 |
' | Jesse | 12 | 89 | 89 |
' | Roseanna | 11 | 94 | 73 |
' | Carol | 11 | 52 | 73 |
' | Wanda | 11 | 73 | 73 |
' | Shangxiu | 11 | 76 | 73 |
' | Larry | 11 | 68 | 73 |
' | Otis | 11 | 75 | 73 |
' | Deborah | 10 | 78 | 68.2 |
' | Yolinda | 10 | 30 | 68.2 |
' | Albert | 10 | 59 | 68.2 |
' | Mary | 10 | 85 | 68.2 |
' | Shawn | 10 | 89 | 68.2 |
' +------------+-------------+------------+-------------------------+