人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

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|

●質問者: hopefully
●カテゴリ:コンピュータ ウェブ制作
✍キーワード:AAA AS AVG GROUP SELECT
○ 状態 :終了
└ 回答数 : 3/3件

▽最新の回答へ

1 ● あすか
●27ポイント

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


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 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

可能なんでしょうか?


2 ● chuken_kenkou
●26ポイント

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
;

3 ● うぃんど
●27ポイント

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
以下同じ
関連質問


●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