开心一刻

  晚上,老婆辅导女儿写语文作业

  填空题:春天来了,__绿了

  女儿:春天来了,爸爸绿了

  老婆一脸不悦地问道:你再说一遍,春天来了,什么玩意绿了?

  女儿:春天来了,爸爸绿了呀

  老婆很生气,但依旧温柔地问道:春天来了,爸爸怎么绿了呢

  女儿略带哭腔的说道:那冬天呢

  老婆急的直挠头:冬,冬,冬...,它跟天气有什么关系啊,那春天来了不应该是小草绿了吗

  女儿疑惑的左看右看,问道:那爸爸啥时候绿?

  老婆气的把笔一甩:我不知道你爸啥时候绿,你问你爸

  女儿转头看向我,问道:爸爸,你啥时候绿?

  我心里咯噔一下,这小棉袄有点漏风呀,小心翼翼的看了一眼老婆,坚定地说道:爸爸是不会绿的!

前情回顾

  前两天翻自己的博客的时候,翻到了:记一次有意思的 SQL 实现 → 分组后取每组的第一条记录

  突然意识到好像有续集没写

  翻到结尾,果然有个留疑

MySQL 分组排序后 → 如何取前N条或倒数N条-LMLPHP

  但我要强调一点:这是我给你们的留疑,并不是我给你们的承诺!

  我没写续集,你们可不能生气,实在是生气,那你来打我呀!

MySQL 分组排序后 → 如何取前N条或倒数N条-LMLPHP

  分组后取第一条记录

  我们先来简单回顾下实现方式

  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 集字符串

MySQL 分组排序后 → 如何取前N条或倒数N条-LMLPHPMySQL 分组排序后 → 如何取前N条或倒数N条-LMLPHP
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;
View Code

MySQL 分组排序后 → 如何取前N条或倒数N条-LMLPHP

   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) 

MySQL 分组排序后 → 如何取前N条或倒数N条-LMLPHP

  倒数 5 条: SUBSTRING_INDEX(GROUP_CONCAT(log_id ORDER BY data_date DESC, modify_time DESC),',', -5) 

MySQL 分组排序后 → 如何取前N条或倒数N条-LMLPHP

  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条

  取第一条

MySQL 分组排序后 → 如何取前N条或倒数N条-LMLPHP

  结果如下

MySQL 分组排序后 → 如何取前N条或倒数N条-LMLPHP

  取前 5 条

MySQL 分组排序后 → 如何取前N条或倒数N条-LMLPHPMySQL 分组排序后 → 如何取前N条或倒数N条-LMLPHP
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;
View Code

MySQL 分组排序后 → 如何取前N条或倒数N条-LMLPHP

  结果如下

MySQL 分组排序后 → 如何取前N条或倒数N条-LMLPHP

  取倒数 5 条

MySQL 分组排序后 → 如何取前N条或倒数N条-LMLPHPMySQL 分组排序后 → 如何取前N条或倒数N条-LMLPHP
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;
View Code

MySQL 分组排序后 → 如何取前N条或倒数N条-LMLPHP

  结果如下

MySQL 分组排序后 → 如何取前N条或倒数N条-LMLPHP

  留个疑问,利用窗口函数如何取倒数第一条?什么?写不出来?

MySQL 分组排序后 → 如何取前N条或倒数N条-LMLPHP

  再看 GROUP BY 结合 MySQL 函数

  我们仔细看看 GROUP BY 结合 MySQL 函数 取倒数 5 条的结果

MySQL 分组排序后 → 如何取前N条或倒数N条-LMLPHP

  我们发现和窗口函数的取倒数 5 条的结果不一致

  那到底是哪种方式不对,还是两种方式都不对?

  我们调整下 GROUP BY 结合 MySQL 函数 取倒数 5 条的写法

MySQL 分组排序后 → 如何取前N条或倒数N条-LMLPHPMySQL 分组排序后 → 如何取前N条或倒数N条-LMLPHP
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;
View Code

MySQL 分组排序后 → 如何取前N条或倒数N条-LMLPHP

  结果如下

MySQL 分组排序后 → 如何取前N条或倒数N条-LMLPHP

  和窗口函数取倒数 5 条的结果一致了

  那之前的这种写法

MySQL 分组排序后 → 如何取前N条或倒数N条-LMLPHP

  是哪里出了问题?

  我们先来看看如下 SQL 

MySQL 分组排序后 → 如何取前N条或倒数N条-LMLPHP

  相信大家都能看懂,一共得到 374 条记录

MySQL 分组排序后 → 如何取前N条或倒数N条-LMLPHP

  我们把 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 函数统计下该字符串长度

MySQL 分组排序后 → 如何取前N条或倒数N条-LMLPHP

  一共 2853 个字节,而 GROUP_CONCAT 限制长度默认是 1024 字节

  我们用 SUBSTRING 函数对字符串截取前 1024 个字节

MySQL 分组排序后 → 如何取前N条或倒数N条-LMLPHP

  得到字符串

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 对如上字符串进行操作

MySQL 分组排序后 → 如何取前N条或倒数N条-LMLPHP

  是不是找到原因了?

MySQL 分组排序后 → 如何取前N条或倒数N条-LMLPHP

  这种写法, GROUP_CONCAT 会先进行 1024 长度的截取,得到一个字符串

  然后 SUBSTRING_INDEX 再在该字符串基础上进行操作,这就导致了最终的结果错误!

总结

  1、MySQL 提供了很多函数,给使用者带来了很多便利,但我们要注意其限制

     GROUP_CONCAT 的默认长度 1024 

  2、窗口函数

    这是本文想引出的重点,是 MySQL8 的新特性

    窗口函数的特别之处在于,它可以将结果集中的每一行看作一个单独的计算对象,而不是将结果集划分为分组并计算每个分组的聚合值

     MySQL8 之前,分组之后只能做聚合操作,不能对组中的每条记录进行单独操作

     MySQL8 及其之后,打破了分组之后只能聚合操作的限制,大大方便了我们实现某些特殊场景

     ROW_NUMBER 只是窗口函数之一, MySQL 还提供了其他的窗口函数,建议大家都去了解下

    某些聚合函数加上 OVER 子句后就变成窗口函数了,实现效果很有意思,推荐大家去好好阅读官方文档

12-12 16:36