MySQLで指定したカラムの中央値を抽出する方法

MySQLで指定したカラムの中央値を抽出したいというケースがあった。MariaDBの10.3.3以上だと中央値を求めるMEDIAN()という関数が実装されているらしいが、当方の環境はMySQL8.0.32のため実装されておらず、SQL文で何とかしないといけないっぽい。以下に対応方法をメモ。

テーブル構造

mysql> SHOW COLUMNS FROM `test_table`;
+-------+------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra          |
+-------+------+------+-----+---------+----------------+
| id    | int  | NO   | PRI | NULL    | auto_increment |
| score | int  | NO   |     | NULL    |                |
+-------+------+------+-----+---------+----------------+

データ内容

mysql> SELECT * FROM `test_table` ORDER BY `score` DESC;
+----+-------+
| id | score |
+----+-------+
|  1 |   100 |
|  4 |    80 |
|  5 |    70 |
| 10 |    70 |
|  3 |    60 |
|  7 |    55 |
|  6 |    50 |
|  2 |    40 |
|  8 |    20 |
|  9 |    20 |
+----+-------+

対応方法

SQL文

SET @row_index := -1;
SELECT AVG(subq.score) AS median_value 
FROM 
    (
        SELECT @row_index:=@row_index + 1 AS row_index, score
        FROM test_table
        ORDER BY score
    ) AS subq
WHERE subq.row_index IN (FLOOR(@row_index / 2), CEIL(@row_index / 2));

出力結果

上記SQL文を実行すると以下が出力される筈。全データの行数が偶数になるため、id3の60とid7の50の平均値が出力される。

+--------------+
| median_value |
+--------------+
|      57.5000 |
+--------------+

全データの行数が奇数の場合

以下のように全データの行数が奇数行の場合、

mysql> SELECT * FROM `test_table` ORDER BY `score` DESC;
+----+-------+
| id | score |
+----+-------+
|  1 |   100 |
|  4 |    80 |
|  5 |    70 |
| 10 |    70 |
|  3 |    60 |
|  7 |    55 |
|  6 |    50 |
|  2 |    40 |
| 11 |    30 |
|  8 |    20 |
|  9 |    20 |
+----+-------+

前述のSQL文を実行すると以下が出力される筈。

+--------------+
| median_value |
+--------------+
|      55.0000 |
+--------------+

こちらはid7の55がそのまま出力された。

参考サイト

https://sebhastian.com/mysql-median/