「平均購入額は5,000円を超えているのに、実感とずれている」。このずれは、一部の高額注文が平均を押し上げることで起こります。データの真ん中を知りたいときは、中央値（メディアン）を見るのが確実です。ただしMySQLには、中央値を一発で返すMEDIAN関数がありません。この記事では、ウィンドウ関数のROW_NUMBERとCOUNTで中央値を出す書き方を、件数が奇数・偶数の両方で確かめます。さらにCUME_DIST・PERCENT_RANK・NTILEを使い、上位10%の境目や4分位でのグループ分けまで行います。
このシナリオで考える
ネットショップの注文テーブルを例にします。東京店と大阪店の注文金額を記録したシンプルな構成です。大阪店には30,000円の大口注文が1件だけ混ざっています。amount列はNULLを許す定義にしました（落とし穴の節で使います）。
CREATE TABLE orders (
id INT PRIMARY KEY AUTO_INCREMENT,
store VARCHAR(10) NOT NULL,
amount INT
);
INSERT INTO orders (store, amount) VALUES
('tokyo', 1200), ('tokyo', 1500), ('tokyo', 1800), ('tokyo', 2500),
('tokyo', 3000), ('tokyo', 4200), ('tokyo', 9800),
('osaka', 800), ('osaka', 1600), ('osaka', 2000), ('osaka', 2400),
('osaka', 5000), ('osaka', 30000);まずは比較のために、店舗ごとの平均を見てみましょう。
SELECT store, COUNT(*) AS cnt, ROUND(AVG(amount)) AS avg_amount
FROM orders
GROUP BY store
ORDER BY store;+-------+-----+------------+
| store | cnt | avg_amount |
+-------+-----+------------+
| osaka | 6 | 6967 |
| tokyo | 7 | 3429 |
+-------+-----+------------+大阪店の平均は6,967円です。ところが6件中4件は2,400円以下で、平均は大口注文1件に引っ張られています。この「実感とのずれ」を、中央値で確かめていきます。
ROW_NUMBERとCOUNTで中央値を求める
中央値は、値を小さい順に並べたときにちょうど真ん中に来る値です。件数が奇数なら真ん中の1件、偶数なら真ん中2件の平均になります。
| 件数 | 真ん中の位置 | 中央値 |
|---|---|---|
| 7件（奇数） | 4番目 | 4番目の値 |
| 6件（偶数） | 3番目と4番目 | 2つの値の平均 |
SQLでは「何番目か」をROW_NUMBER（行番号）で、「全部で何件か」をCOUNT(*) OVER ()で各行に付けます。そのうえで真ん中の行だけを残し、AVGで平均を取ります。
WITH ranked AS (
SELECT
amount,
ROW_NUMBER() OVER (ORDER BY amount) AS rn,
COUNT(*) OVER () AS cnt
FROM orders
WHERE amount IS NOT NULL
)
SELECT AVG(amount) AS median
FROM ranked
WHERE rn IN (FLOOR((cnt + 1) / 2), CEIL((cnt + 1) / 2));+-----------+
| median |
+-----------+
| 2400.0000 |
+-----------+ポイントはFLOOR((cnt + 1) / 2)とCEIL((cnt + 1) / 2)の組み合わせです。全体の13件なら(13+1)/2=7なので、どちらも7になり7番目の1行だけが残ります。6件なら3.5となり、3番目と4番目の2行が残る仕組みです。奇数と偶数で条件を書き分ける必要はありません。
店舗ごとの中央値（PARTITION BY）
グループごとに出したいときは、ウィンドウにPARTITION BYを加えます。ROW_NUMBERとCOUNTの両方を、同じ単位で区切るのがコツです。
WITH ranked AS (
SELECT
store,
amount,
ROW_NUMBER() OVER (PARTITION BY store ORDER BY amount) AS rn,
COUNT(*) OVER (PARTITION BY store) AS cnt
FROM orders
WHERE amount IS NOT NULL
)
SELECT store, AVG(amount) AS median
FROM ranked
WHERE rn IN (FLOOR((cnt + 1) / 2), CEIL((cnt + 1) / 2))
GROUP BY store
ORDER BY store;+-------+-----------+
| store | median |
+-------+-----------+
| osaka | 2200.0000 |
| tokyo | 2500.0000 |
+-------+-----------+平均と並べると、違いがはっきりします。
| 店舗 | 平均 | 中央値 |
|---|---|---|
| 大阪店（6件） | 6,967円 | 2,200円 |
| 東京店（7件） | 3,429円 | 2,500円 |
平均では大阪店が東京店の約2倍です。一方、中央値で見ると普段の注文額はむしろ東京店の方が高いと分かります。中央値は外れ値に強い指標です。平均と一緒に出しておくと、判断を誤りにくくなります。
CUME_DISTとPERCENT_RANKでパーセンタイルを求める
パーセンタイルは「下から何%の位置にある値か」を表す指標です。中央値は50パーセンタイルにあたります。MySQL 8.0には、各行の相対的な位置を0〜1で返すウィンドウ関数が2つあります。
| 関数 | 計算式 | 範囲 | 意味 |
|---|---|---|---|
| PERCENT_RANK() | (順位 − 1) ÷ (行数 − 1) | 0〜1 | 自分より小さい値の割合（最大値を除いて数える） |
| CUME_DIST() | 自分以下の行数 ÷ 行数 | 0より大きく1以下 | 自分以下の値の割合（累積分布） |
SELECT
amount,
ROUND(PERCENT_RANK() OVER (ORDER BY amount), 3) AS pct_rank,
ROUND(CUME_DIST() OVER (ORDER BY amount), 3) AS cume_dist
FROM orders
ORDER BY amount;+--------+----------+-----------+
| amount | pct_rank | cume_dist |
+--------+----------+-----------+
| 800 | 0 | 0.077 |
| 1200 | 0.083 | 0.154 |
| 1500 | 0.167 | 0.231 |
| 1600 | 0.25 | 0.308 |
| 1800 | 0.333 | 0.385 |
| 2000 | 0.417 | 0.462 |
| 2400 | 0.5 | 0.538 |
| 2500 | 0.583 | 0.615 |
| 3000 | 0.667 | 0.692 |
| 4200 | 0.75 | 0.769 |
| 5000 | 0.833 | 0.846 |
| 9800 | 0.917 | 0.923 |
| 30000 | 1 | 1 |
+--------+----------+-----------+最小値のPERCENT_RANKは必ず0で、最大値はどちらの関数でも1になります。同じ値が複数あるときは、同じ順位として同じ数値が付きます。
「上位10%の境目」、つまり90パーセンタイルを求めるには、CUME_DISTが0.9以上になる最初の値を取ります。
WITH dist AS (
SELECT amount, CUME_DIST() OVER (ORDER BY amount) AS cd
FROM orders
WHERE amount IS NOT NULL
)
SELECT MIN(amount) AS p90
FROM dist
WHERE cd >= 0.9;+------+
| p90 |
+------+
| 9800 |
+------+13件のうち12件目でCUME_DISTが0.923となり、初めて0.9を超えます。そのため90パーセンタイルは9,800円です。0.9の部分を0.75や0.95に変えれば、ほかのパーセンタイルも同じ形で出せます。
NTILEで4分位にグループ分けする
NTILE(N)は、並べた行をN個のグループにほぼ同じ件数ずつ分け、グループ番号（1〜N）を返す関数です。NTILE(4)なら4分位、NTILE(10)なら10分位（デシル）になります。顧客を購入額で4段階に分けるような場面で役立ちます。
WITH q AS (
SELECT amount, NTILE(4) OVER (ORDER BY amount) AS quartile
FROM orders
WHERE amount IS NOT NULL
)
SELECT
quartile,
COUNT(*) AS cnt,
MIN(amount) AS min_amount,
MAX(amount) AS max_amount
FROM q
GROUP BY quartile
ORDER BY quartile;+----------+-----+------------+------------+
| quartile | cnt | min_amount | max_amount |
+----------+-----+------------+------------+
| 1 | 4 | 800 | 1600 |
| 2 | 3 | 1800 | 2400 |
| 3 | 3 | 2500 | 4200 |
| 4 | 3 | 5000 | 30000 |
+----------+-----+------------+------------+13件は4で割り切れません。余りの1件は先頭のグループに回るため、1グループ目だけが4件になります。NTILEは、あくまで件数で機械的に分ける関数です。同じ金額の行が、境目で別々のグループに入ることもあります。
用途に合わせて関数を選ぶと迷いません。
| やりたいこと | 使う関数 |
|---|---|
| 中央値を1つの値で出す | ROW_NUMBER + COUNT(*) OVER () |
| 「上位○%の境目」の値を出す | CUME_DIST |
| 各行が全体のどのあたりにあるかを見る | PERCENT_RANK / CUME_DIST |
| 行をN段階のグループに分ける | NTILE |
よくある落とし穴
- NULLを除き忘れる：ROW_NUMBERや
COUNT(*) OVER ()は、NULLの行も数えます。昇順ではNULLが先頭に並ぶため、真ん中の位置がずれてしまいます。AVGはNULLを無視するので、同じ感覚で書くと間違えやすい部分です。WHERE amount IS NOT NULLを必ず入れましょう。 - 偶数件で1行だけ取る：
rn = cnt / 2のように1行だけ取ると、偶数件のときに正しい中央値になりません。CUME_DISTで0.5以上の最初の値を取る方法も、偶数件では真ん中2件の小さい方を返します。 - RANKで番号を振る：RANKは同じ値に同じ順位を付け、次の順位を飛ばします。値が重なると「真ん中の番号」の行が存在しない場合があります。位置の計算にはROW_NUMBERを使ってください。
- 定義の違いで答えが変わる：パーセンタイルには複数の計算方法があります。他のツールと数字を突き合わせるときは、どの定義で計算したかを確認しましょう。
- 大きいテーブルでは重くなる：どの方法も、対象の行をすべて並べ替えます。期間などで絞り込んでから計算するのが基本です。頻繁に見る指標なら、日次バッチで結果を別テーブルに保存するのも有効でしょう。
定義の違いの例として、PostgreSQLの専用関数PERCENTILE_CONTを見てみます。PERCENTILE_CONTは前後の値を線形補間（値と値の間を比例配分）して答えを出します。
-- PostgreSQL の場合
SELECT
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS median,
PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY amount) AS p90
FROM orders; median | p90
--------+------
2400 | 8840中央値はMySQLと同じ2,400円です。ところが90パーセンタイルは、11件目の5,000円と12件目の9,800円の間を補間した8,840円になります。MySQLのCUME_DIST方式（9,800円）とは一致しません。なお、MariaDB（10.3.3以降）にはMEDIAN関数とPERCENTILE_CONTが用意されています。
まとめ
MySQLには中央値の専用関数がありません。それでもROW_NUMBERとCOUNT(*) OVER ()を組み合わせれば、奇数・偶数を書き分けずに中央値を出せます。上位○%の境目にはCUME_DIST、N段階のグループ分けにはNTILEが便利です。NULLの除外と、パーセンタイルの定義の違いには注意してください。平均と中央値を並べて見る習慣が、外れ値に振り回されない集計への近道になります。
参考リンク
- MySQL 8.0 リファレンス: Window Function Descriptions
- MySQL 8.0 リファレンス: Window Function Concepts and Syntax
- PostgreSQL ドキュメント: Aggregate Functions（PERCENTILE_CONT）
- SQLウィンドウ関数入門（ROW_NUMBER / RANK / DENSE_RANK）
- グループごとの最新1件を取る3つのSQL
アイキャッチ画像: Photo by KOBU Agency on Unsplash
