The AVG function returns the average value of a numeric column.
Syntax
AVG([ DISTINCT | UNIQUE | ALL ] expr) [ OVER (analytic_clause) ]
If the function is used as an analytic function, you must use the full syntax of a window function. The function calculates a set of rows and returns multiple values. If the function is used as an aggregate function, the function aggregates a set of rows and returns only one value. In this case, you do not need to add the OVER keyword.
Parameters
| Parameter | Description |
|---|---|
| DISTINCT | Removes duplicate values from the data and ignores NULL values in the data during the query. |
| UNIQUE | Removes duplicate values from the data and ignores NULL values in the data during the query. |
| ALL | Retains duplicate values in the data and ignores NULL values in the data during the query. Default value: ALL. |
| expr | The expression of the numeric type or the types that can be converted to the numeric type. The numeric type can be NUMBER, FLOAT, BINARY_FLOAT, or BINARY_DOUBLE. |
| OVER | Uses the OVER clause to define a window for calculation. |
Notice
If you specify the DISTINCT or UNIQUE keyword, order_by_clause and windowing_clause cannot appear in analytic_clause.
Return type
The return type is the same as the data type of the expr parameter.
Examples
Examples of the analytic function
The following statements create the employees table and insert data into the table:
CREATE TABLE employees (manager_id INT, last_name varchar(50), hiredate varchar(50), SALARY INT);
INSERT INTO employees VALUES(100, 'Raphaely', '2017-07-01', 1700);
INSERT INTO employees VALUES(100, 'De Haan', '2018-05-01',11000);
INSERT INTO employees VALUES(100, 'Errazuriz', '2017-07-21', 1400);
INSERT INTO employees VALUES(100, 'Hartstein', '2019-05-01',14000);
INSERT INTO employees VALUES(100, 'Raphaely', '2017-07-22', 1700);
INSERT INTO employees VALUES(100, 'Weiss', '2019-07-11',13500);
INSERT INTO employees VALUES(100, 'Russell', '2019-10-05', 13000);
INSERT INTO employees VALUES(100, 'Partners', '2018-12-01',14000);
INSERT INTO employees VALUES(200, 'Ross', '2019-06-11',13500);
INSERT INTO employees VALUES(200, 'Bell', '2019-05-25', 13000);
INSERT INTO employees VALUES(200, 'Part', '2018-08-11',14000);
COMMIT;
Execute the following statement to calculate the average value of each column:
SELECT manager_id, last_name, hiredate, salary, AVG(salary) OVER (PARTITION BY manager_id
ORDER BY hiredate ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS c_mavg
FROM employees ORDER BY manager_id, hiredate, salary;
The following result is returned:
+------------+-----------+------------+--------+------------------------------------------+
| MANAGER_ID | LAST_NAME | HIREDATE | SALARY | C_MAVG |
+------------+-----------+------------+--------+------------------------------------------+
| 100 | Errazuriz | 2017-07-21 | 1400 | 1550 |
| 100 | Raphaely | 2017-07-22 | 1700 | 4700 |
| 100 | De Haan | 2018-05-01 | 11000 | 8900 |
| 100 | Partners | 2018-12-01 | 14000 | 13000 |
| 100 | Hartstein | 2019-05-01 | 14000 | 13833.333 |
| 100 | Weiss | 2019-07-11 | 13500 | 13500 |
| 100 | Russell | 2019-10-05 | 13000 | 13250 |
| 200 | Part | 2018-08-11 | 14000 | 13500 |
| 200 | Bell | 2019-05-25 | 13000 | 13500 |
| 200 | Ross | 2019-06-11 | 13500 | 13250 |
+------------+-----------+------------+--------+------------------------------------------+
Examples of the aggregate function
Execute the following statement to calculate the average value of salary :
SELECT AVG(salary) FROM employees;
The following query result is returned:
+------------------------------------------+
| AVG(SALARY) |
+------------------------------------------+
| 10072.7272727272727272727272727272727273 |
+------------------------------------------+