iT邦幫忙

DAY 23
10

MySQL那些事兒系列 第 23

MySQL 分組排名的方法

在第10天介紹了排名的方法,今天將介紹分組排名的方法.
首先建立範例TABLE,以銷售紀錄為例,假設有不同的單位,
例如營業一課,營業一課等.實務上不會只有這樣一個TABLE,
為簡化使易於了解,故用一個TABLE來作範例,並輸入一些
較極端的測試資料.

CREATE TABLE ithelp1023(
sale_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
dept CHAR(10) NOT NULL,
sales_person CHAR(10) NOT NULL,
sales_date DATE NOT NULL,
sales_amt INT UNSIGNED NOT NULL
);

INSERT INTO ithelp1023(dept, sales_person, sales_date, sales_amt) VALUES
('營業一課', '初音', '2013-10-01', 34),
('營業一課', '櫻木', '2013-10-02', 50),
('營業一課', '櫻木', '2013-10-03', 50),
('營業一課', '櫻木', '2013-10-04', 50),
('營業一課', '希崎', '2013-10-05', 47),
('營業二課', '吉澤', '2013-10-06', 33),
('營業二課', '大橋', '2013-10-07', 33),
('營業二課', '上原', '2013-10-08', 33),
('營業二課', '椎名', '2013-10-09', 33),
('營業二課', '仁科', '2013-10-10', 33),
('營業三課', '七瀨', '2013-10-11', 25),
('營業三課', '鶴田', '2013-10-11', 10),
('營業四課', '並木', '2013-10-12', 10),
('營業四課', '西野', '2013-10-13', 50),
('營業四課', '二宮', '2013-10-14', 30),
('營業四課', '北川', '2013-10-15', 40),
('營業四課', '高橋', '2013-10-16', 20);

我們以單位來分組,依照銷售金額來排名.
其他資料庫對於此種分組排名,有新的語法,
可以比較方便的處理.MySQL並未support這些
新的語法,但是MySQL有兩個特殊的函數,
GROUP_CONCAT() 與 FIND_IN_SET(),
將其搭配使用,可以產生有趣的結果.

SELECT j.dept
     , FIND_IN_SET(j.sales_amt, (SELECT GROUP_CONCAT(sales_amt ORDER BY sales_amt DESC)
                                  FROM ithelp1023 i WHERE i.dept = j.dept)) AS MyRank
     , j.sales_person
     , j.sales_date
     , j.sales_amt
  FROM ithelp1023 j
 ORDER BY j.dept, MyRank;

+--------------+--------+--------------+------------+-----------+
| dept         | MyRank | sales_person | sales_date | sales_amt |
+--------------+--------+--------------+------------+-----------+
| 營業一課     |      1 | 櫻木         | 2013-10-02 |        50 |
| 營業一課     |      1 | 櫻木         | 2013-10-03 |        50 |
| 營業一課     |      1 | 櫻木         | 2013-10-04 |        50 |
| 營業一課     |      4 | 希崎         | 2013-10-05 |        47 |
| 營業一課     |      5 | 初音         | 2013-10-01 |        34 |
| 營業三課     |      1 | 七瀨         | 2013-10-11 |        25 |
| 營業三課     |      2 | 鶴田         | 2013-10-11 |        10 |
| 營業二課     |      1 | 仁科         | 2013-10-10 |        33 |
| 營業二課     |      1 | 椎名         | 2013-10-09 |        33 |
| 營業二課     |      1 | 上原         | 2013-10-08 |        33 |
| 營業二課     |      1 | 大橋         | 2013-10-07 |        33 |
| 營業二課     |      1 | 吉澤         | 2013-10-06 |        33 |
| 營業四課     |      1 | 西野         | 2013-10-13 |        50 |
| 營業四課     |      2 | 北川         | 2013-10-15 |        40 |
| 營業四課     |      3 | 二宮         | 2013-10-14 |        30 |
| 營業四課     |      4 | 高橋         | 2013-10-16 |        20 |
| 營業四課     |      5 | 並木         | 2013-10-12 |        10 |
+--------------+--------+--------------+------------+-----------+
17 rows in set (0.00 sec)

