Syntax
AVG([ DISTINCT | ALL ] expr) OVER(analytic_clause)
Purpose
AVG() returns the average value of a specified group. NULL values are ignored. The DISTINCT option can be used to return the average of distinct values of expr. If no matching row is found, AVG() returns NULL.
Examples
obclient> CREATE TABLE employess(last_name CHAR(10), salary DECIMAL, job_id CHAR(32));
Query OK, 0 rows affected
obclient> INSERT INTO employess VALUES('JIM', 2000, 'CLEANER');
Query OK, 1 row affected
obclient> INSERT INTO employess VALUES('MIKE', 12000, 'ENGINEERING');
Query OK, 1 row affected
obclient> INSERT INTO employess VALUES('LILY', 13000, 'ENGINEERING');
Query OK, 1 row affected
obclient> INSERT INTO employess VALUES('TOM', 11000, 'ENGINEERING');
Query OK, 1 row affected
obclient> SELECT last_name, AVG(salary) OVER(PARTITION BY job_id) avg_s FROM employess;
+-----------+------------+
| last_name | avg_s |
+-----------+------------+
| JIM | 2000.0000 |
| MIKE | 12000.0000 |
| LILY | 12000.0000 |
| TOM | 12000.0000 |
+-----------+------------+
4 rows in set