MySQL で中央値を計算する

1. MySQL で中央値を計算する最も正確な方法
2. UDF（ユーザ定義関数）を使って MySQL に機能を追加する

このチュートリアルでは、MySQL で一連の数値の中央値を効率的に計算する方法をいくつか紹介します。

MySQL には、一連の数値の統計的な中央値を計算する関数が組み込まれていません。そのため、中央値を計算する必要がある場合は、カスタムコードを使用して計算する必要があります。

中央値は、分析している一連の数字の中間の要素です。この要素の検出は、大きなサンプルを使用して効率的に行わないと、処理に時間がかかることがあります。また、状況によっては、このプロセスがトリッキーになることもあります。例えば、アルゴリズムによっては、奇数セットの数値では動作するが、偶数セットでは動作しないものもありますし、その逆もあります。

MySQL で中央値を計算する最も正確な方法

テーブル dataset に float 型のカラム MyNumber があるとしましょう。すべてのケースで動作するアルゴリズムを用いて中央値を計算する最も正確な方法は、以下のスクリプトを使用することです。

SELECT AVG(mid_vals) AS 'median' FROM (
 SELECT tab1.MyNumber AS 'mid_vals' FROM
  (
   SELECT @row:=@row+1 AS 'row', a.MyNumber
   FROM dataset AS a, (SELECT @row:=0) AS r
   ORDER BY a.MyNumber
  ) AS tab1,
  (
   SELECT COUNT(*) as 'count'
   FROM dataset x
  ) AS tab2
  WHERE tab1.row >= tab2.count/2 and tab1.row <= ((tab2.count/2) +1)) AS tab3;

テーブルの行数が多い場合、列 MyNumber にインデックスが必要になります。また、そのインデックスがフィルタリングやソートに使用されていることを確認して、ステートメントが正しく動作するようにしてください。

UDF（ユーザ定義関数）を使って MySQL に機能を追加する

数学や統計関数は非常に一般的なもので、中央値のような関数を使用する必要がある場合は、車輪を再発明するべきではありません。そのため、十分にテストされたユーザ定義関数を使用することができます。

このサイトでは、MySQL に統計機能を追加するために無料で利用できる UDF がたくさん紹介されています。これらの UDF の一つが median 関数です。この関数には入力パラメータと出力パラメータが 1つずつあり、どちらも REAL 型です。入力パラメータは中央値を計算するための数値の集合を含む列であり、出力パラメータは計算された中央値です。

ソースコードをダウンロードしたら、このコマンドを用いて UDF を追加します。

CREATE AGGREGATE FUNCTION median RETURNS REAL SONAME 'udf_median.so';

その後、次のように SELECT 文内で関数を使用することができます。

SELECT MEDIAN(MyNumber) FROM dataset