【MySQL】GROUP BYとAVG()を使ってIDごとの平均値を求め、条件に合う最大平均を表示する方法

MySQLで重複して存在するID（プレイヤーIDなど）ごとにスコアの平均値を計算し、その中から条件を満たすレコードを抽出したい場合があります。このような処理には、平均値を求める AVG() 関数と、GROUP BY 句、さらに条件で絞り込む HAVING 句を組み合わせて使用します。

テーブルを作成する

まず、サンプル用のテーブルを作成しましょう。ここでは、プレイヤーID（PlayerId）とスコア（PlayerScore）を持つテーブルを定義します。

mysql> create table DemoTable
    -> (
    -> PlayerId int,
    -> PlayerScore int
    -> );
Query OK, 0 rows affected (0.55 sec)

データを挿入する

次に、INSERT文を使ってテーブルにレコードを挿入します。同じPlayerIdが複数回登場するようにデータを登録します。

mysql> insert into DemoTable values(1,78);
Query OK, 1 row affected (0.16 sec)
mysql> insert into DemoTable values(2,82);
Query OK, 1 row affected (0.25 sec)
mysql> insert into DemoTable values(1,45);
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable values(3,97);
Query OK, 1 row affected (0.22 sec)
mysql> insert into DemoTable values(2,79);
Query OK, 1 row affected (0.12 sec)

登録したデータを確認する

SELECT文ですべてのレコードを表示してみましょう。

mysql> select *from DemoTable;

実行すると、以下のような結果が出力されます。

+----------+-------------+
| PlayerId | PlayerScore |
+----------+-------------+
|        1 |          78 |
|        2 |          82 |
|        1 |          45 |
|        3 |          97 |
|        2 |          79 |
+----------+-------------+
5 rows in set (0.00 sec)

この時点での各プレイヤーの平均スコアは以下の通りです。

• PlayerId = 1：(78 + 45) ÷ 2 = 61.5
• PlayerId = 2：(82 + 79) ÷ 2 = 80.5
• PlayerId = 3：97 ÷ 1 = 97

平均値が80を超えるIDを抽出するクエリ

ここで本題となるクエリです。GROUP BY でPlayerIdごとにグループ化し、HAVING 句で平均スコアが80より大きいグループだけを絞り込みます。

mysql> select PlayerId from DemoTable
   -> group by PlayerId
   -> having avg(PlayerScore) > 80;

実行結果は以下の通りです。

+----------+
| PlayerId |
+----------+
|        2 |
|        3 |
+----------+
2 rows in set (0.00 sec)

解説

このクエリでは、WHERE 句ではなく HAVING 句を使用している点が重要です。WHERE 句はグループ化される前の個々の行に対して条件を適用しますが、AVG() のような集計関数の結果に対して条件を指定する場合は、GROUP BY の後に評価される HAVING 句を使う必要があります。

なお、平均値そのものも一緒に表示したい場合は、SELECT句に avg(PlayerScore) を追加すると便利です。また、最大の平均値だけを取得したい場合は、MAX() 関数や ORDER BY avg(PlayerScore) DESC LIMIT 1 を組み合わせることで実現できます。