MySQL 分組排序後 → 如何取前N條或倒數N條

開心一刻

　　晚上，老婆輔導女兒寫語文作業

　　填空題：春天來了，__綠了

　　女兒：春天來了，爸爸綠了

　　老婆一臉不悅地問道：你再說一遍，春天來了，什麼玩意綠了？

　　女兒：春天來了，爸爸綠了呀

　　老婆很生氣，但依舊溫柔地問道：春天來了，爸爸怎麼綠了呢

　　女兒略帶哭腔的說道：那冬天呢

　　老婆急的直撓頭：冬，冬，冬...，它跟天氣有什麼關係啊，那春天來了不應該是小草綠了嗎

　　女兒疑惑的左看右看，問道：那爸爸啥時候綠？

　　老婆氣的把筆一甩：我不知道你爸啥時候綠，你問你爸

　　女兒轉頭看向我，問道：爸爸，你啥時候綠？

　　我心裡咯噔一下，這小棉襖有點漏風呀，小心翼翼的看了一眼老婆，堅定地說道：爸爸是不會綠的！

前情回顧

　　前兩天翻自己的部落格的時候，翻到了：記一次有意思的 SQL 實現 → 分組後取每組的第一條記錄

　　突然意識到好像有續集沒寫

　　翻到結尾，果然有個留疑

　　但我要強調一點：這是我給你們的留疑，並不是我給你們的承諾！

　　我沒寫續集，你們可不能生氣，實在是生氣，那你來打我呀！

　　分組後取第一條記錄

　　我們先來簡單回顧下實現方式

　　1、迴圈查資料庫

　　　　邏輯很清晰，實現起來也很簡單，但是會迴圈查資料庫，開發規範一般會明確禁止這種寫法

　　2、 GROUP BY 結合 MySQL 函式 

　　　　 GROUP BY 之後，用 GROUP_CONCAT(log_id ORDER BY data_date DESC,modify_time DESC) 對 log_id 進行拼接

　　　　然後用 SUBSTRING_INDEX 函式擷取第一個 log_id 

　　　　最後 INNER JOIN 

　　　　但是， GROUP_CONCAT 有長度限制的問題，預設 1024 個位元組（ show variables like 'group_concat_max_len'; ）

　　3、新增最新記錄表

　　　　專門用一張表來記錄任務最新執行成功記錄

　　　　表資料維護的邏輯：不存在則插入，存在則更新（記錄不存在則插入，存在則更新 → MySQL 的實現方式有哪些？）

取前N條或倒數N條

　　我們回到標題，分組排序後，如何取前N條記錄或倒數N條記錄

　　迴圈查資料庫

　　1、先批次查詢 task_id 

　　2、再根據 task_id 逐個去查 t_task_exec_log ，排序獲取前N條記錄

　　3、最後進行一個資料匯合，封裝成頁面需要的資料格式

　　但這種方式會迴圈查資料庫，一般是被禁止的

　　GROUP BY 結合 MySQL 函式

　　1、先批次查詢 task_id 

　　2、再根據這些 task_id 從 t_task_exec_log 批次查詢每個任務的前N條記錄的 log_id 集字串

SELECT task_id, SUBSTRING_INDEX(GROUP_CONCAT(log_id ORDER BY data_date DESC, modify_time DESC),',', 5) log_ids
FROM t_task_exec_log 
WHERE exec_status='success' AND task_id IN (124,156,158,200,300,358,500,800,1000,1001)
GROUP BY task_id;

　　 SUBSTRING_INDEX(str, delim, count) 不做過多介紹，具體可翻閱：SUBSTRING_INDEX

　　 count 引數可以用來實現前N條或倒數N條

　　比如前 5 條： SUBSTRING_INDEX(GROUP_CONCAT(log_id ORDER BY data_date DESC, modify_time DESC),',', 5) 

　　倒數 5 條： SUBSTRING_INDEX(GROUP_CONCAT(log_id ORDER BY data_date DESC, modify_time DESC),',', -5) 

　　3、log_ids 按逗號（,）進行拆分得到 log_id 列表，然後根據 log_id 列表從 t_task_exec_log 批次查詢

　　4、最後進行資料匯合，封裝成頁面需要的資料格式

　　但 GROUP_CONCAT 長度限制是需要考慮的點

　　新增最新記錄表

