mysql如何取出每个group的topN

mysql在group by之后如何取出每个group下的topN？在mysql 8之前，使用max/min函数能够取出group后某列的top1的值，但是面对topN的问题无能为力，通过子查询的方式进行解决；mysql 8引入的窗口函数对于此类组内统计很实用

取topN的情况并不少见，以下我们给出一个示例——实际上这个问题也是在编写查询zabbix监控数据的sql时遇到的，示例使用一个简化的表说明核心问题

一个用于存储监控数据的表t_monitor_history：

CREATE TABLE `t_monitor_history` (
  `clock` bigint NOT NULL COMMENT '时间戳(s)',
  `monitor_key` varchar(255) NOT NULL COMMENT '监控项key',
  `value` bigint DEFAULT NULL COMMENT '监控值',
  KEY `clock` (`clock`),
  KEY `monitor_key` (`monitor_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;


INSERT INTO `t_monitor_history` (`clock`, `monitor_key`, `value`) VALUES (631126861, 'memory.used', 1024);
INSERT INTO `t_monitor_history` (`clock`, `monitor_key`, `value`) VALUES (631126861, 'memory.total', 2048);
INSERT INTO `t_monitor_history` (`clock`, `monitor_key`, `value`) VALUES (631126862, 'fs.used(/)', 10240);
INSERT INTO `t_monitor_history` (`clock`, `monitor_key`, `value`) VALUES (631126860, 'fs.total(/)', 20480);
INSERT INTO `t_monitor_history` (`clock`, `monitor_key`, `value`) VALUES (631126700, 'memory.used', 128);
INSERT INTO `t_monitor_history` (`clock`, `monitor_key`, `value`) VALUES (631126701, 'memory.total', 2048);
INSERT INTO `t_monitor_history` (`clock`, `monitor_key`, `value`) VALUES (631126704, 'fs.used(/)', 5120);
INSERT INTO `t_monitor_history` (`clock`, `monitor_key`, `value`) VALUES (631126703, 'fs.total(/)', 40960);
INSERT INTO `t_monitor_history` (`clock`, `monitor_key`, `value`) VALUES (631126499, 'memory.used', 199);
INSERT INTO `t_monitor_history` (`clock`, `monitor_key`, `value`) VALUES (631126501, 'memory.total', 1000);
INSERT INTO `t_monitor_history` (`clock`, `monitor_key`, `value`) VALUES (631126501, 'fs.used(/)', 512);
INSERT INTO `t_monitor_history` (`clock`, `monitor_key`, `value`) VALUES (631126502, 'fs.total(/)', 900);

第一个需求：查询每个监控项的最近一次采集值。直接对monitor_key列group后，对clock取max，即可找出每组的monitor_key和对应的最近一次采集值的clock，通过这两个列的值筛选即可：

SELECT
	DATE_FORMAT( FROM_UNIXTIME( t.clock ), '%Y-%m-%d %H:%i:%s' ) AS dt,
	t.monitor_key,
	t.`value` 
FROM
	t_monitor_history t
	JOIN ( 
	  SELECT monitor_key, max( clock ) AS max_clock 
	    FROM t_monitor_history 
	  GROUP BY monitor_key ) tmp 
	ON t.clock = tmp.max_clock 
	AND t.monitor_key = tmp.monitor_key 
ORDER BY
	clock DESC

-- ------ RESULT ---------------------------------
-- dt                   monitor_key       value
--------------------------------------------------
-- 1990-01-01 01:01:01	memory.used	      1024
-- 1990-01-01 01:01:01	memory.total	  2048
-- 1990-01-01 01:01:02	fs.used(/)	      10240
-- 1990-01-01 01:01:00	fs.total(/)	      20480

然后进一步，让我们进入正题：如何查询每个监控项的最近2条采集值记录（注意，我们希望只取2条，即使clock出现并列的情况）？

union all合并多个查询的结果集

分别查询每个group的top2（where group = xx order by … limit 2），使用union all合并结果集——这一方法在小型数据集的分析上是比较简洁的，但需要预先确定数据特征，并不是一个通用解

定义session内变量循环查找

如何解决？在StackOverflow上等网站上有热心网友提供了通过在sql语句内定义session级变量进行循环查找的方式，但本人实测性能下降显著。

这类回答不少，贴个链接作为参考

where条件使用子查询筛选group内topN

SELECT
	DATE_FORMAT( FROM_UNIXTIME( his.clock ), '%Y-%m-%d %H:%i:%s' ) AS dt,
	monitor_key,
	`value` 
FROM
	t_monitor_history AS his 
WHERE
	( SELECT COUNT(*) FROM t_monitor_history AS tmp WHERE his.monitor_key = tmp.monitor_key AND tmp.clock >= his.clock ) <= 2 
ORDER BY
	his.monitor_key DESC

-- ------ RESULT ---------------------------------
-- dt                   monitor_key       value
--------------------------------------------------
-- 1990-01-01 01:01:01	memory.used	      1024
-- 1990-01-01 00:58:20	memory.used	      128
-- 1990-01-01 01:01:01	memory.total	  2048
-- 1990-01-01 00:58:21	memory.total	  2048
-- 1990-01-01 01:01:02	fs.used(/)	      10240
-- 1990-01-01 00:58:24	fs.used(/)	      5120
-- 1990-01-01 01:01:00	fs.total(/)	      20480
-- 1990-01-01 00:58:23	fs.total(/)	      40960

这一方法简洁易实现，但是测试中发现在特定情况下存在问题：如果某个group内的排序列clock的第2名并列，那么只会返回第1位的结果，如下，为memory.used这一个监控项添加一个clock并列第2位的记录：

1	INSERT INTO `sql_test`.`t_monitor_history` (`clock`, `monitor_key`, `value`) VALUES (631126700, 'memory.used', -1);

执行结果成为了：

-- ------ RESULT ---------------------------------
-- dt                   monitor_key       value
--------------------------------------------------
-- 1990-01-01 01:01:01	memory.used	      1024
-- 1990-01-01 01:01:01	memory.total	  2048
-- 1990-01-01 00:58:21	memory.total	  2048
-- 1990-01-01 01:01:02	fs.used(/)	      10240
-- 1990-01-01 00:58:24	fs.used(/)	      5120
-- 1990-01-01 01:01:00	fs.total(/)	      20480
-- 1990-01-01 00:58:23	fs.total(/)	      40960

使用group_concat和find_in_set取group内某列topN

group_concat用于列转行，将多行的值合并到一行内，默认使用’,’连接为一个字符串；find_in_set可在’,’连接的多个字符串中查找出指定的字符串，返回值为0(查找的字符串不存在)，或查找的字符串在字符串列表中的索引值（索引值从1开始）

sql整体采取与取max同样的思路：对monitor_key列group后，找出top2的clock值，通过这两个列的值进行筛选。问题就来到了如何找出每个group内top2的clock值，结合使用group_concat和find_in_set即可实现

SELECT
	DATE_FORMAT( FROM_UNIXTIME( t.clock ), '%Y-%m-%d %H:%i:%s' ) AS dt,
	t.monitor_key,
	t.`value` 
FROM
	t_monitor_history t
	JOIN ( 
		SELECT monitor_key, SUBSTRING_INDEX( GROUP_CONCAT( clock ORDER BY clock DESC ), ',', 2 ) AS max_two_clock_list 
		  FROM t_monitor_history 
		GROUP BY monitor_key ) tmp 
	ON t.monitor_key = tmp.monitor_key 
	AND FIND_IN_SET( t.clock, tmp.max_two_clock_list ) > 0 
ORDER BY
	monitor_key DESC

-- ------ RESULT ---------------------------------
-- dt                   monitor_key       value
--------------------------------------------------
-- 1990-01-01 01:01:01	memory.used	      1024
-- 1990-01-01 00:58:20	memory.used	      128
-- 1990-01-01 01:01:01	memory.total	  2048
-- 1990-01-01 00:58:21	memory.total      2048
-- 1990-01-01 01:01:02	fs.used(/)	      10240
-- 1990-01-01 00:58:24	fs.used(/)	      5120
-- 1990-01-01 01:01:00	fs.total(/)	      20480
-- 1990-01-01 00:58:23	fs.total(/)	      40960

这里在子查询中，首先通过GROUP_CONCAT函数以降序拼接clock值到一行中，然后使用SUBSTRING_INDEX按照分隔符’,’进行截取得到子字符串，分割2次即保留了每个group内最大的两个clock值；之后在外部的筛选中，通过FIND_IN_SET函数过滤出所有子查询结果集提供的clock值，即得到了每个group中clock最大的top2记录

然而与上一条类似，因为外层查询使用top2的clock进行筛选，这一方法在clock列出现并列时会将查询所有并列的记录。插入一条并列记录：

1	INSERT INTO `sql_test`.`t_monitor_history` (`clock`, `monitor_key`, `value`) VALUES (631126700, 'memory.used', -1);

执行结果变成了：

-- ------ RESULT ---------------------------------
-- dt                   monitor_key       value
--------------------------------------------------
-- 1990-01-01 01:01:01	memory.used	      1024
-- 1990-01-01 00:58:20	memory.used	      128
-- 1990-01-01 00:58:20	memory.used	      -1
-- 1990-01-01 01:01:01	memory.total	  2048
-- 1990-01-01 00:58:21	memory.total	  2048
-- 1990-01-01 01:01:02	fs.used(/)	      10240
-- 1990-01-01 00:58:24	fs.used(/)	      5120
-- 1990-01-01 01:01:00	fs.total(/)	      20480
-- 1990-01-01 00:58:23	fs.total(/)	      40960

在实际的查询中，通过在子查询和外部查询添加where条件过滤clock和monitor_key值，可以更充分的利用索引，达到更高的效率

mysql 8使用窗口函数

mysql 8引入了窗口函数，关于窗口函数的使用示例可以参考网友的这一篇介绍文章

使用窗口函数对clock列按照monitor_key分组后排序，并使用ROW_NUMBER()排序生成排序次序列r，在外层查询中使用r筛选即可轻松取到每个分组内按照clock取topN的行：

SELECT
	dt,
	monitor_key,
	`value` 
FROM
	(
	SELECT
		DATE_FORMAT( FROM_UNIXTIME( clock ), '%Y-%m-%d %H:%i:%s' ) AS dt,
		monitor_key,
		`value`,
		ROW_NUMBER() over ( PARTITION BY monitor_key ORDER BY clock DESC ) AS r 
	FROM
		t_monitor_history 
	) t 
WHERE
	r <= 2

-- ------ RESULT ---------------------------------
-- dt                   monitor_key       value
--------------------------------------------------
-- 1990-01-01 01:01:00	fs.total(/)	      20480
-- 1990-01-01 00:58:23	fs.total(/)	      40960
-- 1990-01-01 01:01:02	fs.used(/)	      10240
-- 1990-01-01 00:58:24	fs.used(/)	      5120
-- 1990-01-01 01:01:01	memory.total	  2048
-- 1990-01-01 00:58:21	memory.total	  2048
-- 1990-01-01 01:01:01	memory.used	      1024
-- 1990-01-01 00:58:20	memory.used	      128

ROW_NUMBER()不会考虑并列值，而是为每行生成一个序号，因此group内clock值出现重复时也能准确的取出topN——如果希望取出并列值，可以使用RANK函数