MariaDB – 做出類似 Excel 的 SUMIF , COUNTIF 的功能

測試環境為 CentOS 8 x86_64 虛擬機

MariaDB 有類似 Excel 的 SUMIF , COUNTIF 函數可以使用嗎? 答案是沒有,但我們可以使用 CASE OPERATOR 流程控制函數 (Control Flow Functions) 搭配 SUM , COUNT 函數即可達到相同的功能,來看下面範例說明.

先建立測試要用的資料庫.

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 25
Server version: 5.5.60-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;
Query OK, 1 row affected (0.00 sec)
 
MariaDB [(none)]> USE testdb;
Database changed

建立使用者與其測試分數資料表.

MariaDB [testdb]> CREATE TABLE testscore (ID1 INT(11) NOT NULL AUTO_INCREMENT , name VARCHAR(20) NOT NULL , score INT(11) NOT NULL , PRIMARY KEY (ID1));
Query OK, 0 rows affected (0.007 sec)

新增使用者與其測試分數資料.

MariaDB [testdb]> INSERT INTO testscore (name , score) VALUES('A',95) , ('B',91) , ('C',65) , ('D',68) , ('E',77) , ('F',87) , ('G',98) , ('H',45);
Query OK, 8 rows affected (0.002 sec)
Records: 8  Duplicates: 0  Warnings: 0

MariaDB [testdb]> SELECT * FROM testscore;
+-----+------+-------+
| ID1 | name | score |
+-----+------+-------+
|   1 | A    |    95 |
|   2 | B    |    91 |
|   3 | C    |    65 |
|   4 | D    |    68 |
|   5 | E    |    77 |
|   6 | F    |    87 |
|   7 | G    |    98 |
|   8 | H    |    45 |
+-----+------+-------+
8 rows in set (0.000 sec)

我要怎麼算出 100-90 , 89-80 , 79-70 , 69-60 以及低於 60 區間的人數與平均呢?

使用 COUNT , SUM 時搭配 CASE OPERATOR 關於 流程控制函數 ( Control Flow Functions ) 請參考 – https://benjr.tw/102747 說明.

MariaDB [(none)]> HELP CASE OPERATOR;
Name: 'CASE OPERATOR'
Description:
Syntax:
CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN
result ...] [ELSE result] END
 
CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...]
[ELSE result] END

COUNT

下面範例找出分數區間 > 90 (100-90)分的.

MariaDB [testdb]> SELECT COUNT(CASE WHEN score >90 THEN 1 END) AS '100-90' FROM testscore;
+--------+
| 100-90 |
+--------+
|      3 |
+--------+
1 row in set (0.001 sec)

或是

MariaDB [testdb]> SELECT COUNT(CASE WHEN score >90 THEN score END) AS '100-90' FROM testscore;
+--------+
| 100-90 |
+--------+
|      3 |
+--------+
1 row in set (0.000 sec)

下面範例找出分數區間 > 90 (100-90) 與 (89-80) 區間的.

MariaDB [testdb]> SELECT COUNT(CASE WHEN score >90 THEN 1 END) AS '100-90' , COUNT(CASE WHEN score >=80 AND score <= 89 THEN 1 END) AS '89-80' FROM testscore;
+--------+-------+
| 100-90 | 89-80 |
+--------+-------+
|      3 |     1 |
+--------+-------+
1 row in set (0.001 sec)

其他區間使用方式一樣就不詳細說明.

SUM

下面範例找出分數區間 > 90 (100-90)分的平均分數.

MariaDB [testdb]> SELECT SUM(CASE WHEN score >90 THEN Score END)/COUNT(CASE WHEN score >90 THEN 1 END) AS '100-90' FROM testscore;
+---------+
| 100-90  |
+---------+
| 94.6667 |
+---------+
1 row in set (0.001 sec)