　　這種方式比較契合只取第一條的情況，不適合取N條的情況

　　N不固定，這張表的儲存資料範圍就不好確定

　　如果為了全相容的話，那這張表就成了 t_task_exec_log ，那就沒意義了

　　視窗函式

　　 MySQL8 新增的特性

　　關於視窗函式可查閱官方文件：Window Functions，不做過多介紹

　　我們用 ROW_NUMBER 來實現 取前N條或倒數N條 

　　1、批次查詢 task_id 

　　2、使用 ROW_NUMBER ，取前N條或倒數N條

　　取第一條

　　結果如下

　　取前 5 條

SELECT * from (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY task_id ORDER BY data_date DESC, modify_time DESC) AS rn
    FROM t_task_exec_log 
    WHERE exec_status='success' 
    AND task_id IN (124,156,158,200,300,358,500,800,1000,1001)
) t WHERE rn <= 5;

　　結果如下

　　取倒數 5 條

SELECT * from (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY task_id ORDER BY data_date ASC, modify_time ASC) AS rn
    FROM t_task_exec_log 
    WHERE exec_status='success' 
    AND task_id IN (124,156,158,200,300,358,500,800,1000,1001)
) t WHERE rn <= 5;

　　結果如下

　　留個疑問，利用視窗函式如何取倒數第一條？什麼？寫不出來？

　　再看 GROUP BY 結合 MySQL 函式

　　我們仔細看看 GROUP BY 結合 MySQL 函式 取倒數 5 條的結果

　　我們發現和視窗函式的取倒數 5 條的結果不一致

　　那到底是哪種方式不對，還是兩種方式都不對？

　　我們調整下 GROUP BY 結合 MySQL 函式 取倒數 5 條的寫法

SELECT task_id, SUBSTRING_INDEX(GROUP_CONCAT(log_id ORDER BY data_date ASC, modify_time ASC),',', 5) log_ids
FROM t_task_exec_log 
WHERE exec_status='success' AND task_id IN (124,156,158,200,300,358,500,800,1000,1001)
GROUP BY task_id;

　　結果如下

　　和視窗函式取倒數 5 條的結果一致了

　　那之前的這種寫法

　　是哪裡出了問題？

　　我們先來看看如下 SQL 

　　相信大家都能看懂，一共得到 374 條記錄

　　我們把 log_id 用逗號拼接起來，得到字串

2911732,2859745,2499159,685756,611426,1773618,631452,2641408,862146,1652523,2655517,2829017,1273848,2804800,1346153,936083,326032,864980,2015739,2648288,1839113,1921285,1953625,453123,50498,2743528,2721333,2919955,372642,98491,1424351,411001,1482212,230620,1994696,2918589,729845,2694142,500264,1012517,2713357,2200896,974654,2717787,2457056,172360,2832664,2846066,1848934,1919788,732675,1950937,199880,452272,273194,1616674,1194494,574206,1765082,1689794,631552,2758932,2485780,1715193,459323,1455116,2115301,1233012,320608,2004650,291114,1244414,2924159,254791,1271023,2163511,1565143,2981613,1299572,320289,1732975,210406,1507432,638443,808796,1557188,181660,167930,2272885,1944188,262812,2023062,2462778,2123029,594954,190347,388515,2178560,1598418,2564269,1934342,2925082,1502641,1900920,684906,2154470,2046731,1703184,1291369,1176799,897154,1286441,2138541,358779,943677,2415429,785261,2051755,2038868,1217252,473186,2552463,928982,1151401,1925499,2808729,1921939,2119578,406768,1866953,180496,13656,2333480,2974079,510052,2605676,653081,1659249,1160006,445779,891431,1943934,2489901,2942196,1654209,2486759,2514795,2849804,2258416,1488416,1929865,1183551,2509115,2732442,2085668,155167,1404105,578027,647799,559332,242226,959127,1717819,1457281,2777656,61863,1558242,979673,1622502,2501716,1665362,532434,2753181,2234018,2707034,408087,1611263,534460,1894189,159376,60130,1191876,282199,517385,2858577,1784531,2030854,2314894,679800,809800,2875291,966557,1621580,1992525,2025266,831289,1817299,1927920,891559,2725289,1194667,1550104,332614,2806388,157145,1220399,240821,2063037,154538,359355,2278415,2630602,2902571,1777692,2196687,1350564,1148733,469669,2563548,1936924,2563736,2003906,877726,2292538,2859208,1204177,475146,489706,2378830,2648739,915623,1695372,924324,1256107,540292,1125327,2865163,2533333,2619710,286934,20214,709854,320888,2319692,707114,1191938,2072463,55703,1830338,500835,1807704,1072891,1667013,1112386,110901,1876567,1636858,112749,1492761,1658767,2801747,2958924,48303,1994737,2194535,2393165,671013,2033922,2885396,463995,2241443,990863,2002460,814506,2536522,2885553,2431339,2961562,1542264,243844,657825,1594584,1921044,1138212,2680904,160115,2792129,538559,1267940,2560155,736726,2703598,632802,789810,438915,1370898,1432282,2713566,365961,2606280,2212229,2657542,2937595,2627981,520690,1865823,429311,874447,920179,2931749,2747839,50134,2517467,2222347,2748625,1056689,1868505,487388,1879593,1607657,1160123,2125711,1755572,2387420,1414325,823557,1551361,820297,1127153,1637903,2917492,1120815,1431846,552906,309803,1077061,674581,2414226,1392681,249656,1669143,1981249,2662300,711478,1172051,2332973,714265,471843,2261154,584537,1758386,2711638,502326,714922,1135634,124863,1890229,2653580,1404021,2711808,1146362,1917812,264393,1666930,1442219,2010387,2193352,722880,1982728,475910,1372761,433962,2563593,1637767

