make world

中央値を求める

MySQL

参考はオライリーの SQL Hacks。

実験用テーブルを作成する

% mysql -u littlebuddha -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 48
Server version: 5.0.67 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test_db;
Database changed

mysql> CREATE TABLE IF NOT EXISTS `task` (
  `subject` char(8) NOT NULL,
  `minutes` int(10) unsigned NOT NULL,
  KEY `minutes` (`minutes`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `task` (`subject`, `minutes`) VALUES('Lisa', 1), ('Marge', 2), ('Bart', 3), ('Homer', 4), ('Ralph', 90);
Query OK, 5 rows affected (0.00 sec)

mysql> SELECT * FROM `task`;
+---------+---------+
| subject | minutes |
+---------+---------+
| Lisa    |       1 | 
| Marge   |       2 | 
| Bart    |       3 | 
| Homer   |       4 | 
| Ralph   |      90 | 
+---------+---------+
5 rows in set (0.00 sec)

テンポラリーテーブルを作成する

mysql> CREATE TEMPORARY TABLE `taskI` (
    ->   `posn` int(10) unsigned NOT NULL,
    ->   `subject` char(8) NOT NULL,
    ->   `minutes` int(10) unsigned NOT NULL,
    ->   KEY `minutes` (`minutes`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)

テンポラリーテーブルを作成するのは、

パフォーマンスの問題
中央値を求める条件の問題

がある。
理由は省くので、気になる場合は SQL Hacks を参照する。

中央値を取得する

mysql> SELECT @rownum := 0;
+------------+
| @rownum:=0 |
+------------+
|          0 | 
+------------+
1 row in set (0.00 sec)

mysql> INSERT INTO `taskI`
    -> SELECT @rownum := @rownum + 1, `subject`, `minutes`
    -> FROM `task`
    -> ORDER BY `minutes`;
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM `taskI`;
+------+---------+---------+
| posn | subject | minutes |
+------+---------+---------+
|    1 | Lisa    |       1 | 
|    2 | Marge   |       2 | 
|    3 | Bart    |       3 | 
|    4 | Homer   |       4 | 
|    5 | Ralph   |      90 | 
+------+---------+---------+
5 rows in set (0.00 sec)

mysql> SELECT AVG(`minutes`) FROM `taskI`, (SELECT COUNT(*) AS `n` FROM `task`) AS `t` WHERE `posn` IN (FLOOR((`n`  + 1) / 2), FLOOR(`n` / 2) + 1);
+----------------+
| AVG(`minutes`) |
+----------------+
|         3.0000 | 
+----------------+
1 row in set (0.00 sec)

注意点

テンポラリーテーブルは、MySQL に接続しているセッションのみ有効なため、phpMyAdmin を経由して操作はできない。
ターミナルから接続できないレンタルサーバーなどを利用している場合は、本来のテーブルを作成するか、Perl や Ruby、PHP などのスクリプトを実行するなどして代替できる方法で行う。

参考