从MySQL中选择第n个百分位数 [英] Select nth percentile from MySQL

问题描述

我有一个简单的数据表，我想从查询中选择大约第40个百分位数的行。

I have a simple table of data, and I'd like to select the row that's at about the 40th percentile from the query.

我可以这样做现在通过首先查询以找到行数，然后运行排序并选择第n行的另一个查询：

I can do this right now by first querying to find the number of rows and then running another query that sorts and selects the nth row:

select count(*) as `total` from mydata;

可能会返回93，93 * 0.4 = 37

which may return something like 93, 93*0.4 = 37

select * from mydata order by `field` asc limit 37,1;

我可以将这两个查询合并到单个查询中吗？

Can I combine these two queries into a single query?

推荐答案

这将给你大约第40个百分点，它返回40％的行小于它的行。

This will give you approximately the 40th percentile, it returns the row where 40% of rows are less than it. It sorts rows by how far they are from the 40th percentile, since no row may fall exactly on the 40th percentile.

SELECT m1.field, m1.otherfield, count(m2.field) 
  FROM mydata m1 INNER JOIN mydata m2 ON m2.field<m1.field
GROUP BY 
   m1.field,m1.otherfield
ORDER BY 
   ABS(0.4-(count(m2.field)/(select count(*) from mydata)))
LIMIT 1

这篇关于从MySQL中选择第n个百分位数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！