　　我們用 LENGTH 函式統計下該字串長度

　　一共 2853 個位元組，而 GROUP_CONCAT 限制長度預設是 1024 位元組

　　我們用 SUBSTRING 函式對字串擷取前 1024 個位元組

　　得到字串

2911732,2859745,2499159,685756,611426,1773618,631452,2641408,862146,1652523,2655517,2829017,1273848,2804800,1346153,936083,326032,864980,2015739,2648288,1839113,1921285,1953625,453123,50498,2743528,2721333,2919955,372642,98491,1424351,411001,1482212,230620,1994696,2918589,729845,2694142,500264,1012517,2713357,2200896,974654,2717787,2457056,172360,2832664,2846066,1848934,1919788,732675,1950937,199880,452272,273194,1616674,1194494,574206,1765082,1689794,631552,2758932,2485780,1715193,459323,1455116,2115301,1233012,320608,2004650,291114,1244414,2924159,254791,1271023,2163511,1565143,2981613,1299572,320289,1732975,210406,1507432,638443,808796,1557188,181660,167930,2272885,1944188,262812,2023062,2462778,2123029,594954,190347,388515,2178560,1598418,2564269,1934342,2925082,1502641,1900920,684906,2154470,2046731,1703184,1291369,1176799,897154,1286441,2138541,358779,943677,2415429,785261,2051755,2038868,1217252,473186,2552463,928982,1151401,1925499,2808729,1921939,2119578,406768,1866953,180496,13656,2333480,2974079,51

　　我們再用 SUBSTRING_INDEX 對如上字串進行操作

　　是不是找到原因了？

　　這種寫法， GROUP_CONCAT 會先進行 1024 長度的擷取，得到一個字串

　　然後 SUBSTRING_INDEX 再在該字串基礎上進行操作，這就導致了最終的結果錯誤！

總結

　　1、MySQL 提供了很多函式，給使用者帶來了很多便利，但我們要注意其限制

　　　　 GROUP_CONCAT 的預設長度 1024 

　　2、視窗函式

　　　　這是本文想引出的重點，是 MySQL8 的新特性

　　　　視窗函式的特別之處在於，它可以將結果集中的每一行看作一個單獨的計算物件，而不是將結果集劃分為分組並計算每個分組的聚合值

　　　　 MySQL8 之前，分組之後只能做聚合操作，不能對組中的每條記錄進行單獨操作

　　　　 MySQL8 及其之後，打破了分組之後只能聚合操作的限制，大大方便了我們實現某些特殊場景

　　　　 ROW_NUMBER 只是視窗函式之一， MySQL 還提供了其他的視窗函式，建議大家都去了解下

　　　　某些聚合函式加上 OVER 子句後就變成視窗函式了，實現效果很有意思，推薦大家去好好閱讀官方文件