AVG: Command syntax, parameters, and return value-MaxCompute(MaxCompute)-阿里云帮助中心

Calculates the average of a set of values.

Limits

Window functions are supported only in SELECT statements.
A window function cannot contain nested window functions or nested aggregate functions.
Window functions cannot be used together with aggregate functions at the same level.

Syntax

-- Calculates the average value.
decimal|double avg(<colname>)

-- Calculates the average of expr within a window.
double avg([distinct] double <expr>) over ([partition_clause] [orderby_clause] [frame_clause])
decimal avg([distinct] decimal <expr>) over ([partition_clause] [orderby_clause] [frame_clause])

Description

As an aggregate function, AVG calculates the average of a column. As a window function, it calculates the average of an expression over a group of rows.

Parameters

colname: Required. The name of the column to average. This parameter applies when AVG is used as an aggregate function. The function supports all data types, implicitly converting them to the double data type for calculation.
expr: Required. An expression that evaluates to the numeric values to average. The data type of the expression must be double or decimal.
- The function implicitly converts input values of the string or bigint data type to double for the calculation. Providing any other data type returns an error.
- If an input value is NULL, the function excludes that row from the calculation.
- If you specify the distinct keyword, the function calculates the average of only the unique values.
partition_clause, orderby_clause, and frame_clause: Use these clauses to specify how to partition, order, and frame rows for the window function. For more information, see Windowing definition.

Return value

If a value in the colname column is NULL, the function excludes that row from the calculation. The return type depends on the input type. The following table describes the rules.
Input type
Return type
tinyint
double
smallint
double
int
double
bigint
double
float
double
double
double
decimal
decimal
If expr is a decimal, the function returns a decimal; otherwise, it returns a double. If all input values for expr are NULL, the function returns NULL.

Sample data

This section provides sample source data and examples to help you understand how to use the functions. Create a table named emp and insert the sample data into the table. Sample statement:

CREATE TABLE IF NOT EXISTS emp
   (empno BIGINT,
    ename STRING,
    job STRING,
    mgr BIGINT,
    hiredate DATETIME,
    sal BIGINT,
    comm BIGINT,
    deptno BIGINT);
tunnel upload emp.txt emp;   --Replace emp.txt with the actual path (path and name) to which you upload the data file

The emp.txt file contains the following sample data:

7369,SMITH,CLERK,7902,1980-12-17 00:00:00,800,,20
7499,ALLEN,SALESMAN,7698,1981-02-20 00:00:00,1600,300,30
7521,WARD,SALESMAN,7698,1981-02-22 00:00:00,1250,500,30
7566,JONES,MANAGER,7839,1981-04-02 00:00:00,2975,,20
7654,MARTIN,SALESMAN,7698,1981-09-28 00:00:00,1250,1400,30
7698,BLAKE,MANAGER,7839,1981-05-01 00:00:00,2850,,30
7782,CLARK,MANAGER,7839,1981-06-09 00:00:00,2450,,10
7788,SCOTT,ANALYST,7566,1987-04-19 00:00:00,3000,,20
7839,KING,PRESIDENT,,1981-11-17 00:00:00,5000,,10
7844,TURNER,SALESMAN,7698,1981-09-08 00:00:00,1500,0,30
7876,ADAMS,CLERK,7788,1987-05-23 00:00:00,1100,,20
7900,JAMES,CLERK,7698,1981-12-03 00:00:00,950,,30
7902,FORD,ANALYST,7566,1981-12-03 00:00:00,3000,,20
7934,MILLER,CLERK,7782,1982-01-23 00:00:00,1300,,10
7948,JACCKA,CLERK,7782,1981-04-12 00:00:00,5000,,10
7956,WELAN,CLERK,7649,1982-07-20 00:00:00,2450,,10
7956,TEBAGE,CLERK,7748,1982-12-30 00:00:00,1300,,10

Examples

Example 1: This example calculates the average salary for each department. The query uses deptno as the partitioning column. Because the ORDER BY clause is omitted, the function averages the sal values over all rows in each partition.

select deptno, sal, avg(sal) over (partition by deptno) from emp;

