Amazon Redshift will no longer support the creation of new Python UDFs starting Patch 198. Existing Python UDFs will continue to function until June 30, 2026. For more information, see the [ blog post ](https://aws.amazon.com/blogs/big-data/amazon-redshift-python-user-defined-functions-will-reach-end-of-support-after-june-30-2026/). 

# AVG window function
<a name="r_WF_AVG"></a>

 The AVG window function returns the average (arithmetic mean) of the input expression values. The AVG function works with numeric values and ignores NULL values.

## Syntax
<a name="r_WF_AVG-synopsis"></a>

```
AVG ( [ALL ] expression ) OVER
(
[ PARTITION BY expr_list ]
[ ORDER BY order_list 
                        frame_clause ]
)
```

## Arguments
<a name="r_WF_AVG-arguments"></a>

 *expression *   
The target column or expression that the function operates on. 

ALL   
With the argument ALL, the function retains all duplicate values from the expression for counting. ALL is the default. DISTINCT is not supported.

OVER   
Specifies the window clauses for the aggregation functions. The OVER clause distinguishes window aggregation functions from normal set aggregation functions.

PARTITION BY *expr\$1list*   
Defines the window for the AVG function in terms of one or more expressions.

ORDER BY *order\$1list*   
Sorts the rows within each partition. If no PARTITION BY is specified, ORDER BY uses the entire table.

 *frame\$1clause*   
If an ORDER BY clause is used for an aggregate function, an explicit frame clause is required. The frame clause refines the set of rows in a function's window, including or excluding sets of rows within the ordered result. The frame clause consists of the ROWS keyword and associated specifiers. See [Window function syntax summary](c_Window_functions.md#r_Window_function_synopsis).

## Data types
<a name="c_Supported_data_types_wf_avg"></a>

The argument types supported by the AVG function are SMALLINT, INTEGER, BIGINT, NUMERIC, DECIMAL, REAL, and DOUBLE PRECISION.

The return types supported by the AVG function are: 
+ BIGINT for SMALLINT or INTEGER arguments
+ NUMERIC for BIGINT arguments
+ DOUBLE PRECISION for floating point arguments

## Examples
<a name="r_WF_AVG-examples"></a>

The following example computes a rolling average of quantities sold by date; order the results by date ID and sales ID: 

```
select salesid, dateid, sellerid, qty,
avg(qty) over
(order by dateid, salesid rows unbounded preceding) as avg
from winsales
order by 2,1;

salesid |   dateid   | sellerid | qty | avg
---------+------------+----------+-----+-----
30001 | 2003-08-02 |        3 |  10 |  10
10001 | 2003-12-24 |        1 |  10 |  10
10005 | 2003-12-24 |        1 |  30 |  16
40001 | 2004-01-09 |        4 |  40 |  22
10006 | 2004-01-18 |        1 |  10 |  20
20001 | 2004-02-12 |        2 |  20 |  20
40005 | 2004-02-12 |        4 |  10 |  18
20002 | 2004-02-16 |        2 |  20 |  18
30003 | 2004-04-18 |        3 |  15 |  18
30004 | 2004-04-18 |        3 |  20 |  18
30007 | 2004-09-07 |        3 |  30 |  19
(11 rows)
```

 For a description of the WINSALES table, see [Sample table for window function examples](c_Window_functions.md#r_Window_function_example).