请教高效的分组排序sql

zyrf2001

我有一个表，tmp_subject_order ，存储学生单科成绩，class_student_id 学生id， paper_id  试卷id， score    得分

mysql> explain tmp_subject_score;
+------------------+--------------+------+-----+---------+-------+
| Field            | Type         | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| class_student_id | int(9)       | NO   |     | 0       |       |               
| paper_id         | int(9)       | NO   |     | 0       |       |
| score            | decimal(6,2) | NO   |     | NULL    |       |
+------------------+--------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

我想得到学生的排名，如果成绩相同则排名并列，我的语句如下：

select         class_student_id, paper_id,
               (select count(*) cnt from tmp_subject_score t where t.paper_id = tto.paper_id and tto.score<t.score)+1 town_order
from tmp_subject_score tto;

竟然要1分35秒，记录数是24000
mysql> select count(*) from tmp_subject_score;
+----------+
| count(*) |
+----------+
|    24000 |
+----------+
1 row in set (0.00 sec)


表已经建立了索引
mysql> show index from tmp_subject_score;
+-------------------+------------+----------+--------------+-------------+------
-----+-------------+----------+--------+------+------------+---------+
| Table             | Non_unique | Key_name | Seq_in_index | Column_name | Colla
tion | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------------+------------+----------+--------------+-------------+------
-----+-------------+----------+--------+------+------------+---------+
| tmp_subject_score |          1 | idx_1    |            1 | paper_id    | A
     |           5 |     NULL | NULL   |      | BTREE      |         |
| tmp_subject_score |          1 | idx_1    |            2 | score       | A
     |         585 |     NULL | NULL   |      | BTREE      |         |
+-------------------+------------+----------+--------------+-------------+------
-----+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)


请问大家有什么好的算法？ 不局限于数据库，存储过程也可以，用程序实现也可以，或者改变数据库结构，只要能提高效率的都可以。呵呵，别说换数据库就行。

附件是mysqldump出来的表和数据

[ 本帖最后由 zyrf2001 于 2009-5-22 16:08 编辑 ]

cenalulu

写了一个procedure，用楼主的数据，执行时间在1秒左右。
其中我用的database是test，楼主把procedure里的test.tmp_subject_score 改成自己相应的数据库就ok了
大概的思路就是，对于每个paper_id的学生分数及排名通过下面这句sql得到

select if(tmp_subject_score.score<@scorenow,@rank:=@rank+1,@rank:=@rank) as mrank,         class_student_id,(@scorenow:=score) as mscore         from  test.tmp_subject_score where paper_id = paper_id_now         order by score desc ;

然后建立一个procedure遍历每个paper_id 结果输出。

delimiter // create procedure show_mark() begin declare paper_id_now int ; declare paper_id_csr cursor for select distinct paper_id from test.tmp_subject_score ; open paper_id_csr; begin     loop         fetch paper_id_csr into  paper_id_now;         set @scorenow = 99999;         set @rank = 0;         select if(tmp_subject_score.score<@scorenow,@rank:=@rank+1,@rank:=@rank) as mrank,         class_student_id,(@scorenow:=score) as mscore         from  test.tmp_subject_score where paper_id = paper_id_now         order by score desc ;     end loop; end ; close paper_id_csr; end; // delimiter ; call show_mark();

[ 本帖最后由 cenalulu 于 2009-5-24 22:11 编辑 ]

zyrf2001

谢谢cenalulu 回复，效率确实比较高

mysql> call show_mark();
Query OK, 4800 rows affected (0.17 sec)

但是有两个问题，首先就是没有判断游标循环完，在我这里执行后会报错，我加了个异常处理，请教cenalulu是怎么执行的呢？ 当然算法还是cenalulu的算法，我修改后的语句如下

delimiter //

drop procedure if exists show_mark
//
create procedure show_mark()
begin
                declare l_done int;
                declare paper_id_now int ;
                declare paper_id_csr cursor for select distinct paper_id from tmp_subject_score ;
                DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_done=1;
                open paper_id_csr;
                begin

               
                    drop table if exists tmp_order;
                    create table tmp_order(paper_id int, class_student_id int, score numeric(6,2), order_in_town int);
                    grade_loop:loop
                        fetch paper_id_csr into  paper_id_now;
                        IF l_done=1 THEN
                                       LEAVE grade_loop;
                                    END IF;
               
                        set @scorenow = 99999;
                        set @rank = 0;
                        insert into tmp_order(paper_id, class_student_id, order_in_town, score)
                        select paper_id_now, class_student_id,if(score<@scorenow,@rank:=@rank+1,@rank:=@rank) as mrank,(@scorenow:=score) as mscore
                        from  tmp_subject_score where paper_id = paper_id_now
                        order by score desc ;
                    end loop;
               
                end ;
                close paper_id_csr;

end;
//


delimiter ;
call show_mark();





这样可以执行了，但是结果和期望的有点差别

mysql> select * from tmp_order limit 10;
+----------+------------------+--------+---------------+
| paper_id | class_student_id | score  | order_in_town |
+----------+------------------+--------+---------------+
|       10 |             9168 | 116.00 |             1 |
|       10 |             9153 | 114.00 |             2 |
|       10 |             9416 | 113.00 |             3 |
|       10 |             9513 | 113.00 |             3 |
|       10 |            11088 | 112.00 |             4 |
|       10 |             9254 | 112.00 |             4 |
|       10 |             6501 | 111.00 |             5 |
|       10 |             6728 | 111.00 |             5 |
|       10 |             9240 | 111.00 |             5 |
|       10 |            10218 | 111.00 |             5 |
+----------+------------------+--------+---------------+
10 rows in set (0.00 sec)

