MySQL__查询不同班级前三名的同学的思考，sql语句之近百万级别数据查询_sql查百万级别的数据的前几个怎么查

最近看到一个老问题，虽然不难，但也足以让我思来想去。

问题是这样的，在MySQL中查询不同班级下的前三名的同学（一个table）。

我们假设的简单一些，这张表叫students，然后有一个ID列主键，也表示学生的id，一个classid列表示班级，和一个grade表示分数。我随便插了一些数据。如下

现在就仅仅取出不同班级的前三名就行了。
这里要考虑的问题就是前三名，万一有同分数的怎么办，是取三条数据还是取分数前三的所有人。
网上给出了不同的sql语句。
贴上来一个：

select * from students a where (
	select count(*) from students b where ( 
		b.classid=a.classid and b.grade>a.grade
		) 
)<3 ORDER BY a.classid
1
2
3
4
5

这个是表自身关联查询，查出同一个classid，分数大于这个分数的个数有多少个，如果分数没有大于这个分数的，那这就是最高分，如果有一个大于这个分数，那就是第二分，如果有2个大于这个分数，那不就是第三的分数。
就像你超过了第二名你是第几名一样。
所以只要是0,1,2.那就前三，最后的where（）<3就是这个意思。这就取出不同班级的前三个分数的所有人。

那么这个语句能用吗？

查出来结果，当然能用啦。。。

我这里只有不到100条数据。当然很多数据库不会只有这么点数据。我们也可以想一下，他查询的过程，对于每一条数据，他都会在查询整个表格做一次比较。
之前看Python数据分析与挖掘的时候，有一个电子商务网站用户行为分析的案例，这个案例的数据库数据大概有八十多万条数据，每一条20列左右，数据还算比较大。
数据结构大概样子：

这里只截一部分的图。数据太多。
userOS是不同的操作系统，大概20个不重复的。
timestamp是访问的时间时间戳格式，bigint类型。

现在需求就是取出不同的操作系统，最后三次的访问记录。
就是时间戳最大的三个。

我用上面取前三名的同学的sql语句构建了一个查询：

select * from all_gzdata as a where (
		select count(*) from all_gzdata as b where ( 
				b.userOS = a.userOS and b.`timestamp` > a.`timestamp`
		)
) <3 ORDER BY a.userOS
1
2
3
4
5

然后我懵逼了。
系统一直在查一直在查，时间一秒一秒的往上加。
我知道很慢，因为数据多。所以我就不管他，上了个厕所，倒了杯水，还没结束。
。。。。。。。。十分钟过去后。我终结了它。

我查看了一下代码，把他改成从前1000条数据里查找：

select * from (select * from all_gzdata limit 1000) as a
where (
		select count(*) from (select * from all_gzdata limit 1000) as b where ( 
				b.userOS = a.userOS and b.`timestamp` > a.`timestamp`
		)
) <3 ORDER BY a.userOS
1
2
3
4
5
6

前1000： 用时0.2秒。
前2000： 0.6秒
前3000： 1.4秒
前4000: 2.05秒
前5000： 4秒
前6000： 6秒
前7000： 8.5秒
前8000： 11秒
前9000： 14.5秒
前1万： 18秒
前1万2： 29秒
前1万5： 42秒
前1万7： 55秒
前2万： 76秒
前2万2： 93.5秒
前2万5： 119秒
前2万7： 143.5秒
前3万： 173秒
前3万5： 239秒
前4万： 315秒
前4万5： 402秒

好吧这样太不直观了。
把数据量当成x轴，把时间当成y轴。两个数组，一张图片

# x轴如果写成1000太大了，所以全部除以1000，以单位为K计量
x = np.array([1,2,3,4,5,6,7,8,9,10,12,15,17,20,22,25,27,30,35,40,45])
y = [0.2,0.6,1.4,2.05,4,6,8.5,11,14.5,18,29,42,55,76,93.5,119,143.5,173,239,315,402]
plt.plot(x,y)
1
2
3
4

这大概是一条曲线。我们用sklearn的回归模型做一下预测

from sklearn.svm import SVR
svr = SVR(kernel='poly', degree=2)
svr.fit(x.reshape(-1,1),y)
y_ = svr.predict(x.reshape(-1,1))

plt.plot(x,y_,color='r')
plt.plot(x,y)
1
2
3
4
5
6
7

红色的是拟合的结果。
我预测一下5万的数据量用时为481.5秒（实际用时498秒）
5万5的数据量用时为582.8秒（实际用时594秒）

x_test = np.linspace(0,800,100)
y_test = svr.predict(x_test.reshape(-1,1))

plt.figure(figsize=(12,9))
plt.plot(x,y,color="r",linewidth=4)
plt.plot(x_test,y_test,color="b",)
1
2
3
4
5
6

红色是真实的。另外一个是拟合的效果
对于一个800K级别的数据（80万数据），用时大概120000秒左右。
120000/60/60 = 33.333333333333336
大概33小时左右。

很庆幸我十分钟就终结了它。

下面附一个速度还不错的sql语句：

select * from all_gzdata as a,(
	# 相当于按照timestamp倒叙，把他们组成一个数组，行转列的方式
	select GROUP_CONCAT(timestamp ORDER BY `timestamp` DESC) as times from all_gzdata 

	GROUP BY userOS
) as b 
#最后查找位置在前三个的timestamp，就是最后的三个时间点
where FIND_IN_SET(a.`timestamp`,b.times) BETWEEN 1 and 3 ORDER BY userOS
1
2
3
4
5
6
7
8

八十多万条数据查询大概用时124秒左右

至于别的方法我暂时还没发现，希望有厉害的专业人士帮帮忙！