MySQLで関数を作成して列の値の平均を求める方法

はじめに

MySQLでは、ストアドファンクションを作成することで、特定の条件に合致する列の値の平均を簡単に取得できます。この記事では、サンプルテーブルを作成し、指定した名前の点数（Marks）の平均を返す関数の作成方法を順を追って解説します。

1. テーブルの作成

まず、以下のコマンドでテーブルを作成します。

mysql> create table DemoTable638 (Name varchar(100),Marks int);
Query OK, 0 rows affected (0.68 sec)

2. レコードの挿入

次に、INSERTコマンドを使ってテーブルにレコードを挿入します。

mysql> insert into DemoTable638 values('John',67);
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable638 values('John',90);
Query OK, 1 row affected (0.16 sec)
mysql> insert into DemoTable638 values('David',99);
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable638 values('John',60);
Query OK, 1 row affected (0.13 sec)

3. レコードの確認

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

mysql> select *from DemoTable638;

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

+-------+-------+
| Name  | Marks |
+-------+-------+
| John  | 67    |
| John  | 90    |
| David | 99    |
| John  | 60    |
+-------+-------+
4 rows in set (0.00 sec)

4. 平均値を返す関数の作成

ここからが本題です。バイナリログが有効な環境で関数を作成できるよう、まず log_bin_trust_function_creators を設定します。その後、DELIMITER を変更して関数を定義します。

mysql> set global log_bin_trust_function_creators=1;
Query OK, 0 rows affected (0.28 sec)
mysql> DELIMITER //
mysql> CREATE FUNCTION getAverageDemo()
    RETURNS INT
    BEGIN DECLARE value INT;
       SELECT AVG(Marks) INTO value from DemoTable638 WHERE Name="John";
       RETURN value;
    END;
    //
Query OK, 0 rows affected (0.35 sec)
mysql> DELIMITER ;

この関数では、AVG()関数を使って「John」の点数の平均を計算し、その結果をINT型として返しています。なお、「log_bin_trust_function_creators=1」の設定は、バイナリロギング有効時にストアドファンクションの作成を許可するために必要となる場合があります。

5. 関数の呼び出し

作成した関数は、SELECT文で簡単に呼び出すことができます。

mysql> select getAverageDemo();

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

+------------------+
| getAverageDemo() |
+------------------+
| 72                |
+------------------+
1 row in set (0.01 sec)

まとめ

Johnの点数（67、90、60）の平均値である72が正しく返されました。このように CREATE FUNCTION を使えば、集計処理を関数として切り出し、いつでも再利用できるようになります。WHERE句の条件を引数化すれば、より汎用的な関数に拡張することも可能です。