SQLでパーセンタイル値を求める

SQLでパーセンタイル値を求めたいことがあり、Calculate Percentile Value using MySQL - Stack Overflowを参考に実現できた。

実例

口座 (accounts) テーブルで残高 (balance) column の95パーセンタイル値を求める。

SELECT balance
FROM (SELECT @row_num := @row_num + 1 AS row_num, balance
      FROM accounts t,
           (SELECT @row_num := 0) counter
      ORDER BY balance) temp
WHERE temp.row_num = ROUND(.95 * @row_num)
;

95パーセンタイル以下の値を持つレコードに絞って平均を求める。

SELECT AVG(balance)
FROM (
      SELECT @row_num := @row_num + 1 AS row_num, balance
      FROM accounts t,
           (SELECT @row_num := 0) counter
      ORDER BY balance
     ) temp
WHERE temp.row_num <= ROUND(.95 * @row_num)
;

解説

一番内側のクエリで、指定のカラムを昇順で並べつつ行番号を割り振る。

SELECT @row_num := @row_num + 1 AS row_num, balance
FROM accounts t,
     (SELECT @row_num := 0) counter
ORDER BY balance

以下のような結果が出力される。

row_num	balance
1	1232
2	122090
3	134243
4	230909
5	390901

↑のクエリで得られるレコードが100件あるとしたら、95番目の値が95パーセンタイル値になる。

この中から95%目（というのも変な表現だが）の行だけを選択するにはWHERE temp.row_num = ROUND(.95 * @row_num)で絞り込めば良い。

95パーセンタイル値以下のレコードに絞るのであればWHERE temp.row_num <= ROUND(.95 * @row_num)。

---

PERCENTILE_DISCみたいな良い感じのウィンドウ関数があればよいのだが、使えない環境もある。

https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_WF_PERCENTILE_DISC.html

環境

• MySQL 5.7