MYSQL5.5.8でランキングの値を生成したいのですが

以下のSQLを実行すると、rankの値が1,2,3,4になってしまい

set @a=0;

SELECT TEST1,AVG_INT, @a:=@a+1 as rank FROM (
SELECT TEST1,AVG(TEST2) AS AVG_INT FROM BBB WHERE ID = 4 GROUP BY TEST1
) as AAA ORDER BY AVG_INT DESC;


出来れば、1,2,2,4としたいのですが
どのようなクエリを変えればいいでしょうか?
皆様のお力を、貸して下さい。

データは、以下のようになってます。

|ID| |TEST1| |TEST2|
|4| |2| |5|
|4| |1| |5|
|4| |3| |6|
|4| |8| |2|

回答の条件
  • 1人2回まで
  • 13歳以上
  • 登録:2011/04/07 13:11:43
  • 終了:2011/04/14 13:15:02

回答(3件)

id:asuka645 No.1

あすか回答回数856ベストアンサー獲得回数972011/04/07 13:36:15

ポイント27pt

こんな形でどうでしょうか


SELECT R1.TEST1,AVG(TEST2), 
(SELECT COUNT(R2.TEST2) FROM BBB AS R2 WHERE R2.TEST2 > R1.TEST2) + 1 AS RANK
FROM BBB AS R1
ORDER BY R1.TEST2 DESC;
id:hopefully

ありがとうございます。

これで出来ましたが、以下のレコードを入れると

ID TEST1 TEST2
4 2 5
4 1 5
4 3 6
4 8 2
4 9 2
4 2 1

1、2、2、4、4、6ってなってしまうのですが

こういう場合は、仕方ないんですかね?

出来れば以下のようにしたいのですが・・

1、2、2、3、3、6

可能なんでしょうか?

2011/04/07 15:45:02
id:chuken_kenkou No.2

chuken_kenkou回答回数722ベストアンサー獲得回数542011/04/07 15:49:07

ポイント26pt

MySQLはウインドウ関数をまだ未実装なので、

  1. 自己結合し、大きい件数(あるいは小さい件数)をグループ毎に得て、それをランクの番号にする
  2. ユーザ定義変数で番号を振る

といった方法を取る場合が多いですね。


何度も件数を得るのと、ユーザ定義変数で一旦番号を振ってしまうのと、どちらが操作性がいいとか、性能を出せるとかはケースバイケースなのですが、ユーザ定義変数を使用した例を示します。


-- ----------------------------
-- rank() over()
--  同一値は同じrank
--  rankにギャップを空ける
-- ----------------------------
set @rank=0                 -- ランクの番号
   ,@seq=0                  -- 通番
   ,@avg_int=null           -- 最新の平均値を保持
   ;      
select
  *
 from(
  select
    *
   ,@seq:=@seq+1 as seq -- 通番
   ,@rank:=if(@avg_int=avg_int,@rank,@seq) as rank -- 平均値が変われば
   ,@avg_int:=avg_int as tmp_avg
   from(
    select
      test1
     ,avg(test2) as avg_int
     from bbb
     where id=4
     group by test1
       ) as x
   order by avg_int desc,test1
  ) as xx
 order by rank,test1
;
id:windofjuly No.3

うぃんど回答回数2625ベストアンサー獲得回数11492011/04/07 14:27:04

ポイント27pt

1つ前の状態と比較して、カウント値を使うか、前と同じ値を使うかを選定するという手法

set @a=0, @count=1, @prev=-1;
SELECT TEST1,AVG_INT
    , @a:=IF(AVG_INT<>@prev,@count,@a) as rank
    , @count:=@count+1, @prev:=AVG_INT
FROM (
    SELECT TEST1,AVG(TEST2) AS AVG_INT
    FROM BBB
    WHERE ID = 4
    GROUP BY TEST1
) as AAA
ORDER BY AVG_INT DESC;

余談になりますが1,2,2,3と出力したい場合の変更と比較すると動作を理解しやすいと思います

set @a=0, @prev=-1;
SELECT TEST1,AVG_INT
    , @a:=IF(AVG_INT<>@prev,@a+1,@a) as rank
    , @prev:=AVG_INT
以下同じ
  • id:windofjuly
    うぃんど 2011/04/08 03:09:42
    回答3の前半は今回の質問に応じたもので結果は1、2、2、4、4、6となりますが、
    後半の余談のほうが1、2、2、3、3、4となるものなので、
    比較用に用意しておいた後半のものが役に立つはずなんだけど・・・
     
    3の次が4ではなく、本当に6が来るのだとすれば数えていく法則が見えないので、解説が欲しいです

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません