在 MAriaDB 裡面提到關於 Window Functions Overview – https://mariadb.com/kb/en/window-functions-overview/
關於這些 Function 函數定義請參考.
- Window Functions – https://mariadb.com/kb/en/window-functions/
- Aggregate Functions – https://mariadb.com/kb/en/aggregate-functions/
- Aggregate Functions as Window Functions – https://mariadb.com/kb/en/aggregate-functions-as-window-functions/
使用方式可以搭配 OVER 來使用,這邊討論 OVER 與 PARTITION BY 的用法.
function (expression) OVER ( [ PARTITION BY expression_list ] [ ORDER BY order_list [ frame_clause ] ] )
測試環境為 CentOS 8 x86_64 , Mariadb 10.3.17-1 (虛擬機)
先建立一個測試用資料庫 (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 |
+----+------+---------+----------+--------+---------------+
| 2 | Afa | Power | Engineer | 48000 | Afa@benjr.tw |
| 1 | Ben | Testing | Engineer | 45000 | ben@benjr.tw |
| 3 | Boss | Testing | Manager | 75000 | Boss@benjr.tw |
| 5 | Ian | PM | Engineer | 85000 | ian@benjr.tw |
| 4 | JoJo | Testing | Engineer | 45000 | jojo@benjr.tw |
+----+------+---------+----------+--------+---------------+
5 rows in set (0.001 sec)
GROUP WITH ROLLUP
通常我們利用 GROUP 之後再加上 WITH ROLLUP 來統計這些分類的加總.
MariaDB [testdb]> SELECT IFNULL(Dept,'Total') AS DEPT, SUM(Salary) FROM employee GROUP BY Dept WITH ROLLUP; +---------+-------------+ | DEPT | SUM(Salary) | +---------+-------------+ | PM | 85000 | | Power | 48000 | | Testing | 165000 | | Total | 298000 | +---------+-------------+ 4 rows in set, 1 warning (0.001 sec)
函數說明:
- IFNULL : 用來判斷 Expression 字串(第一個參數)是否為 NULL ,是就以 ALTER_Value (第二個參數)來取代.
OVER PARTITION BY
有辨法能直接多一的欄位來顯示該單位的薪水總數嗎? 可以透過 OVER 的方式來處理.
MariaDB [testdb]> SELECT Name , Dept , JobTitle , Salary, SUM(Salary) OVER (PARTITION BY Dept) AS Dept_Total FROM employee; +------+---------+----------+--------+------------+ | Name | Dept | JobTitle | Salary | Dept_Total | +------+---------+----------+--------+------------+ | Ian | PM | Engineer | 85000 | 85000 | | Afa | Power | Engineer | 48000 | 48000 | | Ben | Testing | Engineer | 45000 | 165000 | | JoJo | Testing | Engineer | 45000 | 165000 | | Boss | Testing | Manager | 75000 | 165000 | +------+---------+----------+--------+------------+ 5 rows in set (0.001 sec)
來看一下 OVER PARTITION BY 的使用方式.
SUM(Salary) OVER (PARTITION BY Dept)
- PARTITION BY Dept – 會依據 Dept 欄位來分類.
- SUM(Salary) OVER – 會依據 PARTITION BY Dept 分類的結果來加總 (SUM).
- AS Dept_Total – 欄位就會顯示依據各單位 Dept 欄位的加總
如果 OVER 不指定欄位,就會把全部資料的 Salary 欄位加總.
MariaDB [testdb]> SELECT Name , Dept , JobTitle , Salary, SUM(Salary) OVER () AS Total FROM employee; +------+---------+----------+--------+--------+ | Name | Dept | JobTitle | Salary | Total | +------+---------+----------+--------+--------+ | Ben | Testing | Engineer | 45000 | 298000 | | JoJo | Testing | Engineer | 45000 | 298000 | | Afa | Power | Engineer | 48000 | 298000 | | Boss | Testing | Manager | 75000 | 298000 | | Ian | PM | Engineer | 85000 | 298000 | +------+---------+----------+--------+--------+ 5 rows in set (0.001 sec)
可以利用各單位與全公司薪資資料來計算個別單位的薪水的比率.
MariaDB [testdb]> SELECT Name , Dept , JobTitle , Salary, SUM(Salary) OVER (PARTITION BY Dept) AS Dept_Total , CONCAT(TRUNCATE(SUM(Salary) OVER(PARTITION BY Dept)*100/SUM(Salary) OVER(),2),"%") AS Percentage FROM employee; +------+---------+----------+--------+------------+------------+ | Name | Dept | JobTitle | Salary | Dept_Total | Percentage | +------+---------+----------+--------+------------+------------+ | Ian | PM | Engineer | 85000 | 85000 | 28.52% | | Afa | Power | Engineer | 48000 | 48000 | 16.10% | | JoJo | Testing | Engineer | 45000 | 165000 | 55.36% | | Ben | Testing | Engineer | 45000 | 165000 | 55.36% | | Boss | Testing | Manager | 75000 | 165000 | 55.36% | +------+---------+----------+--------+------------+------------+ 5 rows in set (0.002 sec)
函數說明:
- SUM : 數字加總.
- CONCAT : 可以將兩個或更多個字串連接在一起.
- TRUNCATE : 取小數點,不計算四捨五入.
OVER PARTITION BY+GROUP BY
使用 OVER PARTITION 的欄位沒有辦法再依據 Dept 欄位來做 GROUP BY,下面範例可看到 Salary 的加總是錯誤的(正確是使用 SUM 函數搭配 GROUP BY Dept 來使用).
MariaDB [testdb]> SELECT Dept , SUM(Salary) OVER (PARTITION BY Dept) AS Dept_Total , CONCAT(TRUNCATE(SUM(Salary) OVER(PARTITION BY Dept)*100/SUM(Salary) OVER(),2),"%") AS Percentage FROM employee GROUP BY Dept; +---------+------------+------------+ | Dept | Dept_Total | Percentage | +---------+------------+------------+ | PM | 85000 | 47.75% | | Power | 48000 | 26.96% | | Testing | 45000 | 25.28% | +---------+------------+------------+ 3 rows in set (0.001 sec)
例外一種處理方式是透過 CTE (Common Table Expression) 暫存資料表的方式來處理,詳細使用語法請參考 – https://benjr.tw/102498
使用 CTE 把剛剛 SELECT 的資料表暫存名稱為 Salary_temp ,也把其中各單位薪水總數欄位別名為 dep_sum ,及全公司薪水欄位別名為 total_sum.後續再從暫存資料表 Salary_temp 中 SELECT+ GROUP BY 需要的資料.
MariaDB [testdb]> WITH Salary_temp as (SELECT Name , Dept , JobTitle , Salary, SUM(Salary) OVER (PARTITION BY Dept) dep_sum , SUM(Salary) OVER () total_sum FROM employee) SELECT Dept , dep_sum AS Dept_Total , total_sum AS Total , dep_sum*100/total_sum AS Percentage FROM Salary_temp GROUP BY Dept; +---------+------------+--------+------------+ | Dept | Dept_Total | Total | Percentage | +---------+------------+--------+------------+ | PM | 85000 | 298000 | 28.5235 | | Power | 48000 | 298000 | 16.1074 | | Testing | 165000 | 298000 | 55.3691 | +---------+------------+--------+------------+ 3 rows in set (0.001 sec)
沒有解決問題,試試搜尋本站其他內容