MySQL——MySQL分组排序取TopN、取第N条

问题背景

业务中要实现这样的逻辑：有一张用户表，每个用户有一个或多个银行账户，每个账户有对应的流水数据，现在想要获取每个用户的每个账户下，交易金额最大的前10条流水数据，该如何实现？

实现方法

组内排序取Top N在业务当中是一个会经常用到的功能。如果你是在大数据框架中取组内Top N，一般的大数据框架都提供了现成的函数，比如Hive中的row_number() + partitionBy()，实现起来会比较简单。而如果你用的是关系型数据库，比如MySQL，就没有类似现成的函数来实现这个功能了(MySQL 8.0开始也提供row_number函数，但是很多公司都还是使用的MySQL5.6/5.7)。

那么在MySQL 8.0 之前如何实现组内排序呢？答案就是，借助变量。

表(bank_transaction)中部分列：

列名	类型	含义
user_id	varchar(64)	用户ID
bank_account	varchar(64)	银行账户
transaction_amount	decimal(17, 2)	交易金额

SQL实现组内求Top N：

select
	user_id,
	bank_account,
	transaction_amount
from (
	select
		user_id,
		bank_account,
		transaction_amount,
		@row_num := if(@group01 = user_id and @group02 = bank_account, @row_num + 1, 1) as rn,
		@group01 := user_id,
		@group02 := bank_account	
	from (
		select
			user_id,
			bank_account,
			transaction_amount
		from bank_transaction 
		order by user_id, bank_account, transaction_amount desc
	) t1, (select @row_num := 0, @group01 := "", @group02 := "")t2
) t
where t.rn <= 6

实现思路：

1. 第一步：对数据进行组内排序，t1这个子查询里面就是对每个用户的每个账户下的所有交易金额进行降序排列
2. 第二步：设置变量，@row_num变量保存的是组内排序时的编号，@group01和@group02保存的是要分组列的临时值
3. 第三步：遍历已排序数据，每次拿后一行与前一行进行比较，如果后一行分组列的值和前一行的相等（对应上面SQL语句中的if条件），就表明后一行与前一行属于同一个组，那么变量@row_num的值就加一；如果不相等，就从1重新开始。以此，给所有数据编号。
4. 第四步：给所有数据编号之后，通过where条件即可取出Top N的记录。

 
另外，SQL也可以这样写，主要是变量的使用方式不同：

set @row_num := 0, @group01 := "", @group02 := "";

select
	user_id,
	bank_account,
	transaction_amount
from (
	select
		user_id,
		bank_account,
		transaction_amount,
		@row_num := if(@group01 = user_id and @group02 = bank_account, @row_num + 1, 1) as rn,
		@group01 := user_id,
		@group02 := bank_account	
	from (
		select
			user_id,
			bank_account,
			transaction_amount
		from bank_transaction 
		order by user_id, bank_account, transaction_amount desc
	) t1
) t
where t.rn <= 6

Hive中如何实现组内排序求Top N，可以参考这里。