📑
MySQLで中央値を取得するSQLを書く
はじめに
MySQLで文字列の長さを求めるときは CHAR_LENGTH() を使います。
文字数がわかったときにそのテーブルのVARCHAR型なカラムの文字数中央値はいくつなんだろうと思ったのですが、
MEDIAN() のような関数はなかったので、どうしようかと思ったときの備忘録です。
この記事では、サンプルテーブルを用意して実際にクエリを動かしながら中央値を求めるクエリを使ってみます。
準備
シンプルな products というidとname(varchar)のテーブルを用意し、そこのデータにクエリを投げることで中央値を求めてみましょう。
サンプルテーブルの作成とデータ投入
CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL
);
適当に日本語のデータを入れます。
INSERT INTO products (name) VALUES
('ペン'),
('ノート'),
('えんぴつ'),
('消しゴム'),
('机'),
('椅子'),
('参考書'),
('定規'),
('コピー用紙'),
('ホワイトボードマーカー'),
('これは本当に存在するのか誰もわからないけれど世界一長い商品名として登録されてしまったとんでもなくふざけた商品サンプルデータですよろしくお願いします1234567890');
select * from products;
+----+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | name |
+----+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | ペン |
| 2 | ノート |
| 3 | えんぴつ |
| 4 | 消しゴム |
| 5 | 机 |
| 6 | 椅子 |
| 7 | 参考書 |
| 8 | 定規 |
| 9 | コピー用紙 |
| 10 | ホワイトボードマーカー |
| 11 | これは本当に存在するのか誰もわからないけれど世界一長い商品名として登録されてしまったとんでもなくふざけた商品サンプルデータですよろしくお願いします1234567890 |
+----+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
11 rows in set (0.003 sec)
各商品の文字数を確認する
CHAR_LENGTH() を使えば文字数がわかります。
mysql> SELECT id, name, CHAR_LENGTH(name) AS len
-> FROM products;
+----+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----+
| id | name | len |
+----+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----+
| 1 | ペン | 2 |
| 2 | ノート | 3 |
| 3 | えんぴつ | 4 |
| 4 | 消しゴム | 4 |
| 5 | 机 | 1 |
| 6 | 椅子 | 2 |
| 7 | 参考書 | 3 |
| 8 | 定規 | 2 |
| 9 | コピー用紙 | 5 |
| 10 | ホワイトボードマーカー | 11 |
| 11 | これは本当に存在するのか誰もわからないけれど世界一長い商品名として登録されてしまったとんでもなくふざけた商品サンプルデータですよろしくお願いします1234567890 | 83 |
+----+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----+
11 rows in set (0.003 sec)
中央値を求める
一旦中央値を確認しておきましょう。
MySQL 8.0から使えるROW_NUMBERを利用するとわかりやすいので活用しました。
全部で11件のデータがあります。文字数を昇順に並べると、中央に位置するのは6番目のレコードです
したがって、今回求めたい中央値は 3 となります。
mysql> select ROW_NUMBER() over (order by len asc) , t.* from (SELECT id, name, CHAR_LENGTH(name) AS len FROM products order by len asc) as t;
+--------------------------------------+----+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----+
| ROW_NUMBER() over (order by len asc) | id | name | len |
+--------------------------------------+----+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----+
| 1 | 5 | 机 | 1 |
| 2 | 1 | ペン | 2 |
| 3 | 6 | 椅子 | 2 |
| 4 | 8 | 定規 | 2 |
| 5 | 2 | ノート | 3 |
| 6 | 7 | 参考書 | 3 |
| 7 | 3 | えんぴつ | 4 |
| 8 | 4 | 消しゴム | 4 |
| 9 | 9 | コピー用紙 | 5 |
| 10 | 10 | ホワイトボードマーカー | 11 |
| 11 | 11 | これは本当に存在するのか誰もわからないけれど世界一長い商品名として登録されてしまったとんでもなくふざけた商品サンプルデータですよろしくお願いします1234567890 | 83 |
+--------------------------------------+----+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----+
11 rows in set (0.002 sec)
方法1: LIMIT + OFFSET を使う
最も直感的で、古いバージョンのMySQLでも使える方法です。
「全体をソートして、真ん中のレコードを直接取得する」というアプローチです。
SELECT
AVG(sub.len) AS median_length
FROM (
SELECT CHAR_LENGTH(name) AS len
FROM products
ORDER BY len
LIMIT 2 - (SELECT COUNT(*) FROM products) % 2 -- 偶数なら2件、奇数なら1件
OFFSET (SELECT (COUNT(*) - 1) / 2 FROM products) -- 中央のレコードまでスキップ
) AS sub;
# サブクエリの値確認
mysql> SELECT (COUNT(*) - 1) / 2 FROM products;
+--------------------+
| (COUNT(*) - 1) / 2 |
+--------------------+
| 5.0000 |
+--------------------+
1 row in set (0.002 sec)
mysql> SELECT (SELECT COUNT(*) FROM products) % 2;
+-------------------------------------+
| (SELECT COUNT(*) FROM products) % 2 |
+-------------------------------------+
| 1 |
+-------------------------------------+
# 値を展開したクエリ
SELECT
AVG(sub.len) AS median_length
FROM (
SELECT CHAR_LENGTH(name) AS len
FROM products
ORDER BY len
LIMIT 1 # = 2 - 1
OFFSET 5
) AS sub;
# つまり5件除外したところから1件というクエリになりました
このクエリは、OFFSETで中央のレコード（または中央の2つのうちの最初のレコード）までスキップし、
LIMIT で1件（奇数時）または2件（偶数時）を取得します。
最後にAVG()で平均を取ることで、件数が偶数の場合にも対応しています。
結果は以下の通りです。
+---------------+
| median_length |
+---------------+
| 3.0 |
+---------------+
方法2: ウィンドウ関数を使う (MySQL 8.0以上)
より柔軟に書く方法として、ウィンドウ関数 ROW_NUMBER() と COUNT(*) OVER () を使います。
OVER()句は、GROUP BYのように行を集約せず、個々のレコードに対して集計や順位付けを行うための強力な機能です。
WITH ordered AS (
SELECT
CHAR_LENGTH(name) AS len,
ROW_NUMBER() OVER (ORDER BY CHAR_LENGTH(name)) AS rn,
COUNT(*) OVER () AS total_count
FROM products
)
SELECT
CASE
WHEN total_count % 2 = 1 THEN
MAX(CASE WHEN rn = (total_count + 1) / 2 THEN len END)
ELSE
AVG(CASE WHEN rn IN (total_count / 2, total_count / 2 + 1) THEN len END)
END AS median_length
FROM ordered;
このクエリでは、WITH句を使って以下の3つの情報を各行に付与しています。
-
len: 文字数 -
rn: 文字数でソートしたときの行番号(ROW_NUMBER()) -
total_count: 全体の行数 (COUNT(*) OVER())
その後のWHERE句で、中央に位置する行番号のレコードのみを抽出しています。
結果は以下の通りです。
+---------------+
| median_length |
+---------------+
| 3.0 |
+---------------+
まとめ
MySQLで中央値を求める2つの方法を紹介しました。
CHAR_LENGTH() で文字数を取得し、行数が少なければ LIMIT + OFFSET でシンプルに、MySQL 8.0 以上なら ウィンドウ関数でスマートにかけました。
実は OVER もこのタイミング知ったのですが、ROW_NUMBERも組み合わせるとすごい便利に感じました。
中央値をまた出したくなる機会があると思うので、そのときにまた使いたいと思います。
Discussion