为 MySQL 中的数值选择 TOP X（或底部）百分比
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4741239/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select TOP X (or bottom) percent for numeric values in MySQL
提问by Spencer
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.
我想知道是否有任何函数可以在 MySQL 中使用来从包含数值的列中选择 TOP X（或底部）百分比。
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?
基本上，我有一列包含价格列表，我只想返回价格前十个百分位数中的那些字段。有什么建议？
采纳答案by Victor Sorokin
UPDATE: Much more thought-out explanation of the subject from much more knowing person here. Nonetheless, it still seems there's no embeddedfunction in MySQL to calculate percentiles.
更新：这里有更多了解的人对这个主题进行了更深思熟虑的解释。尽管如此，MySQL 中似乎仍然没有计算百分位数的嵌入式函数。
Try:
尝试：
SELECT * FROM prices WHERE price >= (SELECT 0.9 * max(price) FROM prices)
SELECT * FROM价格 WHERE price >= (SELECT 0.9 * max(price) FROM价格)
SELECT price FROM prices p1 WHERE
(SELECT count(*) FROM prices p2 WHERE p2.price >= p1.price) <=
(SELECT 0.1 * count(*) FROM prices)
);
This will give price P1for which number of records in Pricetable having price >= P1will be one tenth of total number of records in Pricetable. After that:
这将给出价格P1，其中Price表中具有price >= P1的记录数将是Price表中记录总数的十分之一。在那之后：
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.
注意：我没有检查这个查询的性能，我认为使用临时表/变量的解决方案必须更有效。
回答by jasonmclose
just as an FYI (i know this question is a few years old), this can be done other, cleaner ways as well.
就像仅供参考（我知道这个问题已经有几年了），这也可以通过其他更简洁的方式来完成。
SELECT * FROM product_table WHERE price >= (SELECT price FROM product_table
ORDER BY price DESC LIMIT 1 OFFSET (SELECT 0.1 * COUNT(*) FROM product_table));
i ran a similar query over a very large database, and it ran very quickly.
我在一个非常大的数据库上运行了一个类似的查询，它运行得非常快。
回答by RichardTheKiwi
EDIT - new answer
编辑 - 新答案
Answered in Convert SQL Server query to MySQL
Select *
from
(
SELECT tbl.*, @counter := @counter +1 counter
FROM (select @counter:=0) initvar, tbl
ORDER BY ordcolumn
) X
where counter <= (50/100 * @counter);
ORDER BY ordcolumn
OLD ANSWER
旧答案
For MySQL, you could calculate the batch size required and then LIMIT to that number of records
对于 MySQL，您可以计算所需的批量大小，然后限制到该记录数
SELECT @rows := ROUND(COUNT(*) * 10/100) FROM table;
PREPARE STMT FROM ‘SELECT * FROM tbl ORDER BY price LIMIT ?';
EXECUTE STMT USING @rows;
For a bottom percent, just order in reverse
对于底部百分比，只需反向排序
SELECT @rows := ROUND(COUNT(*) * 10/100) FROM table;
PREPARE STMT FROM ‘SELECT * FROM tbl ORDER BY price DESC LIMIT ?';
EXECUTE STMT USING @rows;
Oops, maybe the DESC belongs in the first query, but you get the meaning.
糟糕，也许 DESC 属于第一个查询，但您明白了含义。
NoteFor SQL Server, the TOP N PERCENT clause certainly helps
注意对于 SQL Server，TOP N PERCENT 子句肯定有帮助
select top 10 PERCENT *
FROM TBL
ORDER BY price
