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


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

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

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

よろしくお願いします。

回答の条件
  • 1人2回まで
  • 13歳以上
  • 登録:2011/02/18 14:16:52
  • 終了:2011/02/25 14:20:08

ベストアンサー

id:chuken_kenkou No.2

chuken_kenkou回答回数722ベストアンサー獲得回数542011/02/18 16:09:48

ポイント35pt

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
;
id:ReoReo7

ありがとうございます。

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

5.1.22-rc

と出ます。

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

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

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

2011/02/18 18:20:53

その他の回答(1件)

id:asuka645 No.1

あすか回答回数856ベストアンサー獲得回数972011/02/18 14:49:34

ポイント35pt

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

以下のようになります。

select key, avg(point), (select count(*) from indexall where count >100 group by icon) / count(*) from table group by key;
id:ReoReo7

ありがとうございます!

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

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

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

2011/02/18 14:58:38
id:chuken_kenkou No.2

chuken_kenkou回答回数722ベストアンサー獲得回数542011/02/18 16:09:48ここでベストアンサー

ポイント35pt

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
;
id:ReoReo7

ありがとうございます。

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

5.1.22-rc

と出ます。

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

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

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

2011/02/18 18:20:53
  • id:ReoReo7
    テーブルサンプルです。

    グループ名, 得点
    A, 10
    B, 0
    C, 0
    D, 30
    A, 0
    A, 10
    C, 20
    X, 0

    として、グループ名はA,B,C,・・・Xまであって、どんなバリエーションがあるかは未知とします(最悪頻出のグループ名を機知として統計を出しても良いですが・・・)。

    出したいデータは、平均点の降順で

    グループ名, 平均点, 0点以外の率
    D, 30, 1
    C, 10, 0.5
    A, 6.6, 0.66
    B, 0, 0
    X, 0, 0

    のうちのトップ3を出したいと思います。

    できればすごく嬉しいので、よろしくお願いします♪
  • id:ReoReo7
    クエリは複数回実行しても良いです。
  • id:taknt
    SELECT グループ,avg(得点) , (全数-ゼロ数)/全数 FROM テーブル1,(select グループ AS グループ2, count(得点) AS 全数 FROM テーブル1 GROUP BY グループ) AS 全数テーブル,(select グループ AS グループ3, count(得点) AS ゼロ数 FROM テーブル1 where 得点 = 0 GROUP BY グループ) AS ゼロ数テーブル where グループ = グループ2 and グループ = グループ3 GROUP BY グループ,全数,ゼロ数 order by avg(得点) desc;

    Accessだと こんな感じでできる。

    あと 上位うんたらは MySQLとやり方が違うので 略
  • id:chuken_kenkou
    >コードは上記を$sqlという変数に入れて、$result = mysql_query($sql)で

    それでいいです。

    提示したコードは、ローカル定義変数の初期化の

    set @rank=0

    と、それ以降のselect文の二つの命令を実行していますので、それぞれを分けてMySQLに投げてください。
  • id:ReoReo7
    ありがとうございます。select文の中にselect文を入れた構造で実現するのですね。
    非常に勉強になります。。。!
  • id:ReoReo7
    (ひとつ前の回答はtakntさん向けのものです)
    >chuken_kenkouさん
    ありがとうございます。
    なるほど、2回mysql_queryすれば良いのですね。
    迷うところでした・・・ありがとうございます!

    あとはこれを応用して所望の動作を実現したいと思います(実際のテーブルのカラムは10を超えるのですが、今の基本がわかれば後は早いはず
    )。
  • id:windofjuly
    うぃんど 2011/02/18 19:03:55
    同点3位が2グループ以上の場合などが考慮されていないように見受けられます
    (同点同率を考慮せずに並び替えて上位3点であればLIMITで切ってしまえば良いですよね)
  • id:chuken_kenkou
    同点がある場合については、windofjulyさんの指摘どおりで、どのように扱うか決めてください。

    単純に平均の上位から3件なら、回答済みのselect文より簡単にできます。

    select
    *
    from(
    select
    grp
    ,avg(tokuten) as avg_tokuten
    ,sum(if(tokuten=0,0,1))/count(*) as zero_igai
    from t1
    group by grp
    ) as x
    order by avg_tokuten desc
    limit 3
    ;

    ただし、MySQLのlimit句にはいろいろ制限があり、相関サブクエリなどで使うと、同等の構文を持つPostgreSQLのようには動いてくれない(「xx毎にn件」のような操作は不可)と記憶しています。

  • id:ReoReo7
    ありがとうございます。

    少し理解の範囲を超えつつありますが、恐らく頂いたコードを試してみれば意味がわかると思います。
    初歩的な質問にこんなに詳しく大勢の方に答えて頂き非常に嬉しいです。
  • id:sayo217sayo
    いつからここ回答欄になったっけか?
    自分よりも優秀な回答と並べられたときの屈辱を味わいたくないという回答者もいるらしいが。
  • id:chuken_kenkou
    ユーザ定義変数での回答が、今回の要件では不要かも知れないので、もう少し意味のある使い方を提示しておきます。

    Oracleでいう分析関数、標準SQLなどでいうウインドウ関数もどきでのランク付けを、MySQLでユーザ定義変数を使って実装。


    rank()相当

    次のようなランク付け

    avg rank
    100 1
    90 2
    90 2
    50 4
    50 5
    50 5
    50 5
    30 8

    dense_rank()相当

    avg rank
    100 1
    90 2
    90 2
    50 3
    50 3
    50 3
    50 3
    30 4

    といったランク付けを行います。

    これを使って、rankを条件に指定できます。

    -- ----------------------------
    -- rank() over()
    -- 同一値は同じrank
    -- rankにギャップを空ける
    -- ----------------------------
    set @rank=0 -- ランクの番号
    ,@seq=0 -- 通番
    ,@avg_tokuten=null -- 最新の平均値を保持
    ;
    select
    *
    from(
    select
    *
    ,@seq:=@seq+1 as seq -- 通番
    ,@rank:=if(@avg_tokuten=avg_tokuten,@rank,@seq) as rank -- 平均値が変われば
    ,@avg_tokuten:=avg_tokuten as avg_ten
    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,grp
    ;
    -- ----------------------------
    -- dense_rank() over()
    -- 同一値は同じrank
    -- rankにギャップを空けない
    -- ----------------------------
    set @rank=0 -- ランクの番号
    ,@avg_tokuten=null -- 最新の平均値を保持
    ;
    select
    *
    from(
    select
    *
    ,@rank:=if(@avg_tokuten=avg_tokuten,@rank,@rank+1) as rank -- 平均値が変われば
    ,@avg_tokuten:=avg_tokuten as avg_ten
    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,grp
    ;
  • id:windofjuly
    うぃんど 2011/02/18 23:49:33
    >大勢の方
    まともに回答しているのは回答2のお一人のみ
     
    回答1は意味不明
    Accessの例も何を参考にしているのやら意味不明
    アラシがまたアカウントを変えてきているけど、これは無視(2/17登録ということかもね)
     
    >理解の範囲
    難しいことを書いたつもりはないのですが、意味不明の書き込みが大半をしめるために解りづらくなっているのかもしれないですね
    整理の意味で補足コメントを残していきます
     
    【1】データ
    基本的に同じなので、ここはMySQL用のみとしています
    (1)元データ
    CREATE TEMPORARY TABLE `得点テーブル`(`グループ名` text,`得点` INT);
    INSERT INTO `得点テーブル` VALUES ('A',10)
    ,('B',0)
    ,('C',0)
    ,('D',30)
    ,('A',0)
    ,('A',10)
    ,('C',20)
    ,('X',0);
    (2)3位に位置するグループAとまったく同じ結果を出したグループEを追加
    INSERT INTO `得点テーブル` VALUES ('E',10)
    ,('E',0)
    ,('E',10);
     
    【2】同点同率を無視して、とにかく3件目が出た時点で以降を無視してしまっても良い場合は下記になります
    データ(1)でまずは結果を出し、データ(2)を追加後にも同様に結果をだしてみると判りますが、
    同点3位のグループAとグループEのどちらかが「3位以内にもかかわらず欠如してしまいます」(先に入れたデータが必ず優先されるというような保証はありませんので、テストではAばかりやEばかりが結果にあらわれたとしてもそれは偶然と捉えておく必要があります)
    (1)MySQL
    SELECT `グループ名`
    , AVG(`得点`) AS `平均点`
    , SUM(CASE WHEN `得点` > 0 THEN 1 ELSE 0 END) / COUNT(*) AS `0点以外の率`
    FROM `得点テーブル`
    GROUP BY `グループ名`
    ORDER BY `平均点` DESC
    LIMIT 3;
    (2)PostgreSQL
    SELECT "グループ名"
    , AVG("得点") AS "平均点"
    , SUM(CASE WHEN "得点" > 0 THEN 1 ELSE 0 END) / COUNT(*) AS "0点以外の率"
    FROM "得点テーブル"
    GROUP BY "グループ名"
    ORDER BY "平均点" DESC
    LIMIT 3;
    (3)Access
    SELECT TOP 3 グループ名
    , AVG(得点) AS 平均点
    , SUM(IIF(得点 > 0, 1, 0)) / COUNT(*) AS 0点以外の率
    FROM 得点テーブル
    GROUP BY グループ名
    ORDER BY AVG(得点) DESC;
     
    【3】同点同率の扱い方について
    (1)サイトデザインの関係などで3件分しか場所が取れない場合
    ・とにもかくにも降順指定で出てきたものを先頭から3件だけ使う
    ・グループの登録日時を用意するなどして、早いほうを優先して出力する
    その他、要望によって対応はさまざま
    (2)融通が利く場合
    この場合には、@RANK+1といった単純な加算ではなくAとEいずれもが3位となるように式を工夫しなければならない
     
    【4】その他
    ウィンドウ関数を持っているPostgreSQLではいとも簡単に出来るけれど、MySQLでは回答2のように@変数を利用したり、サブクエリを組み合わせたりしなければならないので面倒だという事をおっしゃりたかったのではないかと思いますが、この点については今回のご質問終了後に参考として書いておくかもしれません
  • id:sayo217sayo
    コメント欄で他人の投稿を罵倒するとは、いやはや・・・
    そういえば、イルカ賞取得率を自慢にしている回答者がいるらしい。
  • id:ReoReo7
    >chuken_kenkouさん、windofjulyさん

    ありがとうございます。
    コメント読ませて頂きました。

    私の利用しているのはMySQLですが、
    (1)サイトデザインの関係などで3件分しか場所が取れない場合
    ・とにもかくにも降順指定で出てきたものを先頭から3件だけ使う
    特に縦長になってもかまわないことと、同点同率の順序までは特にこだわりがないので、特に同点同率の処理はせずに素直に出力してしまっています。

    今のところこれで問題ありません。
    たくさんの回答感謝致します。

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

トラックバック

  • 人力検索へのフォロー(?) http://q.hatena.ne.jp/1298006210#c196129   どうでもいい話なのでダイアリーのほうに書いておくとします   PostgreSQLでの例 WITH v1 AS ( SELECT &quot;グループ名&quot; , AVG(&quot;
「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

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

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