问题描述
我有一个包含成千上万行的表,我想为一个称为回合"的字段计算第90个百分位数.
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'.
例如,选择舍入值为90%的值.
For example, select the value of round which is at the 90th percentile.
我没有看到在MySQL中执行此操作的直接方法.
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?
谢谢!
推荐答案
首先,假设您有一个带有值列的表.您想获得第95个百分位值的行.换句话说,您正在寻找的值大于所有值的95%.
这是一个简单的答案:
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);
这篇关于使用MySQL计算百分数值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!