MySQLにてグループの平均や最大が知りたい

Question

###前提・実現したいこと
グループごとに存在するスコアがあり、グループごとの最大や直近のスコアの平均を知りたいと考えています。

###該当のソースコード

id,user_id,group,group_num,score
1,1,1,10,85
2,1,1,10,5
3,1,1,10,50
4,2,1,11,93
5,2,1,11,79
6,3,1,12,13
7,4,1,13,32
8,4,1,13,94
9,4,1,13,31
10,5,2,10,5
11,5,2,10,6
12,5,2,10,69
13,6,2,11,87

###意図する結果
最大
group1
85点のフィールドと93点のフィールドと13点のフィールドと94点のフィールドを取得

直近の平均
グループ1の場合、50+79+13+31の各フィールドを取得したり、その平均を求める。idが大きいものが直近になります。

###質問
やはり、クエリを何回もなげて、取得するべきでしょうか。

Comments

[SVC34]

どのカラムでグルーピングするのか、何をもって「直近」と判断するのかよくわからないので明示してください。

[nanamin]

groupカラムでグルーピングします。直近というのは、日付ですね。データが挿入されていくので、idが大きいものが最新になります。あ、間違えました。idをつけておくの忘れました。。。

[SVC34]

groupカラムは2種類しかないですが、groupとgroup_numの2つでグルーピングしたい、もしくは特定のgroupにおいてgroup_numでグルーピングしたいということでしょうか。

[nanamin]

カラムの設計が分かりにくくてすみません。groupはクラス、group_numは出席番号的なイメージです。クラスごとの最大や平均を知りたいということです。

Answer 1

ご希望のことは、下記のような感じでしょうか。

最大

group, user_id 毎の最大

SELECT t1.group, MAX(t1.score) As MAX_score
FROM `t1`
GROUP BY t1.group, t1.user_id;

出力結果
| group | MAX_score |
|-------|-----------|
| 1 | 85 |
| 1 | 93 |
| 1 | 13 |
| 1 | 94 |
| 2 | 69 |
| 2 | 87 |

直近の平均

group, user_id 毎の直近(id最大)の score

    SELECT a.group, a.score
    FROM `t1` a
    INNER JOIN (
        SELECT id, MAX(id) As Max_id
        FROM `t1`
        GROUP BY t1.group, t1.user_id
    ) b ON a.id = b.Max_id

出力結果
| group | score |
|-------|-------|
| 1 | 50 |
| 1 | 79 |
| 1 | 13 |
| 1 | 31 |
| 2 | 69 |
| 2 | 87 |

上記の平均

    SELECT a.group, Avg(a.score) AS Avg_score
    FROM `t1` a
    INNER JOIN (
        SELECT id, MAX(id) As Max_id
        FROM `t1`
        GROUP BY t1.group, t1.user_id
    ) b ON a.id = b.Max_id
    GROUP BY a.group

出力結果
| group | Avg_score |
|-------|-----------|
| 1 | 43.25 |
| 2 | 78 |

サンプルURL

SQL Fiddle

Comments

[nanamin]

なるほど。innter joinというのがあるのですね。参考になりました！
ありがとうございます！