使用MySQL计算百分数值 [英] Calculate Percentile Value using MySQL

问题描述

我有一个包含成千上万行的表，我想为一个称为回合"的字段计算第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计算百分数值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！