MariaDB – 常用搭配 GROUP BY 函數 ( Aggregate Functions )

Loading

測試環境為 CentOS 8 x86_64 虛擬機.

  1. 字串函數 ( String Functions ) 與 型別轉換 https://benjr.tw/102953
  2. 數字函數 (Numeric Functions)https://benjr.tw/101970
  3. 日期與時間函數 (Date & Time Functions)https://benjr.tw/102964
  4. 資訊函數 (Information Functions)https://benjr.tw/102961
  5. 搭配 GROUP BY 函數 (Aggregate Functions) – 這邊介紹.
  6. 流程控制函數 (Control Flow Functions)https://benjr.tw/102747

先建立一個測試用資料庫 (testdb) , 與 tables (employee) ,下面範例會使用到 . 格式為 K1 int(11) – auto_increment & PRIMARY KEY , Name char(20), Dept char(20), jobTitle char(20) ,email char(30) 以及 Salary int(11).

SELECT 查詢需注意資料庫字元編碼 Character Sets 與 文字排序 Collations ,請參考資料庫編碼 – https://benjr.tw/102156 .

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 10.3.11-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CREATE DATABASE testdb DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> USE testdb;
Database changed
MariaDB [testdb]> CREATE TABLE employee (K1 int(11) NOT NULL auto_increment, Name char(20) NOT NULL, Dept char(20) NOT NULL, JobTitle char(20) NOT NULL, Salary int(11) NOT NULL , email char(30) NOT NULL, PRIMARY KEY (K1)) ;
Query OK, 0 rows affected (0.010 sec)
 
MariaDB [testdb]> DESCRIBE employee; 
+----------+----------+------+-----+---------+----------------+
| Field    | Type     | Null | Key | Default | Extra          |
+----------+----------+------+-----+---------+----------------+
| K1       | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name     | char(20) | NO   |     | NULL    |                |
| Dept     | char(20) | NO   |     | NULL    |                |
| JobTitle | char(20) | NO   |     | NULL    |                |
| Salary   | int(11)  | NO   |     | NULL    |                |
| email    | char(30) | NO   |     | NULL    |                |
+----------+----------+------+-----+---------+----------------+
6 rows in set (0.004 sec)

透過 INSERT 新增加了五筆資料.

MariaDB [testdb]> INSERT INTO employee (Name , Dept , JobTitle , Salary , email) VALUES ('Ben' , 'Testing' , 'Engineer' , '45000' , 'ben@benjr.tw') , ('Afa' , 'Power' , 'Engineer' , '48000' , 'Afa@benjr.tw') , ('Boss' ,'Testing' , 'Manager' , '75000' , 'Boss@benjr.tw') , ('JoJo' , 'Testing' , 'Engineer' , '45000' , 'jojo@benjr.tw') ,  ('Ian' , 'PM' , 'Engineer' , '85000' , 'ian@benjr.tw');
Query OK, 5 rows affected (0.002 sec)
Records: 5  Duplicates: 0  Warnings: 0
 
MariaDB [testdb]> SELECT * FROM employee;
+----+------+---------+----------+--------+---------------+
| K1 | Name | Dept    | JobTitle | Salary | email         |
+----+------+---------+----------+--------+---------------+
|  1 | Ben  | Testing | Engineer |  45000 | ben@benjr.tw  |
|  2 | Afa  | Power   | Engineer |  48000 | Afa@benjr.tw  |
|  3 | Boss | Testing | Manager  |  75000 | Boss@benjr.tw |
|  4 | JoJo | Testing | Engineer |  45000 | jojo@benjr.tw |
|  5 | Ian  | PM      | Engineer |  85000 | ian@benjr.tw  |
+----+------+---------+----------+--------+---------------+
5 rows in set (0.001 sec)

