从 MySQL 中选择第 n 个百分位

2021-12-30 00:00:00 percentile count 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% 的行小于它的行.它根据行距第 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

相关文章