🧪
SQLで組み込み関数を使用しないで中央値を簡単に算出する方法
始めに
最近諸事情でMEDIAN関数といったSQLの便利な集計関数を使用できない環境で中央値を算出する機会があったので、その際に作成したSQLについてメモ書き程度に共有します。
-
動作検証済み環境
- Oracle
- SQLServer
- MySQL
この記事では検証用にMySQLを使用しています。
検証用テーブル
検証用に以下の簡単なテーブルを使用します。
なお、既に検証用のテーブルを用意している方はそちらを使用していただいて構いません。
+------+------+---------+-------+
| ID | NAME | SUBJECT | SCORE |
+------+------+---------+-------+
| 1 | John | English | 60 |
| 2 | John | Math | 90 |
| 3 | John | History | 50 |
| 4 | Mary | English | 80 |
| 5 | Mary | Math | 60 |
| 6 | Mary | History | 70 |
| 7 | Bob | English | 50 |
| 8 | Bob | Math | 60 |
| 9 | Bob | History | 40 |
| 10 | Tom | English | 85 |
| 11 | Tom | Math | 65 |
| 12 | Tom | History | 85 |
+------+------+---------+-------+
この検証用のテーブルについて補足しておくと、某学校の試験を受けた生徒名と科目ごとに点数が格納されているテーブルになります。
中央値を算出するSQL
そして、結論から言うと以下のSQLで試験科目ごとの点数の中央値を算出することができます。
SELECT
WORK.SUBJECT,
AVG(WORK.SCORE) AS MEDIAN
FROM
(
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY SUBJECT
ORDER BY
SCORE
) AS ROWNUM,
COUNT(1) OVER(PARTITION BY SUBJECT) AS TOTAL_SUBJECT
FROM
EXAM_SCORE
) WORK
WHERE
WORK.ROWNUM BETWEEN WORK.TOTAL_SUBJECT * 1.0 / 2
AND WORK.TOTAL_SUBJECT * 1.0 / 2 + 1
GROUP BY
WORK.SUBJECT;
上記のSQLを実行すると以下の結果を得られます。
+---------+---------+
| SUBJECT | MEDIAN |
+---------+---------+
| English | 70.0000 |
| History | 60.0000 |
| Math | 62.5000 |
+---------+---------+
念のためGoogle SheetsのMEDIAN関数を使用して上記の算出結果を検証してみました。
完璧ですね。
処理の解説
一見すると難しそうなSQLになりましたが、やっていることはとても単純です。
中間テーブルの作成
まず、上記SQLにある副問合せWORKでなにをしているのかというと、以下の中間テーブルを作成しています。
+------+------+---------+-------+--------+---------------+
| ID | NAME | SUBJECT | SCORE | ROWNUM | TOTAL_SUBJECT |
+------+------+---------+-------+--------+---------------+
| 7 | Bob | English | 50 | 1 | 4 |
| 1 | John | English | 60 | 2 | 4 |
| 4 | Mary | English | 80 | 3 | 4 |
| 10 | Tom | English | 85 | 4 | 4 |
| 9 | Bob | History | 40 | 1 | 4 |
| 3 | John | History | 50 | 2 | 4 |
| 6 | Mary | History | 70 | 3 | 4 |
| 12 | Tom | History | 85 | 4 | 4 |
| 5 | Mary | Math | 60 | 1 | 4 |
| 8 | Bob | Math | 60 | 2 | 4 |
| 11 | Tom | Math | 65 | 3 | 4 |
| 2 | John | Math | 90 | 4 | 4 |
+------+------+---------+-------+--------+---------------+
具体的には以下のような処理の構成になっています。
- 科目をグループ化し、点数の昇順で科目ごとの行番号（ROWNUM）を割り当てる。
- ROW_NUMBER() OVER (ARTITION BY SUBJECT ORDER BY SCORE) AS ROWNUM
- 科目をグループ化し科目の総数を求める。
- COUNT(1) OVER(PARTITION BY SUBJECT) AS TOTAL_SUBJECT
中央値の篩い分け
ここまでの工程で算出した値を以下の式に当てはめることで中央値を篩い分けることができます。
WORK.ROWNUM BETWEEN WORK.TOTAL_SUBJECT * 1.0 / 2 AND WORK.TOTAL_SUBJECT * 1.0 / 2 + 1
つまり、既に科目と点数の昇順に並び替えられた中間テーブルから、科目ごとの最も中央に近い値を取得しています。
後処理
また、中央値を求める際にデータ数が偶数の場合には中央に最も近い2つの値の平均値が中央値となります。
そのため、後処理として科目をグループ化した上で以下の処理を行っています。
AVG(WORK.SCORE) AS MEDIAN
Discussion