为 MySQL 中的数值选择 TOP X(或底部)百分比

2021-11-20 00:00:00 sql select mysql

我想知道是否有任何函数可以在 MySQL 中用于从包含数值的列中选择前 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:来自更了解的人 此处.尽管如此，MySQL 中似乎仍然没有嵌入式函数来计算百分位数.

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 * 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)
);

这将给出价格 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.

相关文章