mysql、clickhouse分组后取每组Topn

数据表如下：

需求：取每门课程的前3名

一、mysql:

SELECT
	course,
	name,
	score
FROM
	student_score t1
WHERE
	(
	select
		count(*)
	from
		student_score t2
	where
		t1.course = t2.course
		AND t1.score<t2.score)<3
order by
	course,
	score desc

说明 ：

要取每门课程的Top3，就要对t1表做where条件筛选，筛选条件即为：t1的分数在同一课程联结时，小于t2分数的个数要小于3。

 二、clickhouse:

select
	course,
	name,
	score
from
	(
	select
		course,
		groupArray(3)(name) as name,
		groupArray(3)(score) as score
	from
		(
		select
			*
		from
			student_score
		order by
			score desc) t1
	group by
		course) t2 
array join name, score

说明：首先确保分数倒序，再使用groupArray函数对每门课程的姓名和分数自上而下取3条进行合并，从而过滤出每门课的前3名，最后使用array join将合并的姓名和分组展开。

结果如下：