Purpose
This function calculates the average of numeric values or expressions that can be converted to numeric values. It can be used as an aggregate or analytic function.
Note
- When used as an analytic function, the
OVERclause is required to define the window for calculation. It computes the average over a set of rows and returns multiple values. - When used as an aggregate function, the function computes 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. Optional. Default value is ALL.
|
| expr | Specifies the column name to calculate. The data type of the column is numeric or an expression that can be converted to a numeric value. Supported numeric types include NUMBER, FLOAT, BINARY_FLOAT, and BINARY_DOUBLE. |
| OVER | Defines the window for 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 be specified in the analytic_clause.
Return type
The return type is the same as the data type of the expr parameter.
Examples
Create a table named 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 the col3 column.
obclient> SELECT AVG(col3) FROM tbl1;
+-----------+
| AVG(COL3) |
+-----------+
| 12.8 |
+-----------+
1 row in set
Analytic function example
Group by the col1 column and sort the col2 column in ascending order. Calculate the average of the previous and next ranges of values in the col3 column.
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