V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
piresion
V2EX  ›  MySQL

求SQL高手解答。。。

  •  
  •   piresion · 2012-12-27 16:40:18 +08:00 · 3610 次点击
    这是一个创建于 4385 天前的主题,其中的信息可能已经有所发展或是发生改变。
    有这个一样一张表,表的结构如下:
    +------+----------+-----------+------+---------+------------+----------+------------+------+
    | 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
        1
    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
        2
    chairo  
       2012-12-27 16:52:38 +08:00   ❤️ 1
    补上楼:
    重复用户数据的问题在临时表中去重……操作一个小表怎么也比你直接在大表中就去重要效率高很多
    piresion
        3
    piresion  
    OP
       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
        4
    chairo  
       2012-12-27 17:05:45 +08:00
    @piresion 看我后边补充的,第一次时候不需要任何group by,只是取最大stage_id,在操作大表时候尽量简化查询条件。取出一部分数据再操作临时表时候可以group by uid等复杂一些的操作。把复杂的sql拆分成简单的多条sql可能最后效率会比一条复杂大sql反而会高……
    123123
        5
    123123  
       2012-12-27 17:45:48 +08:00
    如果最大关卡数是4,那么完成 4 A 的用户优先级会大于 3 S 的用户?如果是这样,那么
    我的思路是先取出最大关卡数评价为S的所有数据,再在这张临时表中进行时间排序,如果不够那么再去取最大关卡数评价为A的所有数据,依次类推直到凑够100个未知。
    这样应该也不会超过你的100秒
    123123
        6
    123123  
       2012-12-27 17:51:06 +08:00
    回复完看了下跟 @chairo 的思路差不多…
    区别在于 @chairo 不管评价直接把最大关卡的所有数据全取出来了
    不过我觉得既然有10万用户最大关卡通关而且评价为S的人数很大几率已经够100了
    piresion
        7
    piresion  
    OP
       2012-12-28 11:02:25 +08:00
    @123123
    @chairo 谢谢大家了。现在我的方案是开始一次性把所有玩家最难,评级最高,时间最早的一次记录读取出来存到一表里,然后每30分钟就读取这30分钟内玩过的玩家的uid,然后再更新他们的记录。
    因为30分钟内有过活跃的用户数也就1K左右,所以处理这1K条数据应该是没有问题的。
    plprapper
        8
    plprapper  
       2012-12-28 17:21:23 +08:00
    在数据库中多存放一个字段 数字类型的 把关卡数 时间 得分评价 转成数字(拼接起来) 对该字段进行排序 (加索引 )这条sql就很简单了 比较这个字段的大小就是你需要的了。 然后 limit100
    piresion
        9
    piresion  
    OP
       2012-12-29 15:30:47 +08:00
    @plprapper 嗯,不错的方案。
    但现在的问题是,不允许更改以前的代码,也不能加字段。。。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   902 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 23ms · UTC 20:06 · PVG 04:06 · LAX 12:06 · JFK 15:06
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.