文档中心

AVG ​

功能描述 ​

计算一组数值的平均值。

语法格式 ​

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

参数说明 ​

  • expr：列名或表达式，用来指定输入数据。
  • OVER (analytic_clause)：可选的表达式，用于分析函数，analytic_clause是常用的分析类语句，如PARTITION BY等。

函数返回类型 ​

DOUBLE或NUMERIC类型。

示例 ​

sql
SQL> CREATE TABLE employees (
        employee_id INT IDENTITY(1,1) PRIMARY KEY,
        manager_id INT NOT NULL,
        last_name VARCHAR(50) NOT NULL,
        hire_date DATE NOT NULL,
        salary NUMERIC(10, 2) NOT NULL
    );

SQL> INSERT INTO employees (manager_id, last_name, hire_date, salary) VALUES
    (100, 'De Haan', TO_DATE('2001-01-13', 'YYYY-MM-DD'), 17000),
    (100, 'Raphaely', TO_DATE('2002-12-07', 'YYYY-MM-DD'), 11000),
    (100, 'Kaufling', TO_DATE('2003-05-01', 'YYYY-MM-DD'), 7900),
    (100, 'Hartstein', TO_DATE('2004-02-17', 'YYYY-MM-DD'), 13000),
    (100, 'Weiss', TO_DATE('2004-07-18', 'YYYY-MM-DD'), 8000),
    (100, 'Russell', TO_DATE('2004-10-01', 'YYYY-MM-DD'), 14000);

聚合函数 ​

sql
SQL> SELECT AVG(salary) "AVERAGE" FROM employees;

AVERAGE | 
------------------------------------------------------------------------------
11816.6666667|

分析函数 ​

sql
SQL> SELECT manager_id, last_name,hire_date,salary,
         AVG(salary) OVER (
           PARTITION BY manager_id ORDER BY hire_date
           ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
         ) AS c_mavg
    FROM employees;

MANAGER_ID | LAST_NAME | HIRE_DATE | SALARY | C_MAVG | 
------------------------------------------------------------------------------
100 | De Haan| 2001-01-13 AD | 17000| 14000|
100 | Raphaely| 2002-12-07 AD | 11000| 11966.6666667|
100 | Kaufling| 2003-05-01 AD | 7900| 10633.3333333|
100 | Hartstein| 2004-02-17 AD | 13000| 9633.3333333|
100 | Weiss| 2004-07-18 AD | 8000| 11666.6666667|
100 | Russell| 2004-10-01 AD | 14000| 11000|