MySQLでHAVING句とAVG()を使って重複する要素ごとの平均値を求め、並べ替える方法

MySQLでグループ化したデータの平均値を条件付きで取得するには

GROUP BYでグループ化した結果に対して集計関数（AVGなど）の条件を指定する場合は、WHERE句ではなくHAVING句を使用します。WHERE句はグループ化の前に行単位でフィルタリングするため、AVG()のような集計関数を直接使うことができません。一方、HAVING句はGROUP BYによるグループ化の後に適用されるため、集計結果に対して条件を設定できます。

ここでは、重複して登録された名前ごとのスコア平均を求め、その平均値が9.5未満のレコードを昇順に並べて表示する例を紹介します。

1. サンプルテーブルの作成

まず、テーブルを作成します。

mysql> create table DemoTable1338
    -> (
    -> Name varchar(10),
    -> Score int
    -> );
Query OK, 0 rows affected (1.54 sec)

2. 重複する名前を持つデータを挿入

INSERTコマンドを使ってレコードを登録します。意図的に同じ名前を異なるスコアで複数件挿入しています。

mysql> insert into DemoTable1338 values('Chris',8);
Query OK, 1 row affected (0.80 sec)
mysql> insert into DemoTable1338 values('Bob',4);
Query OK, 1 row affected (0.20 sec)
mysql> insert into DemoTable1338 values('Bob',9);
Query OK, 1 row affected (0.27 sec)
mysql> insert into DemoTable1338 values('Chris',6);
Query OK, 1 row affected (0.27 sec)
mysql> insert into DemoTable1338 values('David',5);
Query OK, 1 row affected (0.23 sec)
mysql> insert into DemoTable1338 values('David',7);
Query OK, 1 row affected (0.40 sec)

3. 登録したレコードを確認する

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

mysql> select * from DemoTable1338;

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

+-------+-------+
| Name  | Score |
+-------+-------+
| Chris |     8 |
| Bob   |     4 |
| Bob   |     9 |
| Chris |     6 |
| David |     5 |
| David |     7 |
+-------+-------+
6 rows in set (0.00 sec)

4. 重複する個々の要素の平均を求めて並べ替えるクエリ

次のクエリでは、Nameでグループ化してAVG(Score)で平均を計算し、HAVING句で「平均が9.5未満」という条件を絞り込み、最後にORDER BYで平均値の昇順に並べ替えています。

mysql> select Name,avg(Score) from DemoTable1338
    -> group by Name
    -> having avg(Score) < 9.5
    -> order by avg(Score);

実行すると、以下の出力が得られます。

+-------+------------+
| Name  | avg(Score) |
+-------+------------+
| David |     6.0000 |
| Bob   |     6.5000 |
| Chris |     7.0000 |
+-------+------------+
3 rows in set (0.00 sec)

ポイントまとめ

• 集計関数の結果に条件を付けたい場合はWHEREではなくHAVING句を使う。
• GROUP BYでグループ化することで、重複している名前ごとに平均値を算出できる。
• ORDER BY avg(Score) を組み合わせれば、平均値の小さい順に結果を並べ替えられる。