AVG
Returns the average of non-null values in a column or within a window partition.
Limitations
Window functions are supported only in
SELECTstatements.A window function cannot contain nested window functions or nested aggregate functions.
Window functions and aggregate functions of the same level cannot be used together.
Syntax
-- Calculates the average value.
decimal|double avg(<colname>)
-- Calculates the average value of expr within the 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])Parameters
colname: Required.
Accepts all data types that can be implicitly converted to DOUBLE for computation.
expr: Required.
The expression to compute. Accepts DOUBLE or DECIMAL.
STRING and BIGINT values are implicitly converted to DOUBLE; other data types return an error.
NULL values are excluded from the calculation.
When the DISTINCT keyword is specified, the average is computed over unique values only.
partition_clause,orderby_clauseandframe_clause,see windowing_definition.
Return values
For
colname, NULL rows are not used for calculation. Other data types return according to the following rules:Input type
Return type
TINYINT
DOUBLE
SMALLINT
DOUBLE
INT
DOUBLE
BIGINT
DOUBLE
FLOAT
DOUBLE
DOUBLE
DOUBLE
DECIMAL
DECIMAL
If expr is DECIMAL, the return type is DECIMAL. Otherwise, the return type is DOUBLE. If all values of expr are NULL, the return value is NULL.
Sample data
The examples in the next section use the following table and data. To recreate it, run:
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 to your data fileemp.txt contents:
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,,10Examples
Example 1: Partition by department (deptno) and calculate the average salary (sal) with no ordering. Returns the cumulative average from the first row to the last row within the current window (same deptno). The command is as follows:
SELECT deptno, sal, avg(sal) over (partition by deptno) from emp;The return result is as follows:
+------------+------------+------------+ | deptno | sal | _c2 | +------------+------------+------------+ | 10 | 1300 | 2916.6666666666665 | -- Starting row of the window; cumulative average from row 1 to row 6. | 10 | 2450 | 2916.6666666666665 | -- Cumulative average from row 1 to row 6. | 10 | 5000 | 2916.6666666666665 | -- Cumulative average from row 1 to row 6. | 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, partition by department (deptno) and calculate the average salary (sal) with ordering. Returns the cumulative average from the first row to the current row within the current window (same deptno). The command is as follows:
-- Disable Hive-compatible mode. SET odps.sql.hive.compatible=false; -- Run the following SQL command. SELECT deptno, sal, avg(sal) over (partition by deptno order by sal) from emp;The return result is as follows:
+------------+------------+------------+ | deptno | sal | _c2 | +------------+------------+------------+ | 10 | 1300 | 1300.0 | -- Starting row of the window; | 10 | 1300 | 1300.0 | -- Cumulative average from row 1 to row 2. | 10 | 2450 | 1683.3333333333333 | -- Cumulative average from row 1 to row 3. | 10 | 2450 | 1875.0 | -- Cumulative average from row 1 to row 4. | 10 | 5000 | 2500.0 | -- Cumulative average from row 1 to row 5. | 10 | 5000 | 2916.6666666666665 | -- Cumulative average from row 1 to row 6. | 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, partition by department (deptno) and calculate the average salary (sal) with ordering. Returns the cumulative average from the first row to the last row that shares the same value as the current row (rows with the same sal value return the same average). The command is as follows:
-- Enable Hive-compatible mode. SET odps.sql.hive.compatible=true; -- Run the following SQL command. SELECT deptno, sal, avg(sal) over (partition by deptno order by sal) from emp;The return result is as follows:
+------------+------------+------------+ | deptno | sal | _c2 | +------------+------------+------------+ | 10 | 1300 | 1300.0 | -- Starting row of the window. Row 1 and row 2 have the same sal, so the average for row 1 is the cumulative average from row 1 to row 2. | 10 | 1300 | 1300.0 | -- Cumulative average from row 1 to row 2. | 10 | 2450 | 1875.0 | -- Row 3 and row 4 have the same sal, so the average for row 3 is the cumulative average from row 1 to row 4. | 10 | 2450 | 1875.0 | -- Cumulative average from row 1 to row 4. | 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 (sal) of all employees. The command is as follows:
SELECT avg(sal) FROM emp;The return result is as follows:
+------------+ | _c0 | +------------+ | 2222.0588235294117 | +------------+Example 5: Used with GROUP BY, group all employees by department (deptno) and calculate the average salary (sal) for each department. The command is as follows:
SELECT deptno, avg(sal) FROM emp GROUP BY deptno;The return result is as follows:
+------------+------------+ | deptno | _c1 | +------------+------------+ | 10 | 2916.6666666666665 | | 20 | 2175.0 | | 30 | 1566.6666666666667 | +------------+------------+
Related functions
AVG is both an aggregate function and a window function.
For other aggregate functions, see Aggregate functions.
For other window functions, see Window functions.