Purpose
This function calculates the average of a numeric expression or an expression that can be converted to a numeric value. It can be used as an aggregate or analytic function.
Note
- When used as an analytic function, you must use the
OVERclause to define the window for the calculation. It calculates the average over a set of rows and returns multiple values. - When used as an aggregate function, it calculates the average over a set of rows and returns a single value. In this case, the
OVERclause is not required.
Syntax
AVG([ DISTINCT | UNIQUE | ALL ] expr) [ OVER (analytic_clause) ]
Parameters
Parameter |
Description |
|---|---|
| DISTINCT | UNIQUE | ALL | Specifies whether to remove duplicates. This is an optional parameter with a default value of ALL.
|
| expr | Specifies the column to calculate. The data type of the column is numeric or can be converted to a numeric value. Numeric types can be NUMBER, FLOAT, BINARY_FLOAT, or BINARY_DOUBLE. |
| OVER | Defines the window for the calculation using the OVER clause. For more information, see Analytic Function Description. |
Notice
If you specify the DISTINCT or UNIQUE keyword, the order_by_clause and windowing_clause cannot appear in the analytic_clause.
Return type
The return type is the same as the data type of the expr parameter.
Examples
Create a table tbl1 and insert test data.
obclient> CREATE TABLE tbl1 (col1 INT,col2 varchar(10),col3 INT);
Query OK, 0 rows affected
obclient> INSERT INTO tbl1 VALUES (1,'a',5),(1,'b',10),(1,'c',15),
(2,'d',20),(2,'e',18),(2,'f',12),(3,'a',10),
(3,'b',15),(4,'c',15),(4,'a',8);
Query OK, 10 rows affected
Records: 10 Duplicates: 0 Warnings: 0
obclient> SELECT * FROM tbl1;
+------+------+------+
| COL1 | COL2 | COL3 |
+------+------+------+
| 1 | a | 5 |
| 1 | b | 10 |
| 1 | c | 15 |
| 2 | d | 20 |
| 2 | e | 18 |
| 2 | f | 12 |
| 3 | a | 10 |
| 3 | b | 15 |
| 4 | c | 15 |
| 4 | a | 8 |
+------+------+------+
10 rows in set
Aggregate function example
Calculate the average of col3.
obclient> SELECT AVG(col3) FROM tbl1;
+-----------+
| AVG(COL3) |
+-----------+
| 12.8 |
+-----------+
1 row in set
Analytic function example
Group by col1 and sort col2 in ascending order, then calculate the average of the previous and next ranges of values in col3.
obclient> SELECT col1,col2,col3, AVG(col3)
OVER (PARTITION BY col1 ORDER BY col2 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS over_col2
FROM tbl1
ORDER BY col1,col2,col3;
+------+------+------+-------------------------------------------+
| COL1 | COL2 | COL3 | OVER_COL2 |
+------+------+------+-------------------------------------------+
| 1 | a | 5 | 7.5 |
| 1 | b | 10 | 10 |
| 1 | c | 15 | 12.5 |
| 2 | d | 20 | 19 |
| 2 | e | 18 | 16.66666666666666666666666666666666666667 |
| 2 | f | 12 | 15 |
| 3 | a | 10 | 12.5 |
| 3 | b | 15 | 12.5 |
| 4 | a | 8 | 11.5 |
| 4 | c | 15 | 11.5 |
+------+------+------+-------------------------------------------+
10 rows in set