MySQLで中央値（Median）を求める方法【データ集計・分析】

MySQLには中央値（Median）を求めるための関数が用意されていません。MAXやCOUNT、SUMのように簡単に取得することができればいいのですが…。中央値を取るためのロジックを元にSQLで計算してみましょう。

Contents

中央値の意味と求め方

まずはSQLの前に中央値はどのようなものでどうやって求めるかを理解しましょう。

中央値（Median）とは

中央値（Median）とは、データを大きさの順に並べたとき、全体の中央に位置する値のことを指します。
データの数が偶数の場合には中央の2つの値の平均値となります。

データの例

この後のMySQLでの方でも例として使うので今回はusersテーブルにデータが入っている状態とします。
7人分のユーザーの名前（name）と年齢（age）があります。

id	name	age
1	門田正義	18
2	大野さんま	32
3	塩田竜也	52
4	中沢まひる	35
5	中井俊二	12
6	大浦真希	22
7	星鉄洋	58

データを並び替える

今回は年齢（age）の中央値を求めるので年齢順で並び替えます。
下記が年齢で並び替えたデータです。

id	name	age
5	中井俊二	12
1	門田正義	18
6	大浦真希	22
2	大野さんま	32
4	中沢まひる	35
3	塩田竜也	52
7	星鉄洋	58

中央値を求める

このデータ例の場合だとデータ数が7個なので中央は4番目になります。
並び替えた4番目の値（age）は32なので中央は32です。

仮にデータが偶数の場合として今回は一番大きい値を8番目に追加したとすると、中央は4と5番目になります。
4番目が32、5番目が35なので、中央の2つの値の平均を取ると (32 + 35) / 2 で33.5が中央値です。

MySQLで中央値を求める

順番にクエリ実行して求める場合

上記の中央値を求める方法と同じように処理するだけです。

まずは全体の件数を求めましょう。

mysql> SELECT COUNT(*) FROM users;
+----------+
| COUNT(*) |
+----------+
|        7 |
+----------+

mysql> SELECT COUNT(*) FROM users;

+----------+

| COUNT(*) |

+----------+

| 7 |

+----------+

オフセットをずらして

PREPARE SET_STMT FROM 'select age from users order by age limit 1 offset ?';
SELECT FLOOR( COUNT(*) / 2 ) INTO @offset FROM users;
EXECUTE SET_STMT USING @offset;

PREPARE SET_STMT FROM 'select age from users order by age limit 1 offset ?';

SELECT FLOOR( COUNT(*) / 2 ) INTO @offset FROM users;

EXECUTE SET_STMT USING @offset;

偶数だった場合には中央の小さい方を採用するため正確な値とは言えませんが簡単に取れます。

クエリ一発で正確に取得する

参考

SELECT 
    period, 
    AVG(middle_values) AS 'median' 
FROM (
    SELECT t1.sale_price AS 'middle_values', t1.row_num, t1.period, t2.count
    FROM (
        SELECT 
            @last_period:=@period AS 'last_period',
            @period:=DATE_FORMAT(sale_date, '%Y-%m') AS 'period',
            IF (@period<>@last_period, @row:=1, @row:=@row+1) as `row_num`, 
            x.sale_price
          FROM listings AS x, (SELECT @row:=0) AS r
          WHERE 1
            -- where criteria goes here
          ORDER BY DATE_FORMAT(sale_date, '%Y%m'), x.sale_price
        ) AS t1
    LEFT JOIN (  
          SELECT COUNT(*) as 'count', DATE_FORMAT(sale_date, '%Y-%m') AS 'period'
          FROM listings x
          WHERE 1
            -- same where criteria goes here
          GROUP BY DATE_FORMAT(sale_date, '%Y%m')
        ) AS t2
        ON t1.period = t2.period
    ) AS t3
WHERE 
    row_num >= (count/2) 
    AND row_num <= ((count/2) + 1)
GROUP BY t3.period
ORDER BY t3.period;

SELECT

period,

AVG(middle_values) AS 'median'

FROM (

SELECT t1.sale_price AS 'middle_values', t1.row_num, t1.period, t2.count

FROM (

SELECT

@last_period:=@period AS 'last_period',

@period:=DATE_FORMAT(sale_date, '%Y-%m') AS 'period',

IF (@period<>@last_period, @row:=1, @row:=@row+1) as `row_num`,

x.sale_price

FROM listings AS x, (SELECT @row:=0) AS r

WHERE 1

-- where criteria goes here

ORDER BY DATE_FORMAT(sale_date, '%Y%m'), x.sale_price

) AS t1

LEFT JOIN (

SELECT COUNT(*) as 'count', DATE_FORMAT(sale_date, '%Y-%m') AS 'period'

FROM listings x

WHERE 1

-- same where criteria goes here

GROUP BY DATE_FORMAT(sale_date, '%Y%m')

) AS t2

ON t1.period = t2.period

) AS t3

WHERE

row_num >= (count/2)

AND row_num <= ((count/2) + 1)

GROUP BY t3.period

ORDER BY t3.period;

MySQLで中央値（Median）を求める方法【データ集計・分析】

中央値の意味と求め方

中央値（Median）とは

データの例

データを並び替える

中央値を求める

MySQLで中央値を求める

順番にクエリ実行して求める場合

オフセットをずらして

クエリ一発で正確に取得する

この記事を書いた人

関連記事

MySQLでn時間前、n日前、n年後などの日時を指定してデータを取る方法（INTERVAL）

はじめてのRubyプログラミング。さくっと扱えるようにする

WordPressプラグイン「AddQuicktag」でよく使うタグを登録して効率よく記事を書く

CoinExの取引マイニングBOTの作り方-採掘難易度も考慮【毎日CETザクザク】

エンジニアの鳴き声は「にゃーん」！？仕事中に鳴き続けクビになった事例も！【迷惑注意】

CTO（最高技術責任者）の役割とは？求められる能力やなる方法