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函式計算集合的平均值。