在现实生活中，两个并列第3后面应该是第5名了，而不是第4名，下面的并列第5也是一样。如果能达到这种效果最好了，谢谢。

[ 本帖最后由 zyrf2001 于 2009-5-25 15:20 编辑 ]

cenalulu

在你的基础上又改进了下~~昨天写的太匆忙显示的是多个结果集，今天本来想改进的，结果你已经做啦~~

大概的意思就是新加了一个计数器total记录排在自己之前的人的个数，一旦遇到一个新的分数值，就用它来更新rank变量。
我没想出来有什么办法既计算@total:=@total+1又不显示结果，所以就再套了一个select 把这列去掉~笨办法。
楼主要是有啥想法也可以跟贴哈~~


其中红的是增加的代码。

delimiter //

drop procedure if exists show_mark
//
create procedure show_mark()
begin
                declare l_done int;
                declare paper_id_now int ;
                declare paper_id_csr cursor for select distinct paper_id from tmp_subject_score ;
                DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_done=1;
                open paper_id_csr;
                begin

               
                    drop table if exists tmp_order;
                    create table tmp_order(paper_id int, class_student_id int, score numeric(6,2), order_in_town int);
                    grade_loop:loop
                        fetch paper_id_csr into  paper_id_now;
                        IF l_done=1 THEN
                                       LEAVE grade_loop;
                                    END IF;
               
                        set @scorenow = 99999;
                        set @rank = 0;
                        set @total = 0;
                        insert into tmp_order(paper_id, class_student_id, order_in_town, score)
                        select paper_id_now,class_student_id,mrank,mscore from (
                                select (@total:=@total+1) as temp,
                                paper_id_now, class_student_id,
                                if(score<@scorenow,@rank:=@total,@rank:=@rank) as mrank,
                                (@scorenow:=score) as mscore
                                from  tmp_subject_score where paper_id = paper_id_now
                                order by score desc
                        )  as tmp_rank_tbl;
                    end loop;
               
                end ;
                close paper_id_csr;
                select * from tmp_order;
end;
//


delimiter ;
call show_mark();

[ 本帖最后由 cenalulu 于 2009-5-25 17:22 编辑 ]

zyrf2001

强！
太感谢了！

zyrf2001

受cenalulu 的启发，在cenalulu的基础上，我又简化了一下，去掉了对paper_id的循环，不使用游标。速度由0.23s 提高的 0.17s

delimiter //

drop procedure if exists show_mark
//
create procedure show_mark()
begin
  drop table if exists tmp_order;
  create table tmp_order(paper_id int, class_student_id int, score numeric(6,2), order_in_town int);

  set @scorenow = 99999;
  set @rank = 0;
  set @total = 0;
  set @paper_id = 0;
  insert into tmp_order(paper_id, class_student_id, order_in_town, score)
  select paper_id,class_student_id,mrank,mscore from (
          select if(paper_id=@paper_id,@total:=@total+1, @total:=1) as temp,
                                  if(paper_id=@paper_id,null, @rank:=1) as temp1,       
                                  if(paper_id=@paper_id,null, @paper_id:=paper_id) as temp2,       
          paper_id, class_student_id,
          if(score<@scorenow,@rank:=@total,@rank:=@rank) as mrank,
          (@scorenow:=score) as mscore
          from  tmp_subject_score  
          order by paper_id, score desc
  )  as tmp_rank_tbl;
               
end;
//


delimiter ;
call show_mark();

zyrf2001

if(paper_id=@paper_id,@total:=@total+1, @total:=1) as temp,
                                  if(paper_id=@paper_id,null, @rank:=1) as temp1,        
                                  if(paper_id=@paper_id,null, @paper_id:=paper_id) as temp2,        

这个如果能在一个if里处理就好了

发了帖子删不掉了，在这个基础上修改吧

基于上面的想法，我把过程改成了这样

delimiter //

drop procedure if exists show_mark
//
create procedure show_mark()
begin
  drop table if exists tmp_order;
  create table tmp_order(paper_id int, class_student_id int, score numeric(6,2), order_in_town int);

  set @scorenow = 99999;
  set @rank = 0;
  set @total = 0;
  set @paper_id = 0;
  insert into tmp_order(paper_id, class_student_id, order_in_town, score)
  select paper_id,class_student_id,mrank,mscore from (
          select if(paper_id=@paper_id,@total:=@total+1, (@total:=1 and @rank:=1 and @paper_id:=paper_id)) as temp,
          paper_id, class_student_id,
          if(score<@scorenow,@rank:=@total,@rank:=@rank) as mrank,
          (@scorenow:=score) as mscore
          from  tmp_subject_score  
          order by paper_id, score desc
  )  as tmp_rank_tbl;
               
end;
//


delimiter ;
call show_mark();

[ 本帖最后由 zyrf2001 于 2009-5-26 10:32 编辑 ]

lrg223

学习

cenalulu

原帖由 zyrf2001 于 2009-5-26 10:21 发表
受cenalulu 的启发，在cenalulu的基础上，我又简化了一下，去掉了对paper_id的循环，不使用游标。速度由0.23s 提高的 0.17s

delimiter //

drop procedure if exists show_mark
//
create procedure sho ...

赞~~~楼主也是牛人啊~~我咋就没想到用and做多个连续操作呢

zyrf2001

2楼和9楼是不是一个人啊？注册名一样，id不同？？！！这就是传说中的马甲吗？怎么搞的？