SQL 語法 – OVER 與 PARTITION BY 用法

在 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 ,請參考資料庫編碼 – http://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) 暫存資料表的方式來處理,詳細使用語法請參考 – http://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)
沒有解決問題,試試搜尋本站其他內容

發佈留言

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

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