SQL Avg()函數

在本教程中，我們將演示如何使用SQL AVG函數來獲取集合的平均值。

1. SQL AVG函數簡介

SQL AVG函數是一個聚合函數，用於計算集合的平均值。 以下說明了SQL AVG函數的語法：

AVG([ALL|DISTINCT] expression)

如果使用ALL關鍵字，AVG函數將獲取計算中的所有值。 默認情況下，無論是否指定，AVG函數都使用ALL。

如果明確指定DISTINCT關鍵字，AVG函數將僅在計算中採用唯一值。

例如，有一組數據集(1,2,3,3,4)並將AVG(ALL)應用於此集合，AVG函數將執行以下計算：

(1+2+3+3+4)/5 = 2.6

但是，如果指定：AVG(DISTINCT)將按如下方式處理：

(1+2+3+4)/4 = 2.5

2. SQL AVG函數示例

將使用示例數據庫中的employees表來演示SQL AVG函數的工作原理。 以下圖片說明了employees表的結構：

mysql> desc employees;
+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| employee_id   | int(11)      | NO   | PRI | NULL    | auto_increment |
| first_name    | varchar(20)  | YES  |     | NULL    |                |
| last_name     | varchar(25)  | NO   |     | NULL    |                |
| email         | varchar(100) | NO   |     | NULL    |                |
| phone_number  | varchar(20)  | YES  |     | NULL    |                |
| hire_date     | date         | NO   |     | NULL    |                |
| job_id        | int(11)      | NO   | MUL | NULL    |                |
| salary        | decimal(8,2) | NO   |     | NULL    |                |
| manager_id    | int(11)      | YES  | MUL | NULL    |                |
| department_id | int(11)      | YES  | MUL | NULL    |                |
+---------------+--------------+------+-----+---------+----------------+
10 rows in set

要計算所有員工的平均工資，請將AVG函數應用於salary列，如下所示：

SELECT 
    AVG(salary)
FROM
    employees;

執行上面示例代碼，得到以下結果：

+-------------+
| AVG(salary) |
+-------------+
| 8060        |
+-------------+
1 row in set

應用DISTINCT運算符來查看結果是否發生了變化：

SELECT 
    AVG(DISTINCT salary)
FROM
    employees;

執行上面示例代碼，得到以下結果：

+----------------------+
| AVG(DISTINCT salary) |
+----------------------+
| 7845.454545          |
+----------------------+
1 row in set

如上結果有改變，因爲一些員工有相同的薪水。要將結果舍入爲2位小數，請使用ROUND函數，如下所示：

SELECT 
    ROUND(AVG(DISTINCT salary), 2)
FROM
    employees;

要計算值子集的平均值，在SELECT語句中添加WHERE子句。 例如，要計算部門ID爲5中員工的平均工資，使用以下查詢：

SELECT
    AVG(DISTINCT salary)
FROM
    employees
WHERE
    department_id = 5;

以下語句返回工作崗位ID爲6的員工的平均工資：

SELECT 
    AVG(salary)
FROM
    employees
WHERE
    job_id = 6;

執行上面示例代碼，得到以下結果：

+-------------+
| AVG(salary) |
+-------------+
| 7920        |
+-------------+
1 row in set

帶有GROUP BY子句的SQL AVG示例

要計算組的平均值，將AVG函數與GROUP BY子句一起使用。 例如，以下語句返回各部門的員工和員工的平均工資。

SELECT
    department_id,
    AVG(salary)
FROM
    employees
GROUP BY
    department_id;

執行上面示例代碼，得到以下結果：

可以使用inner join子句將employees表與departments表連接起來以獲取部門名稱數據：

SELECT
    e.department_id,
    department_name,
    AVG(salary)
FROM
    employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
    e.department_id;

執行上面示例代碼，得到以下結果：

帶有ORDER BY子句的SQL AVG示例

要對包含AVG結果的結果集進行排序，請使用AVG函數與ORDER BY子句，如下所示：

SELECT
    e.department_id,
    department_name,
    AVG(salary)
FROM
    employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
    e.department_id
ORDER BY
    AVG(salary) DESC;

執行上面查詢語句，得到以下結果：

SQL AVG與HAVING子句示例

要過濾分組，請AVG函數中使用HAVING子句。 例如，以下語句獲取平均薪水小於5000的部門：

SELECT
    e.department_id,
    department_name,
    AVG(salary) AS avgsalary
FROM
    employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
    e.department_id
HAVING avgsalary<5000
ORDER BY
    AVG(salary) DESC;

執行上面查詢語句，得到以下結果：

+---------------+-----------------+-----------+
| department_id | department_name | avgsalary |
+---------------+-----------------+-----------+
|             1 | 管理            | 4400      |
|             3 | 採購            | 4150      |
+---------------+-----------------+-----------+
2 rows in set

SQL AVG與子查詢

可以在單個SQL語句中多次應用AVG函數來計算一組平均值的平均值。

例如，可以使用AVG函數計算每個部門員工的平均工資，並再次應用AVG函數來計算部門的平均工資。

以下查詢演示了這個實現：

SELECT
    AVG(employee_sal_avg)
FROM
    (
        SELECT
            AVG(salary) employee_sal_avg
        FROM
            employees
        GROUP BY
            department_id
    ) t;

查詢語句的工作原理。

• 子查詢返回每個部門的一組員工平均工資。
• 外部查詢返回部門的平均工資。

在本教程中，您學習瞭如何使用SQL AVG函數計算集合的平均值。