17587


`select avg(point) from table where (genre毎のpoint上位100位まで) GROUP BY genre;`

みたいな事がしたいのですが、どうすれば良いでしょうか？  
ポイント上位を入れたテーブルを作るくらいしか思いつかなかったので…


CC BY-SA 3.0
2015-10-13T03:16:27.627
17578
2016-04-10T04:19:36.567
525
question
mysql
標準偏差や平均値をグループ毎の上位に関して出したい


最初は `IN` 演算子とサブクエリで処理しようとしたのですが、以下のエラーメッセージが表示されるので、

[code]

    MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery
    
[/code]

ユーザ定義変数(user-defined variable)とサブクエリを使ってみました。

[code]

    SET @num := 0, @genre := '';
    SELECT t.genre, avg(t.point) AS average
    FROM (
      SELECT t2.genre, t2.point,
        @num := IF(@genre = t2.genre, @num + 1, 1) AS rownum,
        @genre := t2.genre
      FROM test2 AS t2
      ORDER BY t2.genre ASC, t2.point DESC
    ) AS t WHERE t.rownum <= 100 GROUP BY t.genre;
    
[/code]


CC BY-SA 3.0
2015-10-13T05:24:32.607
17587
2015-10-13T05:24:32.607
17578
answer


argusさんのユーザ変数を使った方法ですが、

OracleACEのAketiJyuuzouさんとyoku0825さんと  
日本オラクルの木村さんによると  
MySQLのユーザ変数は評価順序が未定義だそうです。  
<http://qiita.com/AketiJyuuzou/items/cced9b70cc714b382d98>

なので、結果が保証されません。


CC BY-SA 3.0
2016-04-04T07:53:59.757
23728
2016-04-04T07:53:59.757
15031
17578
answer


コメントで勘違いしていると指摘してもらったので修正します。  
これでMySQL特有の機能は不要だと思います。

[code]

    CREATE TABLE items (
      name CHAR(30) PRIMARY KEY,
      point INT NOT NULL,
      genre CHAR(30) NOT NULL
    );
    
    INSERT INTO items (name,point,genre) VALUES
      ("a1", 10,"A"),
      ("a2",100,"A"),
      ("b1",1000,"B"),
      ("c1",1,"C"),
      ("c2",3,"C");
    
    SELECT genre, avg(point)
      FROM items I1
     WHERE (SELECT count(*)
             FROM items I2
            WHERE I1.point <= I2.point
              AND I1.genre = I2.genre) <= 100
     GROUP BY genre;
    
[/code]

* * *

**以下は、問題の意味を勘違いしていたときのものです。  
僕と同じ様な勘違いをする人もいるかもしれないので残しておきます。**

質問内容をよく分かってないかもしれませんが、  
サブクエリとかMySQL特有の機能とか使わなくても  
こんな感じじゃダメですか？

[code]

    CREATE TABLE items (
      name char(30) PRIMARY KEY,
      point INT NOT NULL,
      genre char(30) NOT NULL
    );
    
    INSERT INTO items (name,point,genre) VALUES
      ("a1", 10,"A"),
      ("a2",100,"A"),
      ("b1",1000,"B"),
      ("c1",1,"C"),
      ("c2",3,"C");
    
    SELECT avg(point)
      FROM items
     GROUP BY genre
     ORDER BY sum(point) DESC
     LIMIT 2;
    
[/code]

INSERT をたくさん書くのた面倒なので limit 2 して、  
3位だけ除去しました。  
使う場合は、たくさん INSERT して limit 100 とかすればいいと思います。

あとは必用に応じてサロゲートキーとか付ければいいのかと思います。


CC BY-SA 3.0
2016-04-04T08:24:15.967
23730
2016-04-10T04:19:36.567
2016-04-10T04:19:36.567
15007
15007
17578
answer