mysql percent,使用MySQL计算百分数值

I have a table which contains thousands of rows and I would like to calculate the 90th percentile for one of the fields, called 'round'.

For example, select the value of round which is at the 90th percentile.

I don't see a straightforward way to do this in MySQL.

Can somebody provide some suggestions as to how I may start this sort of calculation?

Thank you!

解决方案

First, lets assume that you have a table with a value column. You want to get the row with 95th percentile value. In other words, you are looking for a value that is bigger than 95 percent of all values.

Here is a simple answer:

SELECT * FROM

(SELECT t.*, @row_num :=@row_num + 1 AS row_num FROM YOUR_TABLE t,

(SELECT @row_num:=0) counter ORDER BY YOUR_VALUE_COLUMN)

temp WHERE temp.row_num = ROUND (.95* @row_num);