The following result is returned:

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 2916.6666666666665 |   -- The average for the entire partition (deptno=10).
| 10         | 2450       | 2916.6666666666665 |   -- The average for the entire partition (deptno=10).
| 10         | 5000       | 2916.6666666666665 |   -- The average for the entire partition (deptno=10).
| 10         | 1300       | 2916.6666666666665 |
| 10         | 5000       | 2916.6666666666665 |
| 10         | 2450       | 2916.6666666666665 |
| 20         | 3000       | 2175.0     |
| 20         | 3000       | 2175.0     |
| 20         | 800        | 2175.0     |
| 20         | 1100       | 2175.0     |
| 20         | 2975       | 2175.0     |
| 30         | 1500       | 1566.6666666666667 |
| 30         | 950        | 1566.6666666666667 |
| 30         | 1600       | 1566.6666666666667 |
| 30         | 1250       | 1566.6666666666667 |
| 30         | 1250       | 1566.6666666666667 |
| 30         | 2850       | 1566.6666666666667 |
+------------+------------+------------+

Example 2: In non-Hive-compatible mode, this example calculates a moving average of salaries within each department. The average is calculated over the rows from the start of the partition to the current row, ordered by salary.

-- Disable Hive-compatible mode.
set odps.sql.hive.compatible=false;
-- Run the following SQL statement.
select deptno, sal, avg(sal) over (partition by deptno order by sal) from emp;

The following result is returned:

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 1300.0     |           -- The first row in the partition.
| 10         | 1300       | 1300.0     |           -- The moving average of the first two rows.
| 10         | 2450       | 1683.3333333333333 |   -- The moving average of the first three rows.
| 10         | 2450       | 1875.0     |           -- The moving average of the first four rows.
| 10         | 5000       | 2500.0     |           -- The moving average of the first five rows.
| 10         | 5000       | 2916.6666666666665 |   -- The moving average of all six rows.
| 20         | 800        | 800.0      |
| 20         | 1100       | 950.0      |
| 20         | 2975       | 1625.0     |
| 20         | 3000       | 1968.75    |
| 20         | 3000       | 2175.0     |
| 30         | 950        | 950.0      |
| 30         | 1250       | 1100.0     |
| 30         | 1250       | 1150.0     |
| 30         | 1500       | 1237.5     |
| 30         | 1600       | 1310.0     |
| 30         | 2850       | 1566.6666666666667 |
+------------+------------+------------+

Example 3: In Hive-compatible mode, calculate the moving average of salaries within each department. The average is calculated for the frame from the start of the partition to the last peer of the current row (all rows with the same salary).

-- Enable Hive-compatible mode.
set odps.sql.hive.compatible=true;
-- Run the following SQL statement.
select deptno, sal, avg(sal) over (partition by deptno order by sal) from emp;

The following result is returned:

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 1300.0     |          -- Peers with sal=1300. The average is calculated over these first two rows: (1300+1300)/2.
| 10         | 1300       | 1300.0     |          -- Same calculation as the row above because they are peers.
| 10         | 2450       | 1875.0     |          -- Peers with sal=2450. The average is calculated over the first four rows.
| 10         | 2450       | 1875.0     |          -- Same calculation as the row above because they are peers.
| 10         | 5000       | 2916.6666666666665 |
| 10         | 5000       | 2916.6666666666665 |
| 20         | 800        | 800.0      |
| 20         | 1100       | 950.0      |
| 20         | 2975       | 1625.0     |
| 20         | 3000       | 2175.0     |
| 20         | 3000       | 2175.0     |
| 30         | 950        | 950.0      |
| 30         | 1250       | 1150.0     |
| 30         | 1250       | 1150.0     |
| 30         | 1500       | 1237.5     |
| 30         | 1600       | 1310.0     |
| 30         | 2850       | 1566.6666666666667 |
+------------+------------+------------+

Example 4: Calculate the average salary for all employees, using AVG as an aggregate function.
```
select avg(sal) from emp;
```
The following result is returned:
```
+------------+
| _c0        |
+------------+
| 2222.0588235294117 |
+------------+
```

Example 5: Use the AVG function with a group by clause to calculate the average salary for each department.

select deptno, avg(sal) from emp group by deptno;

The following result is returned:

+------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | 2916.6666666666665 |
| 20         | 2175.0     |
| 30         | 1566.6666666666667 |
+------------+------------+

Related functions

AVG is an aggregate function and a window function.

For more information about other aggregate functions, see Aggregate functions.
For more information about other window functions, see Window functions.

Input type	Return type
tinyint	double
smallint	double
int	double
bigint	double
float	double
double	double
decimal	decimal