选择TOP X(或底部)百分比作为MySQL中的数值 [英] Select TOP X (or bottom) percent for numeric values in MySQL

问题描述

我想知道MySQL中是否可以使用任何函数从包含数值的列中选择TOP X(或底部)百分比.

I was wondering if there are any functions that can be used in MySQL to select the TOP X(or bottom) percent from a column containing numeric values.

基本上，我有一个包含价格列表的列，我只想返回价格前十个百分位中的那些字段.有什么建议?

Basically, I have a column containing a list of prices and I only want to return those fields in the top ten percentile of prices. Any suggestions?

推荐答案

更新:更多认识的人

UPDATE: Much more thought-out explanation of the subject from much more knowing person here. Nonetheless, it still seems there's no embedded function in MySQL to calculate percentiles.

尝试:

SELECT *从价格中选择价格> =(SELECT 0.9 * max(price)从价格中选择)

SELECT price FROM prices p1 WHERE
(SELECT count(*) FROM prices p2 WHERE p2.price >= p1.price) <=
     (SELECT 0.1 * count(*) FROM prices)
);

这将给出价格 P1 ，其中 Price 表中具有 price> = P1 的记录数将是价格总数的十分之一. Price 表中的记录. 之后:

This will give price P1 for which number of records in Price table having price >= P1 will be one tenth of total number of records in Price table. After that:

SELECT * FROM prices WHERE price >= (SELECT price FROM prices p1 WHERE
(SELECT count(*) FROM prices p2 WHERE p2.price >= p1.price) <=
     (SELECT 0.1 * count(*) FROM prices)
);

将返回所有所需的记录.

will return all desired records.

注意:我没有检查此查询的性能，我认为使用临时表/变量的解决方案必须更有效.

Note: I didn't examine performance of this query, I think solution with temporary table/variable must be more effective.

这篇关于选择TOP X(或底部)百分比作为MySQL中的数值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！