下面幾個函數通常搭配 GROUP BY 來使用.

  • AVG
    計算數字平均.

    MariaDB [testdb]> SELECT AVG(Salary) FROM employee;
    +-------------+
    | AVG(Salary) |
    +-------------+
    |  59600.0000 |
    +-------------+
    1 row in set (0.001 sec)
    
    MariaDB [testdb]> SELECT Dept , AVG(Salary) FROM employee GROUP BY Dept;
    +---------+-------------+
    | Dept    | AVG(Salary) |
    +---------+-------------+
    | PM      |  85000.0000 |
    | Power   |  48000.0000 |
    | Testing |  55000.0000 |
    +---------+-------------+
    3 rows in set (0.001 sec)
    
  • COUNT
    回傳 SELECT 的筆數.

    COUNT(expression)
    

    利用 COUNT(*) 來看回傳的資料筆數.

    MariaDB [testdb]> SELECT GROUP_CONCAT(Name SEPARATOR ',') AS "Member Name" , COUNT(*) COUNT FROM employee;
    +---------------------------+-------+
    | Member Name               | COUNT |
    +---------------------------+-------+
    | Ben,Afa,Boss,JoJo,Ian,Mat |     6 |
    +---------------------------+-------+
    1 row in set (0.000 sec)
    

    可以利用 COUNT(DISTINCT(column)) 或是 COUNT(DISTINCT column) 來計算不重覆的欄位有幾個.

    MariaDB [testdb]> SELECT GROUP_CONCAT(DISTINCT Dept SEPARATOR ',') AS "Dept Name" , COUNT(DISTINCT(Dept)) AS Dept FROM employee;
    +------------------+------+
    | Dept Name        | Dept |
    +------------------+------+
    | Testing,Power,PM |    3 |
    +------------------+------+
    1 row in set (0.001 sec)
    

    COUNT 還可以搭配 IF 來使用 COUNT(IF(express,TRUE,NULL))

    MariaDB [testdb]> SELECT COUNT(IF( Salary > 50000,TRUE,NULL)) AS Dept FROM employee;
    +------+
    | Dept |
    +------+
    |    2 |
    +------+
    1 row in set (0.002 sec)
    
  • GROUP_CONCAT
    通常搭配 GROUP BY 使用,透過 GROUP_CONCAT 函數,可以把同 Group 的欄位文字整合在一起.

    GROUP_CONCAT([DISTINCT] expr [,expr ...]
                 [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
                 [SEPARATOR str_val])
    
    MariaDB [testdb]> SELECT Dept, GROUP_CONCAT(Name SEPARATOR ',') AS "Member Name" FROM employee GROUP BY Dept;
    +---------+---------------+
    | Dept    | Member Name   |
    +---------+---------------+
    | PM      | Ian           |
    | Power   | Afa           |
    | Testing | Ben,Boss,JoJo |
    +---------+---------------+
    3 rows in set (0.001 sec)
    

    如果 GROUPBY 的資料要按照順序,可以加 ORDER BY DESC 或是 ASC

    MariaDB [testdb]> SELECT Dept, GROUP_CONCAT(Name ORDER BY Name DESC SEPARATOR ',') AS "Member Name" FROM employee GROUP BY Dept;
    +---------+---------------+
    | Dept    | Member Name   |
    +---------+---------------+
    | PM      | Ian           |
    | Power   | Afa           |
    | Testing | JoJo,Boss,Ben |
    +---------+---------------+
    3 rows in set (0.001 sec)
    
    MariaDB [testdb]> SELECT Dept, GROUP_CONCAT(Name ORDER BY Name ASC SEPARATOR ',') AS "Member Name" FROM employee GROUP BY Dept;
    +---------+---------------+
    | Dept    | Member Name   |
    +---------+---------------+
    | PM      | Ian           |
    | Power   | Afa           |
    | Testing | Ben,Boss,JoJo |
    +---------+---------------+
    3 rows in set (0.001 sec)
    
  • MAX , MIN
    MAX() 函數會返回數值中最大的.

    MariaDB [testdb]> SELECT MAX(Salary) FROM employee;
    +-------------+
    | MAX(Salary) |
    +-------------+
    |       85000 |
    +-------------+
    1 row in set (0.001 sec)
    
    MariaDB [testdb]> SELECT Name , Dept , Salary FROM employee WHERE Salary = (SELECT MAX(Salary) FROM employee);
    +------+------+--------+
    | Name | Dept | Salary |
    +------+------+--------+
    | Ian  | PM   |  85000 |
    +------+------+--------+
    1 row in set (0.003 sec)
    

    MIN() 函數會返回數值中最小的.

    MariaDB [testdb]> SELECT MIN(Salary) FROM employee;
    +-------------+
    | MIN(Salary) |
    +-------------+
    |       45000 |
    +-------------+
    1 row in set (0.001 sec)
    
    MariaDB [testdb]> SELECT Name , Dept , Salary FROM employee WHERE Salary = (SELECT MIN(Salary) FROM employee);
    +------+---------+--------+
    | Name | Dept    | Salary |
    +------+---------+--------+
    | Ben  | Testing |  45000 |
    | JoJo | Testing |  45000 |
    +------+---------+--------+
    2 rows in set (0.001 sec)
    
  • SUM
    計算數字加總.

    MariaDB [testdb]> SELECT SUM(Salary) FROM employee;
    +-------------+
    | SUM(Salary) |
    +-------------+
    |      298000 |
    +-------------+
    1 row in set (0.001 sec)
    

    通常搭配 GROUP BY 使用,可以依據指定欄位將數字欄位把數字加總,還可以搭背 WITH ROLLUP 可以把分類的資料做個總結(數字部分),詳細範例請參考 – https://benjr.tw/98988 (GROUP BY , HAVING , ORDER BY 以及 LIMIT).

    MariaDB [testdb]> SELECT Dept , SUM(Salary) FROM employee GROUP BY Dept;
    +---------+-------------+
    | Dept    | SUM(Salary) |
    +---------+-------------+
    | PM      |       85000 |
    | Power   |       48000 |
    | Testing |      165000 |
    +---------+-------------+
    3 rows in set (0.002 sec)
    
沒有解決問題,試試搜尋本站其他內容

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

這個網站採用 Akismet 服務減少垃圾留言。進一步了解 Akismet 如何處理網站訪客的留言資料