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

MySQLのクエリについて質問です。

コメント欄に示すような、グループ名と点数の一覧が格納されたテーブルがあります。

これからグループごとの平均点と、0点以外の点を取る率を出し、ベスト3までを表示させたいのですが、クエリがわからないので作ってください。

文末には
GROUP BY `グループ名` ORDER BY AVG(??) DESC LIMIT 3
みたいなことをすれば出せそうな気がするのですが、わかりません。

よろしくお願いします。


●質問者: ReoReo7
●カテゴリ:コンピュータ ウェブ制作
✍キーワード:AVG GROUP MySQL クエリ グループ
○ 状態 :終了
└ 回答数 : 2/2件

▽最新の回答へ

1 ● あすか
●35ポイント

グループ名のカラムを key、特典のカラムを point、テーブル名を table とします。

以下のようになります。

select key, avg(point), (select count(*) from indexall where count >100 group by icon) / count(*) from table group by key;
◎質問者からの返答

ありがとうございます!

得点の降順とトップ3までの限定もできれば実現して頂ければ嬉しいです。

それとカラム名は日本語でも大丈夫です♪

また、group by iconのiconとは何でしょうか?


2 ● chuken_kenkou
●35ポイント ベストアンサー

MySQLのバージョンは、何でしょうか?

MySQL 4.1、5.0、5.1といったレベルまでは、最低限で提示するようにしてください。

大きな機能追加、一部の仕様変更があります。

MySQLでランク付けといった方法としては、

  1. 自己結合で小さいもの(あるいは大きいもの)の行数を得る
  2. ユーザ定義変数(名前が、@で始まる変数)を使う

といった方法があります。

ここでは、ユーザ定義変数を使った例を示します。


set @rank=0;
select
 *
 from(
 select
 *
 ,@rank:=@rank+1 as rank
 from(
 select
 grp
 ,avg(tokuten) as avg_tokuten
 ,sum(if(tokuten=0,0,1))/count(*) as zero_igai
 from t1
 group by grp
 order by avg_tokuten desc -- MySQLでは同じ階層のクエリの別名を書ける
 ) as x
 order by rank,grp
 ) as xx
 where rank<=3
 order by rank
;

平均点の大きい順に、@rankという変数を使って、番号を振っています。この番号にrankという別名を付けることで、その外側のクエリで条件指定に使えます。これにより、上位n番目までということを実現しています。

また、平均点などの小数点以下の桁数を変えたいといった場合は、cast関数でdec(4,2)などに変換する方法があります。

-- キャスト版
set @rank=0;
select
 grp
 ,cast(avg_tokuten as dec(4,2)) as avg_tokuten
 ,cast(zero_igai as dec(4,2)) as zero_igai
 ,rank
 from(
 select
 *
 ,@rank:=@rank+1 as rank
 from(
 select
 grp
 ,avg(tokuten) as avg_tokuten
 ,sum(if(tokuten=0,0,1))/count(*) as zero_igai
 from t1
 group by grp
 order by avg_tokuten desc -- MySQLでは同じ階層のクエリの別名を書ける
 ) as x
 order by rank,grp
 ) as xx
 where rank<=3
 order by rank
;
◎質問者からの返答

ありがとうございます。

ひとまずですが、MySQLのバージョンはselect version()により

5.1.22-rc

と出ます。

また、php5から動かしています。

コードは上記を$sqlという変数に入れて、$result = mysql_query($sql)でよろしいのでしょうか。

追記:上記のコードをphpMyAdminにてSQL生成にて実行したところ、通常版、キャスト版共に非常に良好に(所望の動作で)動作しました。ありがとうございます。あとはこれをphpに実装すれば完成です。

関連質問


●質問をもっと探す●



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