› 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 · Dec 27, 2012 · 4678 views
    This topic created in 5033 days ago, the information mentioned may be changed or developed.
    有这个一样一张表,表的结构如下:
    +------+----------+-----------+------+---------+------------+----------+------------+------+
    | 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 replies  •  1970-01-01 08:00:00 +08:00
    chairo
        1
    chairo  
       Dec 27, 2012
    说下我的思路:
    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  
       Dec 27, 2012   ❤️ 1
    补上楼:
    重复用户数据的问题在临时表中去重……操作一个小表怎么也比你直接在大表中就去重要效率高很多
    piresion
        3
    piresion  
    OP
       Dec 27, 2012
    @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  
       Dec 27, 2012
    @piresion 看我后边补充的,第一次时候不需要任何group by,只是取最大stage_id,在操作大表时候尽量简化查询条件。取出一部分数据再操作临时表时候可以group by uid等复杂一些的操作。把复杂的sql拆分成简单的多条sql可能最后效率会比一条复杂大sql反而会高……
    123123
        5
    123123  
       Dec 27, 2012
    如果最大关卡数是4,那么完成 4 A 的用户优先级会大于 3 S 的用户?如果是这样,那么
    我的思路是先取出最大关卡数评价为S的所有数据,再在这张临时表中进行时间排序,如果不够那么再去取最大关卡数评价为A的所有数据,依次类推直到凑够100个未知。
    这样应该也不会超过你的100秒
    123123
        6
    123123  
       Dec 27, 2012
    回复完看了下跟 @chairo 的思路差不多…
    区别在于 @chairo 不管评价直接把最大关卡的所有数据全取出来了
    不过我觉得既然有10万用户最大关卡通关而且评价为S的人数很大几率已经够100了
    piresion
        7
    piresion  
    OP
       Dec 28, 2012
    @123123
    @chairo 谢谢大家了。现在我的方案是开始一次性把所有玩家最难,评级最高,时间最早的一次记录读取出来存到一表里,然后每30分钟就读取这30分钟内玩过的玩家的uid,然后再更新他们的记录。
    因为30分钟内有过活跃的用户数也就1K左右,所以处理这1K条数据应该是没有问题的。
    plprapper
        8
    plprapper  
       Dec 28, 2012
    在数据库中多存放一个字段 数字类型的 把关卡数 时间 得分评价 转成数字(拼接起来) 对该字段进行排序 (加索引 )这条sql就很简单了 比较这个字段的大小就是你需要的了。 然后 limit100
    piresion
        9
    piresion  
    OP
       Dec 29, 2012
    @plprapper 嗯,不错的方案。
    但现在的问题是,不允许更改以前的代码,也不能加字段。。。
    About   ·   Help   ·   Advertise   ·   Blog   ·   API   ·   FAQ   ·   Privacy   ·   Solana   ·   6061 Online   Highest 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 61ms · UTC 03:15 · PVG 11:15 · LAX 20:15 · JFK 23:15
    ♥ Do have faith in what you're doing.