求SQL高手解答。。。

有这个一样一张表，表的结构如下：
+------+----------+-----------+------+---------+------------+----------+------------+------+
| id | stage_id | uid | type | victory | start_time | duration | created | star |
+------+----------+-----------+------+---------+------------+----------+------------+------+
| 1116 | 1100011 | 102099607 | 0 | 0 | 1353485788 | 0 | 1353485788 | NULL |
| 1117 | 1100011 | 102099605 | 0 | 0 | 1353485958 | 0 | 1353485958 | NULL |
| 1118 | 1100011 | 102099606 | 0 | 1 | 1353486027 | 183 | 1353486027 | E |
| 1119 | 1100011 | 102099608 | 0 | 0 | 1353486430 | 29 | 1353486430 | |
| 1133 | 1100021 | 102099609 | 0 | 0 | 1353570040 | 109 | 1353570040 | |
| 1134 | 1100021 | 102099609 | 0 | 1 | 1353570626 | 363 | 1353570626 | C |
| 1135 | 1100011 | 102099611 | 0 | 0 | 1353580442 | 657 | 1353580442 | |
+------+----------+-----------+------+---------+------------+----------+------------+------+

这里储存着每个用户在每一关卡的情况，stage_id表示关卡ID,stage_id越大表示关卡越难，created表示结束时间，star表示评级（S表示最好，其次是 A>B>C>D>E，空或NULL表示没有成功）
每个人只有完成这一关，才能玩下一关，一关可以玩很多次。
这个表大约有100W，用户数太约有10W.
现在要统计前100名，完成关卡最难，且评级最高，完成时间最早的用户。

不知道讲清楚没有。。。-|-

我的想法是先把每个用户最难，且评级最高，完成时间最早的关卡提取出来，生成一张临时表，然后再在这个临时表里提取前100名。
但耗时实在太长，约100秒。。。

对了，这个结果要求延时在30分钟以内，再一次吐血，求各位SQL高手解答。

关卡

9 条回复 • 1970-01-01 08:00:00 +08:00

chairo

2012-12-27 16:49:00 +08:00

说下我的思路：
1. 取Max(stage_id)
2. 根据上步的stage_id取数据到临时表，同时转换star为一个可排序的数字，S》A》...
3. 如临时表数据超过100，按转换后的star和created排序取前100
4. 不够100，取<Max(stage_id)的第一个stage_id，重复2、3

感觉耗时不会超过你前边的100秒……

chairo

2012-12-27 16:52:38 +08:00

补上楼：
重复用户数据的问题在临时表中去重……操作一个小表怎么也比你直接在大表中就去重要效率高很多

piresion

2012-12-27 17:02:43 +08:00

@chairo 在第一步使用group by uid再取最大的stage_id么？
SELECT uid FROM `table` WHERE stage_id in (SELECT max(stage_id) FROM `table` GROUP BY uid) ORDER BY stage_id desc,star ASC,created ASC limit 0,100
这样么？
这样感觉也有问题呢

chairo

2012-12-27 17:05:45 +08:00

@piresion 看我后边补充的，第一次时候不需要任何group by，只是取最大stage_id，在操作大表时候尽量简化查询条件。取出一部分数据再操作临时表时候可以group by uid等复杂一些的操作。把复杂的sql拆分成简单的多条sql可能最后效率会比一条复杂大sql反而会高……

123123

2012-12-27 17:45:48 +08:00

如果最大关卡数是4,那么完成 4 A 的用户优先级会大于 3 S 的用户？如果是这样，那么
我的思路是先取出最大关卡数评价为S的所有数据，再在这张临时表中进行时间排序，如果不够那么再去取最大关卡数评价为A的所有数据，依次类推直到凑够100个未知。
这样应该也不会超过你的100秒

123123

2012-12-27 17:51:06 +08:00

回复完看了下跟 @chairo 的思路差不多…
区别在于 @chairo 不管评价直接把最大关卡的所有数据全取出来了
不过我觉得既然有10万用户最大关卡通关而且评价为S的人数很大几率已经够100了

piresion

2012-12-28 11:02:25 +08:00

@123123
@chairo 谢谢大家了。现在我的方案是开始一次性把所有玩家最难，评级最高，时间最早的一次记录读取出来存到一表里，然后每30分钟就读取这30分钟内玩过的玩家的uid，然后再更新他们的记录。
因为30分钟内有过活跃的用户数也就1K左右，所以处理这1K条数据应该是没有问题的。

plprapper

2012-12-28 17:21:23 +08:00

在数据库中多存放一个字段数字类型的把关卡数时间得分评价转成数字（拼接起来）对该字段进行排序（加索引）这条sql就很简单了比较这个字段的大小就是你需要的了。然后 limit100

piresion

2012-12-29 15:30:47 +08:00

@plprapper 嗯，不错的方案。
但现在的问题是，不允许更改以前的代码，也不能加字段。。。