如此就能輕鬆的依照分組來作排名.


上一篇
關於索引的一些探討(八)
下一篇
應用實例(一) 預算與實際費用計算
系列文
MySQL那些事兒30
0
0
ckp6250
iT邦研究生 4 級 ‧ 2019-10-30 11:36:40

受益良多,十分感恩。
經測試,若資料量大時,依原程式之方法,執行十分耗時,
略改良如下,可大幅提昇效率。

SELECT j.dept
     , FIND_IN_SET(j.sales_amt,i.sales_amt) AS MyRank
     , j.sales_person
     , j.sales_date
     , j.sales_amt
  FROM ithelp1023 j inner join (SELECT dept,GROUP_CONCAT(sales_amt ORDER BY sales_amt DESC) AS sales_amt FROM ithelp1023 group by dept) i
  ON j.dept=i.dept
 ORDER BY j.dept, MyRank;
看更多先前的回應...收起先前的回應...

蠻好的.
不過現在 MySQL 8 已經有 Window Functions ,
https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html

https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html

Rank() over(PARTITION BY ... order by ...)

這是以前我為了讓只使用MySQL 的人,可以做到分組排名,寫的示範用,
著重在易懂.
實際上我是會說,用 Postgresql吧. Use the force.

ckp6250 iT邦研究生 4 級 ‧ 2019-10-30 15:30:12 檢舉

您這30篇,我每篇都一一拜讀,得到許多啟發。
至於 Postgresql,早期年輕時,也有接觸過,
那時,mysql 還沒有 procedure,Postgresql好像就有了,
但,國內使用人數及參考書籍實在太少,入門困難。

mysql 仍然持續進步中,但還是有一些限制尚未突破,比如虛擬計算欄位,不能使用自訂函數,十分可惜。

另外,條件式索引 mysql 也尚未支援。

我認為PostgreSQL比mysql 強大,但恨不相逢未嫁時。

現在在臉書上有社群了.每個月都會辦活動,基本上我都會去.歡迎參加交流.
https://www.facebook.com/groups/pgsql.tw/

MySQL 有 Generated Columns.
MySQL 有 Stored Procedure Language ,
可以有 UDF.

另外在本站有兩個一級屠豬士,都是我使用的.
之前有用另一個,也回答了一些關於資料庫的,有興趣也可以參考一下.

ckp6250 iT邦研究生 4 級 ‧ 2019-10-30 16:25:10 檢舉

我用的是 mariadb 10.3
Stored Procedure 中可以使用 udf,
但 Generated Columns 中,僅能使用內建函數,不能使用 udf。

我對資料庫很有興趣,但,您知道的,投入越多,抽身越難,
mysql中寫了一大堆 Stored Procedure,轉換根本不可能。
只能望 PostgreSQL 而興嘆。

請教一下,PostgreSQL 對記憶體的需求及佔用,相比於 mysql 如何?

Postgresql 有趣的東西很多,要轉換也沒那樣難,因為 MySQL
的語法很簡單,而 Postgresql 這邊除了 PL/PGSQL, PL/Python,
PL/R, PL/V8 (Javascript), PL/Lua 等等一大堆.
記憶體都是自行看參數調整.沒有說哪種一定多.

ckp6250 iT邦研究生 4 級 ‧ 2019-10-30 17:33:02 檢舉

我先來拜讀您的【以Postgresql為主,聊聊資料庫】這30篇大作。
數十年來,我只遇過一家軟體公司使用 PostgreSQL , 真正曲高和寡的好物。

我和 PostgreSQL 的唯一連結,就是使用 vfp,透過 odbc 擷取資料而已。

Virtual FoxPro 啊....
我以前用過,當然也有用過 FoxBASE+.
還有 FoxBASE+ Mac
https://ithelp.ithome.com.tw/upload/images/20191030/20050647NbCZHXHwTI.jpg

我要留言

立即登入留言