MySQL數(shù)據(jù)庫如何生成分組排序的序號
經(jīng)常進行數(shù)據(jù)分析的小伙伴經(jīng)常會需要生成序號或進行數(shù)據(jù)分組排序并生成序號。在MySQL8.0中可以使用窗口函數(shù)來實現(xiàn)，可以參考歷史文章有了這些函數(shù)，統(tǒng)計分析事半功倍進行了解。而MySQL5.7中由于沒有這類函數(shù)，該如何實現(xiàn)呢，下面對比MySQL8.0，列舉兩種情況的實現(xiàn)。
1. 數(shù)據(jù)準備
創(chuàng)建一張演示表
#創(chuàng)建表CREATE TABLE users (id INT PRIMARY KEY,group_id INT,c_name VARCHAR(64));
插入演示數(shù)據(jù)
-- 插入10行數(shù)據(jù)INSERT INTO users VALUES (1, 1, '張三');INSERT INTO users VALUES (2, 1, '李四');INSERT INTO users VALUES (3, 2, '王五');INSERT INTO users VALUES (4, 2, '趙六');INSERT INTO users VALUES (5, 3, '錢七');INSERT INTO users VALUES (6, 1, '周八');INSERT INTO users VALUES (7, 2, '吳九');INSERT INTO users VALUES (8, 3, '鄭十');INSERT INTO users VALUES (9, 1, '孫十一');INSERT INTO users VALUES (10, 3, '李十二');
2. 生成序號
2.1 使用窗口函數(shù)ROW_NUMBER()實現(xiàn)
在MySQL8.0中可以直接使用窗口函數(shù)ROW_NUMBER()來實現(xiàn)序號的生成，例如
# 根據(jù)c_name字段進行排序生成序號SELECTROW_NUMBER() OVER (ORDER BY c_name) AS row_num,id,c_nameFROMusers;
結(jié)果如下：
+---------+----+-----------+| row_num | id | c_name |+---------+----+-----------+| 1 | 7 | 吳九 || 2 | 6 | 周八 || 3 | 9 | 孫十一 || 4 | 1 | 張三 || 5 | 10 | 李十二 || 6 | 2 | 李四 || 7 | 3 | 王五 || 8 | 4 | 趙六 || 9 | 8 | 鄭十 || 10 | 5 | 錢七 |+---------+----+-----------+10 rows in set, 1 warning (0.00 sec)
2.2 低版本MySQL中的實現(xiàn)
因為在MySQL8.0版本之前無ROW_NUMBER()窗口函數(shù)，因此需要結(jié)束變量來實現(xiàn)。具體示例如下：
SET @row_num = 0;
SELECT(@row_num:=@row_num + 1) AS row_num,id,c_nameFROMusersORDER BYc_name;
結(jié)果如下：
+---------+----+-----------+| row_num | id | c_name |+---------+----+-----------+| 1 | 7 | 吳九 || 2 | 6 | 周八 || 3 | 9 | 孫十一 || 4 | 1 | 張三 || 5 | 10 | 李十二 || 6 | 2 | 李四 || 7 | 3 | 王五 || 8 | 4 | 趙六 || 9 | 8 | 鄭十 || 10 | 5 | 錢七 |+---------+----+-----------+10 rows in set, 1 warning (0.00 sec)
注意：每次執(zhí)行前需要將@row_num重新設(shè)置為0 ，即執(zhí)行SET @row_num = 0;
3. 分組后排序
3.1 繼續(xù)使用窗口函數(shù)ROW_NUMBER()實現(xiàn)
在MySQL8.0中可以繼續(xù)使用窗口函數(shù)ROW_NUMBER()來實現(xiàn)分組排序的功能，例如：
SELECTid,group_id,c_name,ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY id) AS row_numFROMusersORDER BYgroup_id, id;
運行結(jié)果如下：
+----+----------+-----------+---------+| id | group_id | c_name | row_num |+----+----------+-----------+---------+| 1 | 1 | 張三 | 1 || 2 | 1 | 李四 | 2 || 6 | 1 | 周八 | 3 || 9 | 1 | 孫十一 | 4 || 3 | 2 | 王五 | 1 || 4 | 2 | 趙六 | 2 || 7 | 2 | 吳九 | 3 || 5 | 3 | 錢七 | 1 || 8 | 3 | 鄭十 | 2 || 10 | 3 | 李十二 | 3 |+----+----------+-----------+---------+10 rows in set (0.00 sec)
3.2 低版本MySQL中的實現(xiàn)
因為涉及到分組及分組后排序，因此需要引入2個變量，一個用于分組標識，一個用于組內(nèi)排序標識，示例如下：
SET @row_num = 0;SET @g_id = NULL;
SELECTid,group_id,c_name,@row_num := CASEWHEN @g_id = group_id THEN @row_num + 1ELSE 1END AS row_num,@g_id := group_id AS v_gidFROMusersORDER BYgroup_id, id;
運行結(jié)果如下：
+----+----------+-----------+---------+-------+| id | group_id | c_name | row_num | v_gid |+----+----------+-----------+---------+-------+| 1 | 1 | 張三 | 1 | 1 || 2 | 1 | 李四 | 2 | 1 || 6 | 1 | 周八 | 3 | 1 || 9 | 1 | 孫十一 | 4 | 1 || 3 | 2 | 王五 | 1 | 2 || 4 | 2 | 趙六 | 2 | 2 || 7 | 2 | 吳九 | 3 | 2 || 5 | 3 | 錢七 | 1 | 3 || 8 | 3 | 鄭十 | 2 | 3 || 10 | 3 | 李十二 | 3 | 3 |+----+----------+-----------+---------+-------+10 rows in set, 2 warnings (0.00 sec)
這樣就實現(xiàn)了分組及排序的序號生成。
往期精彩回顧
2. mysql8.0新增用戶及加密規(guī)則修改的那些事
3. 比hive快10倍的大數(shù)據(jù)查詢利器-- presto
4. 監(jiān)控利器出鞘：Prometheus+Grafana監(jiān)控MySQL、Redis數(shù)據(jù)庫
5. PostgreSQL主從復(fù)制--物理復(fù)制
6. MySQL傳統(tǒng)點位復(fù)制在線轉(zhuǎn)為GTID模式復(fù)制
掃碼關(guān)注