AVG

声明

AVG([ DISTINCT | ALL ] expr) OVER(analytic_clause)

说明

返回指定组中的平均值，空值被忽略。DISTINCT 选项可用于返回 expr 的不同值的平均值。若找不到匹配的行，则 AVG() 返回 NULL。

示例

CREATE TABLE EXPLOYEES(LAST_NAME CHAR(10), SALARY DECIMAL, JOB_ID CHAR(32));
Query OK, 0 rows affected (0.081 sec)

INSERT INTO EXPLOYEES VALUES('JIM', 2000, 'CLEANER');
Query OK, 1 row affected (0.002 sec)

INSERT INTO EXPLOYEES VALUES('MIKE', 12000, 'ENGINEERING');
Query OK, 1 row affected (0.002 sec)

INSERT INTO EXPLOYEES VALUES('LILY', 13000, 'ENGINEERING');
Query OK, 1 row affected (0.002 sec)

INSERT INTO EXPLOYEES VALUES('TOM', 11000, 'ENGINEERING');
Query OK, 1 row affected (0.002 sec)

SELECT LAST_NAME, AVG(SALARY) OVER(PARTITION BY JOB_ID) AVG_S FROM EXPLOYEES;
+-----------+------------+
| LAST_NAME | AVG_S      |
+-----------+------------+
| JIM       |  2000.0000 |
| MIKE      | 12000.0000 |
| LILY      | 12000.0000 |
| TOM       | 12000.0000 |
+-----------+------------+
4 rows in set (0.002 sec)