Purpose
AVG() evaluates the average of numeric values or an expression that can be converted to numeric values. You can use this function as an aggregate or analytic function.
Note
- When you use this function as an analytic function, use the
OVERclause to define a window over the data on which the function operates. The function operates on a group of rows to return a list of values. - When you use this function as an aggregate function, the function operates on a set of rows and returns a single value. You do not need to add the
OVERclause.
Syntax
AVG([ DISTINCT | UNIQUE | ALL ] expr) [ OVER (analytic_clause) ]
Parameters
| Parameter | Description |
|---|---|
| DISTINCT | UNIQUE | ALL | Specifies whether to remove duplicates during the execution of the query. This parameter is optional. Default value: ALL.
|
| expr | The name of the column to be calculated. The data type of the column must be numeric or an expression that evaluates to a numeric type. Supported numeric data types: NUMBER, FLOAT, BINARY_FLOAT or BINARY_DOUBLE. |
| OVER | You can use the OVER clause to define a window over the data on which the function operates. For more information, see Analytic functions. |
Notice
If you specify the DISTINCT or UNIQUE keyword, you can specify only analytic_clause. order_by_clause and windowing_clause are not allowed.
Return type
The return type is the same as the data type of the expr parameter.
Examples
Create the tbl1 table and insert test data into the table.
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
Example of an aggregate function
Calculate the average of col3.
obclient> SELECT AVG(col3) FROM tbl1;
+-----------+
| AVG(COL3) |
+-----------+
| 12.8 |
+-----------+
1 row in set
Example of an analytic function
Group data by the col1 column. Sort data by the col2 column in ascending order. Calculate the average of the values before the median of col3 and the average of the values after the median.
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