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