MySQL分组排序后取每组的TopN

记录一下

现实开发中，经常遇到需要根据某个字段分组、字段排序后取最早的N条数据，传统的Sql无法满足此项要求（分组后只保留一条数据）。MySQL8之后出现了窗口函数很好的满足了我们的需求。

SELECT * FROM ( SELECT *, ROW_NUMBER () OVER ( PARTITION BY [分组字段] ORDER BY [排序字段] ) rownum FROM 表名 ) t  WHERE rownum = N

业务要求：查询出峰值日活用户数（根据租户、天分组 取数量最高的那条记录）

CREATE TABLE `login_log` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `login_time` datetime NOT NULL COMMENT '登录时间',
  `user_id` bigint(20) DEFAULT NULL COMMENT '用户id',
  `tenant_id` bigint(20) DEFAULT NULL COMMENT '租户id',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT= 12380 DEFAULT CHARSET=utf8 COMMENT='登录日志表';

SELECT	t1.tenant_id, t1.daytime, t1.count FROM ( SELECT ROW_NUMBER ( ) over ( PARTITION BY testTable.tenant_id ORDER BY testTable.count DESC ) AS RowNum, testTable.*  FROM (SELECT tmp.tenant_id,  tmp.dayTime AS dayTime, count( * ) AS 'count' FROM(SELECT  tenant_id,DATE_FORMAT( login_time, '%Y-%m-%d' ) AS dayTime, user_id  FROM login_log WHERE tenant_id IS NOT NULL GROUP BY tenant_id,  DATE_FORMAT( login_time, '%Y-%m-%d' ), user_id  ) tmp GROUP BY tmp.tenant_id,  tmp.dayTime  ORDER BY  tmp.tenant_id ASC, count DESC  ) AS testTable ) AS t1  WHERE RowNum = 1

可以看到查询后的结果就是我们想要的数据。